Chapter 13. PostGIS Special Functions Index
Prev   Next

Chapter 13. PostGIS Special Functions Index

Table of Contents

13.1. PostGIS Aggregate Functions
13.2. PostGIS SQL-MM Compliant Functions
13.3. PostGIS Geography Support Functions
13.4. PostGIS Raster Support Functions
13.5. PostGIS Geometry / Geography / Raster Dump Functions
13.6. PostGIS Box Functions
13.7. PostGIS Functions that support 3D
13.8. PostGIS Curved Geometry Support Functions
13.9. PostGIS Polyhedral Surface Support Functions
13.10. PostGIS Function Support Matrix
13.11. New, Enhanced or changed PostGIS Functions
13.11.1. PostGIS Functions new, behavior changed, or enhanced in 2.0
13.11.2. PostGIS Functions changed behavior in 2.0
13.11.3. PostGIS Functions new, behavior changed, or enhanced in 1.5
13.11.4. PostGIS Functions new, behavior changed, or enhanced in 1.4
13.11.5. PostGIS Functions new in 1.3

13.1. PostGIS Aggregate Functions

The functions given below are spatial aggregate functions provided with PostGIS that can be used just like any other sql aggregate function such as sum, average.

  • ST_3DExtent - an aggregate function that returns the box3D bounding box that bounds rows of geometries.
  • ST_Accum - Aggregate. Constructs an array of geometries.
  • ST_Collect - Return a specified ST_Geometry value from a collection of other geometries.
  • ST_Extent - an aggregate function that returns the bounding box that bounds rows of geometries.
  • ST_MakeLine - Creates a Linestring from point or line geometries.
  • ST_MemUnion - Same as ST_Union, only memory-friendly (uses less memory and more processor time).
  • ST_Polygonize - Aggregate. Creates a GeometryCollection containing possible polygons formed from the constituent linework of a set of geometries.
  • ST_Union - Returns a geometry that represents the point set union of the Geometries.
  • ST_Union - Returns the union of a set of raster tiles into a single raster composed of 1 band. If no band is specified for unioning, band num 1 is assumed. The resulting raster's extent is the extent of the whole set. In the case of intersection, the resulting value is defined by p_expression which is one of the following: LAST - the default when none is specified, MEAN, SUM, FIRST, MAX, MIN.
  • ST_Union - Returns the union of a set of raster tiles into a single raster composed of 1 band. If no band is specified for unioning, band num 1 is assumed. The resulting raster's extent is the extent of the whole set. In the case of intersection, the resulting value is defined by p_expression which is one of the following: LAST - the default when none is specified, MEAN, SUM, FIRST, MAX, MIN.
  • ST_Union - Returns the union of a set of raster tiles into a single raster composed of 1 band. If no band is specified for unioning, band num 1 is assumed. The resulting raster's extent is the extent of the whole set. In the case of intersection, the resulting value is defined by p_expression which is one of the following: LAST - the default when none is specified, MEAN, SUM, FIRST, MAX, MIN.
  • TopoElementArray_Agg - Returns a topoelementarray for a set of element_id, type arrays (topoelements)

13.2. PostGIS SQL-MM Compliant Functions

The functions given below are PostGIS functions that conform to the SQL/MM 3 standard

[Note]

SQL-MM defines the default SRID of all geometry constructors as 0. PostGIS uses a default SRID of -1.

  • ST_3DDWithin - For 3d (z) geometry type Returns true if two geometries 3d distance is within number of units. This method implements the SQL/MM specification. SQL-MM ?
  • ST_3DDistance - For geometry type Returns the 3-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units. This method implements the SQL/MM specification. SQL-MM ?
  • ST_3DIntersects - Returns TRUE if the Geometries "spatially intersect" in 3d - only for points and linestrings This method implements the SQL/MM specification. SQL-MM 3: ?
  • ST_AddEdgeModFace - Add a new edge and, if in doing so it splits a face, modify the original face and add a new face. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.13
  • ST_AddEdgeNewFaces - Add a new edge and, if in doing so it splits a face, delete the original face and replace it with two new faces. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.12
  • ST_AddIsoEdge - Adds an isolated edge defined by geometry alinestring to a topology connecting two existing isolated nodes anode and anothernode and returns the edge id of the new edge. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.4
  • ST_AddIsoNode - Adds an isolated node to a face in a topology and returns the nodeid of the new node. If face is null, the node is still created. This method implements the SQL/MM specification. SQL-MM: Topo-Net Routines: X+1.3.1
  • ST_Area - Returns the area of the surface if it is a polygon or multi-polygon. For "geometry" type area is in SRID units. For "geography" area is in square meters. This method implements the SQL/MM specification. SQL-MM 3: 8.1.2, 9.5.3
  • ST_AsBinary - Return the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data. This method implements the SQL/MM specification. SQL-MM 3: 5.1.37
  • ST_AsText - Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata. This method implements the SQL/MM specification. SQL-MM 3: 5.1.25
  • ST_Boundary - Returns the closure of the combinatorial boundary of this Geometry. This method implements the SQL/MM specification. SQL-MM 3: 5.1.14
  • ST_Buffer - (T) For geometry: 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. For geography: Uses a planar transform wrapper. Introduced in 1.5 support for different end cap and mitre settings to control shape. buffer_style options: quad_segs=#,endcap=round|flat|square,join=round|mitre|bevel,mitre_limit=#.# This method implements the SQL/MM specification. SQL-MM 3: 5.1.17
  • ST_Centroid - Returns the geometric center of a geometry. This method implements the SQL/MM specification. SQL-MM 3: 8.1.4, 9.5.5
  • ST_ChangeEdgeGeom - Changes the shape of an edge without affecting the topology structure. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details X.3.6
  • ST_Contains - Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A. This method implements the SQL/MM specification. SQL-MM 3: 5.1.31
  • ST_ConvexHull - The convex hull of a geometry represents the minimum convex geometry that encloses all geometries within the set. This method implements the SQL/MM specification. SQL-MM 3: 5.1.16
  • ST_CoordDim - Return the coordinate dimension of the ST_Geometry value. This method implements the SQL/MM specification. SQL-MM 3: 5.1.3
  • ST_CreateTopoGeo - Adds a collection of geometries to a given empty topology and returns a message detailing success. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details -- X.3.18
  • ST_Crosses - Returns TRUE if the supplied geometries have some, but not all, interior points in common. This method implements the SQL/MM specification. SQL-MM 3: 5.1.29
  • ST_CurveToLine - Converts a CIRCULARSTRING/CURVEDPOLYGON to a LINESTRING/POLYGON This method implements the SQL/MM specification. SQL-MM 3: 7.1.7
  • ST_Difference - Returns a geometry that represents that part of geometry A that does not intersect with geometry B. This method implements the SQL/MM specification. SQL-MM 3: 5.1.20
  • ST_Dimension - The inherent dimension of this Geometry object, which must be less than or equal to the coordinate dimension. This method implements the SQL/MM specification. SQL-MM 3: 5.1.2
  • ST_Disjoint - Returns TRUE if the Geometries do not "spatially intersect" - if they do not share any space together. This method implements the SQL/MM specification. SQL-MM 3: 5.1.26
  • ST_Distance - For geometry type Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units. For geography type defaults to return spheroidal minimum distance between two geographies in meters. This method implements the SQL/MM specification. SQL-MM 3: 5.1.23
  • ST_EndPoint - Returns the last point of a LINESTRING geometry as a POINT. This method implements the SQL/MM specification. SQL-MM 3: 7.1.4
  • ST_Envelope - Returns a geometry representing the double precision (float8) bounding box of the supplied geometry. This method implements the SQL/MM specification. SQL-MM 3: 5.1.15
  • ST_Equals - Returns true if the given geometries represent the same geometry. Directionality is ignored. This method implements the SQL/MM specification. SQL-MM 3: 5.1.24
  • ST_ExteriorRing - Returns a line string representing the exterior ring of the POLYGON geometry. Return NULL if the geometry is not a polygon. Will not work with MULTIPOLYGON This method implements the SQL/MM specification. SQL-MM 3: 8.2.3, 8.3.3
  • ST_GMLToSQL - Return a specified ST_Geometry value from GML representation. This is an alias name for ST_GeomFromGML This method implements the SQL/MM specification. SQL-MM 3: 5.1.50 (except for curves support).
  • ST_GeomCollFromText - Makes a collection Geometry from collection WKT with the given SRID. If SRID is not give, it defaults to -1. This method implements the SQL/MM specification.
  • ST_GeomFromText - Return a specified ST_Geometry value from Well-Known Text representation (WKT). This method implements the SQL/MM specification. SQL-MM 3: 5.1.40
  • ST_GeomFromWKB - Creates a geometry instance from a Well-Known Binary geometry representation (WKB) and optional SRID. This method implements the SQL/MM specification. SQL-MM 3: 5.1.41
  • ST_GeometryFromText - Return a specified ST_Geometry value from Well-Known Text representation (WKT). This is an alias name for ST_GeomFromText This method implements the SQL/MM specification. SQL-MM 3: 5.1.40
  • ST_GeometryN - Return the 1-based Nth geometry if the geometry is a GEOMETRYCOLLECTION, (MULTI)POINT, (MULTI)LINESTRING, MULTICURVE or (MULTI)POLYGON, POLYHEDRALSURFACE Otherwise, return NULL. This method implements the SQL/MM specification. SQL-MM 3: 9.1.5
  • ST_GeometryType - Return the geometry type of the ST_Geometry value. This method implements the SQL/MM specification. SQL-MM 3: 5.1.4
  • ST_GetFaceEdges - Returns a set of ordered edges that bound aface includes the sequence order. This method implements the SQL/MM specification. SQL-MM 3 Topo-Geo and Topo-Net 3: Routine Details: X.3.5
  • ST_GetFaceGeometry - Returns the polygon in the given topology with the specified face id. This method implements the SQL/MM specification. SQL-MM 3 Topo-Geo and Topo-Net 3: Routine Details: X.3.16
  • ST_InitTopoGeo - Creates a new topology schema and registers this new schema in the topology.topology table and details summary of process. This method implements the SQL/MM specification. SQL-MM 3 Topo-Geo and Topo-Net 3: Routine Details: X.3.17
  • ST_InteriorRingN - Return the Nth interior linestring ring of the polygon geometry. Return NULL if the geometry is not a polygon or the given N is out of range. This method implements the SQL/MM specification. SQL-MM 3: 8.2.6, 8.3.5
  • ST_Intersection - (T) Returns a geometry that represents the shared portion of geomA and geomB. The geography implementation does a transform to geometry to do the intersection and then transform back to WGS84. This method implements the SQL/MM specification. SQL-MM 3: 5.1.18
  • ST_Intersects - Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space) and FALSE if they don't (they are Disjoint). For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect) This method implements the SQL/MM specification. SQL-MM 3: 5.1.27
  • ST_IsClosed - Returns TRUE if the LINESTRING's start and end points are coincident. For Polyhedral surface is closed (volumetric). This method implements the SQL/MM specification. SQL-MM 3: 7.1.5, 9.3.3
  • ST_IsEmpty - Returns true if this Geometry is an empty geometrycollection, polygon, point etc. This method implements the SQL/MM specification. SQL-MM 3: 5.1.7
  • ST_IsRing - Returns TRUE if this LINESTRING is both closed and simple. This method implements the SQL/MM specification. SQL-MM 3: 7.1.6
  • ST_IsSimple - Returns (TRUE) if this Geometry has no anomalous geometric points, such as self intersection or self tangency. This method implements the SQL/MM specification. SQL-MM 3: 5.1.8
  • ST_IsValid - Returns true if the ST_Geometry is well formed. This method implements the SQL/MM specification. SQL-MM 3: 5.1.9
  • ST_Length - Returns the 2d length of the geometry if it is a linestring or multilinestring. geometry are in units of spatial reference and geography are in meters (default spheroid) This method implements the SQL/MM specification. SQL-MM 3: 7.1.2, 9.3.4
  • ST_LineFromText - Makes a Geometry from WKT representation with the given SRID. If SRID is not given, it defaults to -1. This method implements the SQL/MM specification. SQL-MM 3: 7.2.8
  • ST_LineFromWKB - Makes a LINESTRING from WKB with the given SRID This method implements the SQL/MM specification. SQL-MM 3: 7.2.9
  • ST_LinestringFromWKB - Makes a geometry from WKB with the given SRID. This method implements the SQL/MM specification. SQL-MM 3: 7.2.9
  • ST_M - Return the M coordinate of the point, or NULL if not available. Input must be a point. This method implements the SQL/MM specification.
  • ST_MLineFromText - Return a specified ST_MultiLineString value from WKT representation. This method implements the SQL/MM specification.SQL-MM 3: 9.4.4
  • ST_MPointFromText - Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1. This method implements the SQL/MM specification. SQL-MM 3: 9.2.4
  • ST_MPolyFromText - Makes a MultiPolygon Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1. This method implements the SQL/MM specification. SQL-MM 3: 9.6.4
  • ST_ModEdgeHeal - Heal two edges by deleting the node connecting them, modifying the first edgeand deleting the second edge. Returns the id of the deleted node. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.9
  • ST_ModEdgeSplit - Split an edge by creating a new node along an existing edge, modifying the original edge and adding a new edge. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.9
  • ST_MoveIsoNode - Moves an isolated node in a topology from one point to another. If new apoint geometry exists as a node an error is thrown. REturns description of move. This method implements the SQL/MM specification. SQL-MM: Topo-Net Routines: X.3.2
  • ST_NewEdgeHeal - Heal two edges by deleting the node connecting them, deleting both edges,and replacing them with an edge whose direction is the same as the firstedge provided. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.9
  • ST_NewEdgesSplit - Split an edge by creating a new node along an existing edge, deleting the original edge and replacing it with two new edges. Returns the id of the new node created that joins the new edges. This method implements the SQL/MM specification. SQL-MM: Topo-Net Routines: X.3.8
  • ST_NumGeometries - If geometry is a GEOMETRYCOLLECTION (or MULTI*) return the number of geometries, for single geometries will return 1, otherwise return NULL. This method implements the SQL/MM specification. SQL-MM 3: 9.1.4
  • ST_NumInteriorRings - Return the number of interior rings of the a polygon in the geometry. This will work with POLYGON and return NULL for a MULTIPOLYGON type or any other type This method implements the SQL/MM specification. SQL-MM 3: 8.2.5
  • ST_NumPatches - Return the number of faces on a Polyhedral Surface. Will return null for non-polyhedral geometries. This method implements the SQL/MM specification. SQL-MM 3: ?
  • ST_NumPoints - Return the number of points in an ST_LineString or ST_CircularString value. This method implements the SQL/MM specification. SQL-MM 3: 7.2.4
  • ST_OrderingEquals - Returns true if the given geometries represent the same geometry and points are in the same directional order. This method implements the SQL/MM specification. SQL-MM 3: 5.1.43
  • ST_Overlaps - Returns TRUE if the Geometries share space, are of the same dimension, but are not completely contained by each other. This method implements the SQL/MM specification. SQL-MM 3: 5.1.32
  • ST_PatchN - Return the 1-based Nth geometry (face) if the geometry is a POLYHEDRALSURFACE, POLYHEDRALSURFACEM. Otherwise, return NULL. This method implements the SQL/MM specification. SQL-MM 3: ?
  • ST_Perimeter - Return the length measurement of the boundary of an ST_Surface or ST_MultiSurface geometry or geography. (Polygon, Multipolygon). geometry measurement is in units of spatial reference and geography is in meters. This method implements the SQL/MM specification. SQL-MM 3: 8.1.3, 9.5.4
  • ST_Point - Returns an ST_Point with the given coordinate values. OGC alias for ST_MakePoint. This method implements the SQL/MM specification. SQL-MM 3: 6.1.2
  • ST_PointFromText - Makes a point Geometry from WKT with the given SRID. If SRID is not given, it defaults to unknown. This method implements the SQL/MM specification. SQL-MM 3: 6.1.8
  • ST_PointFromWKB - Makes a geometry from WKB with the given SRID This method implements the SQL/MM specification. SQL-MM 3: 6.1.9
  • ST_PointN - Return the Nth point in a linestring or circular linestring in the geometry. Return NULL if geometry is not a linestring or circular linestring This method implements the SQL/MM specification. SQL-MM 3: 7.2.5, 7.3.5
  • ST_PointOnSurface - Returns a POINT guaranteed to lie on the surface. This method implements the SQL/MM specification. SQL-MM 3: 8.1.5, 9.5.6. According to the specs, ST_PointOnSurface works for surface geometries (POLYGONs, MULTIPOLYGONS, CURVED POLYGONS). So PostGIS seems to be extending what the spec allows here. Most databases Oracle,DB II, ESRI SDE seem to only support this function for surfaces. SQL Server 2008 like PostGIS supports for all common geometries.
  • ST_Polygon - Returns a polygon built from the specified linestring and SRID. This method implements the SQL/MM specification. SQL-MM 3: 8.3.2
  • ST_PolygonFromText - Makes a Geometry from WKT with the given SRID. If SRID is not give, it defaults to -1. This method implements the SQL/MM specification. SQL-MM 3: 8.3.6
  • ST_Relate - Returns 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 intersectionMatrixPattern. If no intersectionMatrixPattern is passed in, then returns the maximum intersectionMatrixPattern that relates the 2 geometries. This method implements the SQL/MM specification. SQL-MM 3: 5.1.25
  • ST_RemEdgeModFace - Removes an edge and, if the removed edge separated two faces,delete one of the them and modify the other to take the space of both. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.15
  • ST_RemEdgeNewFace - Removes an edge and, if the removed edge separated two faces,delete the original faces and replace them with a new face. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.14
  • ST_RemoveIsoNode - Removes an isolated node and returns description of action. If the node is not isolated (is start or end of an edge), then an exception is thrown. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X+1.3.3
  • ST_SRID - Returns the spatial reference identifier for the ST_Geometry as defined in spatial_ref_sys table. This method implements the SQL/MM specification. SQL-MM 3: 5.1.5
  • ST_StartPoint - Returns the first point of a LINESTRING geometry as a POINT. This method implements the SQL/MM specification. SQL-MM 3: 7.1.3
  • ST_SymDifference - 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). This method implements the SQL/MM specification. SQL-MM 3: 5.1.21
  • ST_Touches - Returns TRUE if the geometries have at least one point in common, but their interiors do not intersect. This method implements the SQL/MM specification. SQL-MM 3: 5.1.28
  • ST_Transform - Returns a new geometry with its coordinates transformed to the SRID referenced by the integer parameter. This method implements the SQL/MM specification. SQL-MM 3: 5.1.6
  • ST_Union - Returns a geometry that represents the point set union of the Geometries. This method implements the SQL/MM specification. SQL-MM 3: 5.1.19 the z-index (elevation) when polygons are involved.
  • ST_WKBToSQL - Return a specified ST_Geometry value from Well-Known Binary representation (WKB). This is an alias name for ST_GeomFromWKB that takes no srid This method implements the SQL/MM specification. SQL-MM 3: 5.1.36
  • ST_WKTToSQL - Return a specified ST_Geometry value from Well-Known Text representation (WKT). This is an alias name for ST_GeomFromText This method implements the SQL/MM specification. SQL-MM 3: 5.1.34
  • ST_Within - Returns true if the geometry A is completely inside geometry B This method implements the SQL/MM specification. SQL-MM 3: 5.1.30
  • ST_X - Return the X coordinate of the point, or NULL if not available. Input must be a point. This method implements the SQL/MM specification. SQL-MM 3: 6.1.3
  • ST_Y - Return the Y coordinate of the point, or NULL if not available. Input must be a point. This method implements the SQL/MM specification. SQL-MM 3: 6.1.4
  • ST_Z - Return the Z coordinate of the point, or NULL if not available. Input must be a point. This method implements the SQL/MM specification.

