ManagementAddGeometryColumn
DropGeometryColumn
DropGeometryTable
populate_geometry_columns3
postgis_full_version
postgis_geos_version
postgis_lib_version
postgis_proj_version
postgis_version
probe_geometry_columns
ST_SetSRID
UpdateGeometrySRID
Load/Dump Tools
--PostGIS tools --
shp2pgsql
shp2pgsql-gui 3
pgsql2shp
--PostgreSQL --
pg_dump
pg_restore
psql
Meta Tables/Views
spatial_ref_sys
geometry_columns
geography_columns1
Geometry Creation
ST_GeomFromEWKB
ST_GeomFromEWKT
ST_GeogFromText1
ST_GeomFromGML1
ST_GeomFromKML1
ST_GeomFromText
ST_GeomFromWKB
ST_GeogFromWKB1
ST_MakeEnvelope1
ST_MakeLine
ST_MakePolygon
ST_MakePoint
RelationshipST_Contains*2
ST_ContainsProperly*1,2,G3.1
ST_CoveredBy2
ST_Covers2
ST_Crosses*2
ST_Disjoint*
ST_DWithin2,3
ST_DFullyWithin1
ST_Equals*
ST_LineCrossingDirection1
ST_Intersects*2,3
ST_Overlaps*2
ST_Relate*
ST_Touches*2
ST_Within*2
Spatial Aggregates
ST_Accum
ST_Collect
ST_Extent
ST_Union*
ST_MakeLine
ST_Polygonize*
Geometry Editors
ST_AddMeasure
ST_AddPoint
ST_Affine
ST_Collect
ST_CollectionExtract1
ST_Force_collection
ST_Force_2d
ST_Force_3d, ST_Force_3dm
ST_Force_3dz
ST_Force_4d
ST_LineMerge
ST_Multi
ST_RemovePoint
ST_Segmentize
ST_SetPoint
ST_SnapToGrid
Linear Referencing
ST_Line_Interpolate_Point
ST_Line_Substring
ST_Line_Locate_Point
ST_Locate_Along_Measure
ST_Locate_Between_Measures
ST_LocateBetweenElevations1
|
PostGIS ver. 1.5 Quick Guide - Cheatsheet PDF Version
Geometry/Geography 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 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 boxes are equal2 3
Common Use SFSQL Examples
SELECT AddGeometryColumn('public', 'testtable', 'geom', 4326, 'POINT', 2);
INSERT INTO testtable(description, geom)
VALUES('center of boston',
ST_GeomFromText('POINT(-71.0891380310059 42.3123226165771)', 4326));
INSERT INTO testtable(description, geom)
VALUES('center of boston',
ST_SetSRID(ST_MakePoint(-71.0891380310059, 42.3123226165771), 4326));
CREATE TABLE testtable(test_id serial primary key, description text, geog geography(POINT,4326));
INSERT INTO testtable(description, geog)
VALUES('center of boston',
ST_GeogFromText('SRID=4326;POINT(-71.0891380310059 42.3123226165771)'));
ALTER TABLE testtable ALTER COLUMN geom SET NOT NULL;
CREATE INDEX idx_testtable_geom ON testtable USING gist(geom);
ALTER TABLE testtable CLUSTER ON idx_testtable_geom;
SELECT neigh_name, ST_Area(geom)
FROM neighborhoods
ORDER BY ST_Area(geom) limit 1;
SELECT ward, sum(ST_Area(ST_Transform(geom,2249))) as totarea,
avg(ST_Area(ST_Transform(geom,2249))) as avgarea_precinct,
ST_Extent(ST_Transform(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 ST_DWithin(l.geom, l2.geom, 100)
AND l.parcel_id = '1234560000';
SELECT neigh_name,
(ST_Dump(geom)).geom As polygeom
FROM neighborhoods;
SELECT neigh_name, ST_Collect(polygeom) as geom
FROM neighborhoods
GROUP BY neigh_name;
Using Shape Dumper/Loader Commandline Tools
shp2pgsql -s 4326 neighborhoods public.neighborhoods > neighborhoods.sql
psql -h myserver -d mydb -U myuser -f neighborhoods.sql
shp2pgsql -G -s 4326 neighborhoods public.neighborhoods > neighborhoods.sql
psql -h myserver -d mydb -U myuser -f neighborhoods.sql
pgsql2shp -f jpnei -h myserver -u apguser -P apgpassword mygisdb
"SELECT neigh_name, geom FROM neighborhoods WHERE neigh_name = 'Jamaica Plain'"
Boston GIS Paragon Corporation
Postgres OnLine Journal PostGIS in Action |
Accessors
ST_CollectionExtract1
ST_Dimension
ST_Dump
ST_DumpPoints1
ST_DumpRings
ST_EndPoint
ST_Envelope
ST_ExteriorRing
ST_GeometryN
ST_GeometryType
ST_InteriorRingN
ST_IsClosed
ST_IsEmpty
ST_IsRing
ST_IsSimple
ST_IsValid
ST_IsValidReason
ST_mem_size
ST_M
ST_NumGeometries
ST_NumInteriorRings
ST_NumPoints
ST_npoints
ST_PointN
ST_SetSRID
ST_StartPoint
ST_Summary
ST_X
ST_XMin,ST_XMax
ST_Y
YMin,YMax
ST_Z
ZMin,ZMax
Measurement
ST_Area3
ST_Azimuth
ST_Distance
ST_HausdorffDistance1G3.2
ST_distance_sphere3
ST_distance_spheroid3
ST_Length_Spheroid
ST_Length3
ST_MaxDistance1
ST_Perimeter
OutputsST_AsBinary3
ST_AsText3
ST_AsEWKB
ST_AsEWKT
ST_AsHEXEWKB
ST_GeoJSON
ST_AsGML3
ST_AsKML3
ST_AsSVG3
ST_GeoHash
Geometry ProcessorsST_MinimumBoundingCircle
ST_Boundary*
ST_Buffer*3 G3.2
ST_BuildArea*
ST_Centroid
ST_ClosestPoint1
ST_ConvexHull
ST_Difference*
ST_Expand
ST_ForceRHR
ST_LongestLine1
ST_Intersection*
ST_PointOnSurface*
ST_Reverse
ST_RotateX
ST_RotateY
ST_RotateZ
ST_Scale
ST_ShortestLine1
ST_Simplify
ST_SimplifyPreserveTopology
ST_SymDifference
ST_Transform
ST_Translate
ST_TransScale
ST_Union
|