Postgre
From DreamsteepWiki
buffer in open layers
TEST TO SEE IF TSEARCH IS INSTALLED
SELECT to_tsvector('english', 'foo');
MAKE A SERVER OPEN TO A CLIENT
ADD A LINE IN pg_hba.conf
host all all 192.168.0.10/32 md5
LINKS
http://postgis.refractions.net/pipermail/postgis-users/
SHOW FGS LIBRARIES
cd /opt/fgs . setenv.sh fgs version
SHOW LOADED PHP LIBRARIES
<?php
$foo= get_loaded_extensions();
$size = count($foo);
for ($a=0;$a<$size;$a++)
{
print $foo[$a];
print "\n";
}
?>
Create a table in a schema
CREATE SCHEMA hollywood;
CREATE TABLE hollywood.films (title text, release date, awards text[]);
CREATE VIEW hollywood.winners AS
SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;
################################## ##INTERACTING WITH TEH DATABASE ## ################################## #CREATE A DATABASE log in as postuser (su - postgres) #### createdb sample ################ INTERACTIVE CLIENT psql sample ################ CREATE TABLE cartoons(id serial,cartoon varchar(30)); ################ CREATE TABLE characters(id int4,character varchar(15)); ################ INSERT INTO characters(id,character) VALUES(1,'Shaggy'); INSERT INTO characters(id,character) VALUES(1,'Daphne'); ################ CREATE USER cartoonfan PASSWORD 'secretword'; GRANT SELECT,INSERT,UPDATE,DELETE on cartoons to cartoonfan; GRANT SELECT,INSERT,UPDATE,DELETE on characters to cartoonfan; ################ pg_dump sample /keith/foodump.txt ################ ################ ##$ ##$
HELP ON SPECIFIC COMMANDS
\h SELECT
SHOW TABLES IN PSQL
/d
LIST USERS
LIST DATABASES
\l
Most commonly used functions and operators
Measurement functions return in same units geometry SRID except
for the *sphere and *spheroid versions which return in meters
Denotes a new item in this version 1
Denotes automatically uses spatial indexes 2
Denotes enhanced in this version 3
Geometry Types - WKT Representation
POINT(0 0)
LINESTRING(0 0,1 1,1 2)
POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))
MULTIPOINT(0 0,1 2)
MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))
MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ..)
GEOMETRYCOLLECTION(POINT(2 3),LINESTRING((2 3,3 4)))
BBox and Geometry Operators
A &< B (A overlaps or is to the left of B) 2
A &> B (A overlaps or is to the right of B) 2
A << B (A is strictly to the left of B) 2
A >> B (A is strictly to the right of B)2
A &<| B (A overlaps B or is below B)2
A |&> B (A overlaps or is above B)2
A <<| B (A strictly below B)2
A |>> B (A strictly above B)2
A = B (A bbox same as B bbox)
A @ B (A completely contained by B)2
A ~ B (A completely contains B)2
A && B (A and B bboxes intersect)2
A ~= B - true if A and B geometries are geometrically equal2
Common Use SFSQL Examples
--Create a geometry column named the_geom in a
--table called testtable located in schema public
-- to hold point geometries of dimension 2 in WGS84 longlat
SELECT AddGeometryColumn('public', 'testtable', 'the_geom', 4326, 'POINT', 2);
--Insert a record into the new table
INSERT INTO testtable(description, the_geom)
VALUES('center of boston',
ST_GeomFromText('POINT(-71.0891380310059 42.3123226165771)', 4326));
--Insert a point record into the new table - faster than st_geomfromtext for points
INSERT INTO testtable(description, the_geom)
VALUES('center of boston',
ST_SetSRID(ST_MakePoint(-71.0891380310059, 42.3123226165771), 4326));
--Create a spatial index on the new geometry column
ALTER TABLE testtable ALTER COLUMN the_geom SET NOT NULL;
CREATE INDEX idx_testtable_the_geom ON testtable USING gist(the_geom);
ALTER TABLE testtable CLUSTER ON idx_testtable_the_geom;
--Find the neighborhood with the smallest area
SELECT neigh_name, ST_Area(the_geom)
FROM neighborhoods
ORDER BY ST_Area(the_geom) limit 1;
--Find the total area of each ward in square feet of wards in Boston,
--the extent (bounding box) of each ward, average sqft per precinct in each ward
SELECT ward, sum(ST_Area(ST_Transform(the_geom,2249))) as totarea,
avg(ST_Area(ST_Transform(the_geom,2249))) as avgarea_precinct,
ST_Extent(ST_Transform(the_geom,2249)) as wardextent
FROM wardprecincts WHERE city = 'Boston'
GROUP BY ward;
--Find all land parcels within 100 units of a specific parcel.
SELECT l2.parcel_id, l2.st_num, l2.st_name
FROM landparcels l , landparcels l2
WHERE ST_DWithin(l.the_geom, l2.the_geom, 100)
AND l.parcel_id = '1234560000';
--Break up multipolygons into individual polygons
SELECT neigh_name,
(ST_Dump(the_geom)).geom As polygeom
FROM neighborhoods;
--Take individual polygons and create one multipolygon for each neighborhood
--Note if you have a mixed collection of geometries, will return a geometry collection
SELECT neigh_name, ST_Collect(polygeom) as the_geom
FROM neighborhoods
GROUP BY neigh_name;
Using Shape Dumper/Loader Commandline Tools
Load data into PostgreSQL from ESRI shape file
shp2pgsql -s 4326 neighborhoods public.neighborhoods > neighborhoods.sql
psql -h myserver -d mydb -U myuser -f neighborhoods.sql
Exporting data from PostgreSQL to ESRI Shape file
pgsql2shp -f jpnei -h myserver -u apguser -P apgpassword mygisdb
"SELECT neigh_name, the_geom FROM neighborhoods WHERE neigh_name = 'Jamaica Plain'"
Here is a brief description of a LAYER entry using PostGIS
(http://postgis.refractions.net) as a spatial data source:
PostGIS provides a SQL interface to large sets of spatial data. The
'CONNECTIONTYPE' is 'postgis'. The spatial data is stored in tables as a
column alongside the attribute data. Correspondingly, for MapServer to
build a PostGIS layer, it needs to know:
- What database to connect to (CONNECTION)
- While table to get the data from (DATA)
- Which column in the table holds the spatial objects (DATA)
- Any extra filtering information used to subset the data to be
displayed (FILTER)
The database connection is governed by the a 'connection string' which
is a standard set of keys and values like this (with the default values
in <>):
"user=<username> password=<password> dbname=<username> host=<localhost>
port=<5432>"
An empty connection string is still valid, and any of the key/value
pairs can be omitted. At a minimum you will generall supply the database
name and username to connect with.
The connection string goes in the 'CONNECTION' parameter of the LAYER
object:
CONNECTION "user=dbuser dbname=gisdatabase"
A complete SQL query which pulls a windowed subset of spatial data from
a PostGIS database might look like this:
a) SELECT
b) geom FROM roads
c) WHERE
d) geom && 'BOX3D(1423141 1512324 1513214 1652151)'::box3d
e) AND
f) type = 'highway'
When Mapserver pulls data from the database, it constructs this kind of
query. Parts a, c and e are just syntactical. Part d is derived from the
current extents automatically. Parts b and f are supplied in the map
file, in the 'DATA' and 'FILTER' lines of the layer definition
repectively. The example below gives a complete layer, with filtering
and expressions: note that the expressions in the CLASS objects do *not*
use SQL logical syntax, they use Mapserver's internal syntax for
describing logical comparisons.
LAYER
CONNECTIONTYPE postgis
NAME "widehighways"
# Connect to a remote spatial database
CONNECTION "user=dbuser dbname=gisdatabase host=bigserver"
# Get the lines from the 'geom' column of the 'roads' table
DATA "geom from roads"
STATUS ON
TYPE LINE
# Of the lines in the extents, only render the wide highways
FILTER "type = 'highway' and numlanes >= 4"
CLASS
# Make the superhighways brighter and 2 pixels wide
EXPRESSION ([numlanes] >= 6)
COLOR 255 22 22
SYMBOL "solid"
SIZE 2
END
CLASS
# All the rest are darker and only 1 pixel wide
EXPRESSION ([numlanes] < 6)
COLOR 205 92 82
END
END