13.3. PostGIS Geography Support Functions

The functions and operators given below are PostGIS functions/operators that take as input or return as output a geography data type object.

[Note]

Functions with a (T) are not native geodetic functions, and use a ST_Transform call to and from geometry to do the operation. As a result, they may not behave as expected when going over dateline, poles, and for large geometries or geometry pairs that cover more than one UTM zone. Basic tranform - (favoring UTM, Lambert Azimuthal (North/South), and falling back on mercator in worst case scenario)

  • ST_Area - Returns the area of the surface if it is a polygon or multi-polygon. For "geometry" type area is in SRID units. For "geography" area is in square meters.
  • ST_AsBinary - Return the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsEWKT - Return the Well-Known Text (WKT) representation of the geometry with SRID meta data.
  • ST_AsGML - Return the geometry as a GML version 2 or 3 element.
  • ST_AsGeoJSON - Return the geometry as a GeoJSON element.
  • ST_AsKML - Return the geometry as a KML element. Several variants. Default version=2, default precision=15
  • ST_AsSVG - Returns a Geometry in SVG path data given a geometry or geography object.
  • ST_AsText - Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.
  • ST_Azimuth - Returns the north-based azimuth as the angle in radians measured clockwise from the vertical on pointA to pointB.
  • ST_Buffer - (T) For geometry: 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. For geography: Uses a planar transform wrapper. Introduced in 1.5 support for different end cap and mitre settings to control shape. buffer_style options: quad_segs=#,endcap=round|flat|square,join=round|mitre|bevel,mitre_limit=#.#
  • ST_CoveredBy - Returns 1 (TRUE) if no point in Geometry/Geography A is outside Geometry/Geography B
  • ST_Covers - Returns 1 (TRUE) if no point in Geometry B is outside Geometry A
  • ST_DWithin - Returns true if the geometries are within the specified distance of one another. For geometry units are in those of spatial reference and For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around spheroid), for faster check, use_spheroid=false to measure along sphere.
  • ST_Distance - For geometry type Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units. For geography type defaults to return spheroidal minimum distance between two geographies in meters.
  • ST_GeogFromText - Return a specified geography value from Well-Known Text representation or extended (WKT).
  • ST_GeogFromWKB - Creates a geography instance from a Well-Known Binary geometry representation (WKB) or extended Well Known Binary (EWKB).
  • ST_GeographyFromText - Return a specified geography value from Well-Known Text representation or extended (WKT).
  • = - Returns TRUE if A's bounding box is the same as B's. Uses double precision bounding box.
  • ST_Intersection - (T) Returns a geometry that represents the shared portion of geomA and geomB. The geography implementation does a transform to geometry to do the intersection and then transform back to WGS84.
  • ST_Intersects - Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space) and FALSE if they don't (they are Disjoint). For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect)
  • ST_Length - Returns the 2d length of the geometry if it is a linestring or multilinestring. geometry are in units of spatial reference and geography are in meters (default spheroid)
  • ST_Perimeter - Return the length measurement of the boundary of an ST_Surface or ST_MultiSurface geometry or geography. (Polygon, Multipolygon). geometry measurement is in units of spatial reference and geography is in meters.
  • ST_Project - Returns a POINT projected from a start point using a distance in meters and bearing (azimuth) in radians.
  • ST_Summary - Returns a text summary of the contents of the geometry.
  • && - Returns TRUE if A's 2D bounding box intersects B's 2D bounding box.

13.4. PostGIS Raster Support Functions

