Table of Contents
The functions given below are the ones which a user of PostGIS is likely to need. There are other functions which are required support functions to the PostGIS objects which are not of use to a general user.
PostGIS has begun a transition from the existing naming convention to an SQL-MM-centric convention. As a result, most of the functions that you know and love have been renamed using the standard spatial type (ST) prefix. Previous functions are still available, though are not listed in this document where updated functions are equivalent. These will be deprecated in a future release.
Syntax: AddGeometryColumn(<schema_name>,
<table_name>, <column_name>, <srid>,
<type>, <dimension>). Adds a geometry column to
an existing table of attributes. The schema_name
is the name of the table schema (unused for pre-schema
PostgreSQL installations). The srid must be
an integer value reference to an entry in the SPATIAL_REF_SYS
table. The type must be an uppercase string
corresponding to the geometry type, eg, 'POLYGON' or
'MULTILINESTRING'.
Syntax: DropGeometryColumn(<schema_name>, <table_name>, <column_name>). Remove a geometry column from a spatial table. Note that schema_name will need to match the f_schema_name field of the table's row in the geometry_columns table.
Scans all tables with PostGIS geometry constraints and adds them to the geometry_columns table if they are not already there. Also give stats on the number of inserts, already present, or possibly obsolete columns.
Set the SRID on a geometry to a particular integer value. Useful in constructing bounding boxes for queries.
Return the cartesian distance between two geometries in projected units. Does not use indexes.
Returns true if geometries are within the specified distance of one another. Uses indexes if available.
Returns 1 (TRUE) if the given Geometries are "spatially equal". Use this for a 'better' answer than '='. equals('LINESTRING(0 0, 10 10)','LINESTRING(0 0, 5 5, 10 10)') is true.
Performed by the GEOS module
OGC SPEC s2.1.1.2
Returns 1 (TRUE) if the Geometries are "spatially disjoint".
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
NOTE: this is the "allowable" version that returns a boolean, not an integer.
OGC SPEC s2.1.1.2 //s2.1.13.3 - a.Relate(b, 'FF*FF****')
Returns 1 (TRUE) if the Geometries "spatially intersect".
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. To avoid index use, use the function _ST_Intersects.
NOTE: this is the "allowable" version that returns a boolean, not an integer.
OGC SPEC s2.1.1.2 //s2.1.13.3 - Intersects(g1, g2 ) --> Not (Disjoint(g1, g2 ))
Returns 1 (TRUE) if the Geometries "spatially touch".
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. To avoid index use, use the function _ST_Touches.
NOTE: this is the "allowable" version that returns a boolean, not an integer.
OGC SPEC s2.1.1.2 // s2.1.13.3- a.Touches(b) -> (I(a) intersection I(b) = {empty set} ) and (a intersection b) not empty
Returns 1 (TRUE) if the Geometries "spatially cross".
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. To avoid index use, use the function _ST_Crosses.
NOTE: this is the "allowable" version that returns a boolean, not an integer.
OGC SPEC s2.1.1.2 // s2.1.13.3 - a.Relate(b, 'T*T******')
Returns 1 (TRUE) if Geometry A is "spatially within" Geometry B. A has to be completely inside B.
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. To avoid index use, use the function _ST_Within.
NOTE: this is the "allowable" version that returns a boolean, not an integer.
OGC SPEC s2.1.1.2 // s2.1.13.3 - a.Relate(b, 'T*F**F***')
Returns 1 (TRUE) if the Geometries "spatially overlap".
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. To avoid index use, use the function _ST_Overlaps.
NOTE: this is the "allowable" version that returns a boolean, not an integer.
OGC SPEC s2.1.1.2 // s2.1.13.3
Returns 1 (TRUE) if Geometry A "spatially contains" Geometry B.
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. To avoid index use, use the function _ST_Contains.
NOTE: this is the "allowable" version that returns a boolean, not an integer.
OGC SPEC s2.1.1.2 // s2.1.13.3 - same as within(geometry B, geometry A)
Returns 1 (TRUE) if no point in Geometry B is outside Geometry A
Refer to http://lin-ear-th-inking.blogspot.com/2007/06/subtleties-of-ogc-covers-spatial.html for an explanation of the need of this function.
This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. To avoid index use, use the function _ST_Covers.
Returns 1 (TRUE) if no point in Geometry A is outside Geometry B
Refer to http://lin-ear-th-inking.blogspot.com/2007/06/subtleties-of-ogc-covers-spatial.html for an explaination of the need of this function.
Returns 1 (TRUE) if the Geometries "spatially intersect".
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
NOTE: this is the "allowable" version that returns a boolean, not an integer.
OGC SPEC s2.1.1.2 // s2.1.13.3 - NOT disjoint(geometry, geometry)
Returns 1 (TRUE) if this Geometry is spatially related to anotherGeometry, by testing for intersections between the Interior, Boundary and Exterior of the two geometries as specified by the values in the intersectionPatternMatrix.
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
NOTE: this is the "allowable" version that returns a boolean, not an integer.
OGC SPEC s2.1.1.2 // s2.1.13.3
returns the DE-9IM (dimensionally extended nine-intersection matrix)
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
not in OGC spec, but implied. see s2.1.13.2
Returns the centroid of the geometry as a point.
Computation will be more accurate if performed by the GEOS module (enabled at compile time).
Returns the area of the geometry if it is a polygon or multi-polygon.
The length of this Curve in its associated spatial reference.
synonym for length2d()
OGC SPEC 2.1.5.1
Return a Point guaranteed to lie on the surface
Implemented using GEOS
OGC SPEC 3.2.14.2 and 3.2.18.2 -
Returns the closure of the combinatorial boundary of this Geometry. The combinatorial boundary is defined as described in section 3.12.3.2 of the OGC SPEC. Because the result of this function is a closure, and hence topologically closed, the resulting boundary can be represented using representational geometry primitives as discussed in the OGC SPEC, section 3.12.2.
Performed by the GEOS module
OGC SPEC s2.1.1.1
Returns a geometry that represents all points whose distance from this Geometry is less than or equal to distance. Calculations are in the Spatial Reference System of this Geometry. The optional third parameter sets the number of segment used to approximate a quarter circle (defaults to 8).
Performed by the GEOS module.
OGC SPEC s2.1.1.3
The convex hull of a geometry represents the minimum closed geometry that encloses all geometries within the set.
It is usually used with MULTI and Geometry Collections. Although it is not an aggregate - you can use it in conjunction with ST_Collect to get the convex hull of a set of points. ST_ConvexHull(ST_Collect(somepointfield)). It is often used to determine an affected area based on a set of point observations.
SELECT d.disease_type, ST_ConvexHull(ST_Collect(d.the_geom)) As the_geom FROM disease_obs As d GROUP BY d.disease_type
Performed by the GEOS module
OGC SPEC s2.1.1.3
Returns a geometry that represents the point set intersection of the Geometries.
In other words - that portion of geometry A and geometry B that is shared between the two geometries.
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
OGC SPEC s2.1.1.3
Reads every point/vertex in every component of every feature in a geometry, and if the longitude coordinate is <0, adds 360 to it. The result would be a 0-360 version of the data to be plotted in a 180 centric map
Prior to 1.3.4 - there was a bug in this that prevented it with working for MULTIPOINT. This bug is fixed in 1.3.4+
Returns a geometry that represents the portions of A and B that do not intersect. It is called a symmetric difference because ST_SymDifference(A,B) = ST_SymDifference(B,A).
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
OGC SPEC s2.1.1.3
Returns a geometry that represents that part of geometry A that does not intersect with geometry B.
Performed by the GEOS module
Do not call with a GeometryCollection as an argument
OGC SPEC s2.1.1.3
Returns a geometry that represents the point set union of the Geometries.
Performed by the GEOS module.
Do not call with a GeometryCollection as an argument.
NOTE: this function was formerly called GeomUnion(), which was renamed from "Union" because UNION is an SQL reserved word.
OGC SPEC s2.1.1.3
Returns a geometry that represents the point set union of all Geometries in given set.
Performed by the GEOS module.
Do not call with a GeometryCollection in the argument set.
Not explicitly defined in OGC SPEC.
Same as the above, only memory-friendly (uses less memory and more processor time).
Return the Well-Known Text representation of the geometry. For example: POLYGON(0 0,0 1,1 1,1 0,0 0)
OGC SPEC s2.1.1.1
Returns the geometry in the OGC "well-known-binary" format, using the endian encoding of the server on which the database is running. This is useful in binary cursors to pull data out of the database without converting it to a string representation.
OGC SPEC s2.1.1.1 - also see asBinary(<geometry>,'XDR') and asBinary(<geometry>,'NDR')
Returns the integer SRID number of the spatial reference system of the geometry.
OGC SPEC s2.1.1.1
The inherent dimension of this Geometry object, which must be less than or equal to the coordinate dimension. OGC SPEC s2.1.1.1 - returns 0 for points, 1 for lines, 2 for polygons, and the largest dimension of the components of a GEOMETRYCOLLECTION.
select dimension('GEOMETRYCOLLECTION(LINESTRING(1 1,0 0),POINT(0 0)');
dimension
-----------
1Returns a valid geometry (POINT, LINESTRING or POLYGON) representing the bounding box of the geometry. Degenerate cases (vertical lines, point) will return a geometry of lower dimension than POLYGON.
OGC SPEC s2.1.1.1 - The minimum bounding box for this Geometry, returned as a Geometry. The polygon is defined by the corner points of the bounding box ((MINX, MINY), (MAXX, MINY), (MAXX, MAXY), (MINX, MAXY), (MINX, MINY)).
NOTE:PostGIS will add a Zmin/Zmax coordinate as well.
Returns 1 (TRUE) if this Geometry is the empty geometry . If true, then this Geometry represents the empty point set - i.e. GEOMETRYCOLLECTION(EMPTY).
OGC SPEC s2.1.1.1
Returns 1 (TRUE) if this Geometry has no anomalous geometric points, such as self intersection or self tangency.
Performed by the GEOS module
OGC SPEC s2.1.1.1
Returns true of the geometry start and end points are coincident.
Returns 1 (TRUE) if this Curve is closed (StartPoint ( ) = EndPoint ( )) and this Curve is simple (does not pass through the same point more than once).
performed by GEOS
OGC spec 2.1.5.1
If geometry is a GEOMETRYCOLLECTION (or MULTI*) return the number of geometries, otherwise return NULL.
Return the N'th geometry if the geometry is a GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING or MULTIPOLYGON. Otherwise, return NULL.
Index is 1-based as for OGC specs since version 0.8.0. Previous versions implemented this as 0-based instead.
Find and return the number of points in the first linestring in the geometry. Return NULL if there is no linestring in the geometry.
Return the N'th point in the first linestring in the geometry. Return NULL if there is no linestring in the geometry.
Index is 1-based as for OGC specs since version 0.8.0. Previous versions implemented this as 0-based instead.
Return the exterior ring of the polygon geometry. Return NULL if the geometry is not a polygon.
Return the number of interior rings of the first polygon in the geometry. Return NULL if there is no polygon in the geometry.
Synonym to NumInteriorRings(geometry). The OpenGIS specs are ambiguous about the exact function naming, so we provide both spellings.
Return the N'th interior ring of the polygon geometry. Return NULL if the geometry is not a polygon or the given N is out of range.
Index is 1-based as for OGC specs since version 0.8.0. Previous versions implemented this as 0-based instead.
Returns the last point of the LineString geometry as a point.
Returns the first point of the LineString geometry as a point.
Returns the type of the geometry as a string. Eg: 'LINESTRING', 'POLYGON', 'MULTIPOINT', etc.
OGC SPEC s2.1.1.1 - Returns the name of the instantiable subtype of Geometry of which this Geometry instance is a member. The name of the instantiable subtype of Geometry is returned as a string.
This function also indicates if the geometry is measured, by returning a string of the form 'POINTM'.
Returns the type of the geometry as a string. EG: 'Linestring', 'Polygon', etc. This function differs from GeometryType(geometry) in the case of the string that is returned, as well as the fact that it will not indicate whether the geometry is measured.
Return the X coordinate of the point. Input must be a point.
Return the Y coordinate of the point. Input must be a point.
Return the Z coordinate of the point, or NULL if not available. Input must be a point.
Return the M coordinate of the point, or NULL if not available. Input must be a point.
This is not (yet) part of the OGC spec, but is listed here to complete the point coordinate extractor function list.
Makes a Geometry from WKT with the given SRID.
OGC SPEC 3.2.6.2 - option SRID is from the conformance suite
Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.6.2 - option SRID is from the conformance suite
Throws an error if the WKT is not a Point
Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.6.2 - option SRID is from the conformance suite
Throws an error if the WKT is not a Line
Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.
from the conformance suite
Throws an error if the WKT is not a Line
Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.6.2 - option SRID is from the conformance suite
Throws an error if the WKT is not a Polygon
Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.
from the conformance suite
Throws an error if the WKT is not a Polygon
Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.6.2 - option SRID is from the conformance suite
Throws an error if the WKT is not a MULTIPOINT
Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.6.2 - option SRID is from the conformance suite
Throws an error if the WKT is not a MULTILINESTRING
Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.6.2 - option SRID is from the conformance suite
Throws an error if the WKT is not a MULTIPOLYGON
Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.6.2 - option SRID is from the conformance suite
Throws an error if the WKT is not a GEOMETRYCOLLECTION
Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.6.2 - option SRID is from the conformance suite
Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.7.2 - option SRID is from the conformance suite
Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.7.2 - option SRID is from the conformance suite
throws an error if WKB is not a POINT
Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.7.2 - option SRID is from the conformance suite
throws an error if WKB is not a LINESTRING
Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.
from the conformance suite
throws an error if WKB is not a LINESTRING
Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.7.2 - option SRID is from the conformance suite
throws an error if WKB is not a POLYGON
Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.
from the conformance suite
throws an error if WKB is not a POLYGON
Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.7.2 - option SRID is from the conformance suite
throws an error if WKB is not a MULTIPOINT
Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.7.2 - option SRID is from the conformance suite
throws an error if WKB is not a MULTILINESTRING
Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.7.2 - option SRID is from the conformance suite
throws an error if WKB is not a MULTIPOLYGON
Makes a Geometry from WKB with the given SRID. If SRID is not give, it defaults to -1.
OGC SPEC 3.2.7.2 - option SRID is from the conformance suite
throws an error if WKB is not a GEOMETRYCOLLECTION
Construct a Polygon given an arbitrary collection of closed linestrings as a MultiLineString text representation.
Throws an error if WKT is not a MULTILINESTRING. Throws an error if output is a MULTIPOLYGON; use BdMPolyFromText in that case, or see BuildArea() for a postgis-specific approach.
OGC SFSQL 1.1 - 3.2.6.2
Availability: 1.1.0 - requires GEOS >= 2.1.0.
Construct a MultiPolygon given an arbitrary collection of closed linestrings as a MultiLineString text representation.
Throws an error if WKT is not a MULTILINESTRING. Forces MULTIPOLYGON output even when result is really only composed by a single POLYGON; use BdPolyFromText if you're sure a single POLYGON will result from operation, or see BuildArea() for a postgis-specific approach.
OGC SFSQL 1.1 - 3.2.6.2
Availability: 1.1.0 - requires GEOS >= 2.1.0.
Drops a table and all its references in geometry_columns. Note: uses current_schema() on schema-aware pgsql installations if schema is not provided.
Update the SRID of all features in a geometry column updating constraints and reference in geometry_columns. Note: uses current_schema() on schema-aware pgsql installations if schema is not provided.
Update statistics about spatial tables for use by the query planner. You will also need to run "VACUUM ANALYZE [table_name] [column_name]" for the statistics gathering process to be complete. NOTE: starting with PostgreSQL 8.0 statistics gathering is automatically performed running "VACUUM ANALYZE".
Returns PostGIS version number and compile-time options
Prior to version 1.1.0 this was a procedural function, thus possibly returning inaccurate information (in case of incomplete database upgrades).
Returns the version number of the PostGIS library.
Availability: 0.9.0
Returns build date of the PostGIS library.
Availability: 1.0.0RC1
Returns build date of the PostGIS scripts.
Availability: 1.0.0RC1
Returns version of the postgis scripts installed in this database.
If the output of this function doesn't match the output of postgis_scripts_released() you probably missed to properly upgrade an existing database. See the Upgrading section for more info.
Availability: 0.9.0
Returns the version number of the lwpostgis.sql script released with the installed postgis lib.
Starting with version 1.1.0 this function returns the same value of postgis_lib_version(). Kept for backward compatibility.
Availability: 0.9.0
Returns the version number of the GEOS library, or NULL if GEOS support is not enabled.
Availability: 0.9.0
Returns the version number of the JTS library, or NULL if JTS support is not enabled.
Availability: 1.1.0
Returns the version number of the PROJ4 library, or NULL if PROJ4 support is not enabled.
Availability: 0.9.0
Returns true if STATS usage has been enabled, false otherwise.
Availability: 0.9.0
Reports full postgis version and build configuration infos.
Availability: 0.9.0
The "=" operator returns true if A's bounding box is the same as B's bounding box.
This is cause for a lot of confusion. When you compare geometryA = geometryB it will return true even when the geometries are clearly different if their bounding boxes are the same. To check for true equality use ST_OrderingEquals or ST_Equals
The "&<" operator returns true if A's bounding box overlaps or is to the left of B's bounding box.
The "&>" operator returns true if A's bounding box overlaps or is to the right of B's bounding box.
The "<<" operator returns true if A's bounding box is strictly to the left of B's bounding box.
The ">>" operator returns true if A's bounding box is strictly to the right of B's bounding box.
The "&<|" operator returns true if A's bounding box overlaps or is below B's bounding box.
The "|&>" operator returns true if A's bounding box overlaps or is above B's bounding box.
The "<<|" operator returns true if A's bounding box is strictly below B's bounding box.
The "|>>" operator returns true if A's bounding box is strictly above B's bounding box.
The "~=" operator is the "same as" operator. It tests actual geometric equality of two features. So if A and B are the same feature, vertex-by-vertex, the operator returns true.
The "@" operator returns true if A's bounding box is completely contained by B's bounding box.
The "~" operator returns true if A's bounding box completely contains B's bounding box.
The "&&" operator is the "overlaps" operator. If A's bounding box overlaps B's bounding box the operator returns true.
Returns the area of the geometry if it is a polygon or multi-polygon.
Returns linear distance in meters between two lat/lon points. Uses a spherical earth and radius of 6370986 meters. Faster than distance_spheroid(), but less accurate. Only implemented for points.
Returns linear distance between two lat/lon points given a particular spheroid. See the explanation of spheroids given for length_spheroid(). Currently only implemented for points.
Returns the 2-dimensional length of the geometry if it is a linestring or multi-linestring.
Returns the 3-dimensional length of the geometry if it is a linestring or multi-linestring.
Calculates the length of of a geometry on an ellipsoid. This is useful if the coordinates of the geometry are in latitude/longitude and a length is desired without reprojection. The ellipsoid is a separate database type and can be constructed as follows:
SPHEROID[<NAME>,<SEMI-MAJOR
AXIS>,<INVERSE FLATTENING>]
Eg:
SPHEROID["GRS_1980",6378137,298.257222101]
An example calculation might look like this:
SELECT length_spheroid( geometry_column,
'SPHEROID["GRS_1980",6378137,298.257222101]' )
FROM geometry_table;
Calculates the length of of a geometry on an ellipsoid, taking the elevation into account. This is just like length_spheroid except vertical coordinates (expressed in the same units as the spheroid axes) are used to calculate the extra distance vertical displacement adds.
Returns the smaller distance between two geometries.
Returns the largest distance between two line strings.
Returns the 2-dimensional perimeter of the geometry, if it is a polygon or multi-polygon.
Returns the 2-dimensional perimeter of the geometry, if it is a polygon or multi-polygon.
Returns the 3-dimensional perimeter of the geometry, if it is a polygon or multi-polygon.
Returns the azimuth of the segment defined by the given Point geometries, or NULL if the two points are coincident. Return value is in radians.
Availability: 1.1.0
Returns the geometry in the OGC "well-known-binary" format as a bytea, using little-endian (NDR) or big-endian (XDR) encoding. This is useful in binary cursors to pull data out of the database without converting it to a string representation.
Returns a Geometry in EWKT format (as text).
Returns a Geometry in EWKB format (as bytea) using either little-endian (NDR) or big-endian (XDR) encoding.
Returns a Geometry in HEXEWKB format (as text) using either little-endian (NDR) or big-endian (XDR) encoding.
Return the geometry as SVG path data. Use 1 as second argument to have the path data implemented in terms of relative moves, the default (or 0) uses absolute moves. Third argument may be used to reduce the maximum number of decimal digits used in output (defaults to 15). Point geometries will be rendered as cx/cy when 'rel' arg is 0, x/y when 'rel' is 1. Multipoint geometries are delimited by commas (","), GeometryCollection geometries are delimited by semicolons (";").
Return the geometry as a GML element. The version parameter, if specified, may be either 2 or 3. If no version parameter is specified then the default is assumed to be 2. The third argument may be used to reduce the maximum number of significant digits used in output (defaults to 15).
Return the geometry as a KML element. Second argument may be used to reduce the maximum number of significant digits used in output (defaults to 15). Version defaults to 2
Requires PostGIS be compiled with Proj support. Use PostGIS_Full_Version() to confirm you have proj support compiled in.
Return the geometry as a GeoJson element. (Cf GeoJson specifications 1.0). 2D and 3D Geometries are both supported. GeoJson only support SFS 1.1 geometry type (no curve support for example).
The version parameter, if specified, must be 1.
The third argument may be used to reduce the maximum number of decimal places used in output (defaults to 15).
The last 'options' argument could be used to add Bbox or Crs in GeoJSON output:
0: means no option (default value)
1: GeoJson CRS
2: GeoJson Bbox
3: Both GeoJson Bbox and CRS
GeoJson CRS pattern generated is: auth_name:auth_srid
from spatial_ref_sys table (EPSG:4326 for instance).
Availability: 1.3.4
Makes a Geometry from EWKT.
Makes a Geometry from EWKB.
Creates a 2d,3dz or 4d point geometry.
Creates a 3dm point geometry.
Creates a BOX2D defined by the given point geometries.
Creates a BOX3D defined by the given point geometries.
Creates a Linestring from a set of point geometries. You might want to use a subselect to order points before feeding them to this aggregate.
Creates a Linestring from the two given point geometries.
Creates a LineString from a MultiPoint geometry.
Creates a Polygon formed by the given shell and array of holes. You can construct a geometry array using Accum. Input geometries must be closed LINESTRINGS (see IsClosed and GeometryType).
Creates an areal geometry formed by the constituent linework of given geometry. The return type can be a Polygon or MultiPolygon, depending on input. If the input lineworks do not form polygons NULL is returned.
See also BdPolyFromText and BdMPolyFromText - wrappers to this function with standard OGC interface.
Availability: 1.1.0 - requires GEOS >= 2.1.0.
Aggregate. Creates a GeometryCollection containing possible polygons formed from the constituent linework of a set of geometries.
Availability: 1.0.0RC1 - requires GEOS >= 2.1.0.
This function returns a GEOMETRYCOLLECTION or a MULTI object from a set of geometries. The collect() function is an "aggregate" function in the terminology of PostgreSQL. That means that it operates on rows of data, in the same way the sum() and mean() functions do. For example, "SELECT COLLECT(GEOM) FROM GEOMTABLE GROUP BY ATTRCOLUMN" will return a separate GEOMETRYCOLLECTION for each distinct value of ATTRCOLUMN.
ST_Collect and ST_Union are often interchangeable. ST_Collect is in general orders of magnitude faster than ST_Union because it does not try to dissolve boundaries. It merely rolls up single geometries into MULTI and MULTI or mixed geometry types into Geometry Collections. Unfortunately geometry collections are not well-supported by GIS tools. To prevent ST_Collect from returning a Geometry Collection when collecting MULTI geometries, one can use the below trick that utilizes ST_Dump to expand the MULTIs out to singles and then regroup them.
Thread ref: http://postgis.refractions.net/pipermail/postgis-users/2008-June/020331.html SELECT stusps, ST_Multi(ST_Collect(f.the_geom)) as singlegeom FROM (SELECT stusps, (ST_Dump(the_geom)).geom As the_geom FROM somestatetable ) As f GROUP BY stusps
This function returns a geometry being a collection of two input geometries. Output type can be a MULTI* or a GEOMETRYCOLLECTION.
This is a set-returning function (SRF). It returns a set of geometry_dump rows, formed by a geometry (geom) and an array of integers (path). When the input geometry is a simple type (POINT,LINESTRING,POLYGON) a single record will be returned with an empty path array and the input geometry as geom. When the input geometry is a collection or multi it will return a record for each of the collection components, and the path will express the position of the component inside the collection.
ST_Dump is useful for expanding geometries. It is the reverse of a GROUP BY in that it creates new rows. For example it can be use to expand MULTIPOLYGONS into POLYGONS.
SELECT sometable.*, (ST_Dump(the_geom)).geom As the_geom FROM somestatetable
Availability: PostGIS 1.0.0RC1. Requires PostgreSQL 7.3 or higher.
This is a set-returning function (SRF). It returns a set of geometry_dump rows, formed by a geometry (geom) and an array of integers (path). The 'path' field holds the polygon ring index, contains a single element: 0 for the shell, hole number for holes. The 'geom' field contains the corresponding ring as a polygon.
Availability: PostGIS 1.1.3. Requires PostgreSQL 7.3 or higher.
Add bounding box to the geometry. This would make bounding box based queries faster, but will increase the size of the geometry.
Drop the bounding box cache from the geometry. This reduces geometry size, but makes bounding-box based queries slower.
Adds a point to a LineString before point <pos> (0-based index). Third parameter can be omitted or set to -1 for appending.
Removes point from a linestring. Offset is 0-based.
Availability: 1.1.0
Replace point N of linestring with given point. Index is 0-based.
Availability: 1.1.0
Converts the geometry into a GEOMETRYCOLLECTION. This is useful for simplifying the WKB representation.
Forces the geometries into a "2-dimensional mode" so that all output representations will only have the X and Y coordinates. This is useful for force OGC-compliant output (since OGC only specifies 2-D geometries).
Forces the geometries into XYZ mode.
Forces the geometries into XYM mode.
Forces the geometries into XYZM mode.
Returns the geometry as a MULTI* geometry. If the geometry is already a MULTI*, it is returned unchanged.
Returns a new geometry with its coordinates transformed to
the SRID referenced by the integer parameter. The destination
SRID must exist in the SPATIAL_REF_SYS table.
Requires PostGIS be compiled with Proj support. Use PostGIS_Full_Version() to confirm you have proj support compiled in.
Applies an 3d affine transformation to the geometry. The call
Affine(geom, a, b, c, d, e, f, g, h, i, xoff, yoff, zoff)
represents the transformation matrix
/ a b c xoff \ | d e f yoff | | g h i zoff | \ 0 0 0 1 /
and the vertices are transformed as follows:
x' = a*x + b*y + c*z + xoff y' = d*x + e*y + f*z + yoff z' = g*x + h*y + i*z + zoff
All of the translate / scale functions below are expressed via such an affine transformation.
Availability: 1.1.2.
Applies an 2d affine transformation to the geometry. The call
Affine(geom, a, b, d, e, xoff, yoff)
represents the transformation matrix
/ a b 0 xoff \ / a b xoff \ | d e 0 yoff | rsp. | d e yoff | | 0 0 1 0 | \ 0 0 1 / \ 0 0 0 1 /
and the vertices are transformed as follows:
x' = a*x + b*y + xoff y' = d*x + e*y + yoff z' = z
This method is a subcase of the 3D method above.
Availability: 1.1.2.
Translates the geometry to a new location using the numeric parameters as offsets. Ie: translate(geom, X, Y, Z).
scales the geometry to a new size by multiplying the ordinates with the parameters. Ie: scale(geom, Xfactor, Yfactor, Zfactor).
Availability: 1.1.0
Rotate the geometry around the Z, X or Y axis by the given angle given in radians. Follows the right-hand rule.
Availability: 1.1.2.
First, translates the geometry using the first two floats,
then scales it using the second two floats, working in 2D only.
Using transscale(geom, X, Y, XFactor, YFactor)
internally calls affine(geom, XFactor, 0, 0, 0, YFactor,
0, 0, 0, 1, X*XFactor, Y*YFactor, 0).
Availability: 1.1.0.
Returns the geometry with vertex order reversed.
Force polygons of the collection to obey Right-Hand-Rule.
Returns a "simplified" version of the given geometry using the Douglas-Peuker algorithm. Will actually do something only with (multi)lines and (multi)polygons but you can safely call it with any kind of geometry. Since simplification occurs on a object-by-object basis you can also feed a GeometryCollection to this function. Note that returned geometry might loose its simplicity (see IsSimple)
Returns a "simplified" version of the given geometry using the Douglas-Peuker algorithm. Will avoid creating derived geometries (polygons in particular) that are invalid.
Snap all points of the input geometry to the grid defined by its origin and cell size. Remove consecutive points falling on the same cell, eventually returning NULL if output points are not enough to define a geometry of the given type. Collapsed geometries in a collection are stripped from it.
The returned geometry might loose its simplicity (see IsSimple).
Before release 1.1.0 this function always returned a 2d geometry. Starting at 1.1.0 the returned geometry will have same dimensionality as the input one with higher dimension values untouched. Use the version taking a second geometry argument to define all grid dimensions.
Availability: 1.0.0RC1
Snap all points of the input geometry to the grid defined by its origin (the second argument, must be a point) and cell sizes. Specify 0 as size for any dimension you don't want to snap to a grid.
Availability: 1.1.0
Return a modified geometry having no segment longer then the given distance. Interpolated points will have Z and M values (if needed) set to 0. Distance computation is performed in 2d only.
Returns a (set of) LineString(s) formed by sewing together constituent linework of input.
Availability: 1.1.0 - requires GEOS >= 2.1.0
Returns a point interpolated along a line. First argument must be a LINESTRING. Second argument is a float8 between 0 and 1 representing fraction of total 2d length the point has to be located.
See line_locate_point() for computing the line location nearest to a Point.
Since release 1.1.1 this function also interpolates M and Z values (when present), while prior releases set them to 0.0.
Availability: 0.8.2
Return a linestring being a substring of the input one starting and ending at the given fractions of total 2d length. Second and third arguments are float8 values between 0 and 1.
If 'start' and 'end' have the same value this is equivalent to line_interpolate_point().
See line_locate_point() for computing the line location nearest to a Point.
Since release 1.1.1 this function also interpolates M and Z values (when present), while prior releases set them to unspecified values.
Availability: 1.1.0
Returns a float between 0 and 1 representing the location of the closest point on LineString to the given Point, as a fraction of total 2d line length.
You can use the returned location to extract a Point (line_interpolate_point) or a substring (line_substring).
Availability: 1.1.0
Return a derived geometry collection value with elements that match the specified measure. Polygonal elements are not supported.
Semantic is specified by: ISO/IEC CD 13249-3:200x(E) - Text for Continuation CD Editing Meeting
Availability: 1.1.0
Return a derived geometry collection value with elements that match the specified range of measures inclusively. Polygonal elements are not supported.
Semantic is specified by: ISO/IEC CD 13249-3:200x(E) - Text for Continuation CD Editing Meeting
Availability: 1.1.0
Returns a text summary of the contents of the geometry.
Returns a BOX2D representing the maximum extents of the geometry.
Returns a BOX3D representing the maximum extents of the geometry.
The extent() function is an "aggregate" function in the terminology of PostgreSQL. That means that it operators on lists of data, in the same way the sum() and mean() functions do. For example, "SELECT EXTENT(GEOM) FROM GEOMTABLE" will return a BOX3D giving the maximum extend of all features in the table. Similarly, "SELECT EXTENT(GEOM) FROM GEOMTABLE GROUP BY CATEGORY" will return one extent result for each category.
Returns ZM (dimension semantic) flag of the geometries as a small int. Values are: 0=2d, 1=3dm, 2=3dz, 3=4d.
Returns TRUE if the bbox of this geometry is cached, FALSE otherwise. Use addBBOX() and dropBBOX() to control caching.
Returns number of dimensions of the geometry as a small int. Values are: 2,3 or 4.
If the geometry is a polygon or multi-polygon returns the number of rings.
Returns the number of points in the geometry.
returns true if this geometry is valid.
This function returns a bounding box expanded in all directions from the bounding box of the input geometry, by an amount specified in the second argument. Very useful for distance() queries, to add an index filter to the query.
Return the 'estimated' extent of the given spatial table. The estimated is taken from the geometry column's statistics. The current schema will be used if not specified.
For PostgreSQL>=8.0.0 statistics are gathered by VACUUM ANALYZE and resulting extent will be about 95% of the real one.
For PostgreSQL<8.0.0 statistics are gathered by update_geometry_stats() and resulting extent will be exact.
The syntax is find_srid(<db/schema>, <table>, <column>) and the function returns the integer SRID of the specified column by searching through the GEOMETRY_COLUMNS table. If the geometry column has not been properly added with the AddGeometryColumns() function, this function will not work either.
Returns the amount of space (in bytes) the geometry takes.
The syntax for this functions is point_inside_circle(<geometry>,<circle_center_x>,<circle_center_y>,<radius>). Returns the true if the geometry is a point and is inside the circle. Returns false otherwise.
Returns the requested minima of a bounding box.
Returns the requested maxima of a bounding box.
Aggregate. Constructs an array of geometries.
This module and associated pl/pgsql functions have been implemented to provide long locking support required by Web Feature Service specification.
Users must use serializable transaction level otherwise locking mechanism would break.
Enable long transaction support. This function creates the required metadata tables, needs to be called once before using the other functions in this section. Calling it twice is harmless.
Availability: 1.1.3
Disable long transaction support. This function removes the long transaction support metadata tables, and drops all triggers attached to lock-checked tables.
Availability: 1.1.3
Check updates and deletes of rows in given table for being authorized. Identify rows using <rowid_col> column.
Availability: 1.1.3
Set lock/authorization for specific row in table <authid> is a text value, <expires> is a timestamp defaulting to now()+1hour. Returns 1 if lock has been assigned, 0 otherwise (already locked by other auth)
Availability: 1.1.3
Remove all locks held by specified authorization id. Returns the number of locks released.
Availability: 1.1.3
Add an authorization token to be used in current transaction.
Availability: 1.1.3
This is a listing of the SQL-MM defined functions that PostGIS currently supports. The implementations of these functions follow the ArcSDE implementation, and thus deviate somewhat from the spec. These deviations will be noted.
As of version 1.2.0, these functions have been implemented by wrapping existing PostGIS functions. As a result, full support for curved geometries may not be in place for many functions.
SQL-MM defines the default SRID of all geometry constructors as 0. PostGIS uses a default SRID of -1.
Return the area measurement of an ST_Surface or ST_MultiSurface value.
SQL-MM 3: 8.1.2, 9.5.3
Return the well-known binary representation of an ST_Geometry value.
SQL-MM 3: 5.1.37
Return the well-known text representation of an ST_Geometry value.
SQL-MM 3: 5.1.25
Return the boundary of the ST_Geometry value.
SQL-MM 3: 5.1.14
Return a buffer around the ST_Geometry value.
SQL-MM 3: 5.1.17
Return mathematical centroid of the ST_Surface or ST_MultiSurface value.
SQL-MM 3: 8.1.4, 9.5.5
Test if an ST_Geometry value spatially contains another ST_Geometry value.
SQL-MM 3: 5.1.31
The convex hull of a geometry represents the minimum geometry that encloses all geometries within the set.
It is usually used with MULTI and Geometry Collections. Although it is not an aggregate - you can use it in conjunction with ST_Collect to get the convex hull of a set of points. ST_ConvexHull(ST_Collect(somepointfield)). It is often used to determine an affected area based on a set of point observations.
SQL-MM 3: 5.1.16
Return the coordinate dimension of the ST_Geometry value.
SQL-MM 3: 5.1.3
Test if an ST_Geometry value spatially crosses another ST_Geometry value.
SQL-MM 3: 5.1.29
Return an ST_Geometry value that represents the point set difference of two ST_Geometry values.
SQL-MM 3: 5.1.20
Return the dimension of the ST_Geometry value.
SQL-MM 3: 5.1.2
Test if an ST_Geometry value is spatially disjoint from another ST_Geometry value.
SQL-MM 3: 5.1.26
Return the distance between two geometries.
SQL-MM 3: 5.1.23
Return an ST_Point value that is the end point of an ST_Curve value.
SQL-MM 3: 7.1.4
Return the bounding rectangle for the ST_Geometry value.
SQL-MM 3: 5.1.15
Test if an ST_Geometry value as spatially equal to another ST_Geometry value.
SQL-MM 3: 5.1.24
Return the exterior ring of an ST_Surface
SQL-MM 3: 8.2.3, 8.3.3
Return the indicated ST_Geometry value from an ST_GeomCollection.
SQL-MM 3: 9.1.5
Return the geometry type of the ST_Geometry value.
SQL-MM 3: 5.1.4
Return a specified ST_Geometry value.
SQL-MM 3: 5.1.40
Return a specified ST_Geometry value.
SQL-MM 3: 5.1.41
Return the specified interior ring of an ST_Surface value.
SQL-MM 3: 8.2.6, 8.3.5
Return an ST_Geometry value that represents the point set intersection of two ST_Geometry values.
In other words - that portion of geometry A and geometry B that is shared between the two geometries.
SQL-MM 3: 5.1.18
Test if an ST_Geometry value spatially intersects another ST_Geometry value.
SQL-MM 3: 5.1.27
Test if an ST_Curve or ST_MultiCurve value is closed.
SQL-MM defines the result of ST_IsClosed(NULL) to be 0, while PostGIS returns NULL.
SQL-MM 3: 7.1.5, 9.3.3
Test if an ST_Geometry value corresponds to the empty set.
SQL-MM defines the result of ST_IsEmpty(NULL) to be 0, while PostGIS returns NULL.
SQL-MM 3: 5.1.7
Test if an ST_Curve value is a ring.
SQL-MM defines the result of ST_IsRing(NULL) to be 0, while PostGIS returns NULL.
SQL-MM 3: 7.1.6
Test if an ST_Geometry value has no anomalous geometric points, such as self intersection or self tangency.
SQL-MM defines the result of ST_IsSimple(NULL) to be 0, while PostGIS returns NULL.
SQL-MM 3: 5.1.8
Test if an ST_Geometry value is well formed.
SQL-MM defines the result of ST_IsValid(NULL) to be 0, while PostGIS returns NULL.
SQL-MM defines the result of ST_IsValid(NULL) to be 1
SQL-MM 3: 5.1.9
Return the length measurement of an ST_Curve or ST_MultiCurve value.
SQL-MM 3: 7.1.2, 9.3.4
Return a specified ST_LineString value.
SQL-MM 3: 7.2.8
Return a specified ST_LineString value.
SQL-MM 3: 7.2.9
Return a specified ST_MultiLineString value.
SQL-MM 3: 9.4.4
Return a specified ST_MultiLineString value.
SQL-MM 3: 9.4.5
Return a specified ST_MultiPoint value.
SQL-MM 3: 9.2.4
Return a specified ST_MultiPoint value.
SQL-MM 3: 9.2.5
Return a specified ST_MultiPolygon value.
SQL-MM 3: 9.6.4
Return a specified ST_MultiPolygon value.
SQL-MM 3: 9.6.5
Return the number of geometries in an ST_GeomCollection.
SQL-MM 3: 9.1.4
Return the number of interior rings in an ST_Surface.
SQL-MM 3: 8.2.5
Return the number of points in an ST_LineString or ST_CircularString value.
SQL-MM 3: 7.2.4
ST_OrderingEquals compares two geometries and t (TRUE) if the geometries are equal and the coordinates are in the same order; otherwise it returns f (FALSE).
This function is implemented as per the ArcSDE SQL specification rather than SQL-MM. http://edndoc.esri.com/arcsde/9.1/sql_api/sqlapi3.htm#ST_OrderingEquals
SQL-MM 3: 5.1.43
Test if an ST_Geometry value spatially overlays another ST_Geometry value.
SQL-MM 3: 5.1.32
Return the length measurement of the boundary of an ST_Surface or ST_MultiRSurface value.
SQL-MM 3: 8.1.3, 9.5.4
Returns an ST_Point with the given coordinate values.
SQL-MM 3: 6.1.2
Return a specified ST_Point value.
SQL-MM 3: 6.1.8
Return a specified ST_Point value.
SQL-MM 3: 6.1.9
Return the specified ST_Point value in an ST_LineString or ST_CircularString
SQL-MM 3: 7.2.5, 7.3.5
Return an ST_Point value guaranteed to spatially intersect the ST_Surface or ST_MultiSurface value.
SQL-MM 3: 8.1.5, 9.5.6
Return a specified ST_Polygon value.
SQL-MM 3: 8.3.6
Return a specified ST_Polygon value.
SQL-MM 3: 8.3.7
Return a polygon build from the specified linestring and SRID.
SQL-MM 3: 8.3.2
Test if an ST_Geometry value is spatially related to another ST_Geometry value.
SQL-MM 3: 5.1.25
Return the spatial reference system identifier of the ST_Geometry value.
SQL-MM 3: 5.1.5
Return an ST_Point value that is the start point of an ST_Curve value.
SQL-MM 3: 7.1.3
Return an ST_Geometry value that represents the point set symmetric difference of two ST_Geometry values.
SQL-MM 3: 5.1.21
Test if an ST_Geometry value spatially touches another ST_Geometry value.
SQL-MM 3: 5.1.28
Return an ST_Geometry value transformed to the specified spatial reference system.
SQL-MM 3: 5.1.6
Return an ST_Geometry value that represents the point set union of two ST_Geometry values.
SQL-MM 3: 5.1.19
Test if an ST_Geometry value is spatially within another ST_Geometry value.
SQL-MM 3: 5.1.30
Return an ST_Geometry value for a given well-known binary representation.
SQL-MM 3: 5.1.36
Return an ST_Geometry value for a given well-known text representation.
SQL-MM 3: 5.1.34
Returns the x coordinate value of an ST_Point value.
SQL-MM 3: 6.1.3
Returns the y coordinate value of an ST_Point value.
SQL-MM 3: 6.1.4
Additional functions have been added to improve support for an ArcSDE style interface.
Returns t (TRUE) if the envelopes of two geometries intersect; otherwise, it returns f (FALSE).
Test if a geometry value has z coordinate values.
Test if a geometry value has m coordinate values.
Return a derived geometry collection value with elements that match the specified measur.
Return a derived geometry collection value with elements that match the specified range of measures inclusively.
Returns the m coordinate value of an ST_Point value.
Returns the z coordinate value of an ST_Point value