Management
AddGeometryColumn
DropGeometryColumn
DropGeometryTable
postgis_full_version
postgis_geos_version
postgis_lib_version
postgis_proj_version
postgis_version
probe_geometry_columns
SetSRID
UpdateGeometrySRID
Load/Dump Tools
shp2pgsql
pgsql2shp
Meta Tables
spatial_ref_sys
geometry_columns
Geometry Creation
BdMPolyFromText
BdPolyFromText
GeomCollFromText
GeomFromText
GeometryFromWKB
LineFromText
LinestringFromText
MakeLine
MakePolygon
MakePoint
MLineFromText
MPointFromText
MPolyFromText
PointFromText
PolyFromText
Relationship
Contains*
Crosses*
Disjoint*
Equals*
Intersects*
Overlaps*
Relate*
Touches*
Within*
Spatial Aggregates
Accum
Collect
Extent
Extent3D
GeomUnion*
MakeLine
MemCollect
MemGeomUnion*
Geometry Editors
AddBBOX
AddPoint
Affine
Collect
DropBBOX
Force_collection
Force_2d
Force_3d, Force_3dm
Force_3dz
Force_4d
LineMerge
Multi
RemovePoint
Segmentize
SetPoint
SnapToGrid
|
PostGIS ver. 1.2.1 Quick Guide - Cheatsheet PDF VersionOfficial PostGIS Documentation URL: http://postgis.refractions.net/docs/
Geometry Types - WKT RepresentationPOINT(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 Operators
A &< B (A overlaps or is to the left of B)
A &> B (A overlaps or is to the right of B)
A << B (A is strictly to the left of B)
A >> B (A is strictly to the right of B)
A &<| B (A overlaps B or is below B)
A |&> B (A overlaps or is above B)
A <<| B (A strictly below B)
A |>> B (A strictly above B)
A = B (A bbox same as B bbox)
A @ B (A completely contained by B)
A ~ B (A completely contains B)
A && B (A and B bboxes interact)
A ~= B - true if A and B geometries are binary equal?
Common Use SFSQL Examples
SELECT AddGeometryColumn('public', 'testtable', 'the_geom', 4326, 'POINT', 2);
INSERT INTO testtable(description, the_geom)
VALUES('center of boston',
GeomFromText('POINT(-71.0891380310059 42.3123226165771)', 4326));
INSERT INTO testtable(description, the_geom)
VALUES('center of boston',
setsrid(makepoint(-71.0891380310059, 42.3123226165771), 4326));
CREATE INDEX idx_testtable_the_geom ON testtable USING gist(the_geom);
SELECT neigh_name, area2d(the_geom)
FROM neighborhoods
ORDER BY area2d(the_geom) limit 1;
SELECT ward, sum(area2d(transform(the_geom,2249))) as totarea,
avg(area2d(transform(the_geom,2249))) as avgarea_precinct,
extent(transform(the_geom,2249)) as wardextent
FROM wardprecincts WHERE city = 'Boston'
GROUP BY ward;
SELECT l2.parcel_id, l2.st_num, l2.st_name
FROM landparcels l , landparcels l2
WHERE expand(l.the_geom, 100) && l2.the_geom
AND distance(l.the_geom, l2.the_geom) <= 100
AND l.parcel_id = '1234560000';
SELECT neigh_name,
GeometryN(the_geom, generate_series(1, numgeometries(the_geom))) As polygeom
FROM neighborhoods;
SELECT neigh_name, collect(polygeom) as the_geom
FROM neighborhoods
GROUP BY neigh_name;
Using Shape Dumper/Loader Commandline Tools
shp2pgsql -s 2249 neighborhoods public.neighborhoods > neighborhoods.sql
psql -h myserver -d mydb -f neighborhoods.sql
pgsql2shp -f jpnei -h myserver -u apguser -P apgpassword mygisdb
"SELECT neigh_name, the_geom FROM neighborhoods WHERE neigh_name = 'Jamaica Plain'"
Boston GIS Paragon Corporation
Postgres OnLine Journal PostGIS in Action |
Accessors
Dimension
Dump
EndPoint
Envelope
ExteriorRing
GeometryN
GeometryType
InteriorRingN
IsClosed
IsEmpty
IsRing
IsSimple
IsValid
M
NumGeometries
NumInteriorRings
NumPoints
npoints
PointN
SRID
StartPoint
X
XMin,XMax
Y
YMin,YMax
Z
ZMin,ZMax
Measurement
area2d
azimuth
distance
distance_sphere
distance_spheroid
length_spheroid
length2d
length3d
length3d_spheroid
max_distance
perimeter2d
perimeter3d
Outputs
AsBinary
AsText
AsEWKB
AsEWKT
AsHEXEWKB
AsGML
AsKML
AsSVG
Geometry Processors
Boundary*
Buffer*
BuildArea*
Centroid+
ConvexHull*
Difference*
Expand
ForceRHR
GeomUnion*
Intersection*
PointOnSurface*
Reverse
RotateX
RotateY
RotateZ
Scale
Simplify
SymDifference*
Transform
Translate
TransScale
|