The functions and operators given below are PostGIS functions/operators that take as input or return as output a raster data type object. Listed in alphabetical order.

  • Box3D - Returns the box 3d representation of the enclosing box of the raster.
  • &< - Returns TRUE if A's bounding box is to the left of B's.
  • &> - Returns TRUE if A's bounding box is to the right of B's.
  • && - Returns TRUE if A's bounding box overlaps B's.
  • ST_AddBand - Returns a raster with the new band(s) of given type added with given initial value in the given index location. If no index is specified, the band is added to the end.
  • ST_AsBinary - Return the Well-Known Binary (WKB) representation of the raster without SRID meta data.
  • ST_AsGDALRaster - Return the raster tile in the designated GDAL Raster format. Raster formats are one of those supported by your compiled library. Use ST_GDALRasters() to get a list of formats supported by your library.
  • ST_AsJPEG - Return the raster tile selected bands as a single Joint Photographic Exports Group (JPEG) image (byte array). If no band is specified and 1 or more than 3 bands, then only the first band is used. If only 3 bands then all 3 bands are used and mapped to RGB.
  • ST_AsPNG - Return the raster tile selected bands as a single portable network graphics (PNG) image (byte array). If 1, 3, or 4 bands in raster and no bands are specified, then all bands are used. If more 2 or more than 4 bands and no bands specified, then only band 1 is used. Bands are mapped to RGB or RGBA space.
  • ST_AsRaster - Converts a PostGIS geometry to a PostGIS raster.
  • ST_AsTIFF - Return the raster selected bands as a single TIFF image (byte array). If no band is specified, then will try to use all bands.
  • ST_Aspect - Returns the surface aspect of an elevation raster band. Useful for analyzing terrain.
  • ST_Band - Returns one or more bands of an existing raster as a new raster. Useful for building new rasters from existing rasters.
  • ST_BandIsNoData - Returns true if the band is filled with only nodata values.
  • ST_BandMetaData - Returns basic meta data for a specific raster band. band num 1 is assumed if none-specified.
  • ST_BandNoDataValue - Returns the value in a given band that represents no data. If no band num 1 is assumed.
  • ST_BandPath - Returns system file path to a band stored in file system. If no bandnum specified, 1 is assumed.
  • ST_BandPixelType - Returns the type of pixel for given band. If no bandnum specified, 1 is assumed.
  • ST_Clip - Returns the raster clipped by the input geometry. If no band is specified all bands are returned. If crop is not specified, true is assumed meaning the output raster is cropped.
  • ST_ConvexHull - Return the convex hull geometry of the raster including pixel values equal to BandNoDataValue. For regular shaped and non-skewed rasters, this gives the same result as ST_Envelope so only useful for irregularly shaped or skewed rasters.
  • ST_Count - Returns the number of pixels in a given band of a raster or raster coverage. If no band is specified defaults to band 1. If exclude_nodata_value is set to true, will only count pixels that are not equal to the nodata value.
  • ST_DumpAsPolygons - Returns a set of geomval (geom,val) rows, from a given raster band. If no band number is specified, band num defaults to 1.
  • ST_Envelope - Returns the polygon representation of the extent of the raster.
  • ST_GeoReference - Returns the georeference meta data in GDAL or ESRI format as commonly seen in a world file. Default is GDAL.
  • ST_HasNoBand - Returns true if there is no band with given band number. If no band number is specified, then band number 1 is assumed.
  • ST_Height - Returns the height of the raster in pixels.
  • ST_HillShade - Returns the hypothetical illumination of an elevation raster band using provided azimuth, altitude, brightness and elevation scale inputs. Useful for visualizing terrain.
  • ST_Histogram - Returns a set of histogram summarizing a raster or raster coverage data distribution separate bin ranges. Number of bins are autocomputed if not specified.
  • ST_Intersection - Returns a raster or a set of geometry-pixelvalue pairs representing the shared portion of two rasters or the geometrical intersection of a vectorization of the raster and a geometry.
  • ST_Intersects - Return true if the raster spatially intersects a separate raster or geometry. If the band number is not provided (or set to NULL), only the convex hull of the raster is considered in the test. If the band number is provided, only those pixels with value (not NODATA) are considered in the test.
  • ST_IsEmpty - Returns true if the raster is empty (width = 0 and height = 0). Otherwise, returns false.
  • ST_MakeEmptyRaster - Returns an empty raster (having no bands) of given dimensions (width & height), upperleft X and Y, pixel size and rotation (scalex, scaley, skewx & skewy) and reference system (srid). If a raster is passed in, returns a new raster with the same size, alignment and SRID. If srid is left out, the spatial ref is set to unknown (0).
  • ST_MapAlgebraExpr - 1 raster band version: Creates a new one band raster formed by applying a valid PostgreSQL algebraic operation on the input raster band and of pixeltype provided. Band 1 is assumed if no band is specified.
  • ST_MapAlgebraExpr - 2 raster band version: Creates a new one band raster formed by applying a valid PostgreSQL algebraic operation on the two input raster bands and of pixeltype provided. band 1 of each raster is assumed if no band numbers are specified. The resulting raster will be aligned (scale, skew and pixel corners) on the grid defined by the first raster and have its extent defined by the "extenttype" parameter. Values for "extenttype" can be: INTERSECTION, UNION, FIRST, SECOND.
  • ST_MapAlgebraFct - 1 band version - Creates a new one band raster formed by applying a valid PostgreSQL function on the input raster band and of pixeltype prodived. Band 1 is assumed if no band is specified.
  • ST_MapAlgebraFct - 2 band version - Creates a new one band raster formed by applying a valid PostgreSQL function on the 2 input raster bands and of pixeltype prodived. Band 1 is assumed if no band is specified. Extent type defaults to INTERSECTION if not specified.
  • ST_MapAlgebraFctNgb - 1-band version: Map Algebra Nearest Neighbor using user-defined PostgreSQL function. Return a raster which values are the result of a PLPGSQL user function involving a neighborhood of values from the input raster band.
  • ST_MetaData - Returns basic meta data about a raster object such as pixel size, rotation (skew), upper, lower left, etc.
  • ST_NumBands - Returns the number of bands in the raster object.
  • ST_PixelAsPolygon - Returns the geometry that bounds the pixel for a particular row and column.
  • ST_PixelAsPolygons - Returns the geometry that bounds every pixel of a raster band along with the value, the X and the Y raster coordinates of each pixel.
  • ST_PixelHeight - Returns the pixel height in geometric units of the spatial reference system.
  • ST_PixelWidth - Returns the pixel width in geometric units of the spatial reference system.
  • ST_Polygon - Returns a polygon geometry formed by the union of pixels that have a pixel value that is not no data value. If no band number is specified, band num defaults to 1.
  • ST_Quantile - Compute quantiles for a raster or raster table coverage in the context of the sample or population. Thus, a value could be examined to be at the raster's 25%, 50%, 75% percentile.
  • ST_Raster2WorldCoordX - Returns the geometric X coordinate upper left of a raster, column and row. Numbering of columns and rows starts at 1.
  • ST_Raster2WorldCoordY - Returns the geometric Y coordinate upper left corner of a raster, column and row. Numbering of columns and rows starts at 1.
  • ST_Reclass - Creates a new raster composed of band types reclassified from original. The nband is the band to be changed. If nband is not specified assumed to be 1. All other bands are returned unchanged. Use case: convert a 16BUI band to a 8BUI and so forth for simpler rendering as viewable formats.
  • ST_Resample - Resample a raster using a specified resampling algorithm, new dimensions, an arbitrary grid corner and a set of raster georeferencing attributes defined or borrowed from another raster. New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor.
  • ST_Rescale - Resample a raster by adjusting only its scale (or pixel size). New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor.
  • ST_Reskew - Resample a raster by adjusting only its skew (or rotation parameters). New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor.
  • ST_Rotation - Returns the rotation of the raster in radian.
  • ST_SRID - Returns the spatial reference identifier of the raster as defined in spatial_ref_sys table.
  • ST_SameAlignment - Returns true if rasters have same skew, scale, spatial ref and false if they don't with notice detailing issue.
  • ST_ScaleX - Returns the X component of the pixel width in units of coordinate reference system.
  • ST_ScaleY - Returns the Y component of the pixel height in units of coordinate reference system.
  • ST_SetBandIsNoData - Sets the isnodata flag of the band to TRUE.
  • ST_SetBandNoDataValue - Sets the value for the given band that represents no data. Band 1 is assumed if no band is specified. To mark a band as having no nodata value, set the nodata value = NULL.
  • ST_SetGeoReference - Set Georeference 6 georeference parameters in a single call. Numbers should be separated by white space. Accepts inputs in GDAL or ESRI format. Default is GDAL.
  • ST_SetRotation - Set the rotation of the raster in radian.
  • ST_SetSRID - Sets the SRID of a raster to a particular integer srid defined in the spatial_ref_sys table.
  • ST_SetScale - Sets the X and Y size of pixels in units of coordinate reference system. Number units/pixel width/height.
  • ST_SetSkew - Sets the georeference X and Y skew (or rotation parameter). If only one is passed in, sets X and Y to the same value.
  • ST_SetUpperLeft - Sets the value of the upper left corner of the pixel to projected X and Y coordinates.
  • ST_SetValue - Returns modified raster resulting from setting the value of a given band in a given columnx, rowy pixel or at a pixel that intersects a particular geometric point. Band numbers start at 1 and assumed to be 1 if not specified.
  • ST_SkewX - Returns the georeference X skew (or rotation parameter).
  • ST_SkewY - Returns the georeference Y skew (or rotation parameter).
  • ST_Slope - Returns the surface slope of an elevation raster band. Useful for analyzing terrain.
  • ST_SnapToGrid - Resample a raster by snapping it to a grid. New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor.
  • ST_SummaryStats - Returns summary stats consisting of count,sum,mean,stddev,min,max for a given raster band of a raster or raster coverage. Band 1 is assumed is no band is specified.
  • ST_Transform - Reprojects a raster in a known spatial reference system to another known spatial reference system using specified resampling algorithm. Options are NearestNeighbor, Bilinear, Cubic, CubicSpline, Lanczos defaulting to NearestNeighbor.
  • ST_Union - Returns the union of a set of raster tiles into a single raster composed of 1 band. If no band is specified for unioning, band num 1 is assumed. The resulting raster's extent is the extent of the whole set. In the case of intersection, the resulting value is defined by p_expression which is one of the following: LAST - the default when none is specified, MEAN, SUM, FIRST, MAX, MIN.
  • ST_UpperLeftX - Returns the upper left X coordinate of raster in projected spatial ref.
  • ST_UpperLeftY - Returns the upper left Y coordinate of raster in projected spatial ref.
  • ST_Value - Returns the value of a given band in a given columnx, rowy pixel or at a particular geometric point. Band numbers start at 1 and assumed to be 1 if not specified. If exclude_nodata_value is set to false, then all pixels include nodata pixels are considered to intersect and return value. If exclude_nodata_value is not passed in then reads it from metadata of raster.
  • ST_ValueCount - Returns a set of records containing a pixel band value and count of the number of pixels in a given band of a raster (or a raster coverage) that have a given set of values. If no band is specified defaults to band 1. By default nodata value pixels are not counted. and all other values in the pixel are output and pixel band values are rounded to the nearest integer.
  • ST_Width - Returns the width of the raster in pixels.
  • ST_World2RasterCoordX - Returns the column in the raster of the point geometry (pt) or a X and Y world coordinate (xw, yw) represented in world spatial reference system of raster.
  • ST_World2RasterCoordY - Returns the row in the raster of the point geometry (pt) or a X and Y world coordinate (xw, yw) represented in world spatial reference system of raster.

13.5. PostGIS Geometry / Geography / Raster Dump Functions

The functions given below are PostGIS functions that take as input or return as output a set of or single geometry_dump or geomval data type object.

  • ST_DumpAsPolygons - Returns a set of geomval (geom,val) rows, from a given raster band. If no band number is specified, band num defaults to 1.
  • ST_Intersection - Returns a raster or a set of geometry-pixelvalue pairs representing the shared portion of two rasters or the geometrical intersection of a vectorization of the raster and a geometry.
  • ST_Dump - Returns a set of geometry_dump (geom,path) rows, that make up a geometry g1.
  • ST_DumpPoints - Returns a set of geometry_dump (geom,path) rows of all points that make up a geometry.
  • ST_DumpRings - Returns a set of geometry_dump rows, representing the exterior and interior rings of a polygon.

13.6. PostGIS Box Functions

The functions given below are PostGIS functions that take as input or return as output the box* family of PostGIS spatial types. The box family of types consists of box2d, and box3d

  • Box2D - Returns a BOX2D representing the maximum extents of the geometry.
  • Box3D - Returns a BOX3D representing the maximum extents of the geometry.
  • Box3D - Returns the box 3d representation of the enclosing box of the raster.
  • ST_3DExtent - an aggregate function that returns the box3D bounding box that bounds rows of geometries.
  • ST_3DMakeBox - Creates a BOX3D defined by the given 3d point geometries.
  • ST_Estimated_Extent - 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.
  • ST_Expand - Returns bounding box expanded in all directions from the bounding box of the input geometry. Uses double-precision
  • ST_Extent - an aggregate function that returns the bounding box that bounds rows of geometries.
  • ST_MakeBox2D - Creates a BOX2D defined by the given point geometries.
  • ST_XMax - Returns X maxima of a bounding box 2d or 3d or a geometry.
  • ST_XMin - Returns X minima of a bounding box 2d or 3d or a geometry.
  • ST_YMax - Returns Y maxima of a bounding box 2d or 3d or a geometry.
  • ST_YMin - Returns Y minima of a bounding box 2d or 3d or a geometry.
  • ST_ZMax - Returns Z minima of a bounding box 2d or 3d or a geometry.
  • ST_ZMin - Returns Z minima of a bounding box 2d or 3d or a geometry.

13.7. PostGIS Functions that support 3D

