ManagementAddGeometryColumn DropGeometryColumn DropGeometryTable 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 pgsql2shp --PostgreSQL -- pg_dump pg_restore psql Meta Tablesspatial_ref_sys
geometry_columns
Geometry CreationST_BdMPolyFromText ST_BdPolyFromText ST_GeomCollFromText ST_GeomFromEWKB ST_GeomFromEWKT ST_GeomFromText ST_GeomFromWKB ST_MakeLine ST_MakePolygon ST_MakePoint RelationshipST_Contains*2 ST_CoveredBy1,2 ST_Covers1,2 ST_Crosses*2 ST_Disjoint* ST_DWithin1,2 ST_Equals* ST_Intersects*2 ST_Overlaps*2 ST_Relate* ST_Touches*2 ST_Within*2 Spatial AggregatesST_Accum ST_Collect ST_Extent ST_Union* ST_MakeLine ST_MemCollect ST_MemGeomUnion* ST_Polygonize* Geometry EditorsST_AddBBOX ST_AddPoint ST_Affine ST_Collect ST_DropBBOX 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 ReferencingST_line_interpolate_point ST_line_substring ST_line_locate_point ST_locate_along_measure ST_locate_between_measures |
PostGIS ver. 1.3.1 Quick Guide - Cheatsheet PDF VersionOfficial PostGIS Documentation URL: http://postgis.refractions.net/docs/ Refractions PostGIS Support and Training: http://www.refractions.net/products/postgis/ This list is not comprehensive but tries to cover at least 80%. We also leave out alternative names for functions. The main difference between prior versions and 1.3.1 is that Denotes a new item in this version 1 Denotes now automatically uses spatial indexes 2 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 OperatorsThese operators utilize indexes. They compare bounding boxes of 2 geometriesA &< 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 --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_GeometryN(the_geom, generate_series(1, numgeometries(the_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 ToolsLoad 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
|
AccessorsST_Dimension ST_Dump ST_EndPoint ST_Envelope ST_ExteriorRing ST_GeometryN ST_GeometryType ST_InteriorRingN ST_IsClosed ST_IsEmpty ST_IsRing ST_IsSimple ST_IsValid ST_mem_size ST_M ST_NumGeometries ST_NumInteriorRings ST_NumPoints ST_npoints ST_PointN ST_SetSRID ST_StartPoint ST_Summary1 ST_X ST_XMin,ST_XMax ST_Y YMin,YMax ST_Z ZMin,ZMax MeasurementST_Area ST_Azimuth ST_Distance ST_distance_sphere ST_distance_spheroid ST_length_spheroid ST_length length3d_spheroid ST_max_distance ST_Perimeter OutputsST_AsBinary
ST_AsText
ST_AsEWKB
ST_AsEWKT
ST_AsHEXEWKB
ST_AsGML
ST_AsKML
ST_AsSVG
Geometry ProcessorsST_Boundary* ST_Buffer* ST_BuildArea* ST_Centroid+ ST_ConvexHull* ST_Difference* ST_Expand ST_ForceRHR ST_Union* ST_Intersection* ST_PointOnSurface* ST_Reverse ST_RotateX ST_RotateY ST_RotateZ ST_Scale ST_Simplify ST_SymDifference* ST_Transform ST_Translate ST_TransScale |