The functions given below are PostGIS functions that do not throw away the Z-Index.

  • AddGeometryColumn - Adds a geometry column to an existing table of attributes. By default uses type modifier to define rather than constraints. Pass in false for use_typmod to get old check constraint based behavior
  • Box3D - Returns a BOX3D representing the maximum extents of the geometry.
  • DropGeometryColumn - Removes a geometry column from a spatial table.
  • GeometryType - Returns the type of the geometry as a string. Eg: 'LINESTRING', 'POLYGON', 'MULTIPOINT', etc.
  • ST_3DClosestPoint - Returns the 3-dimensional point on g1 that is closest to g2. This is the first point of the 3D shortest line.
  • ST_3DDFullyWithin - Returns true if all of the 3D geometries are within the specified distance of one another.
  • ST_3DDWithin - For 3d (z) geometry type Returns true if two geometries 3d distance is within number of units.
  • ST_3DDistance - For geometry type Returns the 3-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DExtent - an aggregate function that returns the box3D bounding box that bounds rows of geometries.
  • ST_3DIntersects - Returns TRUE if the Geometries "spatially intersect" in 3d - only for points and linestrings
  • ST_3DLength - Returns the 3-dimensional or 2-dimensional length of the geometry if it is a linestring or multi-linestring.
  • ST_3DLength_Spheroid - Calculates the length of a geometry on an ellipsoid, taking the elevation into account. This is just an alias for ST_Length_Spheroid.
  • ST_3DLongestLine - Returns the 3-dimensional longest line between two geometries
  • ST_3DMakeBox - Creates a BOX3D defined by the given 3d point geometries.
  • ST_3DMaxDistance - For geometry type Returns the 3-dimensional cartesian maximum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DPerimeter - Returns the 3-dimensional perimeter of the geometry, if it is a polygon or multi-polygon.
  • ST_3DShortestLine - Returns the 3-dimensional shortest line between two geometries
  • ST_Accum - Aggregate. Constructs an array of geometries.
  • ST_AddMeasure - Return a derived geometry with measure elements linearly interpolated between the start and end points. If the geometry has no measure dimension, one is added. If the geometry has a measure dimension, it is over-written with new values. Only LINESTRINGS and MULTILINESTRINGS are supported.
  • ST_AddPoint - Adds a point to a LineString before point <position> (0-based index).
  • ST_Affine - Applies a 3d affine transformation to the geometry to do things like translate, rotate, scale in one step.
  • ST_AsBinary - Return the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsEWKB - Return the Well-Known Binary (WKB) representation of the geometry with SRID meta data.
  • ST_AsEWKT - Return the Well-Known Text (WKT) representation of the geometry with SRID meta data.
  • ST_AsGML - Return the geometry as a GML version 2 or 3 element.
  • ST_AsGeoJSON - Return the geometry as a GeoJSON element.
  • ST_AsHEXEWKB - Returns a Geometry in HEXEWKB format (as text) using either little-endian (NDR) or big-endian (XDR) encoding.
  • ST_AsKML - Return the geometry as a KML element. Several variants. Default version=2, default precision=15
  • ST_AsX3D - Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML
  • ST_Boundary - Returns the closure of the combinatorial boundary of this Geometry.
  • ST_Collect - Return a specified ST_Geometry value from a collection of other geometries.
  • ST_ConvexHull - The convex hull of a geometry represents the minimum convex geometry that encloses all geometries within the set.
  • ST_CoordDim - Return the coordinate dimension of the ST_Geometry value.
  • ST_CurveToLine - Converts a CIRCULARSTRING/CURVEDPOLYGON to a LINESTRING/POLYGON
  • ST_Difference - Returns a geometry that represents that part of geometry A that does not intersect with geometry B.
  • ST_Dump - Returns a set of geometry_dump (geom,path) rows, that make up a geometry g1.
  • ST_DumpPoints - Returns a set of geometry_dump (geom,path) rows of all points that make up a geometry.
  • ST_DumpRings - Returns a set of geometry_dump rows, representing the exterior and interior rings of a polygon.
  • ST_EndPoint - Returns the last point of a LINESTRING geometry as a POINT.
  • ST_ExteriorRing - Returns a line string representing the exterior ring of the POLYGON geometry. Return NULL if the geometry is not a polygon. Will not work with MULTIPOLYGON
  • ST_FlipCoordinates - Returns a version of the given geometry with X and Y axis flipped. Useful for people who have built latitude/longitude features and need to fix them.
  • ST_ForceRHR - Forces the orientation of the vertices in a polygon to follow the Right-Hand-Rule.
  • ST_Force_2D - Forces the geometries into a "2-dimensional mode" so that all output representations will only have the X and Y coordinates.
  • ST_Force_3D - Forces the geometries into XYZ mode. This is an alias for ST_Force_3DZ.
  • ST_Force_3DZ - Forces the geometries into XYZ mode. This is a synonym for ST_Force_3D.
  • ST_Force_4D - Forces the geometries into XYZM mode.
  • ST_Force_Collection - Converts the geometry into a GEOMETRYCOLLECTION.
  • ST_GeomFromEWKB - Return a specified ST_Geometry value from Extended Well-Known Binary representation (EWKB).
  • ST_GeomFromEWKT - Return a specified ST_Geometry value from Extended Well-Known Text representation (EWKT).
  • ST_GeomFromGML - Takes as input GML representation of geometry and outputs a PostGIS geometry object
  • ST_GeomFromGeoJSON - Takes as input a geojson representation of a geometry and outputs a PostGIS geometry object
  • ST_GeomFromKML - Takes as input KML representation of geometry and outputs a PostGIS geometry object
  • ST_GeometryN - Return the 1-based Nth geometry if the geometry is a GEOMETRYCOLLECTION, (MULTI)POINT, (MULTI)LINESTRING, MULTICURVE or (MULTI)POLYGON, POLYHEDRALSURFACE Otherwise, return NULL.
  • ST_GeometryType - Return the geometry type of the ST_Geometry value.
  • ST_HasArc - Returns true if a geometry or geometry collection contains a circular string
  • ST_InteriorRingN - Return the Nth interior linestring ring of the polygon geometry. Return NULL if the geometry is not a polygon or the given N is out of range.
  • ST_InterpolatePoint - Return the value of the measure dimension of a geometry at the point closed to the provided point.
  • ST_IsClosed - Returns TRUE if the LINESTRING's start and end points are coincident. For Polyhedral surface is closed (volumetric).
  • ST_IsCollection - Returns TRUE if the argument is a collection (MULTI*, GEOMETRYCOLLECTION, ...)
  • ST_IsSimple - Returns (TRUE) if this Geometry has no anomalous geometric points, such as self intersection or self tangency.
  • ST_Length_Spheroid - Calculates the 2D or 3D length of a linestring/multilinestring on an ellipsoid. This is useful if the coordinates of the geometry are in longitude/latitude and a length is desired without reprojection.
  • ST_LineFromMultiPoint - Creates a LineString from a MultiPoint geometry.
  • ST_LineToCurve - Converts a LINESTRING/POLYGON to a CIRCULARSTRING, CURVED POLYGON
  • ST_Line_Interpolate_Point - Returns a point interpolated along a line. Second argument is a float8 between 0 and 1 representing fraction of total length of linestring the point has to be located.
  • ST_Line_Substring - 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.
  • ST_LocateBetweenElevations - Return a derived geometry (collection) value with elements that intersect the specified range of elevations inclusively. Only 3D, 4D LINESTRINGS and MULTILINESTRINGS are supported.
  • ST_M - Return the M coordinate of the point, or NULL if not available. Input must be a point.
  • ST_MakeLine - Creates a Linestring from point or line geometries.
  • ST_MakePoint - Creates a 2D,3DZ or 4D point geometry.
  • ST_MakePolygon - Creates a Polygon formed by the given shell. Input geometries must be closed LINESTRINGS.
  • ST_MakeValid - Attempts to make an invalid geometry valid without losing vertices.
  • ST_MemUnion - Same as ST_Union, only memory-friendly (uses less memory and more processor time).
  • ST_Mem_Size - Returns the amount of space (in bytes) the geometry takes.
  • ST_NDims - Returns coordinate dimension of the geometry as a small int. Values are: 2,3 or 4.
  • ST_NPoints - Return the number of points (vertexes) in a geometry.
  • ST_NRings - If the geometry is a polygon or multi-polygon returns the number of rings.
  • ST_Node - Node a set of linestrings.
  • ST_NumGeometries - If geometry is a GEOMETRYCOLLECTION (or MULTI*) return the number of geometries, for single geometries will return 1, otherwise return NULL.
  • ST_NumPatches - Return the number of faces on a Polyhedral Surface. Will return null for non-polyhedral geometries.
  • ST_PatchN - Return the 1-based Nth geometry (face) if the geometry is a POLYHEDRALSURFACE, POLYHEDRALSURFACEM. Otherwise, return NULL.
  • ST_PointFromWKB - Makes a geometry from WKB with the given SRID
  • ST_PointN - Return the Nth point in a linestring or circular linestring in the geometry. Return NULL if geometry is not a linestring or circular linestring
  • ST_PointOnSurface - Returns a POINT guaranteed to lie on the surface.
  • ST_Polygon - Returns a polygon built from the specified linestring and SRID.
  • ST_RemovePoint - Removes point from a linestring. Offset is 0-based.
  • ST_RemoveRepeatedPoints - Returns a version of the given geometry with duplicated points removed.
  • ST_Rotate - Rotate a geometry rotRadians counter-clockwise about an origin.
  • ST_RotateX - Rotate a geometry rotRadians about the X axis.
  • ST_RotateY - Rotate a geometry rotRadians about the Y axis.
  • ST_RotateZ - Rotate a geometry rotRadians about the Z axis.
  • ST_Scale - Scales the geometry to a new size by multiplying the ordinates with the parameters. Ie: ST_Scale(geom, Xfactor, Yfactor, Zfactor).
  • ST_SetPoint - Replace point N of linestring with given point. Index is 0-based.
  • ST_Shift_Longitude - 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
  • ST_SnapToGrid - Snap all points of the input geometry to a regular grid.
  • ST_StartPoint - Returns the first point of a LINESTRING geometry as a POINT.
  • ST_SymDifference - 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).
  • ST_TransScale - Translates the geometry using the deltaX and deltaY args, then scales it using the XFactor, YFactor args, working in 2D only.
  • ST_Translate - Translates the geometry to a new location using the numeric parameters as offsets. Ie: ST_Translate(geom, X, Y) or ST_Translate(geom, X, Y,Z).
  • ST_UnaryUnion - Like ST_Union, but working at the geometry component level.
  • ST_X - Return the X coordinate of the point, or NULL if not available. Input must be a point.
  • ST_XMax - Returns X maxima of a bounding box 2d or 3d or a geometry.
  • ST_XMin - Returns X minima of a bounding box 2d or 3d or a geometry.
  • ST_Y - Return the Y coordinate of the point, or NULL if not available. Input must be a point.
  • ST_YMax - Returns Y maxima of a bounding box 2d or 3d or a geometry.
  • ST_YMin - Returns Y minima of a bounding box 2d or 3d or a geometry.
  • ST_Z - Return the Z coordinate of the point, or NULL if not available. Input must be a point.
  • ST_ZMax - Returns Z minima of a bounding box 2d or 3d or a geometry.
  • ST_ZMin - Returns Z minima of a bounding box 2d or 3d or a geometry.
  • ST_Zmflag - Returns ZM (dimension semantic) flag of the geometries as a small int. Values are: 0=2d, 1=3dm, 2=3dz, 3=4d.
  • UpdateGeometrySRID - Updates the SRID of all features in a geometry column, geometry_columns metadata and srid. If it was enforced with constraints, the constraints will be updated with new srid constraint. If the old was enforced by type definition, the type definition will be changed.
  • geometry_overlaps_nd - Returns TRUE if A's 3D bounding box intersects B's 3D bounding box.

13.8. PostGIS Curved Geometry Support Functions

The functions given below are PostGIS functions that can use CIRCULARSTRING, CURVEDPOLYGON, and other curved geometry types

  • AddGeometryColumn - Adds a geometry column to an existing table of attributes. By default uses type modifier to define rather than constraints. Pass in false for use_typmod to get old check constraint based behavior
  • Box2D - Returns a BOX2D representing the maximum extents of the geometry.
  • Box3D - Returns a BOX3D representing the maximum extents of the geometry.
  • DropGeometryColumn - Removes a geometry column from a spatial table.
  • GeometryType - Returns the type of the geometry as a string. Eg: 'LINESTRING', 'POLYGON', 'MULTIPOINT', etc.
  • PostGIS_AddBBox - Add bounding box to the geometry.
  • PostGIS_DropBBox - Drop the bounding box cache from the geometry.
  • PostGIS_HasBBox - Returns TRUE if the bbox of this geometry is cached, FALSE otherwise.
  • ST_3DExtent - an aggregate function that returns the box3D bounding box that bounds rows of geometries.
  • ST_Accum - Aggregate. Constructs an array of geometries.
  • ST_Affine - Applies a 3d affine transformation to the geometry to do things like translate, rotate, scale in one step.
  • ST_AsBinary - Return the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsEWKB - Return the Well-Known Binary (WKB) representation of the geometry with SRID meta data.
  • ST_AsEWKT - Return the Well-Known Text (WKT) representation of the geometry with SRID meta data.
  • ST_AsHEXEWKB - Returns a Geometry in HEXEWKB format (as text) using either little-endian (NDR) or big-endian (XDR) encoding.
  • ST_AsText - Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.
  • ST_Collect - Return a specified ST_Geometry value from a collection of other geometries.
  • ST_CoordDim - Return the coordinate dimension of the ST_Geometry value.
  • ST_CurveToLine - Converts a CIRCULARSTRING/CURVEDPOLYGON to a LINESTRING/POLYGON
  • ST_Dump - Returns a set of geometry_dump (geom,path) rows, that make up a geometry g1.
  • ST_Estimated_Extent - 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.
  • ST_FlipCoordinates - Returns a version of the given geometry with X and Y axis flipped. Useful for people who have built latitude/longitude features and need to fix them.
  • ST_Force_2D - Forces the geometries into a "2-dimensional mode" so that all output representations will only have the X and Y coordinates.
  • ST_Force_3D - Forces the geometries into XYZ mode. This is an alias for ST_Force_3DZ.
  • ST_Force_3DM - Forces the geometries into XYM mode.
  • ST_Force_3DZ - Forces the geometries into XYZ mode. This is a synonym for ST_Force_3D.
  • ST_Force_4D - Forces the geometries into XYZM mode.
  • ST_Force_Collection - Converts the geometry into a GEOMETRYCOLLECTION.
  • ST_GeoHash - Return a GeoHash representation (geohash.org) of the geometry.
  • ST_GeogFromWKB - Creates a geography instance from a Well-Known Binary geometry representation (WKB) or extended Well Known Binary (EWKB).
  • ST_GeomFromEWKB - Return a specified ST_Geometry value from Extended Well-Known Binary representation (EWKB).
  • ST_GeomFromEWKT - Return a specified ST_Geometry value from Extended Well-Known Text representation (EWKT).
  • ST_GeomFromText - Return a specified ST_Geometry value from Well-Known Text representation (WKT).
  • ST_GeomFromWKB - Creates a geometry instance from a Well-Known Binary geometry representation (WKB) and optional SRID.
  • ST_GeometryN - Return the 1-based Nth geometry if the geometry is a GEOMETRYCOLLECTION, (MULTI)POINT, (MULTI)LINESTRING, MULTICURVE or (MULTI)POLYGON, POLYHEDRALSURFACE Otherwise, return NULL.
  • = - Returns TRUE if A's bounding box is the same as B's. Uses double precision bounding box.
  • &<| - Returns TRUE if A's bounding box overlaps or is below B's.
  • ST_HasArc - Returns true if a geometry or geometry collection contains a circular string
  • ST_IsClosed - Returns TRUE if the LINESTRING's start and end points are coincident. For Polyhedral surface is closed (volumetric).
  • ST_IsCollection - Returns TRUE if the argument is a collection (MULTI*, GEOMETRYCOLLECTION, ...)
  • ST_IsEmpty - Returns true if this Geometry is an empty geometrycollection, polygon, point etc.
  • ST_LineToCurve - Converts a LINESTRING/POLYGON to a CIRCULARSTRING, CURVED POLYGON
  • ST_Mem_Size - Returns the amount of space (in bytes) the geometry takes.
  • ST_NPoints - Return the number of points (vertexes) in a geometry.
  • ST_NRings - If the geometry is a polygon or multi-polygon returns the number of rings.
  • ST_PointFromWKB - Makes a geometry from WKB with the given SRID
  • ST_PointN - Return the Nth point in a linestring or circular linestring in the geometry. Return NULL if geometry is not a linestring or circular linestring
  • ST_Rotate - Rotate a geometry rotRadians counter-clockwise about an origin.
  • ST_RotateZ - Rotate a geometry rotRadians about the Z axis.
  • ST_SRID - Returns the spatial reference identifier for the ST_Geometry as defined in spatial_ref_sys table.
  • ST_Scale - Scales the geometry to a new size by multiplying the ordinates with the parameters. Ie: ST_Scale(geom, Xfactor, Yfactor, Zfactor).
  • ST_SetSRID - Sets the SRID on a geometry to a particular integer value.
  • ST_TransScale - Translates the geometry using the deltaX and deltaY args, then scales it using the XFactor, YFactor args, working in 2D only.
  • ST_Transform - Returns a new geometry with its coordinates transformed to the SRID referenced by the integer parameter.
  • ST_Translate - Translates the geometry to a new location using the numeric parameters as offsets. Ie: ST_Translate(geom, X, Y) or ST_Translate(geom, X, Y,Z).
  • ST_XMax - Returns X maxima of a bounding box 2d or 3d or a geometry.
  • ST_XMin - Returns X minima of a bounding box 2d or 3d or a geometry.
  • ST_YMax - Returns Y maxima of a bounding box 2d or 3d or a geometry.
  • ST_YMin - Returns Y minima of a bounding box 2d or 3d or a geometry.
  • ST_ZMax - Returns Z minima of a bounding box 2d or 3d or a geometry.
  • ST_ZMin - Returns Z minima of a bounding box 2d or 3d or a geometry.
  • ST_Zmflag - Returns ZM (dimension semantic) flag of the geometries as a small int. Values are: 0=2d, 1=3dm, 2=3dz, 3=4d.
  • UpdateGeometrySRID - Updates the SRID of all features in a geometry column, geometry_columns metadata and srid. If it was enforced with constraints, the constraints will be updated with new srid constraint. If the old was enforced by type definition, the type definition will be changed.
  • && - Returns TRUE if A's 2D bounding box intersects B's 2D bounding box.
  • &&& - Returns TRUE if A's 3D bounding box intersects B's 3D bounding box.

13.9. PostGIS Polyhedral Surface Support Functions

The functions given below are PostGIS functions that can use POLYHEDRALSURFACE, POLYHEDRALSURFACEM geometries

  • Box2D - Returns a BOX2D representing the maximum extents of the geometry.
  • Box3D - Returns a BOX3D representing the maximum extents of the geometry.
  • GeometryType - Returns the type of the geometry as a string. Eg: 'LINESTRING', 'POLYGON', 'MULTIPOINT', etc.
  • ST_3DClosestPoint - Returns the 3-dimensional point on g1 that is closest to g2. This is the first point of the 3D shortest line.
  • ST_3DDFullyWithin - Returns true if all of the 3D geometries are within the specified distance of one another.
  • ST_3DDWithin - For 3d (z) geometry type Returns true if two geometries 3d distance is within number of units.
  • ST_3DDistance - For geometry type Returns the 3-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DExtent - an aggregate function that returns the box3D bounding box that bounds rows of geometries.
  • ST_3DIntersects - Returns TRUE if the Geometries "spatially intersect" in 3d - only for points and linestrings
  • ST_3DLongestLine - Returns the 3-dimensional longest line between two geometries
  • ST_3DMaxDistance - For geometry type Returns the 3-dimensional cartesian maximum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DShortestLine - Returns the 3-dimensional shortest line between two geometries
  • ST_Accum - Aggregate. Constructs an array of geometries.
  • ST_Affine - Applies a 3d affine transformation to the geometry to do things like translate, rotate, scale in one step.
  • ST_Area - Returns the area of the surface if it is a polygon or multi-polygon. For "geometry" type area is in SRID units. For "geography" area is in square meters.
  • ST_AsBinary - Return the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsEWKB - Return the Well-Known Binary (WKB) representation of the geometry with SRID meta data.
  • ST_AsEWKT - Return the Well-Known Text (WKT) representation of the geometry with SRID meta data.
  • ST_AsGML - Return the geometry as a GML version 2 or 3 element.
  • ST_AsX3D - Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML
  • ST_CoordDim - Return the coordinate dimension of the ST_Geometry value.
  • ST_Dimension - The inherent dimension of this Geometry object, which must be less than or equal to the coordinate dimension.
  • ST_Dump - Returns a set of geometry_dump (geom,path) rows, that make up a geometry g1.
  • ST_DumpPoints - Returns a set of geometry_dump (geom,path) rows of all points that make up a geometry.
  • ST_Expand - Returns bounding box expanded in all directions from the bounding box of the input geometry. Uses double-precision
  • ST_Extent - an aggregate function that returns the bounding box that bounds rows of geometries.
  • ST_FlipCoordinates - Returns a version of the given geometry with X and Y axis flipped. Useful for people who have built latitude/longitude features and need to fix them.
  • ST_ForceRHR - Forces the orientation of the vertices in a polygon to follow the Right-Hand-Rule.
  • ST_Force_2D - Forces the geometries into a "2-dimensional mode" so that all output representations will only have the X and Y coordinates.
  • ST_Force_3D - Forces the geometries into XYZ mode. This is an alias for ST_Force_3DZ.
  • ST_Force_3DZ - Forces the geometries into XYZ mode. This is a synonym for ST_Force_3D.
  • ST_Force_Collection - Converts the geometry into a GEOMETRYCOLLECTION.
  • ST_GeomFromEWKB - Return a specified ST_Geometry value from Extended Well-Known Binary representation (EWKB).
  • ST_GeomFromEWKT - Return a specified ST_Geometry value from Extended Well-Known Text representation (EWKT).
  • ST_GeomFromGML - Takes as input GML representation of geometry and outputs a PostGIS geometry object
  • ST_GeometryN - Return the 1-based Nth geometry if the geometry is a GEOMETRYCOLLECTION, (MULTI)POINT, (MULTI)LINESTRING, MULTICURVE or (MULTI)POLYGON, POLYHEDRALSURFACE Otherwise, return NULL.
  • ST_GeometryType - Return the geometry type of the ST_Geometry value.
  • = - Returns TRUE if A's bounding box is the same as B's. Uses double precision bounding box.
  • &<| - Returns TRUE if A's bounding box overlaps or is below B's.
  • ~= - Returns TRUE if A's bounding box is the same as B's.
  • ST_IsClosed - Returns TRUE if the LINESTRING's start and end points are coincident. For Polyhedral surface is closed (volumetric).
  • ST_Mem_Size - Returns the amount of space (in bytes) the geometry takes.
  • ST_NPoints - Return the number of points (vertexes) in a geometry.
  • ST_NumGeometries - If geometry is a GEOMETRYCOLLECTION (or MULTI*) return the number of geometries, for single geometries will return 1, otherwise return NULL.
  • ST_NumPatches - Return the number of faces on a Polyhedral Surface. Will return null for non-polyhedral geometries.
  • ST_PatchN - Return the 1-based Nth geometry (face) if the geometry is a POLYHEDRALSURFACE, POLYHEDRALSURFACEM. Otherwise, return NULL.
  • ST_RemoveRepeatedPoints - Returns a version of the given geometry with duplicated points removed.
  • ST_Rotate - Rotate a geometry rotRadians counter-clockwise about an origin.
  • ST_RotateX - Rotate a geometry rotRadians about the X axis.
  • ST_RotateY - Rotate a geometry rotRadians about the Y axis.
  • ST_RotateZ - Rotate a geometry rotRadians about the Z axis.
  • ST_Scale - Scales the geometry to a new size by multiplying the ordinates with the parameters. Ie: ST_Scale(geom, Xfactor, Yfactor, Zfactor).
  • ST_Shift_Longitude - 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
  • ST_Transform - Returns a new geometry with its coordinates transformed to the SRID referenced by the integer parameter.
  • && - Returns TRUE if A's 2D bounding box intersects B's 2D bounding box.
  • &&& - Returns TRUE if A's 3D bounding box intersects B's 3D bounding box.

13.10. PostGIS Function Support Matrix

Below is an alphabetical listing of spatial specific functions in PostGIS and the kinds of spatial types they work with or OGC/SQL compliance they try to conform to.

  • A means the function works with the type or subtype natively.
  • A means it works but with a transform cast built-in using cast to geometry, transform to a "best srid" spatial ref and then cast back. Results may not be as expected for large areas or areas at poles and may accumulate floating point junk.
  • A means the function works with the type because of a auto-cast to another such as to box3d rather than direct type support.
  • geom - Basic 2D geometry support (x,y).
  • geog - Basic 2D geography support (x,y).
  • 2.5D - basic 2D geometries in 3 D/4D space (has Z or M coord).
  • PS - Polyhedral surfaces
  • T - Triangles and Triangulated Irregular Network surfaces (TIN)

Functiongeomgeog2.5DCurvesSQL MMPST
Box2D    
Box3D   
Find_SRID        
GeometryType   
ST_3DClosestPoint     
ST_3DDFullyWithin     
ST_3DDWithin    
ST_3DDistance    
ST_3DExtent   
ST_3DIntersects    
ST_3DLength      
ST_3DLength_Spheroid      
ST_3DLongestLine     
ST_3DMakeBox      
ST_3DMaxDistance     
ST_3DPerimeter      
ST_3DShortestLine     
ST_Accum   
ST_AddMeasure      
ST_AddPoint      
ST_Affine   
ST_Area    
ST_AsBinary
ST_AsEWKB   
ST_AsEWKT  
ST_AsGML   
ST_AsGeoJSON     
ST_AsHEXEWKB     
ST_AsKML     
ST_AsLatLonText       
ST_AsSVG      
ST_AsText    
ST_AsX3D    
ST_Azimuth      
ST_BdMPolyFromText       
ST_BdPolyFromText       
ST_Boundary     
ST_Buffer     
ST_BuildArea       
ST_Centroid      
ST_ClosestPoint       
ST_Collect     
ST_CollectionExtract       
ST_CollectionHomogenize       
ST_ConcaveHull       
ST_Contains      
ST_ContainsProperly       
ST_ConvexHull     
ST_CoordDim  
ST_CoveredBy      
ST_Covers      
ST_Crosses      
ST_CurveToLine    
ST_DFullyWithin       
ST_DWithin      
ST_Difference     
ST_Dimension    
ST_Disjoint      
ST_Distance     
ST_Distance_Sphere       
ST_Distance_Spheroid       
ST_Dump   
ST_DumpPoints    
ST_DumpRings      
ST_EndPoint     
ST_Envelope      
ST_Equals      
ST_Estimated_Extent      
ST_Expand     
ST_Extent     
ST_ExteriorRing     
ST_FlipCoordinates   
ST_ForceRHR     
ST_Force_2D    
ST_Force_3D    
ST_Force_3DM      
ST_Force_3DZ    
ST_Force_4D     
ST_Force_Collection    
ST_GMLToSQL     
ST_GeoHash      
ST_GeogFromText       
ST_GeogFromWKB      
ST_GeographyFromText       
ST_GeomCollFromText      
ST_GeomFromEWKB   
ST_GeomFromEWKT   
ST_GeomFromGML    
ST_GeomFromGeoJSON      
ST_GeomFromKML      
ST_GeomFromText     
ST_GeomFromWKB     
ST_GeometryFromText      
ST_GeometryN  
ST_GeometryType    
|>>       
<<|       
~       
@       
=    
<<       
|&>       
&<|     
&<       
&>       
>>       
~=      
ST_HasArc     
ST_HausdorffDistance       
ST_InteriorRingN     
ST_InterpolatePoint      
ST_Intersection     
ST_Intersects     
ST_IsClosed   
ST_IsCollection     
ST_IsEmpty     
ST_IsRing      
ST_IsSimple     
ST_IsValid      
ST_IsValidDetail       
ST_IsValidReason       
ST_Length     
ST_Length2D       
ST_Length2D_Spheroid       
ST_Length_Spheroid      
ST_LineCrossingDirection       
ST_LineFromMultiPoint      
ST_LineFromText      
ST_LineFromWKB      
ST_LineMerge       
ST_LineToCurve     
ST_Line_Interpolate_Point      
ST_Line_Locate_Point       
ST_Line_Substring      
ST_LinestringFromWKB      
ST_LocateAlong       
ST_LocateBetween       
ST_LocateBetweenElevations      
ST_LongestLine       
ST_M     
ST_MLineFromText      
ST_MPointFromText      
ST_MPolyFromText      
ST_MakeBox2D       
ST_MakeEnvelope       
ST_MakeLine      
ST_MakePoint      
ST_MakePointM       
ST_MakePolygon      
ST_MakeValid      
ST_MaxDistance       
ST_MemUnion      
ST_Mem_Size   
ST_MinimumBoundingCircle       
ST_Multi       
ST_NDims      
ST_NPoints    
ST_NRings     
ST_Node      
ST_NumGeometries   
ST_NumInteriorRing       
ST_NumInteriorRings      
ST_NumPatches    
ST_NumPoints      
ST_OffsetCurve       
ST_OrderingEquals      
ST_Overlaps      
ST_PatchN    
ST_Perimeter     
ST_Perimeter2D       
ST_Point      
ST_PointFromText      
ST_PointFromWKB    
ST_PointN    
ST_PointOnSurface     
ST_Point_Inside_Circle       
ST_Polygon     
ST_PolygonFromText      
ST_Polygonize       
ST_Project       
ST_Relate      
ST_RelateMatch        
ST_RemovePoint      
ST_RemoveRepeatedPoints     
ST_Reverse       
ST_Rotate   
ST_RotateX    
ST_RotateY    
ST_RotateZ   
ST_SRID     
ST_Scale   
ST_Segmentize       
ST_SetPoint      
ST_SetSRID      
ST_SharedPaths       
ST_Shift_Longitude    
ST_ShortestLine       
ST_Simplify       
ST_SimplifyPreserveTopology       
ST_Snap       
ST_SnapToGrid      
ST_Split       
ST_StartPoint     
ST_Summary      
ST_SymDifference     
ST_Touches      
ST_TransScale     
ST_Transform    
ST_Translate     
ST_UnaryUnion      
ST_Union      
ST_WKBToSQL      
ST_WKTToSQL      
ST_Within      
ST_X     
ST_XMax     
ST_XMin     
ST_Y     
ST_YMax     
ST_YMin     
ST_Z     
ST_ZMax     
ST_ZMin     
ST_Zmflag     
<#>       
<->       
&&    
&&&   

13.11. New, Enhanced or changed PostGIS Functions

13.11.1. PostGIS Functions new, behavior changed, or enhanced in 2.0
13.11.2. PostGIS Functions changed behavior in 2.0
13.11.3. PostGIS Functions new, behavior changed, or enhanced in 1.5
13.11.4. PostGIS Functions new, behavior changed, or enhanced in 1.4
13.11.5. PostGIS Functions new in 1.3

13.11.1. PostGIS Functions new, behavior changed, or enhanced in 2.0

The functions given below are PostGIS functions that were added, enhanced, or have Section 13.11.2, “PostGIS Functions changed behavior in 2.0” breaking changes in 2.0 releases.

New geometry types: TIN and Polyhedral surfaces was introduced in 2.0

[Note]

Greatly improved support for Topology. Please refer to Chapter 11, Topology for more details.

[Note]

In PostGIS 2.0, raster type and raster functionality has been integrated. There are way too many new raster functions to list here and all are new so please refer to Chapter 9, Raster Reference for more details of the raster functions available. Earlier pre-2.0 versions had raster_columns/raster_overviews as real tables. These were changed to views before release. Functions such as ST_AddRasterColumn were removed and replaced with AddRasterConstraints, DropRasterConstraints as a result some apps that created raster tables may need changing.

[Note]

Tiger Geocoder upgraded to work with TIGER 2010 census data and now included in the core PostGIS documentation. A reverse geocoder function was also added. Please refer to Section 12.1, “Tiger Geocoder” for more details.

[Note]

Raster bands can only reference the first 256 bands of out-db rasters.

  • &&& - Availability: 2.0.0 Returns TRUE if A's 3D bounding box intersects B's 3D bounding box.
  • <#> - Availability: 2.0.0 only available for PostgreSQL 9.1+ Returns the distance between bounding box of 2 geometries. For point / point checks it's almost the same as distance (though may be different since the bounding box is at floating point accuracy and geometries are double precision). Useful for doing distance ordering and nearest neighbor limits using KNN gist functionality.
  • <-> - Availability: 2.0.0 only available for PostgreSQL 9.1+ Returns the distance between two points. For point / point checks it uses floating point accuracy (as opposed to the double precision accuracy of the underlying point geometry). For other geometry types the distance between the floating point bounding box centroids is returned. Useful for doing distance ordering and nearest neighbor limits using KNN gist functionality.
  • AddEdge - Availability: 2.0.0 requires GEOS >= 3.3.0. Adds a linestring edge to the edge table and associated start and end points to the point nodes table of the specified topology schema using the specified linestring geometry and returns the edgeid of the new (or existing) edge.
  • AddFace - Availability: 2.0.0 Registers a face primitive to a topology and get it's identifier.
  • AddNode - Availability: 2.0.0 Adds a point node to the node table in the specified topology schema and returns the nodeid of new node. If point already exists as node, the existing nodeid is returned.
  • AddOverviewConstraints - Availability: 2.0.0 Tag a raster column as being an overview of another.
  • AddRasterConstraints - Availability: 2.0.0 Adds raster constraints to a loaded raster table for a specific column that constrains spatial ref, scaling, blocksize, alignment, bands, band type and a flag to denote if raster column is regularly blocked. The table must be loaded with data for the constraints to be inferred. Returns true of the constraint setting was accomplished and if issues a notice.
  • AsGML - Availability: 2.0.0 Returns the GML representation of a topogeometry.
  • CopyTopology - Availability: 2.0.0 Makes a copy of a topology structure (nodes, edges, faces, layers and TopoGeometries).
  • DropOverviewConstraints - Availability: 2.0.0 Untag a raster column from being an overview of another.
  • DropRasterConstraints - Availability: 2.0.0 Drops PostGIS raster constraints that refer to a raster table column. Useful if you need to reload data or update your raster column data.
  • Drop_Indexes_Generate_Script - Availability: 2.0.0 Generates a script that drops all non-primary key and non-unique indexes on tiger schema and user specified schema. Defaults schema to tiger_data if no schema is specified.
  • Drop_State_Tables_Generate_Script - Availability: 2.0.0 Generates a script that drops all tables in the specified schema that are prefixed with the state abbreviation. Defaults schema to tiger_data if no schema is specified.
  • Geocode_Intersection - Availability: 2.0.0 Takes in 2 streets that intersect and a state, city, zip, and outputs a set of possible locations on the first cross street that is at the intersection, also includes a point geometry in NAD 83 long lat, a normalized address for each location, and the rating. The lower the rating the more likely the match. Results are sorted by lowest rating first. Can optionally pass in maximum results, defaults to 10
  • GetEdgeByPoint - Availability: 2.0.0 - requires GEOS >= 3.3.0. Find the edge-id of an edge that intersects a given point
  • GetFaceByPoint - Availability: 2.0.0 - requires GEOS >= 3.3.0. Find the face-id of a face that intersects a given point
  • GetNodeByPoint - Availability: 2.0.0 - requires GEOS >= 3.3.0. Find the id of a node at a point location
  • GetNodeEdges - Availability: 2.0 Returns an ordered set of edges incident to the given node.
  • GetRingEdges - Availability: 2.0 Returns an ordered set of edges forming a ring with the given edge .
  • GetTopologySRID - Availability: 2.0.0 Returns the SRID of a topology in the topology.topology table given the name of the topology.
  • Get_Tract - Availability: 2.0.0 Returns census tract or field from tract table of where the geometry is located. Default to returning short name of tract.
  • Install_Missing_Indexes - Availability: 2.0.0 Finds all tables with key columns used in geocoder joins and filter conditions that are missing used indexes on those columns and will add them.
  • Loader_Generate_Census_Script - Availability: 2.0.0 Generates a shell script for the specified platform for the specified states that will download Tiger census state tract, bg, and tabblocks data tables, stage and load into tiger_data schema. Each state script is returned as a separate record.
  • Loader_Generate_Script - Availability: 2.0.0 to support Tiger 2010 structured data and load census tract (tract), block groups (bg), and blocks (tabblocks) tables . Generates a shell script for the specified platform for the specified states that will download Tiger data, stage and load into tiger_data schema. Each state script is returned as a separate record. Latest version supports Tiger 2010 structural changes and also loads census tract, block groups, and blocks tables.
  • Missing_Indexes_Generate_Script - Availability: 2.0.0 Finds all tables with key columns used in geocoder joins that are missing indexes on those columns and will output the SQL DDL to define the index for those tables.
  • Polygonize - Availability: 2.0.0 Find and register all faces defined by topology edges
  • Reverse_Geocode - Availability: 2.0.0 Takes a geometry point in a known spatial ref sys and returns a record containing an array of theoretically possible addresses and an array of cross streets. If include_strnum_range = true, includes the street range in the cross streets.
  • ST_3DClosestPoint - Availability: 2.0.0 Returns the 3-dimensional point on g1 that is closest to g2. This is the first point of the 3D shortest line.
  • ST_3DDFullyWithin - Availability: 2.0.0 Returns true if all of the 3D geometries are within the specified distance of one another.
  • ST_3DDWithin - Availability: 2.0.0 For 3d (z) geometry type Returns true if two geometries 3d distance is within number of units.
  • ST_3DDistance - Availability: 2.0.0 For geometry type Returns the 3-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DIntersects - Availability: 2.0.0 Returns TRUE if the Geometries "spatially intersect" in 3d - only for points and linestrings
  • ST_3DLongestLine - Availability: 2.0.0 Returns the 3-dimensional longest line between two geometries
  • ST_3DMaxDistance - Availability: 2.0.0 For geometry type Returns the 3-dimensional cartesian maximum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DShortestLine - Availability: 2.0.0 Returns the 3-dimensional shortest line between two geometries
  • ST_AddEdgeModFace - Availability: 2.0 Add a new edge and, if in doing so it splits a face, modify the original face and add a new face.
  • ST_AddEdgeNewFaces - Availability: 2.0 Add a new edge and, if in doing so it splits a face, delete the original face and replace it with two new faces.
  • ST_AsGDALRaster - Availability: 2.0.0 - requires GDAL >= 1.6.0. Return the raster tile in the designated GDAL Raster format. Raster formats are one of those supported by your compiled library. Use ST_GDALRasters() to get a list of formats supported by your library.
  • ST_AsJPEG - Availability: 2.0.0 - requires GDAL >= 1.6.0. Return the raster tile selected bands as a single Joint Photographic Exports Group (JPEG) image (byte array). If no band is specified and 1 or more than 3 bands, then only the first band is used. If only 3 bands then all 3 bands are used and mapped to RGB.
  • ST_AsLatLonText - Availability: 2.0 Return the Degrees, Minutes, Seconds representation of the given point.
  • ST_AsPNG - Availability: 2.0.0 - requires GDAL >= 1.6.0. Return the raster tile selected bands as a single portable network graphics (PNG) image (byte array). If 1, 3, or 4 bands in raster and no bands are specified, then all bands are used. If more 2 or more than 4 bands and no bands specified, then only band 1 is used. Bands are mapped to RGB or RGBA space.
  • ST_AsRaster - Availability: 2.0.0 - requires GDAL >= 1.6.0. Converts a PostGIS geometry to a PostGIS raster.
  • ST_AsTIFF - Availability: 2.0.0 - requires GDAL >= 1.6.0. Return the raster selected bands as a single TIFF image (byte array). If no band is specified, then will try to use all bands.
  • ST_AsX3D - Availability: 2.0.0: ISO-IEC-19776-1.2-X3DEncodings-XML Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML
  • ST_Aspect - Availability: 2.0.0 Returns the surface aspect of an elevation raster band. Useful for analyzing terrain.
  • ST_Band - Availability: 2.0.0 Returns one or more bands of an existing raster as a new raster. Useful for building new rasters from existing rasters.
  • ST_BandIsNoData - Availability: 2.0.0 Returns true if the band is filled with only nodata values.
  • ST_Clip - Availability: 2.0.0 Returns the raster clipped by the input geometry. If no band is specified all bands are returned. If crop is not specified, true is assumed meaning the output raster is cropped.
  • ST_CollectionHomogenize - Availability: 2.0.0 Given a geometry collection, returns the "simplest" representation of the contents.
  • ST_ConcaveHull - Availability: 2.0.0 The concave hull of a geometry represents a possibly concave geometry that encloses all geometries within the set. You can think of it as shrink wrapping.
  • ST_Count - Availability: 2.0.0 Returns the number of pixels in a given band of a raster or raster coverage. If no band is specified defaults to band 1. If exclude_nodata_value is set to true, will only count pixels that are not equal to the nodata value.
  • ST_CreateTopoGeo - Availability: 2.0 Adds a collection of geometries to a given empty topology and returns a message detailing success.
  • ST_Distinct4ma - Availability: 2.0.0 Raster processing function that calculates the number of unique pixel values in a neighborhood.
  • ST_FlipCoordinates - Availability: 2.0.0 Returns a version of the given geometry with X and Y axis flipped. Useful for people who have built latitude/longitude features and need to fix them.
  • ST_GDALDrivers - Availability: 2.0.0 - requires GDAL >= 1.6.0. Returns a list of raster formats supported by your lib gdal. These are the formats you can output your raster using ST_AsGDALRaster.
  • ST_GeomFromGeoJSON - Availability: 2.0.0 requires - JSON-C >= 0.9 Takes as input a geojson representation of a geometry and outputs a PostGIS geometry object
  • ST_GetFaceEdges - Availability: 2.0 Returns a set of ordered edges that bound aface includes the sequence order.
  • ST_HasNoBand - Availability: 2.0.0 Returns true if there is no band with given band number. If no band number is specified, then band number 1 is assumed.
  • ST_HillShade - Availability: 2.0.0 Returns the hypothetical illumination of an elevation raster band using provided azimuth, altitude, brightness and elevation scale inputs. Useful for visualizing terrain.
  • ST_Histogram - Availability: 2.0.0 Returns a set of histogram summarizing a raster or raster coverage data distribution separate bin ranges. Number of bins are autocomputed if not specified.
  • ST_InterpolatePoint - Availability: 2.0.0 Return the value of the measure dimension of a geometry at the point closed to the provided point.
  • ST_IsValidDetail - Availability: 2.0.0 - requires GEOS >= 3.3.0. Returns a valid_detail (valid,reason,location) row stating if a geometry is valid or not and if not valid, a reason why and a location where.
  • ST_IsValidReason - Availability: 2.0 - requires GEOS >= 3.3.0 for the version taking flags. Returns text stating if a geometry is valid or not and if not valid, a reason why.
  • ST_MakeLine - Availability: 2.0.0 - Support for linestring input elements was introduced Creates a Linestring from point or line geometries.
  • ST_MakeValid - Availability: 2.0.0, requires GEOS-3.3.0 Attempts to make an invalid geometry valid without losing vertices.
  • ST_MapAlgebraExpr - Availability: 2.0.0 1 raster band version: Creates a new one band raster formed by applying a valid PostgreSQL algebraic operation on the input raster band and of pixeltype provided. Band 1 is assumed if no band is specified.
  • ST_MapAlgebraExpr - Availability: 2.0.0 2 raster band version: Creates a new one band raster formed by applying a valid PostgreSQL algebraic operation on the two input raster bands and of pixeltype provided. band 1 of each raster is assumed if no band numbers are specified. The resulting raster will be aligned (scale, skew and pixel corners) on the grid defined by the first raster and have its extent defined by the "extenttype" parameter. Values for "extenttype" can be: INTERSECTION, UNION, FIRST, SECOND.
  • ST_MapAlgebraFct - Availability: 2.0.0 1 band version - Creates a new one band raster formed by applying a valid PostgreSQL function on the input raster band and of pixeltype prodived. Band 1 is assumed if no band is specified.
  • ST_MapAlgebraFct - Availability: 2.0.0 2 band version - Creates a new one band raster formed by applying a valid PostgreSQL function on the 2 input raster bands and of pixeltype prodived. Band 1 is assumed if no band is specified. Extent type defaults to INTERSECTION if not specified.
  • ST_MapAlgebraFctNgb - Availability: 2.0.0 1-band version: Map Algebra Nearest Neighbor using user-defined PostgreSQL function. Return a raster which values are the result of a PLPGSQL user function involving a neighborhood of values from the input raster band.
  • ST_Max4ma - Availability: 2.0.0 Raster processing function that calculates the maximum pixel value in a neighborhood.
  • ST_Mean4ma - Availability: 2.0.0 Raster processing function that calculates the mean pixel value in a neighborhood.
  • ST_Min4ma - Availability: 2.0.0 Raster processing function that calculates the minimum pixel value in a neighborhood.
  • ST_ModEdgeHeal - Availability: 2.0 Heal two edges by deleting the node connecting them, modifying the first edgeand deleting the second edge. Returns the id of the deleted node.
  • ST_NewEdgeHeal - Availability: 2.0 Heal two edges by deleting the node connecting them, deleting both edges,and replacing them with an edge whose direction is the same as the firstedge provided.
  • ST_Node - Availability: 2.0.0 - requires GEOS >= 3.3.0. Node a set of linestrings.
  • ST_NumPatches - Availability: 2.0.0 Return the number of faces on a Polyhedral Surface. Will return null for non-polyhedral geometries.
  • ST_OffsetCurve - Availability: 2.0 - requires GEOS >= 3.2, improved with GEOS >= 3.3 Return an offset line at a given distance and side from an input line. Useful for computing parallel lines about a center line
  • ST_PatchN - Availability: 2.0.0 Return the 1-based Nth geometry (face) if the geometry is a POLYHEDRALSURFACE, POLYHEDRALSURFACEM. Otherwise, return NULL.
  • ST_Project - Availability: 2.0.0 Returns a POINT projected from a start point using a distance in meters and bearing (azimuth) in radians.
  • ST_Quantile - Availability: 2.0.0 Compute quantiles for a raster or raster table coverage in the context of the sample or population. Thus, a value could be examined to be at the raster's 25%, 50%, 75% percentile.
  • ST_Range4ma - Availability: 2.0.0 Raster processing function that calculates the range of pixel values in a neighborhood.
  • ST_Reclass - Availability: 2.0.0 Creates a new raster composed of band types reclassified from original. The nband is the band to be changed. If nband is not specified assumed to be 1. All other bands are returned unchanged. Use case: convert a 16BUI band to a 8BUI and so forth for simpler rendering as viewable formats.
  • ST_RelateMatch - Availability: 2.0.0 - requires GEOS >= 3.3.0. Returns true if intersectionMattrixPattern1 implies intersectionMatrixPattern2
  • ST_RemEdgeModFace - Availability: 2.0 Removes an edge and, if the removed edge separated two faces,delete one of the them and modify the other to take the space of both.
  • ST_RemEdgeNewFace - Availability: 2.0 Removes an edge and, if the removed edge separated two faces,delete the original faces and replace them with a new face.
  • ST_RemoveRepeatedPoints - Availability: 2.0.0 Returns a version of the given geometry with duplicated points removed.
  • ST_Resample - Availability: 2.0.0 Requires GDAL 1.6.1+ Resample a raster using a specified resampling algorithm, new dimensions, an arbitrary grid corner and a set of raster georeferencing attributes defined or borrowed from another raster. New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor.
  • ST_Rescale - Availability: 2.0.0 Requires GDAL 1.6.1+ Resample a raster by adjusting only its scale (or pixel size). New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor.
  • ST_Reskew - Availability: 2.0.0 Requires GDAL 1.6.1+ Resample a raster by adjusting only its skew (or rotation parameters). New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor.
  • ST_SameAlignment - Availability: 2.0.0 Returns true if rasters have same skew, scale, spatial ref and false if they don't with notice detailing issue.
  • ST_SetBandIsNoData - Availability: 2.0.0 Sets the isnodata flag of the band to TRUE.
  • ST_SharedPaths - Availability: 2.0.0 requires GEOS >= 3.3.0. Returns a collection containing paths shared by the two input linestrings/multilinestrings.
  • ST_Slope - Availability: 2.0.0 Returns the surface slope of an elevation raster band. Useful for analyzing terrain.
  • ST_Snap - Availability: 2.0.0 requires GEOS >= 3.3.0. Snap segments and vertices of input geometry to vertices of a reference geometry.
  • ST_SnapToGrid - Availability: 2.0.0 Requires GDAL 1.6.1+ Resample a raster by snapping it to a grid. New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor.
  • ST_Split - Availability: 2.0.0 Returns a collection of geometries resulting by splitting a geometry.
  • ST_StdDev4ma - Availability: 2.0.0 Raster processing function that calculates the standard deviation of pixel values in a neighborhood.
  • ST_Sum4ma - Availability: 2.0.0 Raster processing function that calculates the sum of all pixel values in a neighborhood.
  • ST_SummaryStats - Availability: 2.0.0 Returns summary stats consisting of count,sum,mean,stddev,min,max for a given raster band of a raster or raster coverage. Band 1 is assumed is no band is specified.
  • ST_Transform - Availability: 2.0.0 Requires GDAL 1.6.1+ Reprojects a raster in a known spatial reference system to another known spatial reference system using specified resampling algorithm. Options are NearestNeighbor, Bilinear, Cubic, CubicSpline, Lanczos defaulting to NearestNeighbor.
  • ST_UnaryUnion - Availability: 2.0.0 - requires GEOS >= 3.3.0. Like ST_Union, but working at the geometry component level.
  • ST_Union - Availability: 2.0.0 Returns the union of a set of raster tiles into a single raster composed of 1 band. If no band is specified for unioning, band num 1 is assumed. The resulting raster's extent is the extent of the whole set. In the case of intersection, the resulting value is defined by p_expression which is one of the following: LAST - the default when none is specified, MEAN, SUM, FIRST, MAX, MIN.
  • ST_ValueCount - Availability: 2.0.0 Returns a set of records containing a pixel band value and count of the number of pixels in a given band of a raster (or a raster coverage) that have a given set of values. If no band is specified defaults to band 1. By default nodata value pixels are not counted. and all other values in the pixel are output and pixel band values are rounded to the nearest integer.
  • TopoElementArray_Agg - Availability: 2.0.0 Returns a topoelementarray for a set of element_id, type arrays (topoelements)
  • TopoGeo_AddLineString - Availability: 2.0.0 Adds a linestring to an existing topology using a tolerance and possibly splitting existing edges/faces.
  • TopoGeo_AddPoint - Availability: 2.0.0 Adds a point to an existing topology using a tolerance and possibly splitting an existing edge.
  • TopoGeo_AddPolygon - Availability: 2.0.0 Adds a polygon to an existing topology using a tolerance and possibly splitting existing edges/faces.
  • TopologySummary - Availability: 2.0.0 Takes a topology name and provides summary totals of types of objects in topology
  • Topology_Load_Tiger - Availability: 2.0.0 Loads a defined region of tiger data into a PostGIS Topology and transforming the tiger data to spatial reference of the topology and snapping to the precision tolerance of the topology.
  • toTopoGeom - Availability: 2.0 Creates a new topo geometry from a simple geometry

The functions given below are PostGIS functions that are enhanced in PostGIS 2.0.

  • AddGeometryColumn - Enhanced: 2.0.0 use_typmod argument introduced. Defaults to creating typmod geometry column instead of constraint-based.
  • Box2D - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • Box3D - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • Geocode - Enhanced: 2.0.0 to support Tiger 2010 structured data and revised some logic to improve speed, accuracy of geocoding, and to offset point from centerline to side of street address is located on. New parameter max_results useful for specifying ot just return the best result.
  • GeometryType - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • Populate_Geometry_Columns - Enhanced: 2.0.0 use_typmod optional argument was introduced that allows controlling if columns are created with typmodifiers or with check constraints.
  • ST_Intersection - Enhanced: 2.0.0 - Intersection in the raster space was introduced. In earlier pre-2.0.0 versions, only intersection performed in vector space were supported.
  • ST_Intersects - Enhanced: 2.0.0 support raster/raster intersects was introduced.
  • ST_Value - Enhanced: 2.0.0 exclude_nodata_value optional argument was added.
  • ST_3DExtent - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_Accum - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_Affine - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_Area - Enhanced: 2.0.0 - support for 2D polyhedral surfaces was introduced.
  • ST_AsBinary - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_AsBinary - Enhanced: 2.0.0 support for higher coordinate dimensions was introduced.
  • ST_AsBinary - Enhanced: 2.0.0 support for specifying endian with geography was introduced.
  • ST_AsEWKB - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_AsEWKT - Enhanced: 2.0.0 support for Geography, Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_AsGML - Enhanced: 2.0.0 prefix support was introduced. Option 4 for GML3 was introduced to allow using LineString instead of Curve tag for lines. GML3 Support for Polyhedral surfaces and TINS was introduced. Option 32 was introduced to output the box.
  • ST_AsKML - Enhanced: 2.0.0 - Add prefix namespace. Default is no prefix
  • ST_Azimuth - Enhanced: 2.0.0 support for geography was introduced.
  • ST_ChangeEdgeGeom - Enhanced: 2.0.0 adds topological consistency enforcement
  • ST_Dimension - Enhanced: 2.0.0 support for Polyhedral surfaces and TINs was introduced. No longer throws an exception if given empty geometry.
  • ST_Dump - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_DumpPoints - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_Expand - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_Extent - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_ForceRHR - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.
  • ST_Force_2D - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.
  • ST_Force_3D - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.
  • ST_Force_3DZ - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.
  • ST_Force_Collection - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.
  • ST_GMLToSQL - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced.
  • ST_GMLToSQL - Enhanced: 2.0.0 default srid optional parameter added.
  • ST_GeomFromEWKB - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced.
  • ST_GeomFromEWKT - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced.
  • ST_GeomFromGML - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced.
  • ST_GeomFromGML - Enhanced: 2.0.0 default srid optional parameter added.
  • ST_GeometryN - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_GeometryType - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.
  • ST_IsClosed - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.
  • ST_MakeEnvelope - Enhanced: 2.0: Ability to specify an envelope without specifying an SRID was introduced.
  • ST_MakeValid - Enhanced: 2.0.1, speed improvements requires GEOS-3.3.4
  • ST_NPoints - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.
  • ST_NumGeometries - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_Relate - Enhanced: 2.0.0 - added support for specifying boundary node rule (requires GEOS >= 3.0).
  • ST_Rotate - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_Rotate - Enhanced: 2.0.0 additional parameters for specifying the origin of rotation were added.
  • ST_RotateX - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_RotateY - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_RotateZ - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_Scale - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.
  • ST_Shift_Longitude - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced.
  • ST_Transform - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.
  • ValidateTopology - Enhanced: 2.0.0 more efficient edge crossing detection and fixes for false positives that were existent in prior versions.
  • && - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.

13.11.2. PostGIS Functions changed behavior in 2.0

The functions given below are PostGIS functions that have changed behavior in PostGIS 2.0 and may require application changes.

[Note]

Most deprecated functions have been removed. These are functions that haven't been documented since 1.2 or some internal functions that were never documented. If you are using a function that you don't see documented, it's probably deprecated, about to be deprecated, or internal and should be avoided. If you have applications or tools that rely on deprecated functions, please refer to ??? for more details.

[Note]

Bounding boxes of geometries have been changed from float4 to double precision (float8). This has an impact on answers you get using bounding box operators and casting of bounding boxes to geometries. E.g ST_SetSRID(abbox) will often return a different more accurate answer in PostGIS 2.0+ than it did in prior versions which may very well slightly change answers to view port queries.

[Note]

The arguments hasnodata was replaced with exclude_nodata_value which has the same meaning as the older hasnodata but clearer in purpose.

  • AddGeometryColumn - Changed: 2.0.0 This function no longer updates geometry_columns since geometry_columns is a view that reads from system catalogs. It by default also does not create constraints, but instead uses the built in type modifier behavior of PostgreSQL. So for example building a wgs84 POINT column with this function is now equivalent to: ALTER TABLE some_table ADD COLUMN geom geometry(Point,4326);
  • AddGeometryColumn - Changed: 2.0.0 If you require the old behavior of constraints use the default use_typmod, but set it to false.
  • AddGeometryColumn - Changed: 2.0.0 Views can no longer be manually registered in geometry_columns, however views built against geometry typmod tables geometries and used without wrapper functions will register themselves correctly because they inherit the typmod behavior of their parent table column. Views that use geometry functions that output other geometries will need to be cast to typmod geometries for these view geometry columns to be registered correctly in geometry_columns. Refer to .
  • DropGeometryColumn - Changed: 2.0.0 This function is provided for backward compatibility. Now that since geometry_columns is now a view against the system catalogs, you can drop a geometry column like any other table column using ALTER TABLE
  • DropGeometryTable - Changed: 2.0.0 This function is provided for backward compatibility. Now that since geometry_columns is now a view against the system catalogs, you can drop a table with geometry columns like any other table using DROP TABLE
  • Populate_Geometry_Columns - Changed: 2.0.0 By default, now uses type modifiers instead of check constraints to constrain geometry types. You can still use check constraint behavior instead by using the new use_typmod and setting it to false.
  • Box3D - Changed: 2.0.0 In pre-2.0 versions, there used to be a box2d instead of box3d. Since box2d is a deprecated type, this was changed to box3d.
  • ST_ScaleX - Changed: 2.0.0. In WKTRaster versions this was called ST_PixelSizeX.
  • ST_ScaleY - Changed: 2.0.0. In WKTRaster versions this was called ST_PixelSizeY.
  • ST_SetScale - Changed: 2.0.0 In WKTRaster versions this was called ST_SetPixelSize. This was changed in 2.0.0.
  • ST_SetValue - Changed: 2.0.2 Geometry variant of ST_SetValue() now checks to make sure that the input raster and geometry have the same SRID
  • ST_3DExtent - Changed: 2.0.0 In prior versions this used to be called ST_Extent3D
  • ST_3DLength - Changed: 2.0.0 In prior versions this used to be called ST_Length3D
  • ST_3DLength_Spheroid - Changed: 2.0.0 In prior versions this used to return 0 for anything that is not a MULTILINESTRING or LINESTRING and in 2.0.0 on returns the perimeter of if given a polgon.
  • ST_3DLength_Spheroid - Changed: 2.0.0 In prior versions this used to be called ST_Length3d_Spheroid
  • ST_3DMakeBox - Changed: 2.0.0 In prior versions this used to be called ST_MakeBox3D
  • ST_3DPerimeter - Changed: 2.0.0 In prior versions this used to be called ST_Perimeter3D
  • ST_AsGML - Changed: 2.0.0 use default named args
  • ST_AsGeoJSON - Changed: 2.0.0 support default args and named args.
  • ST_AsKML - Changed: 2.0.0 - uses default args and supports named args
  • ST_AsSVG - Changed: 2.0.0 to use default args and support named args
  • ST_EndPoint - Changed: 2.0.0 no longer works with single geometry multilinestrings. In older versions of PostGIS -- a single line multilinestring would work happily with this function and return the start point. In 2.0.0 it just returns NULL like any other multilinestring. The older behavior was an undocumented feature, but people who assumed they had their data stored as LINESTRING may experience these returning NULL in 2.0 now.
  • ST_GeomFromText - Changed: 2.0.0 In prior versions of PostGIS ST_GeomFromText('GEOMETRYCOLLECTION(EMPTY)') was allowed. This is now illegal in PostGIS 2.0.0 to better conform with SQL/MM standards. This should now be written as ST_GeomFromText('GEOMETRYCOLLECTION EMPTY')
  • ST_GeometryN - Changed: 2.0.0 Prior versions would return NULL for singular geometries. This was changed to return the geometry for ST_GeometryN(..,1) case.
  • ST_IsEmpty - Changed: 2.0.0 In prior versions of PostGIS ST_GeomFromText('GEOMETRYCOLLECTION(EMPTY)') was allowed. This is now illegal in PostGIS 2.0.0 to better conform with SQL/MM standards
  • ST_Length - Changed: 2.0.0 Breaking change -- in prior versions applying this to a MULTI/POLYGON of type geography would give you the perimeter of the POLYGON/MULTIPOLYGON. In 2.0.0 this was changed to return 0 to be in line with geometry behavior. Please use ST_Perimeter if you want the perimeter of a polygon
  • ST_LocateAlong - Changed: 2.0.0 in prior versions this used to be called ST_Locate_Along_Measure. The old name has been deprecated and will be removed in the future but is still available.
  • ST_LocateBetween - Changed: 2.0.0 - in prior versions this used to be called ST_Locate_Between_Measures. The old name has been deprecated and will be removed in the future but is still available for backward compatibility.
  • ST_ModEdgeSplit - Changed: 2.0 - In prior versions, this was misnamed ST_ModEdgesSplit
  • ST_NumGeometries - Changed: 2.0.0 In prior versions this would return NULL if the geometry was not a collection/MULTI type. 2.0.0+ now returns 1 for single geometries e.g POLYGON, LINESTRING, POINT.
  • ST_NumInteriorRings - Changed: 2.0.0 - in prior versions it would return the number of interior rings for the first POLYGON in a MULTIPOLYGON.
  • ST_PointN - Changed: 2.0.0 no longer works with single geometry multilinestrings. In older versions of PostGIS -- a single line multilinestring would work happily with this function and return the start point. In 2.0.0 it just returns NULL like any other multilinestring.
  • ST_Simplify - Changed: 2.0.0 -- the behavior of this function changed to be more consistent. This made some people unhappy.
  • ST_Simplify - Changed: 2.0.2 -- the behavior of this function was changed back 1.5 behavior. This made people who liked the newer more consistent behavior unhappy resulting in long painful discussions. So will probably result in a change back to newer behavior.
  • ST_StartPoint - Changed: 2.0.0 no longer works with single geometry multilinestrings. In older versions of PostGIS -- a single line multilinestring would work happily with this function and return the start point. In 2.0.0 it just returns NULL like any other multilinestring. The older behavior was an undocumented feature, but people who assumed they had their data stored as LINESTRING may experience these returning NULL in 2.0 now.

13.11.3. PostGIS Functions new, behavior changed, or enhanced in 1.5

The functions given below are PostGIS functions that were introduced or enhanced in this minor release.

  • PostGIS_LibXML_Version - Availability: 1.5 Returns the version number of the libxml2 library.
  • ST_AddMeasure - Availability: 1.5.0 Return a derived geometry with measure elements linearly interpolated between the start and end points. If the geometry has no measure dimension, one is added. If the geometry has a measure dimension, it is over-written with new values. Only LINESTRINGS and MULTILINESTRINGS are supported.
  • ST_AsBinary - Availability: 1.5.0 geography support was introduced. Return the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsGML - Availability: 1.5.0 geography support was introduced. Return the geometry as a GML version 2 or 3 element.
  • ST_AsGeoJSON - Availability: 1.5.0 geography support was introduced. Return the geometry as a GeoJSON element.
  • ST_AsText - Availability: 1.5 - support for geography was introduced. Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.
  • ST_Buffer - Availability: 1.5 - ST_Buffer was enhanced to support different endcaps and join types. These are useful for example to convert road linestrings into polygon roads with flat or square edges instead of rounded edges. Thin wrapper for geography was added. - requires GEOS >= 3.2 to take advantage of advanced geometry functionality. (T) For geometry: 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. For geography: Uses a planar transform wrapper. Introduced in 1.5 support for different end cap and mitre settings to control shape. buffer_style options: quad_segs=#,endcap=round|flat|square,join=round|mitre|bevel,mitre_limit=#.#
  • ST_ClosestPoint - Availability: 1.5.0 Returns the 2-dimensional point on g1 that is closest to g2. This is the first point of the shortest line.
  • ST_CollectionExtract - Availability: 1.5.0 Given a (multi)geometry, returns a (multi)geometry consisting only of elements of the specified type.
  • ST_Covers - Availability: 1.5 - support for geography was introduced. Returns 1 (TRUE) if no point in Geometry B is outside Geometry A
  • ST_DFullyWithin - Availability: 1.5.0 Returns true if all of the geometries are within the specified distance of one another
  • ST_DWithin - Availability: 1.5.0 support for geography was introduced Returns true if the geometries are within the specified distance of one another. For geometry units are in those of spatial reference and For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around spheroid), for faster check, use_spheroid=false to measure along sphere.
  • ST_Distance - Availability: 1.5.0 geography support was introduced in 1.5. Speed improvements for planar to better handle large or many vertex geometries For geometry type Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units. For geography type defaults to return spheroidal minimum distance between two geographies in meters.
  • ST_Distance_Sphere - Availability: 1.5 - support for other geometry types besides points was introduced. Prior versions only work with points. Returns minimum distance in meters between two lon/lat geometries. Uses a spherical earth and radius of 6370986 meters. Faster than ST_Distance_Spheroid , but less accurate. PostGIS versions prior to 1.5 only implemented for points.
  • ST_Distance_Spheroid - Availability: 1.5 - support for other geometry types besides points was introduced. Prior versions only work with points. Returns the minimum distance between two lon/lat geometries given a particular spheroid. PostGIS versions prior to 1.5 only support points.
  • ST_DumpPoints - Availability: 1.5.0 Returns a set of geometry_dump (geom,path) rows of all points that make up a geometry.
  • ST_Envelope - Availability: 1.5.0 behavior changed to output double precision instead of float4 Returns a geometry representing the double precision (float8) bounding box of the supplied geometry.
  • ST_GMLToSQL - Availability: 1.5, requires libxml2 1.6+ Return a specified ST_Geometry value from GML representation. This is an alias name for ST_GeomFromGML
  • ST_GeomFromGML - Availability: 1.5, requires libxml2 1.6+ Takes as input GML representation of geometry and outputs a PostGIS geometry object
  • ST_GeomFromKML - Availability: 1.5,libxml2 2.6+ Takes as input KML representation of geometry and outputs a PostGIS geometry object
  • ~= - Availability: 1.5.0 changed behavior Returns TRUE if A's bounding box is the same as B's.
  • ST_HausdorffDistance - Availability: 1.5.0 - requires GEOS >= 3.2.0 Returns the Hausdorff distance between two geometries. Basically a measure of how similar or dissimilar 2 geometries are. Units are in the units of the spatial reference system of the geometries.
  • ST_Intersection - Availability: 1.5 support for geography data type was introduced. (T) Returns a geometry that represents the shared portion of geomA and geomB. The geography implementation does a transform to geometry to do the intersection and then transform back to WGS84.
  • ST_Intersects - Availability: 1.5 support for geography was introduced. Returns TRUE if the Geometries/Geography "spatially intersect in 2D" - (share any portion of space) and FALSE if they don't (they are Disjoint). For geography -- tolerance is 0.00001 meters (so any points that close are considered to intersect)
  • ST_Length - Availability: 1.5.0 geography support was introduced in 1.5. Returns the 2d length of the geometry if it is a linestring or multilinestring. geometry are in units of spatial reference and geography are in meters (default spheroid)
  • ST_LongestLine - Availability: 1.5.0 Returns the 2-dimensional longest line points of two geometries. The function will only return the first longest line if more than one, that the function finds. The line returned will always start in g1 and end in g2. The length of the line this function returns will always be the same as st_maxdistance returns for g1 and g2.
  • ST_MakeEnvelope - Availability: 1.5 Creates a rectangular Polygon formed from the given minimums and maximums. Input values must be in SRS specified by the SRID.
  • ST_MaxDistance - Availability: 1.5.0 Returns the 2-dimensional largest distance between two geometries in projected units.
  • ST_ShortestLine - Availability: 1.5.0 Returns the 2-dimensional shortest line between two geometries
  • && - Availability: 1.5.0 support for geography was introduced. Returns TRUE if A's 2D bounding box intersects B's 2D bounding box.

13.11.4. PostGIS Functions new, behavior changed, or enhanced in 1.4

The functions given below are PostGIS functions that were introduced or enhanced in the 1.4 release.

  • Populate_Geometry_Columns - Ensures geometry columns are defined with type modifiers or have appropriate spatial constraints This ensures they will be registered correctly in geometry_columns view. By default will convert all geometry columns with no type modifier to ones with type modifiers. To get old behavior set use_typmod=false Availability: 1.4.0
  • ST_AsSVG - Returns a Geometry in SVG path data given a geometry or geography object. Availability: 1.2.2. Availability: 1.4.0 Changed in PostGIS 1.4.0 to include L command in absolute path to conform to http://www.w3.org/TR/SVG/paths.html#PathDataBNF
  • ST_Collect - Return a specified ST_Geometry value from a collection of other geometries. Availability: 1.4.0 - ST_Collect(geomarray) was introduced. ST_Collect was enhanced to handle more geometries faster.
  • ST_ContainsProperly - Returns true if B intersects the interior of A but not the boundary (or exterior). A does not contain properly itself, but does contain itself. Availability: 1.4.0 - requires GEOS >= 3.1.0.
  • ST_Extent - an aggregate function that returns the bounding box that bounds rows of geometries. Availability: 1.4.0
  • ST_GeoHash - Return a GeoHash representation (geohash.org) of the geometry. Availability: 1.4.0
  • ST_IsValidReason - Returns text stating if a geometry is valid or not and if not valid, a reason why. Availability: 1.4 - requires GEOS >= 3.1.0.
  • ST_LineCrossingDirection - Given 2 linestrings, returns a number between -3 and 3 denoting what kind of crossing behavior. 0 is no crossing. Availability: 1.4
  • ST_LocateBetweenElevations - Return a derived geometry (collection) value with elements that intersect the specified range of elevations inclusively. Only 3D, 4D LINESTRINGS and MULTILINESTRINGS are supported. Availability: 1.4.0
  • ST_MakeLine - Creates a Linestring from point or line geometries. Availability: 1.4.0 - ST_MakeLine(geomarray) was introduced. ST_MakeLine aggregate functions was enhanced to handle more points faster.
  • ST_MinimumBoundingCircle - Returns the smallest circle polygon that can fully contain a geometry. Default uses 48 segments per quarter circle. Availability: 1.4.0 - requires GEOS
  • ST_Union - Returns a geometry that represents the point set union of the Geometries. Availability: 1.4.0 - ST_Union was enhanced. ST_Union(geomarray) was introduced and also faster aggregate collection in PostgreSQL. If you are using GEOS 3.1.0+ ST_Union will use the faster Cascaded Union algorithm described in http://blog.cleverelephant.ca/2009/01/must-faster-unions-in-postgis-14.html

13.11.5. PostGIS Functions new in 1.3

The functions given below are PostGIS functions that were introduced in the 1.3 release.

  • ST_AsGML - Return the geometry as a GML version 2 or 3 element. Availability: 1.3.2
  • ST_AsGeoJSON - Return the geometry as a GeoJSON element. Availability: 1.3.4
  • ST_SimplifyPreserveTopology - Returns a "simplified" version of the given geometry using the Douglas-Peucker algorithm. Will avoid creating derived geometries (polygons in particular) that are invalid. Availability: 1.3.3
Prev   Next
Topology_Load_Tiger Home Chapter 14. Reporting Problems