Chapter 12. PostGIS Special Functions Index

Table of Contents
12.1. PostGIS Aggregate Functions
12.2. PostGIS Window Functions
12.3. PostGIS SQL-MM Compliant Functions
12.4. PostGIS Geography Support Functions
12.5. PostGIS Raster Support Functions
12.6. PostGIS Geometry / Geography / Raster Dump Functions
12.7. PostGIS Box Functions
12.8. PostGIS Functions that support 3D
12.9. PostGIS Curved Geometry Support Functions
12.10. PostGIS Polyhedral Surface Support Functions
12.11. PostGIS Function Support Matrix
12.12. New, Enhanced or changed PostGIS Functions
12.12.1. PostGIS Functions new or enhanced in 3.4
12.12.2. PostGIS Functions new or enhanced in 3.3
12.12.3. PostGIS Functions new or enhanced in 3.2
12.12.4. PostGIS Functions new or enhanced in 3.1
12.12.5. PostGIS Functions new or enhanced in 3.0
12.12.6. PostGIS Functions new or enhanced in 2.5
12.12.7. PostGIS Functions new or enhanced in 2.4
12.12.8. PostGIS Functions new or enhanced in 2.3
12.12.9. PostGIS Functions new or enhanced in 2.2
12.12.10. PostGIS Functions new or enhanced in 2.1
12.12.11. PostGIS Functions new or enhanced in 2.0
12.12.12. PostGIS Functions new or enhanced in 1.5
12.12.13. PostGIS Functions new or enhanced in 1.4
12.12.14. PostGIS Functions new or enhanced in 1.3

12.1. PostGIS Aggregate Functions

The functions below are spatial aggregate functions that are used in the same way as SQL aggregate function such as sum and average.

  • ST_3DExtent - Aggregate function that returns the 3D bounding box of geometries.
  • ST_3DUnion - Perform 3D union.
  • ST_AsFlatGeobuf - Return a FlatGeobuf representation of a set of rows.
  • ST_AsGeobuf - Return a Geobuf representation of a set of rows.
  • ST_AsMVT - Aggregate function returning a MVT representation of a set of rows.
  • ST_ClusterIntersecting - Aggregate function that clusters input geometries into connected sets.
  • ST_ClusterWithin - Aggregate function that clusters geometries by separation distance.
  • ST_Collect - Creates a GeometryCollection or Multi* geometry from a set of geometries.
  • ST_CoverageUnion - Computes the union of a set of polygons forming a coverage by removing shared edges.
  • ST_Extent - Aggregate function that returns the bounding box of geometries.
  • ST_MakeLine - Creates a LineString from Point, MultiPoint, or LineString geometries.
  • ST_MemUnion - Aggregate function which unions geometries in a memory-efficent but slower way
  • ST_Polygonize - Computes a collection of polygons formed from the linework of a set of geometries.
  • ST_SameAlignment - Returns true if rasters have same skew, scale, spatial ref, and offset (pixels can be put on same grid without cutting into pixels) and false if they don't with notice detailing issue.
  • ST_Union - Computes a geometry representing the point-set union of the input geometries.
  • TopoElementArray_Agg - Returns a topoelementarray for a set of element_id, type arrays (topoelements).

12.2. PostGIS Window Functions

The functions below are spatial window functions that are used in the same way as SQL window functions such as row_number(), lead(), and lag(). They must be followed by an OVER() clause.

  • ST_ClusterDBSCAN - Window function that returns a cluster id for each input geometry using the DBSCAN algorithm.
  • ST_ClusterIntersectingWin - Window function that returns a cluster id for each input geometry, clustering input geometries into connected sets.
  • ST_ClusterKMeans - Window function that returns a cluster id for each input geometry using the K-means algorithm.
  • ST_ClusterWithinWin - Window function that returns a cluster id for each input geometry, clustering using separation distance.
  • ST_CoverageInvalidEdges - Window function that finds locations where polygons fail to form a valid coverage.
  • ST_CoverageSimplify - Window function that simplifies the edges of a polygonal coverage.

12.3. PostGIS SQL-MM Compliant Functions

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

  • ST_3DArea - Computes area of 3D surface geometries. Will return 0 for solids. Description Availability: 2.1.0 This method needs SFCGAL backend. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 8.1, 10.5 This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN).
  • ST_3DDWithin - Tests if two 3D geometries are within a given 3D distance Description Returns true if the 3D distance between two geometry values is no larger than distance distance_of_srid. The distance is specified in units defined by the spatial reference system of the geometries. For this function to make sense the source geometries must be in the same coordinate system (have the same SRID). This function automatically includes a bounding box comparison that makes use of any spatial indexes that are available on the geometries. This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces. This method implements the SQL/MM specification. SQL-MM ? Availability: 2.0.0
  • ST_3DDifference - Perform 3D difference Description Returns that part of geom1 that is not part of geom2. Availability: 2.2.0 This method needs SFCGAL backend. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1 This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN).
  • ST_3DDistance - Returns the 3D cartesian minimum distance (based on spatial ref) between two geometries in projected units. Description Returns the 3-dimensional minimum cartesian distance between two geometries in projected units (spatial ref units). This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces. This method implements the SQL/MM specification. SQL-MM ISO/IEC 13249-3 Availability: 2.0.0 Changed: 2.2.0 - In case of 2D and 3D, Z is no longer assumed to be 0 for missing Z. Changed: 3.0.0 - SFCGAL version removed
  • ST_3DIntersection - Perform 3D intersection Description Return a geometry that is the shared portion between geom1 and geom2. Availability: 2.1.0 This method needs SFCGAL backend. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1 This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN).
  • ST_3DIntersects - Tests if two geometries spatially intersect in 3D - only for points, linestrings, polygons, polyhedral surface (area) Description Overlaps, Touches, Within all imply spatial intersection. If any of the aforementioned returns true, then the geometries also spatially intersect. Disjoint implies false for spatial intersection. This function automatically includes a bounding box comparison that makes use of any spatial indexes that are available on the geometries. Changed: 3.0.0 SFCGAL backend removed, GEOS backend supports TINs. Availability: 2.0.0 This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN). This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1
  • ST_3DLength - Returns the 3D length of a linear geometry. Description Returns the 3-dimensional or 2-dimensional length of the geometry if it is a LineString or MultiLineString. For 2-d lines it will just return the 2-d length (same as ST_Length and ST_Length2D) This function supports 3d and will not drop the z-index. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 7.1, 10.3 Changed: 2.0.0 In prior versions this used to be called ST_Length3D
  • ST_3DPerimeter - Returns the 3D perimeter of a polygonal geometry. Description Returns the 3-dimensional perimeter of the geometry, if it is a polygon or multi-polygon. If the geometry is 2-dimensional, then the 2-dimensional perimeter is returned. This function supports 3d and will not drop the z-index. This method implements the SQL/MM specification. SQL-MM ISO/IEC 13249-3: 8.1, 10.5 Changed: 2.0.0 In prior versions this used to be called ST_Perimeter3D
  • ST_3DUnion - Perform 3D union. Description Availability: 2.2.0 Availability: 3.3.0 aggregate variant was added This method needs SFCGAL backend. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1 This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN). Aggregate variant: returns a geometry that is the 3D union of a rowset of geometries. The ST_3DUnion() function is an "aggregate" function in the terminology of PostgreSQL. That means that it operates on rows of data, in the same way the SUM() and AVG() functions do and like most aggregates, it also ignores NULL geometries.
  • ST_AddEdgeModFace - Add a new edge and, if in doing so it splits a face, modify the original face and add a new face. Description Add a new edge and, if doing so splits a face, modify the original face and add a new one. If possible, the new face will be created on left side of the new edge. This will not be possible if the face on the left side will need to be the Universe face (unbounded). Returns the id of the newly added edge. Updates all existing joined edges and relationships accordingly. If any arguments are null, the given nodes are unknown (must already exist in the node table of the topology schema) , the acurve is not a LINESTRING, the anode and anothernode are not the start and endpoints of acurve then an error is thrown. If the spatial reference system (srid) of the acurve geometry is not the same as the topology an exception is thrown. Availability: 2.0 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. Description Add a new edge and, if in doing so it splits a face, delete the original face and replace it with two new faces. Returns the id of the newly added edge. Updates all existing joined edges and relationships accordingly. If any arguments are null, the given nodes are unknown (must already exist in the node table of the topology schema) , the acurve is not a LINESTRING, the anode and anothernode are not the start and endpoints of acurve then an error is thrown. If the spatial reference system (srid) of the acurve geometry is not the same as the topology an exception is thrown. Availability: 2.0 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. Description 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. If the spatial reference system (srid) of the alinestring geometry is not the same as the topology, any of the input arguments are null, or the nodes are contained in more than one face, or the nodes are start or end nodes of an existing edge, then an exception is thrown. If the alinestring is not within the face of the face the anode and anothernode belong to, then an exception is thrown. If the anode and anothernode are not the start and end points of the alinestring then an exception is thrown. Availability: 1.1 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. Description Adds an isolated node with point location apoint to an existing face with faceid aface to a topology atopology and returns the nodeid of the new node. If the spatial reference system (srid) of the point geometry is not the same as the topology, the apoint is not a point geometry, the point is null, or the point intersects an existing edge (even at the boundaries) then an exception is thrown. If the point already exists as a node, an exception is thrown. If aface is not null and the apoint is not within the face, then an exception is thrown. Availability: 1.1 This method implements the SQL/MM specification. SQL-MM: Topo-Net Routines: X+1.3.1
  • ST_Area - Returns the area of a polygonal geometry. Description Returns the area of a polygonal geometry. For geometry types a 2D Cartesian (planar) area is computed, with units specified by the SRID. For geography types by default area is determined on a spheroid with units in square meters. To compute the area using the faster but less accurate spherical model use ST_Area(geog,false). Enhanced: 2.0.0 - support for 2D polyhedral surfaces was introduced. Enhanced: 2.2.0 - measurement on spheroid performed with GeographicLib for improved accuracy and robustness. Requires PROJ >= 4.9.0 to take advantage of the new feature. Changed: 3.0.0 - does not depend on SFCGAL anymore. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 8.1.2, 9.5.3 This function supports Polyhedral surfaces. For polyhedral surfaces, only supports 2D polyhedral surfaces (not 2.5D). For 2.5D, may give a non-zero answer, but only for the faces that sit completely in XY plane.
  • ST_AsBinary - Return the OGC/ISO Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data. Description Returns the OGC/ISO Well-Known Binary (WKB) representation of the geometry. The first function variant defaults to encoding using server machine endian. The second function variant takes a text argument specifying the endian encoding, either little-endian ('NDR') or big-endian ('XDR'). WKB format is useful to read geometry data from the database and maintaining full numeric precision. This avoids the precision rounding that can happen with text formats such as WKT. To perform the inverse conversion of WKB to PostGIS geometry use . The OGC/ISO WKB format does not include the SRID. To get the EWKB format which does include the SRID use The default behavior in PostgreSQL 9.0 has been changed to output bytea in hex encoding. If your GUI tools require the old behavior, then SET bytea_output='escape' in your database. Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Enhanced: 2.0.0 support for higher coordinate dimensions was introduced. Enhanced: 2.0.0 support for specifying endian with geography was introduced. Availability: 1.5.0 geography support was introduced. Changed: 2.0.0 Inputs to this function can not be unknown -- must be geometry. Constructs such as ST_AsBinary('POINT(1 2)') are no longer valid and you will get an n st_asbinary(unknown) is not unique error. Code like that needs to be changed to ST_AsBinary('POINT(1 2)'::geometry);. If that is not possible, then install legacy.sql. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.1 This method implements the SQL/MM specification. SQL-MM 3: 5.1.37 This method supports Circular Strings and Curves. This function supports Polyhedral surfaces. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN). This function supports 3d and will not drop the z-index.
  • ST_AsGML - Return the geometry as a GML version 2 or 3 element. Description Return the geometry as a Geography Markup Language (GML) element. The version parameter, if specified, may be either 2 or 3. If no version parameter is specified then the default is assumed to be 2. The maxdecimaldigits argument may be used to reduce the maximum number of decimal places used in output (defaults to 15). Using the maxdecimaldigits parameter can cause output geometry to become invalid. To avoid this use with a suitable gridsize first. GML 2 refer to 2.1.2 version, GML 3 to 3.1.1 version The 'options' argument is a bitfield. It could be used to define CRS output type in GML output, and to declare data as lat/lon: 0: GML Short CRS (e.g EPSG:4326), default value 1: GML Long CRS (e.g urn:ogc:def:crs:EPSG::4326) 2: For GML 3 only, remove srsDimension attribute from output. 4: For GML 3 only, use <LineString> rather than <Curve> tag for lines. 16: Declare that datas are lat/lon (e.g srid=4326). Default is to assume that data are planars. This option is useful for GML 3.1.1 output only, related to axis order. So if you set it, it will swap the coordinates so order is lat lon instead of database lon lat. 32: Output the box of the geometry (envelope). The 'namespace prefix' argument may be used to specify a custom namespace prefix or no prefix (if empty). If null or omitted 'gml' prefix is used Availability: 1.3.2 Availability: 1.5.0 geography support was introduced. 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. Changed: 2.0.0 use default named args Enhanced: 2.1.0 id support was introduced, for GML 3. Only version 3+ of ST_AsGML supports Polyhedral Surfaces and TINS. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 17.2 This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN).
  • ST_AsText - Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata. Description Returns the OGC Well-Known Text (WKT) representation of the geometry/geography. The optional maxdecimaldigits argument may be used to limit the number of digits after the decimal point in output ordinates (defaults to 15). To perform the inverse conversion of WKT representation to PostGIS geometry use . The standard OGC WKT representation does not include the SRID. To include the SRID as part of the output representation, use the non-standard PostGIS function The textual representation of numbers in WKT may not maintain full floating-point precision. To ensure full accuracy for data storage or transport it is best to use Well-Known Binary (WKB) format (see and maxdecimaldigits). Using the maxdecimaldigits parameter can cause output geometry to become invalid. To avoid this use with a suitable gridsize first. Availability: 1.5 - support for geography was introduced. Enhanced: 2.5 - optional parameter precision introduced. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.1 This method implements the SQL/MM specification. SQL-MM 3: 5.1.25 This method supports Circular Strings and Curves.
  • ST_Boundary - Returns the boundary of a geometry. Description Returns the closure of the combinatorial boundary of this Geometry. The combinatorial boundary is defined as described in section 3.12.3.2 of the OGC SPEC. Because the result of this function is a closure, and hence topologically closed, the resulting boundary can be represented using representational geometry primitives as discussed in the OGC SPEC, section 3.12.2. Performed by the GEOS module Prior to 2.0.0, this function throws an exception if used with GEOMETRYCOLLECTION. From 2.0.0 up it will return NULL instead (unsupported input). This method implements the OGC Simple Features Implementation Specification for SQL 1.1. OGC SPEC s2.1.1.1 This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1.17 This function supports 3d and will not drop the z-index. Enhanced: 2.1.0 support for Triangle was introduced Changed: 3.2.0 support for TIN, does not use geos, does not linearize curves
  • ST_Buffer - Computes a geometry covering all points within a given distance from a geometry. Description Computes a POLYGON or MULTIPOLYGON that represents all points whose distance from a geometry/geography is less than or equal to a given distance. A negative distance shrinks the geometry rather than expanding it. A negative distance may shrink a polygon completely, in which case POLYGON EMPTY is returned. For points and lines negative distances always return empty results. For geometry, the distance is specified in the units of the Spatial Reference System of the geometry. For geography, the distance is specified in meters. The optional third parameter controls the buffer accuracy and style. The accuracy of circular arcs in the buffer is specified as the number of line segments used to approximate a quarter circle (default is 8). The buffer style can be specifed by providing a list of blank-separated key=value pairs as follows: 'quad_segs=#' : number of line segments used to approximate a quarter circle (default is 8). 'endcap=round|flat|square' : endcap style (defaults to "round"). 'butt' is accepted as a synonym for 'flat'. 'join=round|mitre|bevel' : join style (defaults to "round"). 'miter' is accepted as a synonym for 'mitre'. 'mitre_limit=#.#' : mitre ratio limit (only affects mitered join style). 'miter_limit' is accepted as a synonym for 'mitre_limit'. 'side=both|left|right' : 'left' or 'right' performs a single-sided buffer on the geometry, with the buffered side relative to the direction of the line. This is only applicable to LINESTRING geometry and does not affect POINT or POLYGON geometries. By default end caps are square. For geography this is a thin wrapper around the geometry implementation. It determines a planar spatial reference system that best fits the bounding box of the geography object (trying UTM, Lambert Azimuthal Equal Area (LAEA) North/South pole, and finally Mercator ). The buffer is computed in the planar space, and then transformed back to WGS84. This may not produce the desired behavior if the input object is much larger than a UTM zone or crosses the dateline Buffer output is always a valid polygonal geometry. Buffer can handle invalid inputs, so buffering by distance 0 is sometimes used as a way of repairing invalid polygons. can also be used for this purpose. Buffering is sometimes used to perform a within-distance search. For this use case it is more efficient to use . This function ignores the Z dimension. It always gives a 2D result even when used on a 3D geometry. Enhanced: 2.5.0 - ST_Buffer geometry support was enhanced to allow for side buffering specification side=both|left|right. 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. Performed by the GEOS module. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.3 This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1.30
  • ST_Centroid - Returns the geometric center of a geometry. Description Computes a point which is the geometric center of mass of a geometry. For [MULTI]POINTs, the centroid is the arithmetic mean of the input coordinates. For [MULTI]LINESTRINGs, the centroid is computed using the weighted length of each line segment. For [MULTI]POLYGONs, the centroid is computed in terms of area. If an empty geometry is supplied, an empty GEOMETRYCOLLECTION is returned. If NULL is supplied, NULL is returned. If CIRCULARSTRING or COMPOUNDCURVE are supplied, they are converted to linestring with CurveToLine first, then same than for LINESTRING For mixed-dimension input, the result is equal to the centroid of the component Geometries of highest dimension (since the lower-dimension geometries contribute zero "weight" to the centroid). Note that for polygonal geometries the centroid does not necessarily lie in the interior of the polygon. For example, see the diagram below of the centroid of a C-shaped polygon. To construct a point guaranteed to lie in the interior of a polygon use . New in 2.3.0 : supports CIRCULARSTRING and COMPOUNDCURVE (using CurveToLine) Availability: 2.4.0 support for geography was introduced. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. 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. Description Changes the shape of an edge without affecting the topology structure. If any arguments are null, the given edge does not exist in the edge table of the topology schema, the acurve is not a LINESTRING, or the modification would change the underlying topology then an error is thrown. If the spatial reference system (srid) of the acurve geometry is not the same as the topology an exception is thrown. If the new acurve is not simple, then an error is thrown. If moving the edge from old to new position would hit an obstacle then an error is thrown. Availability: 1.1.0 Enhanced: 2.0.0 adds topological consistency enforcement This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details X.3.6
  • ST_Contains - Tests if every point of B lies in A, and their interiors have a point in common Description Returns TRUE if geometry A contains geometry B. A contains B if and only if all points of B lie inside (i.e. in the interior or boundary of) A (or equivalently, no points of B lie in the exterior of A), and the interiors of A and B have at least one point in common. In mathematical terms: ST_Contains(A, B) ⇔ (A ⋂ B = B) ∧ (Int(A) ⋂ Int(B) ≠ ∅) The contains relationship is reflexive: every geometry contains itself. (In contrast, in the predicate a geometry does not properly contain itself.) The relationship is antisymmetric: if ST_Contains(A,B) = true and ST_Contains(B,A) = true, then the two geometries must be topologically equal (ST_Equals(A,B) = true). ST_Contains is the converse of . So, ST_Contains(A,B) = ST_Within(B,A). Because the interiors must have a common point, a subtlety of the definition is that polygons and lines do not contain lines and points lying fully in their boundary. For further details see Subtleties of OGC Covers, Contains, Within. The predicate provides a more inclusive relationship. This function automatically includes a bounding box comparison that makes use of any spatial indexes that are available on the geometries. To avoid index use, use the function _ST_Contains. Performed by the GEOS module Enhanced: 2.3.0 Enhancement to PIP short-circuit extended to support MultiPoints with few points. Prior versions only supported point in polygon. Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Do not use this function with invalid geometries. You will get unexpected results. NOTE: this is the "allowable" version that returns a boolean, not an integer. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.2 // s2.1.13.3 - same as within(geometry B, geometry A) This method implements the SQL/MM specification. SQL-MM 3: 5.1.31
  • ST_ConvexHull - Computes the convex hull of a geometry. Description Computes the convex hull of a geometry. The convex hull is the smallest convex geometry that encloses all geometries in the input. One can think of the convex hull as the geometry obtained by wrapping an rubber band around a set of geometries. This is different from a concave hull which is analogous to "shrink-wrapping" the geometries. A convex hull is often used to determine an affected area based on a set of point observations. In the general case the convex hull is a Polygon. The convex hull of two or more collinear points is a two-point LineString. The convex hull of one or more identical points is a Point. This is not an aggregate function. To compute the convex hull of a set of geometries, use to aggregate them into a geometry collection (e.g. ST_ConvexHull(ST_Collect(geom)). Performed by the GEOS module This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.3 This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1.16 This function supports 3d and will not drop the z-index.
  • ST_CoordDim - Return the coordinate dimension of a geometry. Description Return the coordinate dimension of the ST_Geometry value. This is the MM compliant alias name for This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 5.1.3 This method supports Circular Strings and Curves. This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN).
  • ST_CreateTopoGeo - Adds a collection of geometries to a given empty topology and returns a message detailing success. Description Adds a collection of geometries to a given empty topology and returns a message detailing success. Useful for populating an empty topology. Availability: 2.0 This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details -- X.3.18
  • ST_Crosses - Tests if two geometries have some, but not all, interior points in common Description Compares two geometry objects and returns true if their intersection "spatially crosses"; that is, the geometries have some, but not all interior points in common. The intersection of the interiors of the geometries must be non-empty and must have dimension less than the maximum dimension of the two input geometries, and the intersection of the two geometries must not equal either geometry. Otherwise, it returns false. The crosses relation is symmetric and irreflexive. In mathematical terms: ST_Crosses(A, B) ⇔ (dim( Int(A) ⋂ Int(B) ) < max( dim( Int(A) ), dim( Int(B) ) )) ∧ (A ⋂ B ≠ A) ∧ (A ⋂ B ≠ B) Geometries cross if their DE-9IM Intersection Matrix matches: T*T****** for Point/Line, Point/Area, and Line/Area situations T*****T** for Line/Point, Area/Point, and Area/Line situations 0******** for Line/Line situations the result is false for Point/Point and Area/Area situations The OpenGIS Simple Features Specification defines this predicate only for Point/Line, Point/Area, Line/Line, and Line/Area situations. JTS / GEOS extends the definition to apply to Line/Point, Area/Point and Area/Line situations as well. This makes the relation symmetric. This function automatically includes a bounding box comparison that makes use of any spatial indexes that are available on the geometries. Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.13.3 This method implements the SQL/MM specification. SQL-MM 3: 5.1.29
  • ST_CurveToLine - Converts a geometry containing curves to a linear geometry. Description Converts a CIRCULAR STRING to regular LINESTRING or CURVEPOLYGON to POLYGON or MULTISURFACE to MULTIPOLYGON. Useful for outputting to devices that can't support CIRCULARSTRING geometry types Converts a given geometry to a linear geometry. Each curved geometry or segment is converted into a linear approximation using the given `tolerance` and options (32 segments per quadrant and no options by default). The 'tolerance_type' argument determines interpretation of the `tolerance` argument. It can take the following values: 0 (default): Tolerance is max segments per quadrant. 1: Tolerance is max-deviation of line from curve, in source units. 2: Tolerance is max-angle, in radians, between generating radii. The 'flags' argument is a bitfield. 0 by default. Supported bits are: 1: Symmetric (orientation idependent) output. 2: Retain angle, avoids reducing angles (segment lengths) when producing symmetric output. Has no effect when Symmetric flag is off. Availability: 1.3.0 Enhanced: 2.4.0 added support for max-deviation and max-angle tolerance, and for symmetric output. Enhanced: 3.0.0 implemented a minimum number of segments per linearized arc to prevent topological collapse. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 7.1.7 This function supports 3d and will not drop the z-index. This method supports Circular Strings and Curves.
  • ST_Difference - Computes a geometry representing the part of geometry A that does not intersect geometry B. Description Returns a geometry representing the part of geometry A that does not intersect geometry B. This is equivalent to A - ST_Intersection(A,B). If A is completely contained in B then an empty atomic geometry of appropriate type is returned. This is the only overlay function where input order matters. ST_Difference(A, B) always returns a portion of A. If the optional gridSize argument is provided, the inputs are snapped to a grid of the given size, and the result vertices are computed on that same grid. (Requires GEOS-3.9.0 or higher) Performed by the GEOS module Enhanced: 3.1.0 accept a gridSize parameter. Requires GEOS >= 3.9.0 to use the gridSize parameter. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.3 This method implements the SQL/MM specification. SQL-MM 3: 5.1.20 This function supports 3d and will not drop the z-index. However, the result is computed using XY only. The result Z values are copied, averaged or interpolated.
  • ST_Dimension - Returns the topological dimension of a geometry. Description Return the topological dimension of this Geometry object, which must be less than or equal to the coordinate dimension. OGC SPEC s2.1.1.1 - returns 0 for POINT, 1 for LINESTRING, 2 for POLYGON, and the largest dimension of the components of a GEOMETRYCOLLECTION. If the dimension is unknown (e.g. for an empty GEOMETRYCOLLECTION) 0 is returned. This method implements the SQL/MM specification. SQL-MM 3: 5.1.2 Enhanced: 2.0.0 support for Polyhedral surfaces and TINs was introduced. No longer throws an exception if given empty geometry. Prior to 2.0.0, this function throws an exception if used with empty geometry. This function supports Polyhedral surfaces. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN).
  • ST_Disjoint - Tests if two geometries have no points in common Description Returns true if two geometries are disjoint. Geometries are disjoint if they have no point in common. If any other spatial relationship is true for a pair of geometries, they are not disjoint. Disjoint implies that is false. In mathematical terms: ST_Disjoint(A, B) ⇔ A ⋂ B = ∅ Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Performed by the GEOS module This function call does not use indexes. A negated predicate can be used as a more performant alternative that uses indexes: ST_Disjoint(A,B) = NOT ST_Intersects(A,B) NOTE: this is the "allowable" version that returns a boolean, not an integer. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.2 //s2.1.13.3 - a.Relate(b, 'FF*FF****') This method implements the SQL/MM specification. SQL-MM 3: 5.1.26
  • ST_Distance - Returns the distance between two geometry or geography values. Description For types returns the minimum 2D Cartesian (planar) distance between two geometries, in projected units (spatial ref units). For types defaults to return the minimum geodesic distance between two geographies in meters, compute on the spheroid determined by the SRID. If use_spheroid is false, a faster spherical calculation is used. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 5.1.23 This method supports Circular Strings and Curves. Availability: 1.5.0 geography support was introduced in 1.5. Speed improvements for planar to better handle large or many vertex geometries Enhanced: 2.1.0 improved speed for geography. See Making Geography faster for details. Enhanced: 2.1.0 - support for curved geometries was introduced. Enhanced: 2.2.0 - measurement on spheroid performed with GeographicLib for improved accuracy and robustness. Requires PROJ >= 4.9.0 to take advantage of the new feature. Changed: 3.0.0 - does not depend on SFCGAL anymore.
  • ST_EndPoint - Returns the last point of a LineString or CircularLineString. Description Returns the last point of a LINESTRING or CIRCULARLINESTRING geometry as a POINT. Returns NULL if the input is not a LINESTRING or CIRCULARLINESTRING. This method implements the SQL/MM specification. SQL-MM 3: 7.1.4 This function supports 3d and will not drop the z-index. This method supports Circular Strings and Curves. Changed: 2.0.0 no longer works with single geometry MultiLineStrings. In older versions of PostGIS a single-line MultiLineString would work with this function and return the end point. In 2.0.0 it returns NULL like any other MultiLineString. The old behavior was an undocumented feature, but people who assumed they had their data stored as LINESTRING may experience these returning NULL in 2.0.0.
  • ST_Envelope - Returns a geometry representing the bounding box of a geometry. Description Returns the double-precision (float8) minimum bounding box for the supplied geometry, as a geometry. The polygon is defined by the corner points of the bounding box ((MINX, MINY), (MINX, MAXY), (MAXX, MAXY), (MAXX, MINY), (MINX, MINY)). (PostGIS will add a ZMIN/ZMAX coordinate as well). Degenerate cases (vertical lines, points) will return a geometry of lower dimension than POLYGON, ie. POINT or LINESTRING. Availability: 1.5.0 behavior changed to output double precision instead of float4 This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.1 This method implements the SQL/MM specification. SQL-MM 3: 5.1.19
  • ST_Equals - Tests if two geometries include the same set of points Description Returns true if the given geometries are "topologically equal". Use this for a 'better' answer than '='. Topological equality means that the geometries have the same dimension, and their point-sets occupy the same space. This means that the order of vertices may be different in topologically equal geometries. To verify the order of points is consistent use (it must be noted ST_OrderingEquals is a little more stringent than simply verifying order of points are the same). In mathematical terms: ST_Equals(A, B) ⇔ A = B The following relation holds: ST_Equals(A, B) ⇔ ST_Within(A,B) ∧ ST_Within(B,A) Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.2 This method implements the SQL/MM specification. SQL-MM 3: 5.1.24 Changed: 2.2.0 Returns true even for invalid geometries if they are binary equal
  • ST_ExteriorRing - Returns a LineString representing the exterior ring of a Polygon. Description Returns a LINESTRING representing the exterior ring (shell) of a POLYGON. Returns NULL if the geometry is not a polygon. This function does not support MULTIPOLYGONs. For MULTIPOLYGONs use in conjunction with or This method implements the OGC Simple Features Implementation Specification for SQL 1.1. 2.1.5.1 This method implements the SQL/MM specification. SQL-MM 3: 8.2.3, 8.3.3 This function supports 3d and will not drop the z-index.
  • ST_GMLToSQL - Return a specified ST_Geometry value from GML representation. This is an alias name for ST_GeomFromGML Description This method implements the SQL/MM specification. SQL-MM 3: 5.1.50 (except for curves support). Availability: 1.5, requires libxml2 1.6+ Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced. Enhanced: 2.0.0 default srid optional parameter added.
  • ST_GeomCollFromText - Makes a collection Geometry from collection WKT with the given SRID. If SRID is not given, it defaults to 0. Description Makes a collection Geometry from the Well-Known-Text (WKT) representation with the given SRID. If SRID is not given, it defaults to 0. OGC SPEC 3.2.6.2 - option SRID is from the conformance suite Returns null if the WKT is not a GEOMETRYCOLLECTION If you are absolutely sure all your WKT geometries are collections, don't use this function. It is slower than ST_GeomFromText since it adds an additional validation step. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s3.2.6.2 This method implements the SQL/MM specification.
  • ST_GeomFromText - Return a specified ST_Geometry value from Well-Known Text representation (WKT). Description Constructs a PostGIS ST_Geometry object from the OGC Well-Known text representation. There are two variants of ST_GeomFromText function. The first takes no SRID and returns a geometry with no defined spatial reference system (SRID=0). The second takes a SRID as the second argument and returns a geometry that includes this SRID as part of its metadata. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s3.2.6.2 - option SRID is from the conformance suite. This method implements the SQL/MM specification. SQL-MM 3: 5.1.40 This method supports Circular Strings and Curves. While not OGC-compliant, is faster than ST_GeomFromText and ST_PointFromText. It is also easier to use for numeric coordinate values. is another option similar in speed to and is OGC-compliant, but doesn't support anything but 2D points. 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_GeomFromWKB - Creates a geometry instance from a Well-Known Binary geometry representation (WKB) and optional SRID. Description The ST_GeomFromWKB function, takes a well-known binary representation of a geometry and a Spatial Reference System ID (SRID) and creates an instance of the appropriate geometry type. This function plays the role of the Geometry Factory in SQL. This is an alternate name for ST_WKBToSQL. If SRID is not specified, it defaults to 0 (Unknown). This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s3.2.7.2 - the optional SRID is from the conformance suite This method implements the SQL/MM specification. SQL-MM 3: 5.1.41 This method supports Circular Strings and Curves.
  • ST_GeometryFromText - Return a specified ST_Geometry value from Well-Known Text representation (WKT). This is an alias name for ST_GeomFromText Description This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 5.1.40
  • ST_GeometryN - Return an element of a geometry collection. Description Return the 1-based Nth element geometry of an input geometry which is a GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, MULTICURVE, MULTI)POLYGON, or POLYHEDRALSURFACE. Otherwise, returns NULL. Index is 1-based as for OGC specs since version 0.8.0. Previous versions implemented this as 0-based instead. To extract all elements of a geometry, is more efficient and works for atomic geometries. Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Changed: 2.0.0 Prior versions would return NULL for singular geometries. This was changed to return the geometry for ST_GeometryN(..,1) case. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 9.1.5 This function supports 3d and will not drop the z-index. This method supports Circular Strings and Curves. This function supports Polyhedral surfaces. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN).
  • ST_GeometryType - Returns the SQL-MM type of a geometry as text. Description Returns the type of the geometry as a string. EG: 'ST_LineString', 'ST_Polygon','ST_MultiPolygon' etc. This function differs from GeometryType(geometry) in the case of the string and ST in front that is returned, as well as the fact that it will not indicate whether the geometry is measured. Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. This method implements the SQL/MM specification. SQL-MM 3: 5.1.4 This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces.
  • ST_GetFaceEdges - Returns a set of ordered edges that bound aface. Description Returns a set of ordered edges that bound aface. Each output consists of a sequence and edgeid. Sequence numbers start with value 1. Enumeration of each ring edges start from the edge with smallest identifier. Order of edges follows a left-hand-rule (bound face is on the left of each directed edge). Availability: 2.0 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. Description Returns the polygon in the given topology with the specified face id. Builds the polygon from the edges making up the face. Availability: 1.1 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 it in the topology.topology table. Description This is the SQL-MM equivalent of . It lacks options for spatial reference system and tolerance. it returns a text description of the topology creation, instead of the topology id. Availability: 1.1 This method implements the SQL/MM specification. SQL-MM 3 Topo-Geo and Topo-Net 3: Routine Details: X.3.17
  • ST_InteriorRingN - Returns the Nth interior ring (hole) of a Polygon. Description Returns the Nth interior ring (hole) of a POLYGON geometry as a LINESTRING. The index starts at 1. Returns NULL if the geometry is not a polygon or the index is out of range. This function does not support MULTIPOLYGONs. For MULTIPOLYGONs use in conjunction with or This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 8.2.6, 8.3.5 This function supports 3d and will not drop the z-index.
  • ST_Intersection - Computes a geometry representing the shared portion of geometries A and B. Description Returns a geometry representing the point-set intersection of two geometries. In other words, that portion of geometry A and geometry B that is shared between the two geometries. If the geometries have no points in common (i.e. are disjoint) then an empty atomic geometry of appropriate type is returned. If the optional gridSize argument is provided, the inputs are snapped to a grid of the given size, and the result vertices are computed on that same grid. (Requires GEOS-3.9.0 or higher) ST_Intersection in conjunction with is useful for clipping geometries such as in bounding box, buffer, or region queries where you only require the portion of a geometry that is inside a country or region of interest. For geography this is a thin wrapper around the geometry implementation. It first determines the best SRID that fits the bounding box of the 2 geography objects (if geography objects are within one half zone UTM but not same UTM will pick one of those) (favoring UTM or Lambert Azimuthal Equal Area (LAEA) north/south pole, and falling back on mercator in worst case scenario) and then intersection in that best fit planar spatial ref and retransforms back to WGS84 geography. This function will drop the M coordinate values if present. If working with 3D geometries, you may want to use SFGCAL based which does a proper 3D intersection for 3D geometries. Although this function works with Z-coordinate, it does an averaging of Z-Coordinate. Performed by the GEOS module Enhanced: 3.1.0 accept a gridSize parameter Requires GEOS >= 3.9.0 to use the gridSize parameter Changed: 3.0.0 does not depend on SFCGAL. Availability: 1.5 support for geography data type was introduced. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.3 This method implements the SQL/MM specification. SQL-MM 3: 5.1.18 This function supports 3d and will not drop the z-index. However, the result is computed using XY only. The result Z values are copied, averaged or interpolated.
  • ST_Intersects - Tests if two geometries intersect (they have at least one point in common) Description Returns true if two geometries intersect. Geometries intersect if they have any point in common. For geography, a distance tolerance of 0.00001 meters is used (so points that are very close are considered to intersect). In mathematical terms: ST_Intersects(A, B) ⇔ A ⋂ B ≠ ∅ Geometries intersect if their DE-9IM Intersection Matrix matches one of: T******** *T******* ***T***** ****T**** Spatial intersection is implied by all the other spatial relationship tests, except , which tests that geometries do NOT intersect. This function automatically includes a bounding box comparison that makes use of any spatial indexes that are available on the geometries. Changed: 3.0.0 SFCGAL version removed and native support for 2D TINS added. Enhanced: 2.5.0 Supports GEOMETRYCOLLECTION. Enhanced: 2.3.0 Enhancement to PIP short-circuit extended to support MultiPoints with few points. Prior versions only supported point in polygon. Performed by the GEOS module (for geometry), geography is native Availability: 1.5 support for geography was introduced. For geography, this function has a distance tolerance of about 0.00001 meters and uses the sphere rather than spheroid calculation. NOTE: this is the "allowable" version that returns a boolean, not an integer. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.2 //s2.1.13.3 - ST_Intersects(g1, g2 ) --> Not (ST_Disjoint(g1, g2 )) This method implements the SQL/MM specification. SQL-MM 3: 5.1.27 This method supports Circular Strings and Curves. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN).
  • ST_IsClosed - Tests if a LineStrings's start and end points are coincident. For a PolyhedralSurface tests if it is closed (volumetric). Description Returns TRUE if the LINESTRING's start and end points are coincident. For Polyhedral Surfaces, reports if the surface is areal (open) or volumetric (closed). This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 7.1.5, 9.3.3 SQL-MM defines the result of ST_IsClosed(NULL) to be 0, while PostGIS returns NULL. This function supports 3d and will not drop the z-index. This method supports Circular Strings and Curves. Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. This function supports Polyhedral surfaces.
  • ST_IsEmpty - Tests if a geometry is empty. Description Returns true if this Geometry is an empty geometry. If true, then this Geometry represents an empty geometry collection, polygon, point etc. SQL-MM defines the result of ST_IsEmpty(NULL) to be 0, while PostGIS returns NULL. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.1 This method implements the SQL/MM specification. SQL-MM 3: 5.1.7 This method supports Circular Strings and Curves. 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_IsRing - Tests if a LineString is closed and simple. Description Returns TRUE if this LINESTRING is both (ST_StartPoint(g) ~= ST_Endpoint(g)) and (does not self intersect). This method implements the OGC Simple Features Implementation Specification for SQL 1.1. 2.1.5.1 This method implements the SQL/MM specification. SQL-MM 3: 7.1.6 SQL-MM defines the result of ST_IsRing(NULL) to be 0, while PostGIS returns NULL.
  • ST_IsSimple - Tests if a geometry has no points of self-intersection or self-tangency. Description Returns true if this Geometry has no anomalous geometric points, such as self-intersection or self-tangency. For more information on the OGC's definition of geometry simplicity and validity, refer to "Ensuring OpenGIS compliancy of geometries" SQL-MM defines the result of ST_IsSimple(NULL) to be 0, while PostGIS returns NULL. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.1 This method implements the SQL/MM specification. SQL-MM 3: 5.1.8 This function supports 3d and will not drop the z-index.
  • ST_IsValid - Tests if a geometry is well-formed in 2D. Description Tests if an ST_Geometry value is well-formed and valid in 2D according to the OGC rules. For geometries with 3 and 4 dimensions, the validity is still only tested in 2 dimensions. For geometries that are invalid, a PostgreSQL NOTICE is emitted providing details of why it is not valid. For the version with the flags parameter, supported values are documented in This version does not print a NOTICE explaining invalidity. For more information on the definition of geometry validity, refer to SQL-MM defines the result of ST_IsValid(NULL) to be 0, while PostGIS returns NULL. Performed by the GEOS module. The version accepting flags is available starting with 2.0.0. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 5.1.9 Neither OGC-SFS nor SQL-MM specifications include a flag argument for ST_IsValid. The flag is a PostGIS extension.
  • ST_Length - Returns the 2D length of a linear geometry. Description For geometry types: returns the 2D Cartesian length of the geometry if it is a LineString, MultiLineString, ST_Curve, ST_MultiCurve. For areal geometries 0 is returned; use instead. The units of length is determined by the spatial reference system of the geometry. For geography types: computation is performed using the inverse geodesic calculation. Units of length are in meters. If PostGIS is compiled with PROJ version 4.8.0 or later, the spheroid is specified by the SRID, otherwise it is exclusive to WGS84. If use_spheroid = false, then the calculation is based on a sphere instead of a spheroid. Currently for geometry this is an alias for ST_Length2D, but this may change to support higher dimensions. 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 For geography the calculation defaults to using a spheroidal model. To use the faster but less accurate spherical calculation use ST_Length(gg,false); This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.5.1 This method implements the SQL/MM specification. SQL-MM 3: 7.1.2, 9.3.4 Availability: 1.5.0 geography support was introduced in 1.5.
  • ST_LineFromText - Makes a Geometry from WKT representation with the given SRID. If SRID is not given, it defaults to 0. Description Makes a Geometry from WKT with the given SRID. If SRID is not given, it defaults to 0. If WKT passed in is not a LINESTRING, then null is returned. OGC SPEC 3.2.6.2 - option SRID is from the conformance suite. If you know all your geometries are LINESTRINGS, its more efficient to just use ST_GeomFromText. This just calls ST_GeomFromText and adds additional validation that it returns a linestring. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s3.2.6.2 This method implements the SQL/MM specification. SQL-MM 3: 7.2.8
  • ST_LineFromWKB - Makes a LINESTRING from WKB with the given SRID Description The ST_LineFromWKB function, takes a well-known binary representation of geometry and a Spatial Reference System ID (SRID) and creates an instance of the appropriate geometry type - in this case, a LINESTRING geometry. This function plays the role of the Geometry Factory in SQL. If an SRID is not specified, it defaults to 0. NULL is returned if the input bytea does not represent a LINESTRING. OGC SPEC 3.2.6.2 - option SRID is from the conformance suite. If you know all your geometries are LINESTRINGs, its more efficient to just use . This function just calls and adds additional validation that it returns a linestring. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s3.2.6.2 This method implements the SQL/MM specification. SQL-MM 3: 7.2.9
  • ST_LinestringFromWKB - Makes a geometry from WKB with the given SRID. Description The ST_LinestringFromWKB function, takes a well-known binary representation of geometry and a Spatial Reference System ID (SRID) and creates an instance of the appropriate geometry type - in this case, a LINESTRING geometry. This function plays the role of the Geometry Factory in SQL. If an SRID is not specified, it defaults to 0. NULL is returned if the input bytea does not represent a LINESTRING geometry. This an alias for . OGC SPEC 3.2.6.2 - optional SRID is from the conformance suite. If you know all your geometries are LINESTRINGs, it's more efficient to just use . This function just calls and adds additional validation that it returns a LINESTRING. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s3.2.6.2 This method implements the SQL/MM specification. SQL-MM 3: 7.2.9
  • ST_LocateAlong - Returns the point(s) on a geometry that match a measure value. Description Returns the location(s) along a measured geometry that have the given measure values. The result is a Point or MultiPoint. Polygonal inputs are not supported. If offset is provided, the result is offset to the left or right of the input line by the specified distance. A positive offset will be to the left, and a negative one to the right. Use this function only for linear geometries with an M component The semantic is specified by the ISO/IEC 13249-3 SQL/MM Spatial standard. Availability: 1.1.0 by old name ST_Locate_Along_Measure. Changed: 2.0.0 in prior versions this used to be called ST_Locate_Along_Measure. This function supports M coordinates. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1.13
  • ST_LocateBetween - Returns the portions of a geometry that match a measure range. Description Return a geometry (collection) with the portions of the input measured geometry that match the specified measure range (inclusively). If the offset is provided, the result is offset to the left or right of the input line by the specified distance. A positive offset will be to the left, and a negative one to the right. Clipping a non-convex POLYGON may produce invalid geometry. The semantic is specified by the ISO/IEC 13249-3 SQL/MM Spatial standard. Availability: 1.1.0 by old name ST_Locate_Between_Measures. Changed: 2.0.0 - in prior versions this used to be called ST_Locate_Between_Measures. Enhanced: 3.0.0 - added support for POLYGON, TIN, TRIANGLE. This function supports M coordinates. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1
  • ST_M - Returns the M coordinate of a Point. Description Return the M coordinate of a Point, or NULL if not available. Input must be a Point. This is not (yet) part of the OGC spec, but is listed here to complete the point coordinate extractor function list. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. This function supports 3d and will not drop the z-index.
  • ST_MLineFromText - Return a specified ST_MultiLineString value from WKT representation. Description Makes a Geometry from Well-Known-Text (WKT) with the given SRID. If SRID is not given, it defaults to 0. OGC SPEC 3.2.6.2 - option SRID is from the conformance suite Returns null if the WKT is not a MULTILINESTRING If you are absolutely sure all your WKT geometries are points, don't use this function. It is slower than ST_GeomFromText since it adds an additional validation step. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s3.2.6.2 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 given, it defaults to 0. Description Makes a Geometry from WKT with the given SRID. If SRID is not given, it defaults to 0. OGC SPEC 3.2.6.2 - option SRID is from the conformance suite Returns null if the WKT is not a MULTIPOINT If you are absolutely sure all your WKT geometries are points, don't use this function. It is slower than ST_GeomFromText since it adds an additional validation step. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. 3.2.6.2 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 given, it defaults to 0. Description Makes a MultiPolygon from WKT with the given SRID. If SRID is not given, it defaults to 0. OGC SPEC 3.2.6.2 - option SRID is from the conformance suite Throws an error if the WKT is not a MULTIPOLYGON If you are absolutely sure all your WKT geometries are multipolygons, don't use this function. It is slower than ST_GeomFromText since it adds an additional validation step. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s3.2.6.2 This method implements the SQL/MM specification. SQL-MM 3: 9.6.4
  • ST_ModEdgeHeal - Heals two edges by deleting the node connecting them, modifying the first edgeand deleting the second edge. Returns the id of the deleted node. Description Heals two edges by deleting the node connecting them, modifying the first edge and deleting the second edge. Returns the id of the deleted node. Updates all existing joined edges and relationships accordingly. Availability: 2.0 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. Description Split an edge by creating a new node along an existing edge, modifying the original edge and adding a new edge. Updates all existing joined edges and relationships accordingly. Returns the identifier of the newly added node. Availability: 1.1 Changed: 2.0 - In prior versions, this was misnamed ST_ModEdgesSplit 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. Description Moves an isolated node in a topology from one point to another. If new apoint geometry exists as a node an error is thrown. If any arguments are null, the apoint is not a point, the existing node is not isolated (is a start or end point of an existing edge), new node location intersects an existing edge (even at the end points) or the new location is in a different face (since 3.2.0) then an exception is thrown. If the spatial reference system (srid) of the point geometry is not the same as the topology an exception is thrown. Availability: 2.0.0 Enhanced: 3.2.0 ensures the nod cannot be moved in a different face This method implements the SQL/MM specification. SQL-MM: Topo-Net Routines: X.3.2
  • ST_NewEdgeHeal - Heals 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. Description Heals two edges by deleting the node connecting them, deleting both edges, and replacing them with an edge whose direction is the same as the first edge provided. Returns the id of the new edge replacing the healed ones. Updates all existing joined edges and relationships accordingly. Availability: 2.0 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. Description Split an edge with edge id anedge by creating a new node with point location apoint along current 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. Updates all existing joined edges and relationships accordingly. If the spatial reference system (srid) of the point geometry is not the same as the topology, the apoint is not a point geometry, the point is null, the point already exists as a node, the edge does not correspond to an existing edge or the point is not within the edge then an exception is thrown. Availability: 1.1 This method implements the SQL/MM specification. SQL-MM: Topo-Net Routines: X.3.8
  • ST_NumGeometries - Returns the number of elements in a geometry collection. Description Returns the number of elements in a geometry collection (GEOMETRYCOLLECTION or MULTI*). For non-empty atomic geometries returns 1. For empty geometries returns 0. Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. 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. This method implements the SQL/MM specification. SQL-MM 3: 9.1.4 This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN).
  • ST_NumInteriorRings - Returns the number of interior rings (holes) of a Polygon. Description Return the number of interior rings of a polygon geometry. Return NULL if the geometry is not a polygon. This method implements the SQL/MM specification. SQL-MM 3: 8.2.5 Changed: 2.0.0 - in prior versions it would allow passing a MULTIPOLYGON, returning the number of interior rings of first POLYGON.
  • ST_NumPatches - Return the number of faces on a Polyhedral Surface. Will return null for non-polyhedral geometries. Description Return the number of faces on a Polyhedral Surface. Will return null for non-polyhedral geometries. This is an alias for ST_NumGeometries to support MM naming. Faster to use ST_NumGeometries if you don't care about MM convention. Availability: 2.0.0 This function supports 3d and will not drop the z-index. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM ISO/IEC 13249-3: 8.5 This function supports Polyhedral surfaces.
  • ST_NumPoints - Returns the number of points in a LineString or CircularString. Description Return the number of points in an ST_LineString or ST_CircularString value. Prior to 1.4 only works with linestrings as the specs state. From 1.4 forward this is an alias for ST_NPoints which returns number of vertexes for not just linestrings. Consider using ST_NPoints instead which is multi-purpose and works with many geometry types. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 7.2.4
  • ST_OrderingEquals - Tests if two geometries represent the same geometry and have points in the same directional order Description ST_OrderingEquals compares two geometries and returns t (TRUE) if the geometries are equal and the coordinates are in the same order; otherwise it returns f (FALSE). This function is implemented as per the ArcSDE SQL specification rather than SQL-MM. http://edndoc.esri.com/arcsde/9.1/sql_api/sqlapi3.htm#ST_OrderingEquals This method implements the SQL/MM specification. SQL-MM 3: 5.1.43
  • ST_Overlaps - Tests if two geometries have the same dimension and intersect, but each has at least one point not in the other Description Returns TRUE if geometry A and B "spatially overlap". Two geometries overlap if they have the same dimension, their interiors intersect in that dimension. and each has at least one point inside the other (or equivalently, neither one covers the other). The overlaps relation is symmetric and irreflexive. In mathematical terms: ST_Overlaps(A, B) ⇔ ( dim(A) = dim(B) = dim( Int(A) ⋂ Int(B) )) ∧ (A ⋂ B ≠ A) ∧ (A ⋂ B ≠ B) This function automatically includes a bounding box comparison that makes use of any spatial indexes that are available on the geometries. To avoid index use, use the function _ST_Overlaps. Performed by the GEOS module Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION NOTE: this is the "allowable" version that returns a boolean, not an integer. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.2 // s2.1.13.3 This method implements the SQL/MM specification. SQL-MM 3: 5.1.32
  • ST_PatchN - Returns the Nth geometry (face) of a PolyhedralSurface. Description Returns the 1-based Nth geometry (face) if the geometry is a POLYHEDRALSURFACE or POLYHEDRALSURFACEM. Otherwise, returns NULL. This returns the same answer as ST_GeometryN for PolyhedralSurfaces. Using ST_GeometryN is faster. Index is 1-based. If you want to extract all elements of a geometry is more efficient. Availability: 2.0.0 This method implements the SQL/MM specification. SQL-MM ISO/IEC 13249-3: 8.5 This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces.
  • ST_Perimeter - Returns the length of the boundary of a polygonal geometry or geography. Description Returns the 2D perimeter of the geometry/geography if it is a ST_Surface, ST_MultiSurface (Polygon, MultiPolygon). 0 is returned for non-areal geometries. For linear geometries use . For geometry types, units for perimeter measures are specified by the spatial reference system of the geometry. For geography types, the calculations are performed using the inverse geodesic problem, where perimeter units are in meters. If PostGIS is compiled with PROJ version 4.8.0 or later, the spheroid is specified by the SRID, otherwise it is exclusive to WGS84. If use_spheroid = false, then calculations will approximate a sphere instead of a spheroid. Currently this is an alias for ST_Perimeter2D, but this may change to support higher dimensions. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.5.1 This method implements the SQL/MM specification. SQL-MM 3: 8.1.3, 9.5.4 Availability 2.0.0: Support for geography was introduced
  • ST_Point - Creates a Point with X, Y and SRID values. Description Returns a Point with the given X and Y coordinate values. This is the SQL-MM equivalent for that takes just X and Y. For geodetic coordinates, X is longitude and Y is latitude Enhanced: 3.2.0 srid as an extra optional argument was added. Older installs require combining with ST_SetSRID to mark the srid on the geometry. 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. Description Constructs a PostGIS ST_Geometry point object from the OGC Well-Known text representation. If SRID is not given, it defaults to unknown (currently 0). If geometry is not a WKT point representation, returns null. If completely invalid WKT, then throws an error. There are 2 variants of ST_PointFromText function, the first takes no SRID and returns a geometry with no defined spatial reference system. The second takes a spatial reference id as the second argument and returns an ST_Geometry that includes this srid as part of its meta-data. The srid must be defined in the spatial_ref_sys table. If you are absolutely sure all your WKT geometries are points, don't use this function. It is slower than ST_GeomFromText since it adds an additional validation step. If you are building points from long lat coordinates and care more about performance and accuracy than OGC compliance, use or OGC compliant alias . This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s3.2.6.2 - option SRID is from the conformance suite. This method implements the SQL/MM specification. SQL-MM 3: 6.1.8
  • ST_PointFromWKB - Makes a geometry from WKB with the given SRID Description The ST_PointFromWKB function, takes a well-known binary representation of geometry and a Spatial Reference System ID (SRID) and creates an instance of the appropriate geometry type - in this case, a POINT geometry. This function plays the role of the Geometry Factory in SQL. If an SRID is not specified, it defaults to 0. NULL is returned if the input bytea does not represent a POINT geometry. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s3.2.7.2 This method implements the SQL/MM specification. SQL-MM 3: 6.1.9 This function supports 3d and will not drop the z-index. This method supports Circular Strings and Curves.
  • ST_PointN - Returns the Nth point in the first LineString or circular LineString in a geometry. Description Return the Nth point in a single linestring or circular linestring in the geometry. Negative values are counted backwards from the end of the LineString, so that -1 is the last point. Returns NULL if there is no linestring in the geometry. Index is 1-based as for OGC specs since version 0.8.0. Backward indexing (negative index) is not in OGC Previous versions implemented this as 0-based instead. If you want to get the Nth point of each LineString in a MultiLineString, use in conjunction with ST_Dump This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 7.2.5, 7.3.5 This function supports 3d and will not drop the z-index. This method supports Circular Strings and Curves. 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. Changed: 2.3.0 : negative indexing available (-1 is last point)
  • ST_PointOnSurface - Computes a point guaranteed to lie in a polygon, or on a geometry. Description Returns a POINT which is guaranteed to lie in the interior of a surface (POLYGON, MULTIPOLYGON, and CURVED POLYGON). In PostGIS this function also works on line and point geometries. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s3.2.14.2 // s3.2.18.2 This method implements the SQL/MM specification. SQL-MM 3: 8.1.5, 9.5.6. The specifications define ST_PointOnSurface for surface geometries only. PostGIS extends the function to support all common geometry types. Other databases (Oracle, DB2, ArcSDE) seem to support this function only for surfaces. SQL Server 2008 supports all common geometry types. This function supports 3d and will not drop the z-index.
  • ST_Polygon - Creates a Polygon from a LineString with a specified SRID. Description Returns a polygon built from the given LineString and sets the spatial reference system from the srid. ST_Polygon is similar to Variant 1 with the addition of setting the SRID. To create polygons with holes use Variant 2 and then . This function does not accept MultiLineStrings. Use to generate a LineString, or to extract LineStrings. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 8.3.2 This function supports 3d and will not drop the z-index.
  • ST_PolygonFromText - Makes a Geometry from WKT with the given SRID. If SRID is not given, it defaults to 0. Description Makes a Geometry from WKT with the given SRID. If SRID is not given, it defaults to 0. Returns null if WKT is not a polygon. OGC SPEC 3.2.6.2 - option SRID is from the conformance suite If you are absolutely sure all your WKT geometries are polygons, don't use this function. It is slower than ST_GeomFromText since it adds an additional validation step. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s3.2.6.2 This method implements the SQL/MM specification. SQL-MM 3: 8.3.6
  • ST_Relate - Tests if two geometries have a topological relationship matching an Intersection Matrix pattern, or computes their Intersection Matrix Description These functions allow testing and evaluating the spatial (topological) relationship between two geometries, as defined by the Dimensionally Extended 9-Intersection Model (DE-9IM). The DE-9IM is specified as a 9-element matrix indicating the dimension of the intersections between the Interior, Boundary and Exterior of two geometries. It is represented by a 9-character text string using the symbols 'F', '0', '1', '2' (e.g. 'FF1FF0102'). A specific kind of spatial relationship can be tested by matching the intersection matrix to an intersection matrix pattern. Patterns can include the additional symbols 'T' (meaning "intersection is non-empty") and '*' (meaning "any value"). Common spatial relationships are provided by the named functions , , , , , , , , , , and . Using an explicit pattern allows testing multiple conditions of intersects, crosses, etc in one step. It also allows testing spatial relationships which do not have a named spatial relationship function. For example, the relationship "Interior-Intersects" has the DE-9IM pattern T********, which is not evaluated by any named predicate. For more information refer to . Variant 1: Tests if two geometries are spatially related according to the given intersectionMatrixPattern. Unlike most of the named spatial relationship predicates, this does NOT automatically include an index call. The reason is that some relationships are true for geometries which do NOT intersect (e.g. Disjoint). If you are using a relationship pattern that requires intersection, then include the && index call. It is better to use a named relationship function if available, since they automatically use a spatial index where one exists. Also, they may implement performance optimizations which are not available with full relate evalation. Variant 2: Returns the DE-9IM matrix string for the spatial relationship between the two input geometries. The matrix string can be tested for matching a DE-9IM pattern using . Variant 3: Like variant 2, but allows specifying a Boundary Node Rule. A boundary node rule allows finer control over whether the endpoints of MultiLineStrings are considered to lie in the DE-9IM Interior or Boundary. The boundaryNodeRule values are: 1: OGC-Mod2 - line endpoints are in the Boundary if they occur an odd number of times. This is the rule defined by the OGC SFS standard, and is the default for ST_Relate. 2: Endpoint - all endpoints are in the Boundary. 3: MultivalentEndpoint - endpoints are in the Boundary if they occur more than once. In other words, the boundary is all the "attached" or "inner" endpoints (but not the "unattached/outer" ones). 4: MonovalentEndpoint - endpoints are in the Boundary if they occur only once. In other words, the boundary is all the "unattached" or "outer" endpoints. This function is not in the OGC spec, but is implied. see s2.1.13.2 This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.2 // s2.1.13.3 This method implements the SQL/MM specification. SQL-MM 3: 5.1.25 Performed by the GEOS module Enhanced: 2.0.0 - added support for specifying boundary node rule. Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION
  • ST_RemEdgeModFace - Removes an edge, and if the edge separates two facesdeletes one face and modifies the other face to cover the space of both. Description Removes an edge, and if the removed edge separates two faces deletes one face and modifies the other face to cover the space of both. Preferentially keeps the face on the right, to be consistent with . Returns the id of the face which is preserved. Updates all existing joined edges and relationships accordingly. Refuses to remove an edge participating in the definition of an existing TopoGeometry. Refuses to heal two faces if any TopoGeometry is defined by only one of them (and not the other). If any arguments are null, the given edge is unknown (must already exist in the edge table of the topology schema), the topology name is invalid then an error is thrown. Availability: 2.0 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. Description Removes an edge and, if the removed edge separated two faces, delete the original faces and replace them with a new face. Returns the id of a newly created face or NULL, if no new face is created. No new face is created when the removed edge is dangling or isolated or confined with the universe face (possibly making the universe flood into the face on the other side). Updates all existing joined edges and relationships accordingly. Refuses to remove an edge participating in the definition of an existing TopoGeometry. Refuses to heal two faces if any TopoGeometry is defined by only one of them (and not the other). If any arguments are null, the given edge is unknown (must already exist in the edge table of the topology schema), the topology name is invalid then an error is thrown. Availability: 2.0 This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.14
  • ST_RemoveIsoEdge - Removes an isolated edge and returns description of action. If the edge is not isolated, then an exception is thrown. Description Removes an isolated edge and returns description of action. If the edge is not isolated, then an exception is thrown. Availability: 1.1 This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X+1.3.3
  • 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. Description 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. Availability: 1.1 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 a geometry. Description Returns the spatial reference identifier for the ST_Geometry as defined in spatial_ref_sys table. spatial_ref_sys table is a table that catalogs all spatial reference systems known to PostGIS and is used for transformations from one spatial reference system to another. So verifying you have the right spatial reference system identifier is important if you plan to ever transform your geometries. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.1 This method implements the SQL/MM specification. SQL-MM 3: 5.1.5 This method supports Circular Strings and Curves.
  • ST_StartPoint - Returns the first point of a LineString. Description Returns the first point of a LINESTRING or CIRCULARLINESTRING geometry as a POINT. Returns NULL if the input is not a LINESTRING or CIRCULARLINESTRING. This method implements the SQL/MM specification. SQL-MM 3: 7.1.3 This function supports 3d and will not drop the z-index. This method supports Circular Strings and Curves. Enhanced: 3.2.0 returns a point for all geometries. Prior behavior returns NULLs if input was not a LineString. 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 old behavior was an undocumented feature, but people who assumed they had their data stored as LINESTRING may experience these returning NULL in 2.0.0.
  • ST_SymDifference - Computes a geometry representing the portions of geometries A and B that do not intersect. Description Returns a geometry representing the portions of geonetries A and B that do not intersect. This is equivalent to ST_Union(A,B) - ST_Intersection(A,B). It is called a symmetric difference because ST_SymDifference(A,B) = ST_SymDifference(B,A). If the optional gridSize argument is provided, the inputs are snapped to a grid of the given size, and the result vertices are computed on that same grid. (Requires GEOS-3.9.0 or higher) Performed by the GEOS module Enhanced: 3.1.0 accept a gridSize parameter. Requires GEOS >= 3.9.0 to use the gridSize parameter This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.3 This method implements the SQL/MM specification. SQL-MM 3: 5.1.21 This function supports 3d and will not drop the z-index. However, the result is computed using XY only. The result Z values are copied, averaged or interpolated.
  • ST_Touches - Tests if two geometries have at least one point in common, but their interiors do not intersect Description Returns TRUE if A and B intersect, but their interiors do not intersect. Equivalently, A and B have at least one point in common, and the common points lie in at least one boundary. For Point/Point inputs the relationship is always FALSE, since points do not have a boundary. In mathematical terms: ST_Touches(A, B) ⇔ (Int(A) ⋂ Int(B) ≠ ∅) ∧ (A ⋂ B ≠ ∅) This relationship holds if the DE-9IM Intersection Matrix for the two geometries matches one of: FT******* F**T***** F***T**** This function automatically includes a bounding box comparison that makes use of any spatial indexes that are available on the geometries. To avoid using an index, use _ST_Touches instead. Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.2 // s2.1.13.3 This method implements the SQL/MM specification. SQL-MM 3: 5.1.28
  • ST_Transform - Return a new geometry with coordinates transformed to a different spatial reference system. Description Returns a new geometry with its coordinates transformed to a different spatial reference system. The destination spatial reference to_srid may be identified by a valid SRID integer parameter (i.e. it must exist in the spatial_ref_sys table). Alternatively, a spatial reference defined as a PROJ.4 string can be used for to_proj and/or from_proj, however these methods are not optimized. If the destination spatial reference system is expressed with a PROJ.4 string instead of an SRID, the SRID of the output geometry will be set to zero. With the exception of functions with from_proj, input geometries must have a defined SRID. ST_Transform is often confused with . ST_Transform actually changes the coordinates of a geometry from one spatial reference system to another, while ST_SetSRID() simply changes the SRID identifier of the geometry. ST_Transform automatically selects a suitable conversion pipeline given the source and target spatial reference systems. To use a specific conversion method, use . Requires PostGIS be compiled with PROJ support. Use to confirm you have PROJ support compiled in. If using more than one transformation, it is useful to have a functional index on the commonly used transformations to take advantage of index usage. Prior to 1.3.4, this function crashes if used with geometries that contain CURVES. This is fixed in 1.3.4+ Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Enhanced: 2.3.0 support for direct PROJ.4 text was introduced. This method implements the SQL/MM specification. SQL-MM 3: 5.1.6 This method supports Circular Strings and Curves. This function supports Polyhedral surfaces.
  • ST_Union - Computes a geometry representing the point-set union of the input geometries. Description Unions the input geometries, merging geometry to produce a result geometry with no overlaps. The output may be an atomic geometry, a MultiGeometry, or a Geometry Collection. Comes in several variants: Two-input variant: returns a geometry that is the union of two input geometries. If either input is NULL, then NULL is returned. Array variant: returns a geometry that is the union of an array of geometries. Aggregate variant: returns a geometry that is the union of a rowset of geometries. The ST_Union() function is an "aggregate" function in the terminology of PostgreSQL. That means that it operates on rows of data, in the same way the SUM() and AVG() functions do and like most aggregates, it also ignores NULL geometries. See for a non-aggregate, single-input variant. The ST_Union array and set variants use the fast Cascaded Union algorithm described in http://blog.cleverelephant.ca/2009/01/must-faster-unions-in-postgis-14.html A gridSize can be specified to work in fixed-precision space. The inputs are snapped to a grid of the given size, and the result vertices are computed on that same grid. (Requires GEOS-3.9.0 or higher) may sometimes be used in place of ST_Union, if the result is not required to be non-overlapping. ST_Collect is usually faster than ST_Union because it performs no processing on the collected geometries. Performed by the GEOS module. ST_Union creates MultiLineString and does not sew LineStrings into a single LineString. Use to sew LineStrings. NOTE: this function was formerly called GeomUnion(), which was renamed from "Union" because UNION is an SQL reserved word. Enhanced: 3.1.0 accept a gridSize parameter. Requires GEOS >= 3.9.0 to use the gridSize parameter Changed: 3.0.0 does not depend on SFCGAL. Availability: 1.4.0 - ST_Union was enhanced. ST_Union(geomarray) was introduced and also faster aggregate collection in PostgreSQL. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.3 Aggregate version is not explicitly defined in OGC SPEC. This method implements the SQL/MM specification. SQL-MM 3: 5.1.19 the z-index (elevation) when polygons are involved. This function supports 3d and will not drop the z-index. However, the result is computed using XY only. The result Z values are copied, averaged or interpolated.
  • ST_Volume - Computes the volume of a 3D solid. If applied to surface (even closed) geometries will return 0. Description Availability: 2.2.0 This method needs SFCGAL backend. This function supports 3d and will not drop the z-index. This function supports Polyhedral surfaces. This function supports Triangles and Triangulated Irregular Network Surfaces (TIN). This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 9.1 (same as ST_3DVolume)
  • 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 Description 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 Description This method implements the SQL/MM specification. SQL-MM 3: 5.1.34
  • ST_Within - Tests if every point of A lies in B, and their interiors have a point in common Description Returns TRUE if geometry A is within geometry B. A is within B if and only if all points of A lie inside (i.e. in the interior or boundary of) B (or equivalently, no points of A lie in the exterior of B), and the interiors of A and B have at least one point in common. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID. In mathematical terms: ST_Within(A, B) ⇔ (A ⋂ B = A) ∧ (Int(A) ⋂ Int(B) ≠ ∅) The within relation is reflexive: every geometry is within itself. The relation is antisymmetric: if ST_Within(A,B) = true and ST_Within(B,A) = true, then the two geometries must be topologically equal (ST_Equals(A,B) = true). ST_Within is the converse of . So, ST_Within(A,B) = ST_Contains(B,A). Because the interiors must have a common point, a subtlety of the definition is that lines and points lying fully in the boundary of polygons or lines are not within the geometry. For further details see Subtleties of OGC Covers, Contains, Within. The predicate provides a more inclusive relationship. This function automatically includes a bounding box comparison that makes use of any spatial indexes that are available on the geometries. To avoid index use, use the function _ST_Within. Performed by the GEOS module Enhanced: 2.3.0 Enhancement to PIP short-circuit for geometry extended to support MultiPoints with few points. Prior versions only supported point in polygon. Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Do not use this function with invalid geometries. You will get unexpected results. NOTE: this is the "allowable" version that returns a boolean, not an integer. This method implements the OGC Simple Features Implementation Specification for SQL 1.1. s2.1.1.2 // s2.1.13.3 - a.Relate(b, 'T*F**F***') This method implements the SQL/MM specification. SQL-MM 3: 5.1.30
  • ST_X - Returns the X coordinate of a Point. Description Return the X coordinate of the point, or NULL if not available. Input must be a point. To get the minimum and maximum X value of geometry coordinates use the functions and . This method implements the SQL/MM specification. SQL-MM 3: 6.1.3 This function supports 3d and will not drop the z-index.
  • ST_Y - Returns the Y coordinate of a Point. Description Return the Y coordinate of the point, or NULL if not available. Input must be a point. To get the minimum and maximum Y value of geometry coordinates use the functions and . This method implements the OGC Simple Features Implementation Specification for SQL 1.1. This method implements the SQL/MM specification. SQL-MM 3: 6.1.4 This function supports 3d and will not drop the z-index.
  • ST_Z - Returns the Z coordinate of a Point. Description Return the Z coordinate of the point, or NULL if not available. Input must be a point. To get the minimum and maximum Z value of geometry coordinates use the functions and . This method implements the SQL/MM specification. This function supports 3d and will not drop the z-index.
  • TG_ST_SRID - Returns the spatial reference identifier for a topogeometry. Description Returns the spatial reference identifier for the ST_Geometry as defined in spatial_ref_sys table. spatial_ref_sys table is a table that catalogs all spatial reference systems known to PostGIS and is used for transformations from one spatial reference system to another. So verifying you have the right spatial reference system identifier is important if you plan to ever transform your geometries. Availability: 3.2.0 This method implements the SQL/MM specification. SQL-MM 3: 14.1.5

12.4. 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 transform - (favoring UTM, Lambert Azimuthal (North/South), and falling back on mercator in worst case scenario)

  • ST_Area - Returns the area of a polygonal geometry.
  • ST_AsBinary - Return the OGC/ISO 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 a geometry as a GeoJSON element.
  • ST_AsKML - Return the geometry as a KML element.
  • ST_AsSVG - Returns SVG path data for a geometry.
  • ST_AsText - Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.
  • ST_Azimuth - Returns the north-based azimuth of a line between two points.
  • ST_Buffer - Computes a geometry covering all points within a given distance from a geometry.
  • ST_Centroid - Returns the geometric center of a geometry.
  • ST_ClosestPoint - Returns the 2D point on g1 that is closest to g2. This is the first point of the shortest line from one geometry to the other.
  • ST_CoveredBy - Tests if every point of A lies in B
  • ST_Covers - Tests if every point of B lies in A
  • ST_DWithin - Tests if two geometries are within a given distance
  • ST_Distance - Returns the distance between two geometry or geography values.
  • 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 the coordinates and coordinate order geometry/geography A are the same as the coordinates and coordinate order of geometry/geography B.
  • ST_Intersection - Computes a geometry representing the shared portion of geometries A and B.
  • ST_Intersects - Tests if two geometries intersect (they have at least one point in common)
  • ST_Length - Returns the 2D length of a linear geometry.
  • ST_LineInterpolatePoint - Returns a point interpolated along a line at a fractional location.
  • ST_LineInterpolatePoints - Returns points interpolated along a line at a fractional interval.
  • ST_LineLocatePoint - Returns the fractional location of the closest point on a line to a point.
  • ST_LineSubstring - Returns the part of a line between two fractional locations.
  • ST_Perimeter - Returns the length of the boundary of a polygonal geometry or geography.
  • ST_Project - Returns a point projected from a start point by a distance and bearing (azimuth).
  • ST_Segmentize - Returns a modified geometry/geography having no segment longer than a given distance.
  • ST_ShortestLine - Returns the 2D shortest line between two geometries
  • ST_Summary - Returns a text summary of the contents of a geometry.
  • <-> - Returns the 2D distance between A and B.
  • && - Returns TRUE if A's 2D bounding box intersects B's 2D bounding box.

12.5. 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 contained by B's. Uses double precision bounding box.
  • ~ - Returns TRUE if A's bounding box is contains B's. Uses double precision bounding box.
  • = - 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 intersects B's bounding box.
  • &< - 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 is the same as B's.
  • ST_Retile - Return a set of configured tiles from an arbitrarily tiled raster coverage.
  • 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/ST_AsWKB - Return the Well-Known Binary (WKB) representation of the raster.
  • 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_GDALDrivers() to get a list of formats supported by your library.
  • ST_AsHexWKB - Return the Well-Known Binary (WKB) in Hex representation of the raster.
  • 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 or any of specified bands does not exist in the raster, then will try to use all bands.
  • ST_Aspect - Returns the aspect (in degrees by default) 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_BandFileSize - Returns the file size of a band stored in file system. If no bandnum specified, 1 is assumed.
  • ST_BandFileTimestamp - Returns the file timestamp of a band stored in file system. If no bandnum specified, 1 is assumed.
  • 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 band number not is specified, all bands are processed. If crop is not specified or TRUE, the output raster is cropped.
  • ST_ColorMap - Creates a new raster of up to four 8BUI bands (grayscale, RGB, RGBA) from the source raster and a specified band. Band 1 is assumed if not specified.
  • ST_Contains - Return true if no points of raster rastB lie in the exterior of raster rastA and at least one point of the interior of rastB lies in the interior of rastA.
  • ST_ContainsProperly - Return true if rastB intersects the interior of rastA but not the boundary or exterior of rastA.
  • ST_Contour - Generates a set of vector contours from the provided raster band, using the GDAL contouring algorithm.
  • 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_CountAgg - Aggregate. Returns the number of pixels in a given band of a set of rasters. 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_CoveredBy - Return true if no points of raster rastA lie outside raster rastB.
  • ST_Covers - Return true if no points of raster rastB lie outside raster rastA.
  • ST_DFullyWithin - Return true if rasters rastA and rastB are fully within the specified distance of each other.
  • ST_DWithin - Return true if rasters rastA and rastB are within the specified distance of each other.
  • ST_Disjoint - Return true if raster rastA does not spatially intersect rastB.
  • 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_DumpValues - Get the values of the specified band as a 2-dimension array.
  • ST_Envelope - Returns the polygon representation of the extent of the raster.
  • ST_FromGDALRaster - Returns a raster from a supported GDAL raster file.
  • ST_GeoReference - Returns the georeference meta data in GDAL or ESRI format as commonly seen in a world file. Default is GDAL.
  • ST_Grayscale - Creates a new one-8BUI band raster from the source raster and specified bands representing Red, Green and Blue
  • 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 scale inputs.
  • ST_Histogram - Returns a set of record summarizing a raster or raster coverage data distribution separate bin ranges. Number of bins are autocomputed if not specified.
  • ST_InterpolateRaster - Interpolates a gridded surface based on an input set of 3-d points, using the X- and Y-values to position the points on the grid and the Z-value of the points as the surface elevation.
  • 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 raster rastA spatially intersects raster rastB.
  • ST_IsEmpty - Returns true if the raster is empty (width = 0 and height = 0). Otherwise, returns false.
  • ST_MakeEmptyCoverage - Cover georeferenced area with a grid of empty raster tiles.
  • 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_MapAlgebra (callback function version) - Callback function version - Returns a one-band raster given one or more input rasters, band indexes and one user-specified callback function.
  • 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_MapAlgebra (expression version) - Expression version - Returns a one-band raster given one or two input rasters, band indexes and one or more user-specified SQL expressions.
  • ST_MemSize - Returns the amount of space (in bytes) the raster takes.
  • ST_MetaData - Returns basic meta data about a raster object such as pixel size, rotation (skew), upper, lower left, etc.
  • ST_MinConvexHull - Return the convex hull geometry of the raster excluding NODATA pixels.
  • ST_NearestValue - Returns the nearest non-NODATA value of a given band's pixel specified by a columnx and rowy or a geometric point expressed in the same spatial reference coordinate system as the raster.
  • ST_Neighborhood - Returns a 2-D double precision array of the non-NODATA values around a given band's pixel specified by either a columnX and rowY or a geometric point expressed in the same spatial reference coordinate system as the raster.
  • ST_NotSameAlignmentReason - Returns text stating if rasters are aligned and if not aligned, a reason why.
  • ST_NumBands - Returns the number of bands in the raster object.
  • ST_Overlaps - Return true if raster rastA and rastB intersect but one does not completely contain the other.
  • ST_PixelAsCentroid - Returns the centroid (point geometry) of the area represented by a pixel.
  • ST_PixelAsCentroids - Returns the centroid (point geometry) for each pixel of a raster band along with the value, the X and the Y raster coordinates of each pixel. The point geometry is the centroid of the area represented by a pixel.
  • ST_PixelAsPoint - Returns a point geometry of the pixel's upper-left corner.
  • ST_PixelAsPoints - Returns a point geometry for each pixel of a raster band along with the value, the X and the Y raster coordinates of each pixel. The coordinates of the point geometry are of the pixel's upper-left corner.
  • ST_PixelAsPolygon - Returns the polygon geometry that bounds the pixel for a particular row and column.
  • ST_PixelAsPolygons - Returns the polygon 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_PixelOfValue - Get the columnx, rowy coordinates of the pixel whose value equals the search value.
  • ST_PixelWidth - Returns the pixel width in geometric units of the spatial reference system.
  • ST_Polygon - Returns a multipolygon 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_RastFromHexWKB - Return a raster value from a Hex representation of Well-Known Binary (WKB) raster.
  • ST_RastFromWKB - Return a raster value from a Well-Known Binary (WKB) raster.
  • ST_RasterToWorldCoord - Returns the raster's upper left corner as geometric X and Y (longitude and latitude) given a column and row. Column and row starts at 1.
  • ST_RasterToWorldCoordX - Returns the geometric X coordinate upper left of a raster, column and row. Numbering of columns and rows starts at 1.
  • ST_RasterToWorldCoordY - 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.
  • 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, Lanczos, Max or Min resampling algorithm. Default is NearestNeighbor.
  • ST_Resize - Resize a raster to a new width/height
  • 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_Roughness - Returns a raster with the calculated "roughness" of a DEM.
  • 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 offset (pixels can be put on same grid without cutting into pixels) 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_SetBandIndex - Update the external band number of an out-db band
  • 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_SetBandPath - Update the external path and band number of an out-db band
  • 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_SetM - Returns a geometry with the same X/Y coordinates as the input geometry, and values from the raster copied into the M dimension using the requested resample algorithm.
  • 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 of the raster 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 the pixels that intersect a particular geometry. Band numbers start at 1 and assumed to be 1 if not specified.
  • ST_SetValues - Returns modified raster resulting from setting the values of a given band.
  • ST_SetZ - Returns a geometry with the same X/Y coordinates as the input geometry, and values from the raster copied into the Z dimension using the requested resample algorithm.
  • ST_SkewX - Returns the georeference X skew (or rotation parameter).
  • ST_SkewY - Returns the georeference Y skew (or rotation parameter).
  • ST_Slope - Returns the slope (in degrees by default) 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_Summary - Returns a text summary of the contents of the raster.
  • ST_SummaryStats - Returns summarystats 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_SummaryStatsAgg - Aggregate. Returns summarystats consisting of count, sum, mean, stddev, min, max for a given raster band of a set of raster. Band 1 is assumed is no band is specified.
  • ST_TPI - Returns a raster with the calculated Topographic Position Index.
  • ST_TRI - Returns a raster with the calculated Terrain Ruggedness Index.
  • ST_Tile - Returns a set of rasters resulting from the split of the input raster based upon the desired dimensions of the output rasters.
  • ST_Touches - Return true if raster rastA and rastB have at least one point in common but their interiors do not intersect.
  • 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 or more bands.
  • 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_Within - Return true if no points of raster rastA lie in the exterior of raster rastB and at least one point of the interior of rastA lies in the interior of rastB.
  • ST_WorldToRasterCoord - Returns the upper left corner as column and row given geometric X and Y (longitude and latitude) or a point geometry expressed in the spatial reference coordinate system of the raster.
  • ST_WorldToRasterCoordX - 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_WorldToRasterCoordY - 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.
  • UpdateRasterSRID - Change the SRID of all rasters in the user-specified column and table.

12.6. 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 rows for the components of a geometry.
  • ST_DumpPoints - Returns a set of geometry_dump rows for the coordinates in a geometry.
  • ST_DumpRings - Returns a set of geometry_dump rows for the exterior and interior rings of a Polygon.
  • ST_DumpSegments - Returns a set of geometry_dump rows for the segments in a geometry.

12.7. 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 2D extent of a geometry.
  • Box3D - Returns a BOX3D representing the 3D extent of a geometry.
  • Box3D - Returns the box 3d representation of the enclosing box of the raster.
  • ST_3DExtent - Aggregate function that returns the 3D bounding box of geometries.
  • ST_3DMakeBox - Creates a BOX3D defined by two 3D point geometries.
  • ST_AsMVTGeom - Transforms a geometry into the coordinate space of a MVT tile.
  • ST_AsTWKB - Returns the geometry as TWKB, aka "Tiny Well-Known Binary"
  • ST_Box2dFromGeoHash - Return a BOX2D from a GeoHash string.
  • ST_ClipByBox2D - Computes the portion of a geometry falling within a rectangle.
  • ST_EstimatedExtent - Returns the estimated extent of a spatial table.
  • ST_Expand - Returns a bounding box expanded from another bounding box or a geometry.
  • ST_Extent - Aggregate function that returns the bounding box of geometries.
  • ST_MakeBox2D - Creates a BOX2D defined by two 2D point geometries.
  • ST_XMax - Returns the X maxima of a 2D or 3D bounding box or a geometry.
  • ST_XMin - Returns the X minima of a 2D or 3D bounding box or a geometry.
  • ST_YMax - Returns the Y maxima of a 2D or 3D bounding box or a geometry.
  • ST_YMin - Returns the Y minima of a 2D or 3D bounding box or a geometry.
  • ST_ZMax - Returns the Z maxima of a 2D or 3D bounding box or a geometry.
  • ST_ZMin - Returns the Z minima of a 2D or 3D bounding box or a geometry.
  • RemoveUnusedPrimitives - Removes topology primitives which not needed to define existing TopoGeometry objects.
  • ValidateTopology - Returns a set of validatetopology_returntype objects detailing issues with topology.
  • ~(box2df,box2df) - Returns TRUE if a 2D float precision bounding box (BOX2DF) contains another 2D float precision bounding box (BOX2DF).
  • ~(box2df,geometry) - Returns TRUE if a 2D float precision bounding box (BOX2DF) contains a geometry's 2D bonding box.
  • ~(geometry,box2df) - Returns TRUE if a geometry's 2D bonding box contains a 2D float precision bounding box (GIDX).
  • @(box2df,box2df) - Returns TRUE if a 2D float precision bounding box (BOX2DF) is contained into another 2D float precision bounding box.
  • @(box2df,geometry) - Returns TRUE if a 2D float precision bounding box (BOX2DF) is contained into a geometry's 2D bounding box.
  • @(geometry,box2df) - Returns TRUE if a geometry's 2D bounding box is contained into a 2D float precision bounding box (BOX2DF).
  • &&(box2df,box2df) - Returns TRUE if two 2D float precision bounding boxes (BOX2DF) intersect each other.
  • &&(box2df,geometry) - Returns TRUE if a 2D float precision bounding box (BOX2DF) intersects a geometry's (cached) 2D bounding box.
  • &&(geometry,box2df) - Returns TRUE if a geometry's (cached) 2D bounding box intersects a 2D float precision bounding box (BOX2DF).

12.8. 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.
  • Box3D - Returns a BOX3D representing the 3D extent of a geometry.
  • DropGeometryColumn - Removes a geometry column from a spatial table.
  • GeometryType - Returns the type of a geometry as text.
  • ST_3DArea - Computes area of 3D surface geometries. Will return 0 for solids.
  • ST_3DClosestPoint - Returns the 3D point on g1 that is closest to g2. This is the first point of the 3D shortest line.
  • ST_3DConvexHull - Computes the 3D convex hull of a geometry.
  • ST_3DDFullyWithin - Tests if two 3D geometries are entirely within a given 3D distance
  • ST_3DDWithin - Tests if two 3D geometries are within a given 3D distance
  • ST_3DDifference - Perform 3D difference
  • ST_3DDistance - Returns the 3D cartesian minimum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DExtent - Aggregate function that returns the 3D bounding box of geometries.
  • ST_3DIntersection - Perform 3D intersection
  • ST_3DIntersects - Tests if two geometries spatially intersect in 3D - only for points, linestrings, polygons, polyhedral surface (area)
  • ST_3DLength - Returns the 3D length of a linear geometry.
  • ST_3DLineInterpolatePoint - Returns a point interpolated along a 3D line at a fractional location.
  • ST_3DLongestLine - Returns the 3D longest line between two geometries
  • ST_3DMaxDistance - Returns the 3D cartesian maximum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DPerimeter - Returns the 3D perimeter of a polygonal geometry.
  • ST_3DShortestLine - Returns the 3D shortest line between two geometries
  • ST_3DUnion - Perform 3D union.
  • ST_AddMeasure - Interpolates measures along a linear geometry.
  • ST_AddPoint - Add a point to a LineString.
  • ST_Affine - Apply a 3D affine transformation to a geometry.
  • ST_ApproximateMedialAxis - Compute the approximate medial axis of an areal geometry.
  • ST_AsBinary - Return the OGC/ISO Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsEWKB - Return the Extended Well-Known Binary (EWKB) 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 a 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.
  • ST_AsX3D - Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML
  • ST_Boundary - Returns the boundary of a geometry.
  • ST_BoundingDiagonal - Returns the diagonal of a geometry's bounding box.
  • ST_CPAWithin - Tests if the closest point of approach of two trajectoriesis within the specified distance.
  • ST_ChaikinSmoothing - Returns a smoothed version of a geometry, using the Chaikin algorithm
  • ST_ClosestPointOfApproach - Returns a measure at the closest point of approach of two trajectories.
  • ST_Collect - Creates a GeometryCollection or Multi* geometry from a set of geometries.
  • ST_ConstrainedDelaunayTriangles - Return a constrained Delaunay triangulation around the given input geometry.
  • ST_ConvexHull - Computes the convex hull of a geometry.
  • ST_CoordDim - Return the coordinate dimension of a geometry.
  • ST_CurveToLine - Converts a geometry containing curves to a linear geometry.
  • ST_DelaunayTriangles - Returns the Delaunay triangulation of the vertices of a geometry.
  • ST_Difference - Computes a geometry representing the part of geometry A that does not intersect geometry B.
  • ST_DistanceCPA - Returns the distance between the closest point of approach of two trajectories.
  • ST_Dump - Returns a set of geometry_dump rows for the components of a geometry.
  • ST_DumpPoints - Returns a set of geometry_dump rows for the coordinates in a geometry.
  • ST_DumpRings - Returns a set of geometry_dump rows for the exterior and interior rings of a Polygon.
  • ST_DumpSegments - Returns a set of geometry_dump rows for the segments in a geometry.
  • ST_EndPoint - Returns the last point of a LineString or CircularLineString.
  • ST_ExteriorRing - Returns a LineString representing the exterior ring of a Polygon.
  • ST_Extrude - Extrude a surface to a related volume
  • ST_FlipCoordinates - Returns a version of a geometry with X and Y axis flipped.
  • ST_Force2D - Force the geometries into a "2-dimensional mode".
  • ST_ForceCurve - Upcast a geometry into its curved type, if applicable.
  • ST_ForceLHR - Force LHR orientation
  • ST_ForcePolygonCCW - Orients all exterior rings counter-clockwise and all interior rings clockwise.
  • ST_ForcePolygonCW - Orients all exterior rings clockwise and all interior rings counter-clockwise.
  • ST_ForceRHR - Force the orientation of the vertices in a polygon to follow the Right-Hand-Rule.
  • ST_ForceSFS - Force the geometries to use SFS 1.1 geometry types only.
  • ST_Force_3D - Force the geometries into XYZ mode. This is an alias for ST_Force3DZ.
  • ST_Force_3DZ - Force the geometries into XYZ mode.
  • ST_Force_4D - Force the geometries into XYZM mode.
  • ST_Force_Collection - Convert 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_GeometricMedian - Returns the geometric median of a MultiPoint.
  • ST_GeometryN - Return an element of a geometry collection.
  • ST_GeometryType - Returns the SQL-MM type of a geometry as text.
  • ST_HasArc - Tests if a geometry contains a circular arc
  • ST_InteriorRingN - Returns the Nth interior ring (hole) of a Polygon.
  • ST_InterpolatePoint - Returns the interpolated measure of a geometry closest to a point.
  • ST_Intersection - Computes a geometry representing the shared portion of geometries A and B.
  • ST_IsClosed - Tests if a LineStrings's start and end points are coincident. For a PolyhedralSurface tests if it is closed (volumetric).
  • ST_IsCollection - Tests if a geometry is a geometry collection type.
  • ST_IsPlanar - Check if a surface is or not planar
  • ST_IsPolygonCCW - Tests if Polygons have exterior rings oriented counter-clockwise and interior rings oriented clockwise.
  • ST_IsPolygonCW - Tests if Polygons have exterior rings oriented clockwise and interior rings oriented counter-clockwise.
  • ST_IsSimple - Tests if a geometry has no points of self-intersection or self-tangency.
  • ST_IsSolid - Test if the geometry is a solid. No validity check is performed.
  • ST_IsValidTrajectory - Tests if the geometry is a valid trajectory.
  • ST_Length_Spheroid - Returns the 2D or 3D length/perimeter of a lon/lat geometry on a spheroid.
  • ST_LineFromMultiPoint - Creates a LineString from a MultiPoint geometry.
  • ST_LineInterpolatePoint - Returns a point interpolated along a line at a fractional location.
  • ST_LineInterpolatePoints - Returns points interpolated along a line at a fractional interval.
  • ST_LineSubstring - Returns the part of a line between two fractional locations.
  • ST_LineToCurve - Converts a linear geometry to a curved geometry.
  • ST_LocateBetweenElevations - Returns the portions of a geometry that lie in an elevation (Z) range.
  • ST_M - Returns the M coordinate of a Point.
  • ST_MakeLine - Creates a LineString from Point, MultiPoint, or LineString geometries.
  • ST_MakePoint - Creates a 2D, 3DZ or 4D Point.
  • ST_MakePolygon - Creates a Polygon from a shell and optional list of holes.
  • ST_MakeSolid - Cast the geometry into a solid. No check is performed. To obtain a valid solid, the input geometry must be a closed Polyhedral Surface or a closed TIN.
  • ST_MakeValid - Attempts to make an invalid geometry valid without losing vertices.
  • ST_MemSize - Returns the amount of memory space a geometry takes.
  • ST_MemUnion - Aggregate function which unions geometries in a memory-efficent but slower way
  • ST_NDims - Returns the coordinate dimension of a geometry.
  • ST_NPoints - Returns the number of points (vertices) in a geometry.
  • ST_NRings - Returns the number of rings in a polygonal geometry.
  • ST_Node - Nodes a collection of lines.
  • ST_NumGeometries - Returns the number of elements in a geometry collection.
  • ST_NumPatches - Return the number of faces on a Polyhedral Surface. Will return null for non-polyhedral geometries.
  • ST_Orientation - Determine surface orientation
  • ST_PatchN - Returns the Nth geometry (face) of a PolyhedralSurface.
  • ST_PointFromWKB - Makes a geometry from WKB with the given SRID
  • ST_PointN - Returns the Nth point in the first LineString or circular LineString in a geometry.
  • ST_PointOnSurface - Computes a point guaranteed to lie in a polygon, or on a geometry.
  • ST_Points - Returns a MultiPoint containing the coordinates of a geometry.
  • ST_Polygon - Creates a Polygon from a LineString with a specified SRID.
  • ST_RemovePoint - Remove a point from a linestring.
  • ST_RemoveRepeatedPoints - Returns a version of a geometry with duplicate points removed.
  • ST_Reverse - Return the geometry with vertex order reversed.
  • ST_Rotate - Rotates a geometry about an origin point.
  • ST_RotateX - Rotates a geometry about the X axis.
  • ST_RotateY - Rotates a geometry about the Y axis.
  • ST_RotateZ - Rotates a geometry about the Z axis.
  • ST_Scale - Scales a geometry by given factors.
  • ST_Scroll - Change start point of a closed LineString.
  • ST_SetPoint - Replace point of a linestring with a given point.
  • ST_ShiftLongitude - Shifts the longitude coordinates of a geometry between -180..180 and 0..360.
  • ST_SnapToGrid - Snap all points of the input geometry to a regular grid.
  • ST_StartPoint - Returns the first point of a LineString.
  • ST_StraightSkeleton - Compute a straight skeleton from a geometry
  • ST_SwapOrdinates - Returns a version of the given geometry with given ordinate values swapped.
  • ST_SymDifference - Computes a geometry representing the portions of geometries A and B that do not intersect.
  • ST_Tesselate - Perform surface Tesselation of a polygon or polyhedralsurface and returns as a TIN or collection of TINS
  • ST_TransScale - Translates and scales a geometry by given offsets and factors.
  • ST_Translate - Translates a geometry by given offsets.
  • ST_UnaryUnion - Computes the union of the components of a single geometry.
  • ST_Union - Computes a geometry representing the point-set union of the input geometries.
  • ST_Volume - Computes the volume of a 3D solid. If applied to surface (even closed) geometries will return 0.
  • ST_WrapX - Wrap a geometry around an X value.
  • ST_X - Returns the X coordinate of a Point.
  • ST_XMax - Returns the X maxima of a 2D or 3D bounding box or a geometry.
  • ST_XMin - Returns the X minima of a 2D or 3D bounding box or a geometry.
  • ST_Y - Returns the Y coordinate of a Point.
  • ST_YMax - Returns the Y maxima of a 2D or 3D bounding box or a geometry.
  • ST_YMin - Returns the Y minima of a 2D or 3D bounding box or a geometry.
  • ST_Z - Returns the Z coordinate of a Point.
  • ST_ZMax - Returns the Z maxima of a 2D or 3D bounding box or a geometry.
  • ST_ZMin - Returns the Z minima of a 2D or 3D bounding box or a geometry.
  • ST_Zmflag - Returns a code indicating the ZM coordinate dimension of a geometry.
  • TG_Equals - Returns true if two topogeometries are composed of the same topology primitives.
  • TG_Intersects - Returns true if any pair of primitives from the two topogeometries intersect.
  • UpdateGeometrySRID - Updates the SRID of all features in a geometry column, and the table metadata.
  • geometry_overlaps_nd - Returns TRUE if A's n-D bounding box intersects B's n-D bounding box.
  • overlaps_nd_geometry_gidx - Returns TRUE if a geometry's (cached) n-D bounding box intersects a n-D float precision bounding box (GIDX).
  • overlaps_nd_gidx_geometry - Returns TRUE if a n-D float precision bounding box (GIDX) intersects a geometry's (cached) n-D bounding box.
  • overlaps_nd_gidx_gidx - Returns TRUE if two n-D float precision bounding boxes (GIDX) intersect each other.
  • postgis_sfcgal_full_version - Returns the full version of SFCGAL in use including CGAL and Boost versions
  • postgis_sfcgal_version - Returns the version of SFCGAL in use

12.9. PostGIS Curved Geometry Support Functions

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

  • AddGeometryColumn - Adds a geometry column to an existing table.
  • Box2D - Returns a BOX2D representing the 2D extent of a geometry.
  • Box3D - Returns a BOX3D representing the 3D extent of a geometry.
  • DropGeometryColumn - Removes a geometry column from a spatial table.
  • GeometryType - Returns the type of a geometry as text.
  • 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 - Aggregate function that returns the 3D bounding box of geometries.
  • ST_Affine - Apply a 3D affine transformation to a geometry.
  • ST_AsBinary - Return the OGC/ISO Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsEWKB - Return the Extended Well-Known Binary (EWKB) 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_AsSVG - Returns SVG path data for a geometry.
  • ST_AsText - Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.
  • ST_ClusterDBSCAN - Window function that returns a cluster id for each input geometry using the DBSCAN algorithm.
  • ST_ClusterWithin - Aggregate function that clusters geometries by separation distance.
  • ST_ClusterWithinWin - Window function that returns a cluster id for each input geometry, clustering using separation distance.
  • ST_Collect - Creates a GeometryCollection or Multi* geometry from a set of geometries.
  • ST_CoordDim - Return the coordinate dimension of a geometry.
  • ST_CurveToLine - Converts a geometry containing curves to a linear geometry.
  • ST_Distance - Returns the distance between two geometry or geography values.
  • ST_Dump - Returns a set of geometry_dump rows for the components of a geometry.
  • ST_DumpPoints - Returns a set of geometry_dump rows for the coordinates in a geometry.
  • ST_EndPoint - Returns the last point of a LineString or CircularLineString.
  • ST_EstimatedExtent - Returns the estimated extent of a spatial table.
  • ST_FlipCoordinates - Returns a version of a geometry with X and Y axis flipped.
  • ST_Force2D - Force the geometries into a "2-dimensional mode".
  • ST_ForceCurve - Upcast a geometry into its curved type, if applicable.
  • ST_ForceSFS - Force the geometries to use SFS 1.1 geometry types only.
  • ST_Force3D - Force the geometries into XYZ mode. This is an alias for ST_Force3DZ.
  • ST_Force3DM - Force the geometries into XYM mode.
  • ST_Force3DZ - Force the geometries into XYZ mode.
  • ST_Force4D - Force the geometries into XYZM mode.
  • ST_ForceCollection - Convert the geometry into a GEOMETRYCOLLECTION.
  • ST_GeoHash - Return a GeoHash representation 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 an element of a geometry collection.
  • = - Returns TRUE if the coordinates and coordinate order geometry/geography A are the same as the coordinates and coordinate order of geometry/geography B.
  • &<| - Returns TRUE if A's bounding box overlaps or is below B's.
  • ST_HasArc - Tests if a geometry contains a circular arc
  • ST_Intersects - Tests if two geometries intersect (they have at least one point in common)
  • ST_IsClosed - Tests if a LineStrings's start and end points are coincident. For a PolyhedralSurface tests if it is closed (volumetric).
  • ST_IsCollection - Tests if a geometry is a geometry collection type.
  • ST_IsEmpty - Tests if a geometry is empty.
  • ST_LineToCurve - Converts a linear geometry to a curved geometry.
  • ST_MemSize - Returns the amount of memory space a geometry takes.
  • ST_NPoints - Returns the number of points (vertices) in a geometry.
  • ST_NRings - Returns the number of rings in a polygonal geometry.
  • ST_PointFromWKB - Makes a geometry from WKB with the given SRID
  • ST_PointN - Returns the Nth point in the first LineString or circular LineString in a geometry.
  • ST_Points - Returns a MultiPoint containing the coordinates of a geometry.
  • ST_Rotate - Rotates a geometry about an origin point.
  • ST_RotateZ - Rotates a geometry about the Z axis.
  • ST_SRID - Returns the spatial reference identifier for a geometry.
  • ST_Scale - Scales a geometry by given factors.
  • ST_SetSRID - Set the SRID on a geometry.
  • ST_StartPoint - Returns the first point of a LineString.
  • ST_Summary - Returns a text summary of the contents of a geometry.
  • ST_SwapOrdinates - Returns a version of the given geometry with given ordinate values swapped.
  • ST_TransScale - Translates and scales a geometry by given offsets and factors.
  • ST_Transform - Return a new geometry with coordinates transformed to a different spatial reference system.
  • ST_Translate - Translates a geometry by given offsets.
  • ST_XMax - Returns the X maxima of a 2D or 3D bounding box or a geometry.
  • ST_XMin - Returns the X minima of a 2D or 3D bounding box or a geometry.
  • ST_YMax - Returns the Y maxima of a 2D or 3D bounding box or a geometry.
  • ST_YMin - Returns the Y minima of a 2D or 3D bounding box or a geometry.
  • ST_ZMax - Returns the Z maxima of a 2D or 3D bounding box or a geometry.
  • ST_ZMin - Returns the Z minima of a 2D or 3D bounding box or a geometry.
  • ST_Zmflag - Returns a code indicating the ZM coordinate dimension of a geometry.
  • UpdateGeometrySRID - Updates the SRID of all features in a geometry column, and the table metadata.
  • ~(box2df,box2df) - Returns TRUE if a 2D float precision bounding box (BOX2DF) contains another 2D float precision bounding box (BOX2DF).
  • ~(box2df,geometry) - Returns TRUE if a 2D float precision bounding box (BOX2DF) contains a geometry's 2D bonding box.
  • ~(geometry,box2df) - Returns TRUE if a geometry's 2D bonding box contains a 2D float precision bounding box (GIDX).
  • && - Returns TRUE if A's 2D bounding box intersects B's 2D bounding box.
  • &&& - Returns TRUE if A's n-D bounding box intersects B's n-D bounding box.
  • @(box2df,box2df) - Returns TRUE if a 2D float precision bounding box (BOX2DF) is contained into another 2D float precision bounding box.
  • @(box2df,geometry) - Returns TRUE if a 2D float precision bounding box (BOX2DF) is contained into a geometry's 2D bounding box.
  • @(geometry,box2df) - Returns TRUE if a geometry's 2D bounding box is contained into a 2D float precision bounding box (BOX2DF).
  • &&(box2df,box2df) - Returns TRUE if two 2D float precision bounding boxes (BOX2DF) intersect each other.
  • &&(box2df,geometry) - Returns TRUE if a 2D float precision bounding box (BOX2DF) intersects a geometry's (cached) 2D bounding box.
  • &&(geometry,box2df) - Returns TRUE if a geometry's (cached) 2D bounding box intersects a 2D float precision bounding box (BOX2DF).
  • &&&(geometry,gidx) - Returns TRUE if a geometry's (cached) n-D bounding box intersects a n-D float precision bounding box (GIDX).
  • &&&(gidx,geometry) - Returns TRUE if a n-D float precision bounding box (GIDX) intersects a geometry's (cached) n-D bounding box.
  • &&&(gidx,gidx) - Returns TRUE if two n-D float precision bounding boxes (GIDX) intersect each other.

12.10. PostGIS Polyhedral Surface Support Functions

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

  • AddGeometryColumn - Adds a geometry column to an existing table.
  • Box2D - Returns a BOX2D representing the 2D extent of a geometry.
  • Box3D - Returns a BOX3D representing the 3D extent of a geometry.
  • DropGeometryColumn - Removes a geometry column from a spatial table.
  • GeometryType - Returns the type of a geometry as text.
  • 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 - Aggregate function that returns the 3D bounding box of geometries.
  • ST_Affine - Apply a 3D affine transformation to a geometry.
  • ST_AsBinary - Return the OGC/ISO Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsEWKB - Return the Extended Well-Known Binary (EWKB) 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_AsSVG - Returns SVG path data for a geometry.
  • ST_AsText - Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.
  • ST_ClusterDBSCAN - Window function that returns a cluster id for each input geometry using the DBSCAN algorithm.
  • ST_ClusterWithin - Aggregate function that clusters geometries by separation distance.
  • ST_ClusterWithinWin - Window function that returns a cluster id for each input geometry, clustering using separation distance.
  • ST_Collect - Creates a GeometryCollection or Multi* geometry from a set of geometries.
  • ST_CoordDim - Return the coordinate dimension of a geometry.
  • ST_CurveToLine - Converts a geometry containing curves to a linear geometry.
  • ST_Distance - Returns the distance between two geometry or geography values.
  • ST_Dump - Returns a set of geometry_dump rows for the components of a geometry.
  • ST_DumpPoints - Returns a set of geometry_dump rows for the coordinates in a geometry.
  • ST_EndPoint - Returns the last point of a LineString or CircularLineString.
  • ST_EstimatedExtent - Returns the estimated extent of a spatial table.
  • ST_FlipCoordinates - Returns a version of a geometry with X and Y axis flipped.
  • ST_Force2D - Force the geometries into a "2-dimensional mode".
  • ST_ForceCurve - Upcast a geometry into its curved type, if applicable.
  • ST_ForceSFS - Force the geometries to use SFS 1.1 geometry types only.
  • ST_Force3D - Force the geometries into XYZ mode. This is an alias for ST_Force3DZ.
  • ST_Force3DM - Force the geometries into XYM mode.
  • ST_Force3DZ - Force the geometries into XYZ mode.
  • ST_Force4D - Force the geometries into XYZM mode.
  • ST_ForceCollection - Convert the geometry into a GEOMETRYCOLLECTION.
  • ST_GeoHash - Return a GeoHash representation 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 an element of a geometry collection.
  • = - Returns TRUE if the coordinates and coordinate order geometry/geography A are the same as the coordinates and coordinate order of geometry/geography B.
  • &<| - Returns TRUE if A's bounding box overlaps or is below B's.
  • ST_HasArc - Tests if a geometry contains a circular arc
  • ST_Intersects - Tests if two geometries intersect (they have at least one point in common)
  • ST_IsClosed - Tests if a LineStrings's start and end points are coincident. For a PolyhedralSurface tests if it is closed (volumetric).
  • ST_IsCollection - Tests if a geometry is a geometry collection type.
  • ST_IsEmpty - Tests if a geometry is empty.
  • ST_LineToCurve - Converts a linear geometry to a curved geometry.
  • ST_MemSize - Returns the amount of memory space a geometry takes.
  • ST_NPoints - Returns the number of points (vertices) in a geometry.
  • ST_NRings - Returns the number of rings in a polygonal geometry.
  • ST_PointFromWKB - Makes a geometry from WKB with the given SRID
  • ST_PointN - Returns the Nth point in the first LineString or circular LineString in a geometry.
  • ST_Points - Returns a MultiPoint containing the coordinates of a geometry.
  • ST_Rotate - Rotates a geometry about an origin point.
  • ST_RotateZ - Rotates a geometry about the Z axis.
  • ST_SRID - Returns the spatial reference identifier for a geometry.
  • ST_Scale - Scales a geometry by given factors.
  • ST_SetSRID - Set the SRID on a geometry.
  • ST_StartPoint - Returns the first point of a LineString.
  • ST_Summary - Returns a text summary of the contents of a geometry.
  • ST_SwapOrdinates - Returns a version of the given geometry with given ordinate values swapped.
  • ST_TransScale - Translates and scales a geometry by given offsets and factors.
  • ST_Transform - Return a new geometry with coordinates transformed to a different spatial reference system.
  • ST_Translate - Translates a geometry by given offsets.
  • ST_XMax - Returns the X maxima of a 2D or 3D bounding box or a geometry.
  • ST_XMin - Returns the X minima of a 2D or 3D bounding box or a geometry.
  • ST_YMax - Returns the Y maxima of a 2D or 3D bounding box or a geometry.
  • ST_YMin - Returns the Y minima of a 2D or 3D bounding box or a geometry.
  • ST_ZMax - Returns the Z maxima of a 2D or 3D bounding box or a geometry.
  • ST_ZMin - Returns the Z minima of a 2D or 3D bounding box or a geometry.
  • ST_Zmflag - Returns a code indicating the ZM coordinate dimension of a geometry.
  • UpdateGeometrySRID - Updates the SRID of all features in a geometry column, and the table metadata.
  • ~(box2df,box2df) - Returns TRUE if a 2D float precision bounding box (BOX2DF) contains another 2D float precision bounding box (BOX2DF).
  • ~(box2df,geometry) - Returns TRUE if a 2D float precision bounding box (BOX2DF) contains a geometry's 2D bonding box.
  • ~(geometry,box2df) - Returns TRUE if a geometry's 2D bonding box contains a 2D float precision bounding box (GIDX).
  • && - Returns TRUE if A's 2D bounding box intersects B's 2D bounding box.
  • &&& - Returns TRUE if A's n-D bounding box intersects B's n-D bounding box.
  • @(box2df,box2df) - Returns TRUE if a 2D float precision bounding box (BOX2DF) is contained into another 2D float precision bounding box.
  • @(box2df,geometry) - Returns TRUE if a 2D float precision bounding box (BOX2DF) is contained into a geometry's 2D bounding box.
  • @(geometry,box2df) - Returns TRUE if a geometry's 2D bounding box is contained into a 2D float precision bounding box (BOX2DF).
  • &&(box2df,box2df) - Returns TRUE if two 2D float precision bounding boxes (BOX2DF) intersect each other.
  • &&(box2df,geometry) - Returns TRUE if a 2D float precision bounding box (BOX2DF) intersects a geometry's (cached) 2D bounding box.
  • &&(geometry,box2df) - Returns TRUE if a geometry's (cached) 2D bounding box intersects a 2D float precision bounding box (BOX2DF).
  • &&&(geometry,gidx) - Returns TRUE if a geometry's (cached) n-D bounding box intersects a n-D float precision bounding box (GIDX).
  • &&&(gidx,geometry) - Returns TRUE if a n-D float precision bounding box (GIDX) intersects a geometry's (cached) n-D bounding box.
  • &&&(gidx,gidx) - Returns TRUE if two n-D float precision bounding boxes (GIDX) intersect each other.

12.11. 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.
  • A means the function only available if PostGIS compiled with SFCGAL support.
  • A means the function support is provided by SFCGAL if PostGIS compiled with SFCGAL support, otherwise GEOS/built-in 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    
GeometryType    
PostGIS_AddBBox       
PostGIS_DropBBox       
PostGIS_HasBBox       
ST_3DArea    
ST_3DClosestPoint       
ST_3DConvexHull     
ST_3DDFullyWithin       
ST_3DDWithin      
ST_3DDifference    
ST_3DDistance      
ST_3DExtent    
ST_3DIntersection    
ST_3DIntersects    
ST_3DLength       
ST_3DLineInterpolatePoint       
ST_3DLongestLine       
ST_3DMakeBox       
ST_3DMaxDistance       
ST_3DPerimeter       
ST_3DShortestLine       
ST_3DUnion    
ST_AddMeasure       
ST_AddPoint       
ST_Affine    
ST_AlphaShape       
ST_Angle       
ST_ApproximateMedialAxis     
ST_Area     
ST_AsBinary
ST_AsEWKB    
ST_AsEWKT  
ST_AsEncodedPolyline       
ST_AsFlatGeobuf       
ST_AsGML  
ST_AsGeoJSON     
ST_AsGeobuf       
ST_AsHEXEWKB      
ST_AsKML     
ST_AsLatLonText       
ST_AsMARC21       
ST_AsMVT       
ST_AsMVTGeom       
ST_AsSVG      
ST_AsTWKB       
ST_AsText     
ST_AsX3D     
ST_Azimuth      
ST_BdMPolyFromText       
ST_BdPolyFromText       
ST_Boundary       
ST_BoundingDiagonal       
ST_Box2dFromGeoHash       
ST_Buffer      
ST_BuildArea       
ST_CPAWithin       
ST_Centroid      
ST_ChaikinSmoothing       
ST_ClipByBox2D       
ST_ClosestPoint      
ST_ClosestPointOfApproach       
ST_ClusterDBSCAN       
ST_ClusterIntersecting       
ST_ClusterIntersectingWin       
ST_ClusterKMeans       
ST_ClusterWithin       
ST_ClusterWithinWin       
ST_Collect      
ST_CollectionExtract       
ST_CollectionHomogenize       
ST_ConcaveHull       
ST_ConstrainedDelaunayTriangles       
ST_Contains       
ST_ContainsProperly       
ST_ConvexHull       
ST_CoordDim  
ST_CoverageInvalidEdges       
ST_CoverageSimplify       
ST_CoverageUnion       
ST_CoveredBy      
ST_Covers      
ST_Crosses       
ST_CurveToLine     
ST_DFullyWithin       
ST_DWithin      
ST_DelaunayTriangles      
ST_Difference       
ST_Dimension    
ST_Disjoint       
ST_Distance     
ST_DistanceCPA       
ST_DistanceSphere       
ST_DistanceSpheroid       
ST_Dump    
ST_DumpPoints    
ST_DumpRings       
ST_DumpSegments      
ST_EndPoint     
ST_Envelope       
ST_Equals       
ST_EstimatedExtent       
ST_Expand     
ST_Extent     
ST_ExteriorRing       
ST_Extrude     
ST_FilterByM       
ST_FlipCoordinates    
ST_Force2D      
ST_ForceCurve      
ST_ForceLHR     
ST_ForcePolygonCCW       
ST_ForcePolygonCW       
ST_ForceRHR       
ST_ForceSFS    
ST_Force3D      
ST_Force3DM       
ST_Force3DZ      
ST_Force4D      
ST_ForceCollection      
ST_FrechetDistance       
ST_FromFlatGeobuf        
ST_FromFlatGeobufToTable        
ST_GMLToSQL       
ST_GeneratePoints       
ST_GeoHash       
ST_GeogFromText        
ST_GeogFromWKB        
ST_GeographyFromText        
ST_GeomCollFromText       
ST_GeomFromEWKB    
ST_GeomFromEWKT    
ST_GeomFromGML     
ST_GeomFromGeoHash       
ST_GeomFromGeoJSON       
ST_GeomFromKML       
ST_GeomFromMARC21       
ST_GeomFromTWKB       
ST_GeomFromText      
ST_GeomFromWKB      
ST_GeometricMedian       
ST_GeometryFromText       
ST_GeometryN  
ST_GeometryType      
|>>       
<<|       
~       
@       
=      
<<       
|&>       
&<|       
&<       
&>       
>>       
~=       
ST_HasArc      
ST_HausdorffDistance       
ST_Hexagon       
ST_HexagonGrid       
ST_InteriorRingN       
ST_InterpolatePoint       
ST_Intersection     
ST_Intersects    
ST_InverseTransformPipeline       
ST_IsClosed    
ST_IsCollection      
ST_IsEmpty      
ST_IsPlanar     
ST_IsPolygonCCW       
ST_IsPolygonCW       
ST_IsRing       
ST_IsSimple       
ST_IsSolid     
ST_IsValid       
ST_IsValidDetail       
ST_IsValidReason       
ST_IsValidTrajectory       
ST_LargestEmptyCircle       
ST_Length      
ST_Length2D       
ST_LengthSpheroid       
ST_Letters       
ST_LineCrossingDirection       
ST_LineExtend       
ST_LineFromEncodedPolyline       
ST_LineFromMultiPoint       
ST_LineFromText       
ST_LineFromWKB       
ST_LineInterpolatePoint     
ST_LineInterpolatePoints     
ST_LineLocatePoint      
ST_LineMerge       
ST_LineSubstring     
ST_LineToCurve      
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_MakeSolid     
ST_MakeValid       
ST_MaxDistance       
ST_MaximumInscribedCircle       
ST_MemSize    
ST_MemUnion       
ST_MinimumBoundingCircle       
ST_MinimumBoundingRadius       
ST_MinimumClearance       
ST_MinimumClearanceLine       
ST_MinkowskiSum       
ST_Multi       
ST_NDims       
ST_NPoints      
ST_NRings      
ST_Node       
ST_Normalize       
ST_NumGeometries    
ST_NumInteriorRing       
ST_NumInteriorRings       
ST_NumPatches      
ST_NumPoints       
ST_OffsetCurve       
ST_OptimalAlphaShape       
ST_OrderingEquals       
ST_Orientation       
ST_OrientedEnvelope       
ST_Overlaps       
ST_PatchN      
ST_Perimeter      
ST_Perimeter2D       
ST_Point       
ST_PointFromGeoHash        
ST_PointFromText       
ST_PointFromWKB     
ST_PointInsideCircle       
ST_PointM       
ST_PointN     
ST_PointOnSurface       
ST_PointZ       
ST_PointZM       
ST_Points      
ST_Polygon       
ST_PolygonFromText       
ST_Polygonize       
ST_Project      
ST_QuantizeCoordinates       
ST_ReducePrecision       
ST_Relate       
ST_RelateMatch        
ST_RemovePoint       
ST_RemoveRepeatedPoints       
ST_Reverse       
ST_Rotate    
ST_RotateX     
ST_RotateY     
ST_RotateZ    
ST_SRID      
ST_Scale    
ST_Scroll       
ST_Segmentize      
ST_SetEffectiveArea       
ST_SetPoint       
ST_SetSRID       
ST_SharedPaths       
ST_ShiftLongitude     
ST_ShortestLine      
ST_Simplify       
ST_SimplifyPolygonHull       
ST_SimplifyPreserveTopology       
ST_SimplifyVW       
ST_Snap       
ST_SnapToGrid       
ST_Split       
ST_Square       
ST_SquareGrid       
ST_StartPoint     
ST_StraightSkeleton     
ST_Subdivide       
ST_Summary    
ST_SwapOrdinates    
ST_SymDifference       
ST_Tesselate     
ST_TileEnvelope       
ST_Touches       
ST_TransScale      
ST_Transform     
ST_TransformPipeline       
ST_Translate      
ST_TriangulatePolygon       
ST_UnaryUnion       
ST_Union       
ST_Volume    
ST_VoronoiLines       
ST_VoronoiPolygons       
ST_WKBToSQL       
ST_WKTToSQL       
ST_Within       
ST_WrapX       
ST_X       
ST_XMax      
ST_XMin      
ST_Y       
ST_YMax      
ST_YMin      
ST_Z       
ST_ZMax      
ST_ZMin      
ST_Zmflag      
~(box2df,box2df)       
~(box2df,geometry)       
~(geometry,box2df)       
<#>       
<<#>>       
<<->>       
|=|       
<->      
&&      
&&&    
@(box2df,box2df)       
@(box2df,geometry)       
@(geometry,box2df)       
&&(box2df,box2df)       
&&(box2df,geometry)       
&&(geometry,box2df)       
&&&(geometry,gidx)    
&&&(gidx,geometry)    
&&&(gidx,gidx)     
postgis.backend        
postgis.enable_outdb_rasters        
postgis.gdal_datapath        
postgis.gdal_enabled_drivers        
postgis.gdal_vsi_options        
postgis_sfcgal_full_version      
postgis_sfcgal_version      
postgis_srs        
postgis_srs_all        
postgis_srs_codes        
postgis_srs_search       

12.12. New, Enhanced or changed PostGIS Functions

12.12.1. PostGIS Functions new or enhanced in 3.4

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 3.4

  • PostGIS_GEOS_Compiled_Version - Availability: 3.4.0 Returns the version number of the GEOS library against which PostGIS was built.
  • ST_ClusterIntersectingWin - Availability: 3.4.0 Window function that returns a cluster id for each input geometry, clustering input geometries into connected sets.
  • ST_ClusterWithinWin - Availability: 3.4.0 Window function that returns a cluster id for each input geometry, clustering using separation distance.
  • ST_CoverageInvalidEdges - Availability: 3.4.0 Window function that finds locations where polygons fail to form a valid coverage.
  • ST_CoverageSimplify - Availability: 3.4.0 Window function that simplifies the edges of a polygonal coverage.
  • ST_CoverageUnion - Availability: 3.4.0 - requires GEOS >= 3.8.0 Computes the union of a set of polygons forming a coverage by removing shared edges.
  • ST_InverseTransformPipeline - Availability: 3.4.0 Return a new geometry with coordinates transformed to a different spatial reference system using the inverse of a defined coordinate transformation pipeline.
  • ST_LargestEmptyCircle - Availability: 3.4.0. Computes the largest circle not overlapping a geometry.
  • ST_LineExtend - Availability: 3.4.0 Returns a line with the last and first segments extended the specified distance(s).
  • ST_TransformPipeline - Availability: 3.4.0 Return a new geometry with coordinates transformed to a different spatial reference system using a defined coordinate transformation pipeline.
  • postgis_srs - Availability: 3.4.0 Return a metadata record for the requested authority and srid.
  • postgis_srs_all - Availability: 3.4.0 Return metadata records for every spatial reference system in the underlying Proj database.
  • postgis_srs_codes - Availability: 3.4.0 Return the list of SRS codes associated with the given authority.
  • postgis_srs_search - Availability: 3.4.0 Return metadata records for projected coordinate systems that have areas of useage that fully contain the bounds parameter.

Functions enhanced in PostGIS 3.4

  • PostGIS_Full_Version - Enhanced: 3.4.0 now includes extra PROJ configurations NETWORK_ENABLED, URL_ENDPOINT and DATABASE_PATH of proj.db location Reports full PostGIS version and build configuration infos.
  • PostGIS_PROJ_Version - Enhanced: 3.4.0 now includes NETWORK_ENABLED, URL_ENDPOINT and DATABASE_PATH of proj.db location Returns the version number of the PROJ4 library.
  • ST_AsSVG - Enhanced: 3.4.0 to support all curve types Returns SVG path data for a geometry.
  • ST_ClosestPoint - Enhanced: 3.4.0 - Support for geography. Returns the 2D point on g1 that is closest to g2. This is the first point of the shortest line from one geometry to the other.
  • ST_LineSubstring - Enhanced: 3.4.0 - Support for geography was introduced. Returns the part of a line between two fractional locations.
  • ST_Project - Enhanced: 3.4.0 Allow geometry arguments and two-point form omitting azimuth. Returns a point projected from a start point by a distance and bearing (azimuth).
  • ST_ShortestLine - Enhanced: 3.4.0 - support for geography. Returns the 2D shortest line between two geometries

Functions changed in PostGIS 3.4

  • PostGIS_Extensions_Upgrade - Changed: 3.4.0 to add target_version argument. Packages and upgrades PostGIS extensions (e.g. postgis_raster,postgis_topology, postgis_sfcgal) to given or latest version.

12.12.2. PostGIS Functions new or enhanced in 3.3

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 3.3

  • ST_3DConvexHull - Availability: 3.3.0 Computes the 3D convex hull of a geometry.
  • ST_3DUnion - Availability: 3.3.0 aggregate variant was added Perform 3D union.
  • ST_AlphaShape - Availability: 3.3.0 - requires SFCGAL >= 1.4.1. Computes an Alpha-shape enclosing a geometry
  • ST_AsMARC21 - Availability: 3.3.0 Returns geometry as a MARC21/XML record with a geographic datafield (034).
  • ST_GeomFromMARC21 - Availability: 3.3.0, requires libxml2 2.6+ Takes MARC21/XML geographic data as input and returns a PostGIS geometry object.
  • ST_Letters - Availability: 3.3.0 Returns the input letters rendered as geometry with a default start position at the origin and default text height of 100.
  • ST_OptimalAlphaShape - Availability: 3.3.0 - requires SFCGAL >= 1.4.1. Computes an Alpha-shape enclosing a geometry using an "optimal" alpha value.
  • ST_SimplifyPolygonHull - Availability: 3.3.0. Computes a simplifed topology-preserving outer or inner hull of a polygonal geometry.
  • ST_TriangulatePolygon - Availability: 3.3.0. Computes the constrained Delaunay triangulation of polygons
  • postgis_sfcgal_full_version - Availability: 3.3.0 Returns the full version of SFCGAL in use including CGAL and Boost versions

Functions enhanced in PostGIS 3.3

  • ST_ConcaveHull - Enhanced: 3.3.0, GEOS native implementation enabled for GEOS 3.11+ Computes a possibly concave geometry that contains all input geometry vertices
  • ST_LineMerge - Enhanced: 3.3.0 accept a directed parameter. Return the lines formed by sewing together a MultiLineString.

Functions changed in PostGIS 3.3

  • PostGIS_Extensions_Upgrade - Changed: 3.3.0 support for upgrades from any PostGIS version. Does not work on all systems. Packages and upgrades PostGIS extensions (e.g. postgis_raster,postgis_topology, postgis_sfcgal) to given or latest version.

12.12.3. PostGIS Functions new or enhanced in 3.2

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 3.2

  • ST_AsFlatGeobuf - Availability: 3.2.0 Return a FlatGeobuf representation of a set of rows.
  • ST_DumpSegments - Availability: 3.2.0 Returns a set of geometry_dump rows for the segments in a geometry.
  • ST_FromFlatGeobuf - Availability: 3.2.0 Reads FlatGeobuf data.
  • ST_FromFlatGeobufToTable - Availability: 3.2.0 Creates a table based on the structure of FlatGeobuf data.
  • ST_Scroll - Availability: 3.2.0 Change start point of a closed LineString.
  • postgis.gdal_vsi_options - Availability: 3.2.0 A string configuration to set options used when working with an out-db raster.

Functions enhanced in PostGIS 3.2

  • ST_ClusterKMeans - Enhanced: 3.2.0 Support for max_radius Window function that returns a cluster id for each input geometry using the K-means algorithm.
  • ST_MakeValid - Enhanced: 3.2.0, added algorithm options, 'linework' and 'structure' which requires GEOS >= 3.10.0. Attempts to make an invalid geometry valid without losing vertices.
  • ST_Point - Enhanced: 3.2.0 srid as an extra optional argument was added. Older installs require combining with ST_SetSRID to mark the srid on the geometry. Creates a Point with X, Y and SRID values.
  • ST_PointM - Enhanced: 3.2.0 srid as an extra optional argument was added. Older installs require combining with ST_SetSRID to mark the srid on the geometry. Creates a Point with X, Y, M and SRID values.
  • ST_PointZ - Enhanced: 3.2.0 srid as an extra optional argument was added. Older installs require combining with ST_SetSRID to mark the srid on the geometry. Creates a Point with X, Y, Z and SRID values.
  • ST_PointZM - Enhanced: 3.2.0 srid as an extra optional argument was added. Older installs require combining with ST_SetSRID to mark the srid on the geometry. Creates a Point with X, Y, Z, M and SRID values.
  • ST_RemovePoint - Enhanced: 3.2.0 Remove a point from a linestring.
  • ST_RemoveRepeatedPoints - Enhanced: 3.2.0 Returns a version of a geometry with duplicate points removed.
  • ST_StartPoint - Enhanced: 3.2.0 returns a point for all geometries. Prior behavior returns NULLs if input was not a LineString. Returns the first point of a LineString.

Functions changed in PostGIS 3.2

  • ST_Boundary - Changed: 3.2.0 support for TIN, does not use geos, does not linearize curves Returns the boundary of a geometry.

12.12.4. PostGIS Functions new or enhanced in 3.1

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 3.1

  • ST_Hexagon - Availability: 3.1.0 Returns a single hexagon, using the provided edge size and cell coordinate within the hexagon grid space.
  • ST_HexagonGrid - Availability: 3.1.0 Returns a set of hexagons and cell indices that completely cover the bounds of the geometry argument.
  • ST_MaximumInscribedCircle - Availability: 3.1.0. Computes the largest circle contained within a geometry.
  • ST_ReducePrecision - Availability: 3.1.0. Returns a valid geometry with points rounded to a grid tolerance.
  • ST_Square - Availability: 3.1.0 Returns a single square, using the provided edge size and cell coordinate within the square grid space.
  • ST_SquareGrid - Availability: 3.1.0 Returns a set of grid squares and cell indices that completely cover the bounds of the geometry argument.

Functions enhanced in PostGIS 3.1

  • ST_AsEWKT - Enhanced: 3.1.0 support for optional precision parameter. Return the Well-Known Text (WKT) representation of the geometry with SRID meta data.
  • ST_ClusterKMeans - Enhanced: 3.1.0 Support for 3D geometries and weights Window function that returns a cluster id for each input geometry using the K-means algorithm.
  • ST_Difference - Enhanced: 3.1.0 accept a gridSize parameter. Computes a geometry representing the part of geometry A that does not intersect geometry B.
  • ST_Intersection - Enhanced: 3.1.0 accept a gridSize parameter Computes a geometry representing the shared portion of geometries A and B.
  • ST_MakeValid - Enhanced: 3.1.0, added removal of Coordinates with NaN values. Attempts to make an invalid geometry valid without losing vertices.
  • ST_Subdivide - Enhanced: 3.1.0 accept a gridSize parameter. Computes a rectilinear subdivision of a geometry.
  • ST_SymDifference - Enhanced: 3.1.0 accept a gridSize parameter. Computes a geometry representing the portions of geometries A and B that do not intersect.
  • ST_TileEnvelope - Enhanced: 3.1.0 Added margin parameter. Creates a rectangular Polygon in Web Mercator (SRID:3857) using the XYZ tile system.
  • ST_UnaryUnion - Enhanced: 3.1.0 accept a gridSize parameter. Computes the union of the components of a single geometry.
  • ST_Union - Enhanced: 3.1.0 accept a gridSize parameter. Computes a geometry representing the point-set union of the input geometries.

Functions changed in PostGIS 3.1

  • ST_Force3D - Changed: 3.1.0. Added support for supplying a non-zero Z value. Force the geometries into XYZ mode. This is an alias for ST_Force3DZ.
  • ST_Force3DM - Changed: 3.1.0. Added support for supplying a non-zero M value. Force the geometries into XYM mode.
  • ST_Force3DZ - Changed: 3.1.0. Added support for supplying a non-zero Z value. Force the geometries into XYZ mode.
  • ST_Force4D - Changed: 3.1.0. Added support for supplying non-zero Z and M values. Force the geometries into XYZM mode.

12.12.5. PostGIS Functions new or enhanced in 3.0

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 3.0

  • ST_3DLineInterpolatePoint - Availability: 3.0.0 Returns a point interpolated along a 3D line at a fractional location.
  • ST_ConstrainedDelaunayTriangles - Availability: 3.0.0 Return a constrained Delaunay triangulation around the given input geometry.
  • ST_TileEnvelope - Availability: 3.0.0 Creates a rectangular Polygon in Web Mercator (SRID:3857) using the XYZ tile system.

Functions enhanced in PostGIS 3.0

  • ST_AsMVT - Enhanced: 3.0 - added support for Feature ID. Aggregate function returning a MVT representation of a set of rows.
  • ST_Contains - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if every point of B lies in A, and their interiors have a point in common
  • ST_ContainsProperly - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if every point of B lies in the interior of A
  • ST_CoveredBy - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if every point of A lies in B
  • ST_Covers - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if every point of B lies in A
  • ST_Crosses - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if two geometries have some, but not all, interior points in common
  • ST_CurveToLine - Enhanced: 3.0.0 implemented a minimum number of segments per linearized arc to prevent topological collapse. Converts a geometry containing curves to a linear geometry.
  • ST_Disjoint - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if two geometries have no points in common
  • ST_Equals - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if two geometries include the same set of points
  • ST_GeneratePoints - Enhanced: 3.0.0, added seed parameter Generates random points contained in a Polygon or MultiPolygon.
  • ST_GeomFromGeoJSON - Enhanced: 3.0.0 parsed geometry defaults to SRID=4326 if not specified otherwise. Takes as input a geojson representation of a geometry and outputs a PostGIS geometry object
  • ST_LocateBetween - Enhanced: 3.0.0 - added support for POLYGON, TIN, TRIANGLE. Returns the portions of a geometry that match a measure range.
  • ST_LocateBetweenElevations - Enhanced: 3.0.0 - added support for POLYGON, TIN, TRIANGLE. Returns the portions of a geometry that lie in an elevation (Z) range.
  • ST_Overlaps - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if two geometries have the same dimension and intersect, but each has at least one point not in the other
  • ST_Relate - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if two geometries have a topological relationship matching an Intersection Matrix pattern, or computes their Intersection Matrix
  • ST_Segmentize - Enhanced: 3.0.0 Segmentize geometry now produces equal-length subsegments Returns a modified geometry/geography having no segment longer than a given distance.
  • ST_Touches - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if two geometries have at least one point in common, but their interiors do not intersect
  • ST_Within - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if every point of A lies in B, and their interiors have a point in common

Functions changed in PostGIS 3.0

  • PostGIS_Extensions_Upgrade - Changed: 3.0.0 to repackage loose extensions and support postgis_raster. Packages and upgrades PostGIS extensions (e.g. postgis_raster,postgis_topology, postgis_sfcgal) to given or latest version.
  • ST_3DDistance - Changed: 3.0.0 - SFCGAL version removed Returns the 3D cartesian minimum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DIntersects - Changed: 3.0.0 SFCGAL backend removed, GEOS backend supports TINs. Tests if two geometries spatially intersect in 3D - only for points, linestrings, polygons, polyhedral surface (area)
  • ST_Area - Changed: 3.0.0 - does not depend on SFCGAL anymore. Returns the area of a polygonal geometry.
  • ST_AsGeoJSON - Changed: 3.0.0 support records as input Return a geometry as a GeoJSON element.
  • ST_AsGeoJSON - Changed: 3.0.0 output SRID if not EPSG:4326. Return a geometry as a GeoJSON element.
  • ST_AsKML - Changed: 3.0.0 - Removed the "versioned" variant signature Return the geometry as a KML element.
  • ST_Distance - Changed: 3.0.0 - does not depend on SFCGAL anymore. Returns the distance between two geometry or geography values.
  • ST_Intersection - Changed: 3.0.0 does not depend on SFCGAL. Computes a geometry representing the shared portion of geometries A and B.
  • ST_Intersects - Changed: 3.0.0 SFCGAL version removed and native support for 2D TINS added. Tests if two geometries intersect (they have at least one point in common)
  • ST_Union - Changed: 3.0.0 does not depend on SFCGAL. Computes a geometry representing the point-set union of the input geometries.

12.12.6. PostGIS Functions new or enhanced in 2.5

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 2.5

  • ST_QuantizeCoordinates - Availability: 2.5.0 Sets least significant bits of coordinates to zero
  • PostGIS_Extensions_Upgrade - Availability: 2.5.0 Packages and upgrades PostGIS extensions (e.g. postgis_raster,postgis_topology, postgis_sfcgal) to given or latest version.
  • ST_Angle - Availability: 2.5.0 Returns the angle between two vectors defined by 3 or 4 points, or 2 lines.
  • ST_ChaikinSmoothing - Availability: 2.5.0 Returns a smoothed version of a geometry, using the Chaikin algorithm
  • ST_FilterByM - Availability: 2.5.0 Removes vertices based on their M value
  • ST_LineInterpolatePoints - Availability: 2.5.0 Returns points interpolated along a line at a fractional interval.
  • ST_OrientedEnvelope - Availability: 2.5.0. Returns a minimum-area rectangle containing a geometry.

Functions enhanced in PostGIS 2.5

  • ST_GeometricMedian - Enhanced: 2.5.0 Added support for M as weight of points. Returns the geometric median of a MultiPoint.
  • ST_AsMVT - Enhanced: 2.5.0 - added support parallel query. Aggregate function returning a MVT representation of a set of rows.
  • ST_AsText - Enhanced: 2.5 - optional parameter precision introduced. Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.
  • ST_Buffer - Enhanced: 2.5.0 - ST_Buffer geometry support was enhanced to allow for side buffering specification side=both|left|right. Computes a geometry covering all points within a given distance from a geometry.
  • ST_GeomFromGeoJSON - Enhanced: 2.5.0 can now accept json and jsonb as inputs. Takes as input a geojson representation of a geometry and outputs a PostGIS geometry object
  • ST_Intersects - Enhanced: 2.5.0 Supports GEOMETRYCOLLECTION. Tests if two geometries intersect (they have at least one point in common)
  • ST_OffsetCurve - Enhanced: 2.5 - added support for GEOMETRYCOLLECTION and MULTILINESTRING Returns an offset line at a given distance and side from an input line.
  • ST_Scale - Enhanced: 2.5.0 support for scaling relative to a local origin (origin parameter) was introduced. Scales a geometry by given factors.
  • ST_Split - Enhanced: 2.5.0 support for splitting a polygon by a multiline was introduced. Returns a collection of geometries created by splitting a geometry by another geometry.
  • ST_Subdivide - Enhanced: 2.5.0 reuses existing points on polygon split, vertex count is lowered from 8 to 5. Computes a rectilinear subdivision of a geometry.

12.12.7. PostGIS Functions new or enhanced in 2.4

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 2.4

  • ST_ForcePolygonCCW - Availability: 2.4.0 Orients all exterior rings counter-clockwise and all interior rings clockwise.
  • ST_ForcePolygonCW - Availability: 2.4.0 Orients all exterior rings clockwise and all interior rings counter-clockwise.
  • ST_IsPolygonCCW - Availability: 2.4.0 Tests if Polygons have exterior rings oriented counter-clockwise and interior rings oriented clockwise.
  • ST_IsPolygonCW - Availability: 2.4.0 Tests if Polygons have exterior rings oriented clockwise and interior rings oriented counter-clockwise.
  • ST_AsGeobuf - Availability: 2.4.0 Return a Geobuf representation of a set of rows.
  • ST_AsMVT - Availability: 2.4.0 Aggregate function returning a MVT representation of a set of rows.
  • ST_AsMVTGeom - Availability: 2.4.0 Transforms a geometry into the coordinate space of a MVT tile.
  • ST_Centroid - Availability: 2.4.0 support for geography was introduced. Returns the geometric center of a geometry.
  • ST_FrechetDistance - Availability: 2.4.0 - requires GEOS >= 3.7.0 Returns the Fréchet distance between two geometries.

Functions enhanced in PostGIS 2.4

  • ST_AsTWKB - Enhanced: 2.4.0 memory and speed improvements. Returns the geometry as TWKB, aka "Tiny Well-Known Binary"
  • ST_Covers - Enhanced: 2.4.0 Support for polygon in polygon and line in polygon added for geography type Tests if every point of B lies in A
  • ST_CurveToLine - Enhanced: 2.4.0 added support for max-deviation and max-angle tolerance, and for symmetric output. Converts a geometry containing curves to a linear geometry.
  • ST_Project - Enhanced: 2.4.0 Allow negative distance and non-normalized azimuth. Returns a point projected from a start point by a distance and bearing (azimuth).
  • ST_Reverse - Enhanced: 2.4.0 support for curves was introduced. Return the geometry with vertex order reversed.

Functions changed in PostGIS 2.4

  • = - Changed: 2.4.0, in prior versions this was bounding box equality not a geometric equality. If you need bounding box equality, use instead. Returns TRUE if the coordinates and coordinate order geometry/geography A are the same as the coordinates and coordinate order of geometry/geography B.
  • ST_Node - Changed: 2.4.0 this function uses GEOSNode internally instead of GEOSUnaryUnion. This may cause the resulting linestrings to have a different order and direction compared to PostGIS < 2.4. Nodes a collection of lines.

12.12.8. PostGIS Functions new or enhanced in 2.3

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 2.3

  • ST_GeometricMedian - Availability: 2.3.0 Returns the geometric median of a MultiPoint.
  • &&&(geometry,gidx) - Availability: 2.3.0 support for Block Range INdexes (BRIN) was introduced. Requires PostgreSQL 9.5+. Returns TRUE if a geometry's (cached) n-D bounding box intersects a n-D float precision bounding box (GIDX).
  • &&&(gidx,geometry) - Availability: 2.3.0 support for Block Range INdexes (BRIN) was introduced. Requires PostgreSQL 9.5+. Returns TRUE if a n-D float precision bounding box (GIDX) intersects a geometry's (cached) n-D bounding box.
  • &&&(gidx,gidx) - Availability: 2.3.0 support for Block Range INdexes (BRIN) was introduced. Requires PostgreSQL 9.5+. Returns TRUE if two n-D float precision bounding boxes (GIDX) intersect each other.
  • &&(box2df,box2df) - Availability: 2.3.0 support for Block Range INdexes (BRIN) was introduced. Requires PostgreSQL 9.5+. Returns TRUE if two 2D float precision bounding boxes (BOX2DF) intersect each other.
  • &&(box2df,geometry) - Availability: 2.3.0 support for Block Range INdexes (BRIN) was introduced. Requires PostgreSQL 9.5+. Returns TRUE if a 2D float precision bounding box (BOX2DF) intersects a geometry's (cached) 2D bounding box.
  • &&(geometry,box2df) - Availability: 2.3.0 support for Block Range INdexes (BRIN) was introduced. Requires PostgreSQL 9.5+. Returns TRUE if a geometry's (cached) 2D bounding box intersects a 2D float precision bounding box (BOX2DF).
  • @(box2df,box2df) - Availability: 2.3.0 support for Block Range INdexes (BRIN) was introduced. Requires PostgreSQL 9.5+. Returns TRUE if a 2D float precision bounding box (BOX2DF) is contained into another 2D float precision bounding box.
  • @(box2df,geometry) - Availability: 2.3.0 support for Block Range INdexes (BRIN) was introduced. Requires PostgreSQL 9.5+. Returns TRUE if a 2D float precision bounding box (BOX2DF) is contained into a geometry's 2D bounding box.
  • @(geometry,box2df) - Availability: 2.3.0 support for Block Range INdexes (BRIN) was introduced. Requires PostgreSQL 9.5+. Returns TRUE if a geometry's 2D bounding box is contained into a 2D float precision bounding box (BOX2DF).
  • ST_ClusterDBSCAN - Availability: 2.3.0 Window function that returns a cluster id for each input geometry using the DBSCAN algorithm.
  • ST_ClusterKMeans - Availability: 2.3.0 Window function that returns a cluster id for each input geometry using the K-means algorithm.
  • ST_GeneratePoints - Availability: 2.3.0 Generates random points contained in a Polygon or MultiPolygon.
  • ST_MakeLine - Availability: 2.3.0 - Support for MultiPoint input elements was introduced Creates a LineString from Point, MultiPoint, or LineString geometries.
  • ST_MinimumBoundingRadius - Availability - 2.3.0 Returns the center point and radius of the smallest circle that contains a geometry.
  • ST_MinimumClearance - Availability: 2.3.0 Returns the minimum clearance of a geometry, a measure of a geometry's robustness.
  • ST_MinimumClearanceLine - Availability: 2.3.0 - requires GEOS >= 3.6.0 Returns the two-point LineString spanning a geometry's minimum clearance.
  • ST_Normalize - Availability: 2.3.0 Return the geometry in its canonical form.
  • ST_Points - Availability: 2.3.0 Returns a MultiPoint containing the coordinates of a geometry.
  • ST_VoronoiLines - Availability: 2.3.0 Returns the boundaries of the Voronoi diagram of the vertices of a geometry.
  • ST_VoronoiPolygons - Availability: 2.3.0 Returns the cells of the Voronoi diagram of the vertices of a geometry.
  • ST_WrapX - Availability: 2.3.0 requires GEOS Wrap a geometry around an X value.
  • ~(box2df,box2df) - Availability: 2.3.0 support for Block Range INdexes (BRIN) was introduced. Requires PostgreSQL 9.5+. Returns TRUE if a 2D float precision bounding box (BOX2DF) contains another 2D float precision bounding box (BOX2DF).
  • ~(box2df,geometry) - Availability: 2.3.0 support for Block Range INdexes (BRIN) was introduced. Requires PostgreSQL 9.5+. Returns TRUE if a 2D float precision bounding box (BOX2DF) contains a geometry's 2D bonding box.
  • ~(geometry,box2df) - Availability: 2.3.0 support for Block Range INdexes (BRIN) was introduced. Requires PostgreSQL 9.5+. Returns TRUE if a geometry's 2D bonding box contains a 2D float precision bounding box (GIDX).

Functions enhanced in PostGIS 2.3

  • ST_Contains - Enhanced: 2.3.0 Enhancement to PIP short-circuit extended to support MultiPoints with few points. Prior versions only supported point in polygon. Tests if every point of B lies in A, and their interiors have a point in common
  • ST_Covers - Enhanced: 2.3.0 Enhancement to PIP short-circuit for geometry extended to support MultiPoints with few points. Prior versions only supported point in polygon. Tests if every point of B lies in A
  • ST_Expand - Enhanced: 2.3.0 support was added to expand a box by different amounts in different dimensions. Returns a bounding box expanded from another bounding box or a geometry.
  • ST_Intersects - Enhanced: 2.3.0 Enhancement to PIP short-circuit extended to support MultiPoints with few points. Prior versions only supported point in polygon. Tests if two geometries intersect (they have at least one point in common)
  • ST_Segmentize - Enhanced: 2.3.0 Segmentize geography now produces equal-length subsegments Returns a modified geometry/geography having no segment longer than a given distance.
  • ST_Transform - Enhanced: 2.3.0 support for direct PROJ.4 text was introduced. Return a new geometry with coordinates transformed to a different spatial reference system.
  • ST_Within - Enhanced: 2.3.0 Enhancement to PIP short-circuit for geometry extended to support MultiPoints with few points. Prior versions only supported point in polygon. Tests if every point of A lies in B, and their interiors have a point in common

Functions changed in PostGIS 2.3

  • ST_PointN - Changed: 2.3.0 : negative indexing available (-1 is last point) Returns the Nth point in the first LineString or circular LineString in a geometry.

12.12.9. PostGIS Functions new or enhanced in 2.2

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 2.2

  • <<#>> - Availability: 2.2.0 -- KNN only available for PostgreSQL 9.1+ Returns the n-D distance between A and B bounding boxes.
  • <<->> - Availability: 2.2.0 -- KNN only available for PostgreSQL 9.1+ Returns the n-D distance between the centroids of A and B boundingboxes.
  • ST_3DDifference - Availability: 2.2.0 Perform 3D difference
  • ST_3DUnion - Availability: 2.2.0 Perform 3D union.
  • ST_ApproximateMedialAxis - Availability: 2.2.0 Compute the approximate medial axis of an areal geometry.
  • ST_AsEncodedPolyline - Availability: 2.2.0 Returns an Encoded Polyline from a LineString geometry.
  • ST_AsTWKB - Availability: 2.2.0 Returns the geometry as TWKB, aka "Tiny Well-Known Binary"
  • ST_BoundingDiagonal - Availability: 2.2.0 Returns the diagonal of a geometry's bounding box.
  • ST_CPAWithin - Availability: 2.2.0 Tests if the closest point of approach of two trajectoriesis within the specified distance.
  • ST_ClipByBox2D - Availability: 2.2.0 Computes the portion of a geometry falling within a rectangle.
  • ST_ClosestPointOfApproach - Availability: 2.2.0 Returns a measure at the closest point of approach of two trajectories.
  • ST_ClusterIntersecting - Availability: 2.2.0 Aggregate function that clusters input geometries into connected sets.
  • ST_ClusterWithin - Availability: 2.2.0 Aggregate function that clusters geometries by separation distance.
  • ST_DistanceCPA - Availability: 2.2.0 Returns the distance between the closest point of approach of two trajectories.
  • ST_ForceCurve - Availability: 2.2.0 Upcast a geometry into its curved type, if applicable.
  • ST_IsPlanar - Availability: 2.2.0: This was documented in 2.1.0 but got accidentally left out in 2.1 release. Check if a surface is or not planar
  • ST_IsSolid - Availability: 2.2.0 Test if the geometry is a solid. No validity check is performed.
  • ST_IsValidTrajectory - Availability: 2.2.0 Tests if the geometry is a valid trajectory.
  • ST_LineFromEncodedPolyline - Availability: 2.2.0 Creates a LineString from an Encoded Polyline.
  • ST_MakeSolid - Availability: 2.2.0 Cast the geometry into a solid. No check is performed. To obtain a valid solid, the input geometry must be a closed Polyhedral Surface or a closed TIN.
  • ST_RemoveRepeatedPoints - Availability: 2.2.0 Returns a version of a geometry with duplicate points removed.
  • ST_SetEffectiveArea - Availability: 2.2.0 Sets the effective area for each vertex, using the Visvalingam-Whyatt algorithm.
  • ST_SimplifyVW - Availability: 2.2.0 Returns a simplified version of a geometry, using the Visvalingam-Whyatt algorithm
  • ST_Subdivide - Availability: 2.2.0 Computes a rectilinear subdivision of a geometry.
  • ST_SwapOrdinates - Availability: 2.2.0 Returns a version of the given geometry with given ordinate values swapped.
  • ST_Volume - Availability: 2.2.0 Computes the volume of a 3D solid. If applied to surface (even closed) geometries will return 0.
  • postgis.enable_outdb_rasters - Availability: 2.2.0 A boolean configuration option to enable access to out-db raster bands.
  • postgis.gdal_datapath - Availability: 2.2.0 A configuration option to assign the value of GDAL's GDAL_DATA option. If not set, the environmentally set GDAL_DATA variable is used.
  • postgis.gdal_enabled_drivers - Availability: 2.2.0 A configuration option to set the enabled GDAL drivers in the PostGIS environment. Affects the GDAL configuration variable GDAL_SKIP.
  • |=| - Availability: 2.2.0. Index-supported only available for PostgreSQL 9.5+ Returns the distance between A and B trajectories at their closest point of approach.

Functions enhanced in PostGIS 2.2

  • <-> - Enhanced: 2.2.0 -- True KNN ("K nearest neighbor") behavior for geometry and geography for PostgreSQL 9.5+. Note for geography KNN is based on sphere rather than spheroid. For PostgreSQL 9.4 and below, geography support is new but only supports centroid box. Returns the 2D distance between A and B.
  • ST_Area - Enhanced: 2.2.0 - measurement on spheroid performed with GeographicLib for improved accuracy and robustness. Requires PROJ >= 4.9.0 to take advantage of the new feature. Returns the area of a polygonal geometry.
  • ST_AsX3D - Enhanced: 2.2.0: Support for GeoCoordinates and axis (x/y, long/lat) flipping. Look at options for details. Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML
  • ST_Azimuth - Enhanced: 2.2.0 measurement on spheroid performed with GeographicLib for improved accuracy and robustness. Requires PROJ >= 4.9.0 to take advantage of the new feature. Returns the north-based azimuth of a line between two points.
  • ST_Distance - Enhanced: 2.2.0 - measurement on spheroid performed with GeographicLib for improved accuracy and robustness. Requires PROJ >= 4.9.0 to take advantage of the new feature. Returns the distance between two geometry or geography values.
  • ST_Scale - Enhanced: 2.2.0 support for scaling all dimension (factor parameter) was introduced. Scales a geometry by given factors.
  • ST_Split - Enhanced: 2.2.0 support for splitting a line by a multiline, a multipoint or (multi)polygon boundary was introduced. Returns a collection of geometries created by splitting a geometry by another geometry.
  • ST_Summary - Enhanced: 2.2.0 Added support for TIN and Curves Returns a text summary of the contents of a geometry.

Functions changed in PostGIS 2.2

  • <-> - Changed: 2.2.0 -- For PostgreSQL 9.5 users, old Hybrid syntax may be slower, so you'll want to get rid of that hack if you are running your code only on PostGIS 2.2+ 9.5+. See examples below. Returns the 2D distance between A and B.
  • ST_3DClosestPoint - Changed: 2.2.0 - if 2 2D geometries are input, a 2D point is returned (instead of old behavior assuming 0 for missing Z). In case of 2D and 3D, Z is no longer assumed to be 0 for missing Z. Returns the 3D point on g1 that is closest to g2. This is the first point of the 3D shortest line.
  • ST_3DDistance - Changed: 2.2.0 - In case of 2D and 3D, Z is no longer assumed to be 0 for missing Z. Returns the 3D cartesian minimum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DLongestLine - Changed: 2.2.0 - if 2 2D geometries are input, a 2D point is returned (instead of old behavior assuming 0 for missing Z). In case of 2D and 3D, Z is no longer assumed to be 0 for missing Z. Returns the 3D longest line between two geometries
  • ST_3DMaxDistance - Changed: 2.2.0 - In case of 2D and 3D, Z is no longer assumed to be 0 for missing Z. Returns the 3D cartesian maximum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DShortestLine - Changed: 2.2.0 - if 2 2D geometries are input, a 2D point is returned (instead of old behavior assuming 0 for missing Z). In case of 2D and 3D, Z is no longer assumed to be 0 for missing Z. Returns the 3D shortest line between two geometries
  • ST_DistanceSphere - Changed: 2.2.0 In prior versions this used to be called ST_Distance_Sphere Returns minimum distance in meters between two lon/lat geometries using a spherical earth model.
  • ST_DistanceSpheroid - Changed: 2.2.0 In prior versions this was called ST_Distance_Spheroid Returns the minimum distance between two lon/lat geometries using a spheroidal earth model.
  • ST_Equals - Changed: 2.2.0 Returns true even for invalid geometries if they are binary equal Tests if two geometries include the same set of points
  • ST_LengthSpheroid - Changed: 2.2.0 In prior versions this was called ST_Length_Spheroid and had the alias ST_3DLength_Spheroid Returns the 2D or 3D length/perimeter of a lon/lat geometry on a spheroid.
  • ST_MemSize - Changed: 2.2.0 name changed to ST_MemSize to follow naming convention. Returns the amount of memory space a geometry takes.
  • ST_PointInsideCircle - Changed: 2.2.0 In prior versions this was called ST_Point_Inside_Circle Tests if a point geometry is inside a circle defined by a center and radius

12.12.10. PostGIS Functions new or enhanced in 2.1

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 2.1

  • ST_3DArea - Availability: 2.1.0 Computes area of 3D surface geometries. Will return 0 for solids.
  • ST_3DIntersection - Availability: 2.1.0 Perform 3D intersection
  • ST_Box2dFromGeoHash - Availability: 2.1.0 Return a BOX2D from a GeoHash string.
  • ST_DelaunayTriangles - Availability: 2.1.0 Returns the Delaunay triangulation of the vertices of a geometry.
  • ST_Extrude - Availability: 2.1.0 Extrude a surface to a related volume
  • ST_ForceLHR - Availability: 2.1.0 Force LHR orientation
  • ST_GeomFromGeoHash - Availability: 2.1.0 Return a geometry from a GeoHash string.
  • ST_MinkowskiSum - Availability: 2.1.0 Performs Minkowski sum
  • ST_Orientation - Availability: 2.1.0 Determine surface orientation
  • ST_PointFromGeoHash - Availability: 2.1.0 Return a point from a GeoHash string.
  • ST_StraightSkeleton - Availability: 2.1.0 Compute a straight skeleton from a geometry
  • ST_Tesselate - Availability: 2.1.0 Perform surface Tesselation of a polygon or polyhedralsurface and returns as a TIN or collection of TINS
  • postgis.backend - Availability: 2.1.0 The backend to service a function where GEOS and SFCGAL overlap. Options: geos or sfcgal. Defaults to geos.
  • postgis_sfcgal_version - Availability: 2.1.0 Returns the version of SFCGAL in use

Functions enhanced in PostGIS 2.1

  • ST_AsGML - Enhanced: 2.1.0 id support was introduced, for GML 3. Return the geometry as a GML version 2 or 3 element.
  • ST_Boundary - Enhanced: 2.1.0 support for Triangle was introduced Returns the boundary of a geometry.
  • ST_DWithin - Enhanced: 2.1.0 improved speed for geography. See Making Geography faster for details. Tests if two geometries are within a given distance
  • ST_DWithin - Enhanced: 2.1.0 support for curved geometries was introduced. Tests if two geometries are within a given distance
  • ST_Distance - Enhanced: 2.1.0 improved speed for geography. See Making Geography faster for details. Returns the distance between two geometry or geography values.
  • ST_Distance - Enhanced: 2.1.0 - support for curved geometries was introduced. Returns the distance between two geometry or geography values.
  • ST_DumpPoints - Enhanced: 2.1.0 Faster speed. Reimplemented as native-C. Returns a set of geometry_dump rows for the coordinates in a geometry.
  • ST_MakeValid - Enhanced: 2.1.0, added support for GEOMETRYCOLLECTION and MULTIPOINT. Attempts to make an invalid geometry valid without losing vertices.
  • ST_Segmentize - Enhanced: 2.1.0 support for geography was introduced. Returns a modified geometry/geography having no segment longer than a given distance.
  • ST_Summary - Enhanced: 2.1.0 S flag to denote if has a known spatial reference system Returns a text summary of the contents of a geometry.

Functions changed in PostGIS 2.1

  • ST_EstimatedExtent - Changed: 2.1.0. Up to 2.0.x this was called ST_Estimated_Extent. Returns the estimated extent of a spatial table.
  • ST_Force2D - Changed: 2.1.0. Up to 2.0.x this was called ST_Force_2D. Force the geometries into a "2-dimensional mode".
  • ST_Force3D - Changed: 2.1.0. Up to 2.0.x this was called ST_Force_3D. Force the geometries into XYZ mode. This is an alias for ST_Force3DZ.
  • ST_Force3DM - Changed: 2.1.0. Up to 2.0.x this was called ST_Force_3DM. Force the geometries into XYM mode.
  • ST_Force3DZ - Changed: 2.1.0. Up to 2.0.x this was called ST_Force_3DZ. Force the geometries into XYZ mode.
  • ST_Force4D - Changed: 2.1.0. Up to 2.0.x this was called ST_Force_4D. Force the geometries into XYZM mode.
  • ST_ForceCollection - Changed: 2.1.0. Up to 2.0.x this was called ST_Force_Collection. Convert the geometry into a GEOMETRYCOLLECTION.
  • ST_LineInterpolatePoint - Changed: 2.1.0. Up to 2.0.x this was called ST_Line_Interpolate_Point. Returns a point interpolated along a line at a fractional location.
  • ST_LineLocatePoint - Changed: 2.1.0. Up to 2.0.x this was called ST_Line_Locate_Point. Returns the fractional location of the closest point on a line to a point.
  • ST_LineSubstring - Changed: 2.1.0. Up to 2.0.x this was called ST_Line_Substring. Returns the part of a line between two fractional locations.
  • ST_Segmentize - Changed: 2.1.0 As a result of the introduction of geography support, the usage ST_Segmentize('LINESTRING(1 2, 3 4)', 0.5) causes an ambiguous function error. The input needs to be properly typed as a geometry or geography. Use ST_GeomFromText, ST_GeogFromText or a cast to the required type (e.g. ST_Segmentize('LINESTRING(1 2, 3 4)'::geometry, 0.5) ) Returns a modified geometry/geography having no segment longer than a given distance.

12.12.11. PostGIS Functions new or enhanced in 2.0

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 2.0

  • &&& - Availability: 2.0.0 Returns TRUE if A's n-D bounding box intersects B's n-D bounding box.
  • <#> - Availability: 2.0.0 -- KNN only available for PostgreSQL 9.1+ Returns the 2D distance between A and B bounding boxes.
  • <-> - Availability: 2.0.0 -- Weak KNN provides nearest neighbors based on geometry centroid distances instead of true distances. Exact results for points, inexact for all other types. Available for PostgreSQL 9.1+ Returns the 2D distance between A and B.
  • ST_3DClosestPoint - Availability: 2.0.0 Returns the 3D point on g1 that is closest to g2. This is the first point of the 3D shortest line.
  • ST_3DDFullyWithin - Availability: 2.0.0 Tests if two 3D geometries are entirely within a given 3D distance
  • ST_3DDWithin - Availability: 2.0.0 Tests if two 3D geometries are within a given 3D distance
  • ST_3DDistance - Availability: 2.0.0 Returns the 3D cartesian minimum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DIntersects - Availability: 2.0.0 Tests if two geometries spatially intersect in 3D - only for points, linestrings, polygons, polyhedral surface (area)
  • ST_3DLongestLine - Availability: 2.0.0 Returns the 3D longest line between two geometries
  • ST_3DMaxDistance - Availability: 2.0.0 Returns the 3D cartesian maximum distance (based on spatial ref) between two geometries in projected units.
  • ST_3DShortestLine - Availability: 2.0.0 Returns the 3D shortest line between two geometries
  • ST_AsLatLonText - Availability: 2.0 Return the Degrees, Minutes, Seconds representation of the given point.
  • 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_CollectionHomogenize - Availability: 2.0.0 Returns the simplest representation of a geometry collection.
  • ST_ConcaveHull - Availability: 2.0.0 Computes a possibly concave geometry that contains all input geometry vertices
  • ST_FlipCoordinates - Availability: 2.0.0 Returns a version of a geometry with X and Y axis flipped.
  • 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_InterpolatePoint - Availability: 2.0.0 Returns the interpolated measure of a geometry closest to a point.
  • ST_IsValidDetail - Availability: 2.0.0 Returns a valid_detail row stating if a geometry is valid or if not a reason and a location.
  • ST_IsValidReason - Availability: 2.0 version taking flags. Returns text stating if a geometry is valid, or a reason for invalidity.
  • ST_MakeLine - Availability: 2.0.0 - Support for LineString input elements was introduced Creates a LineString from Point, MultiPoint, or LineString geometries.
  • ST_MakeValid - Availability: 2.0.0 Attempts to make an invalid geometry valid without losing vertices.
  • ST_Node - Availability: 2.0.0 Nodes a collection of lines.
  • 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 Returns an offset line at a given distance and side from an input line.
  • ST_PatchN - Availability: 2.0.0 Returns the Nth geometry (face) of a PolyhedralSurface.
  • ST_Perimeter - Availability 2.0.0: Support for geography was introduced Returns the length of the boundary of a polygonal geometry or geography.
  • ST_Project - Availability: 2.0.0 Returns a point projected from a start point by a distance and bearing (azimuth).
  • ST_RelateMatch - Availability: 2.0.0 Tests if a DE-9IM Intersection Matrix matches an Intersection Matrix pattern
  • ST_SharedPaths - Availability: 2.0.0 Returns a collection containing paths shared by the two input linestrings/multilinestrings.
  • ST_Snap - Availability: 2.0.0 Snap segments and vertices of input geometry to vertices of a reference geometry.
  • ST_Split - Availability: 2.0.0 requires GEOS Returns a collection of geometries created by splitting a geometry by another geometry.
  • ST_UnaryUnion - Availability: 2.0.0 Computes the union of the components of a single geometry.

Functions enhanced in PostGIS 2.0

  • && - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Returns TRUE if A's 2D bounding box intersects B's 2D bounding box.
  • AddGeometryColumn - Enhanced: 2.0.0 use_typmod argument introduced. Defaults to creating typmod geometry column instead of constraint-based. Adds a geometry column to an existing table.
  • Box2D - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Returns a BOX2D representing the 2D extent of a geometry.
  • Box3D - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Returns a BOX3D representing the 3D extent of a geometry.
  • GeometryType - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Returns the type of a geometry as text.
  • 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. Ensures geometry columns are defined with type modifiers or have appropriate spatial constraints.
  • ST_3DExtent - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Aggregate function that returns the 3D bounding box of geometries.
  • ST_Affine - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Apply a 3D affine transformation to a geometry.
  • ST_Area - Enhanced: 2.0.0 - support for 2D polyhedral surfaces was introduced. Returns the area of a polygonal geometry.
  • ST_AsBinary - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Return the OGC/ISO Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsBinary - Enhanced: 2.0.0 support for higher coordinate dimensions was introduced. Return the OGC/ISO Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsBinary - Enhanced: 2.0.0 support for specifying endian with geography was introduced. Return the OGC/ISO Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsEWKB - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Return the Extended Well-Known Binary (EWKB) representation of the geometry with SRID meta data.
  • ST_AsEWKT - Enhanced: 2.0.0 support for Geography, Polyhedral surfaces, Triangles and TIN was introduced. Return the Well-Known Text (WKT) representation of the geometry with SRID meta data.
  • 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. Return the geometry as a GML version 2 or 3 element.
  • ST_AsKML - Enhanced: 2.0.0 - Add prefix namespace, use default and named args Return the geometry as a KML element.
  • ST_Azimuth - Enhanced: 2.0.0 support for geography was introduced. Returns the north-based azimuth of a line between two points.
  • ST_Dimension - Enhanced: 2.0.0 support for Polyhedral surfaces and TINs was introduced. No longer throws an exception if given empty geometry. Returns the topological dimension of a geometry.
  • ST_Dump - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Returns a set of geometry_dump rows for the components of a geometry.
  • ST_DumpPoints - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Returns a set of geometry_dump rows for the coordinates in a geometry.
  • ST_Expand - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Returns a bounding box expanded from another bounding box or a geometry.
  • ST_Extent - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Aggregate function that returns the bounding box of geometries.
  • ST_Force2D - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Force the geometries into a "2-dimensional mode".
  • ST_Force3D - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Force the geometries into XYZ mode. This is an alias for ST_Force3DZ.
  • ST_Force3DZ - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Force the geometries into XYZ mode.
  • ST_ForceCollection - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Convert the geometry into a GEOMETRYCOLLECTION.
  • ST_ForceRHR - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Force the orientation of the vertices in a polygon to follow the Right-Hand-Rule.
  • ST_GMLToSQL - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced. Return a specified ST_Geometry value from GML representation. This is an alias name for ST_GeomFromGML
  • ST_GMLToSQL - Enhanced: 2.0.0 default srid optional parameter added. Return a specified ST_Geometry value from GML representation. This is an alias name for ST_GeomFromGML
  • ST_GeomFromEWKB - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced. Return a specified ST_Geometry value from Extended Well-Known Binary representation (EWKB).
  • ST_GeomFromEWKT - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced. Return a specified ST_Geometry value from Extended Well-Known Text representation (EWKT).
  • ST_GeomFromGML - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced. Takes as input GML representation of geometry and outputs a PostGIS geometry object
  • ST_GeomFromGML - Enhanced: 2.0.0 default srid optional parameter added. Takes as input GML representation of geometry and outputs a PostGIS geometry object
  • ST_GeometryN - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Return an element of a geometry collection.
  • ST_GeometryType - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Returns the SQL-MM type of a geometry as text.
  • ST_IsClosed - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Tests if a LineStrings's start and end points are coincident. For a PolyhedralSurface tests if it is closed (volumetric).
  • ST_MakeEnvelope - Enhanced: 2.0: Ability to specify an envelope without specifying an SRID was introduced. Creates a rectangular Polygon from minimum and maximum coordinates.
  • ST_MakeValid - Enhanced: 2.0.1, speed improvements Attempts to make an invalid geometry valid without losing vertices.
  • ST_NPoints - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Returns the number of points (vertices) in a geometry.
  • ST_NumGeometries - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Returns the number of elements in a geometry collection.
  • ST_Relate - Enhanced: 2.0.0 - added support for specifying boundary node rule. Tests if two geometries have a topological relationship matching an Intersection Matrix pattern, or computes their Intersection Matrix
  • ST_Rotate - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Rotates a geometry about an origin point.
  • ST_Rotate - Enhanced: 2.0.0 additional parameters for specifying the origin of rotation were added. Rotates a geometry about an origin point.
  • ST_RotateX - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Rotates a geometry about the X axis.
  • ST_RotateY - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Rotates a geometry about the Y axis.
  • ST_RotateZ - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Rotates a geometry about the Z axis.
  • ST_Scale - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced. Scales a geometry by given factors.
  • ST_ShiftLongitude - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced. Shifts the longitude coordinates of a geometry between -180..180 and 0..360.
  • ST_Summary - Enhanced: 2.0.0 added support for geography Returns a text summary of the contents of a geometry.
  • ST_Transform - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced. Return a new geometry with coordinates transformed to a different spatial reference system.

Functions changed in PostGIS 2.0

  • 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); Adds a geometry column to an existing table.
  • AddGeometryColumn - Changed: 2.0.0 If you require the old behavior of constraints use the default use_typmod, but set it to false. Adds a geometry column to an existing table.
  • 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 . Adds a geometry column to an existing table.
  • 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 Removes a geometry column from a spatial 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 Drops a table and all its references in geometry_columns.
  • 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. Ensures geometry columns are defined with type modifiers or have appropriate spatial constraints.
  • ST_3DExtent - Changed: 2.0.0 In prior versions this used to be called ST_Extent3D Aggregate function that returns the 3D bounding box of geometries.
  • ST_3DLength - Changed: 2.0.0 In prior versions this used to be called ST_Length3D Returns the 3D length of a linear geometry.
  • ST_3DMakeBox - Changed: 2.0.0 In prior versions this used to be called ST_MakeBox3D Creates a BOX3D defined by two 3D point geometries.
  • ST_3DPerimeter - Changed: 2.0.0 In prior versions this used to be called ST_Perimeter3D Returns the 3D perimeter of a polygonal geometry.
  • ST_AsBinary - Changed: 2.0.0 Inputs to this function can not be unknown -- must be geometry. Constructs such as ST_AsBinary('POINT(1 2)') are no longer valid and you will get an n st_asbinary(unknown) is not unique error. Code like that needs to be changed to ST_AsBinary('POINT(1 2)'::geometry);. If that is not possible, then install legacy.sql. Return the OGC/ISO Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
  • ST_AsGML - Changed: 2.0.0 use default named args Return the geometry as a GML version 2 or 3 element.
  • ST_AsGeoJSON - Changed: 2.0.0 support default args and named args. Return a geometry as a GeoJSON element.
  • ST_AsSVG - Changed: 2.0.0 to use default args and support named args Returns SVG path data for a geometry.
  • ST_EndPoint - Changed: 2.0.0 no longer works with single geometry MultiLineStrings. In older versions of PostGIS a single-line MultiLineString would work with this function and return the end point. In 2.0.0 it returns NULL like any other MultiLineString. The old behavior was an undocumented feature, but people who assumed they had their data stored as LINESTRING may experience these returning NULL in 2.0.0. Returns the last point of a LineString or CircularLineString.
  • 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') Return a specified ST_Geometry value from Well-Known Text representation (WKT).
  • 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. Return an element of a geometry collection.
  • 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 Tests if a geometry is empty.
  • 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 Returns the 2D length of a linear geometry.
  • ST_LocateAlong - Changed: 2.0.0 in prior versions this used to be called ST_Locate_Along_Measure. Returns the point(s) on a geometry that match a measure value.
  • ST_LocateBetween - Changed: 2.0.0 - in prior versions this used to be called ST_Locate_Between_Measures. Returns the portions of a geometry that match a measure range.
  • 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. Returns the number of elements in a geometry collection.
  • ST_NumInteriorRings - Changed: 2.0.0 - in prior versions it would allow passing a MULTIPOLYGON, returning the number of interior rings of first POLYGON. Returns the number of interior rings (holes) of a Polygon.
  • 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. Returns the Nth point in the first LineString or circular LineString in a geometry.
  • 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 old behavior was an undocumented feature, but people who assumed they had their data stored as LINESTRING may experience these returning NULL in 2.0.0. Returns the first point of a LineString.

12.12.12. PostGIS Functions new or enhanced in 1.5

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 1.5

  • && - Availability: 1.5.0 support for geography was introduced. Returns TRUE if A's 2D bounding box intersects B's 2D bounding box.
  • PostGIS_LibXML_Version - Availability: 1.5 Returns the version number of the libxml2 library.
  • ST_AddMeasure - Availability: 1.5.0 Interpolates measures along a linear geometry.
  • ST_AsBinary - Availability: 1.5.0 geography support was introduced. Return the OGC/ISO 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 a 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. Computes a geometry covering all points within a given distance from a geometry.
  • ST_ClosestPoint - Availability: 1.5.0 Returns the 2D point on g1 that is closest to g2. This is the first point of the shortest line from one geometry to the other.
  • ST_CollectionExtract - Availability: 1.5.0 Given a geometry collection, returns a multi-geometry containing only elements of a specified type.
  • ST_Covers - Availability: 1.5 - support for geography was introduced. Tests if every point of B lies in A
  • ST_DFullyWithin - Availability: 1.5.0 Tests if two geometries are entirely within a given distance
  • ST_DWithin - Availability: 1.5.0 support for geography was introduced Tests if two geometries are within a given distance
  • 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 Returns the distance between two geometry or geography values.
  • ST_DistanceSphere - 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 using a spherical earth model.
  • ST_DistanceSpheroid - 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 using a spheroidal earth model.
  • ST_DumpPoints - Availability: 1.5.0 Returns a set of geometry_dump rows for the coordinates in a geometry.
  • ST_Envelope - Availability: 1.5.0 behavior changed to output double precision instead of float4 Returns a geometry representing the bounding box of a geometry.
  • ST_Expand - Availability: 1.5.0 behavior changed to output double precision instead of float4 coordinates. Returns a bounding box expanded from another bounding box or a 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, requires libxml2 2.6+ Takes as input KML representation of geometry and outputs a PostGIS geometry object
  • ST_HausdorffDistance - Availability: 1.5.0 Returns the Hausdorff distance between two geometries.
  • ST_Intersection - Availability: 1.5 support for geography data type was introduced. Computes a geometry representing the shared portion of geometries A and B.
  • ST_Intersects - Availability: 1.5 support for geography was introduced. Tests if two geometries intersect (they have at least one point in common)
  • ST_Length - Availability: 1.5.0 geography support was introduced in 1.5. Returns the 2D length of a linear geometry.
  • ST_LongestLine - Availability: 1.5.0 Returns the 2D longest line between two geometries.
  • ST_MakeEnvelope - Availability: 1.5 Creates a rectangular Polygon from minimum and maximum coordinates.
  • ST_MaxDistance - Availability: 1.5.0 Returns the 2D largest distance between two geometries in projected units.
  • ST_ShortestLine - Availability: 1.5.0 Returns the 2D shortest line between two geometries
  • ~= - Availability: 1.5.0 changed behavior Returns TRUE if A's bounding box is the same as B's.

12.12.13. PostGIS Functions new or enhanced in 1.4

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 1.4

  • Populate_Geometry_Columns - Availability: 1.4.0 Ensures geometry columns are defined with type modifiers or have appropriate spatial constraints.
  • ST_Collect - Availability: 1.4.0 - ST_Collect(geomarray) was introduced. ST_Collect was enhanced to handle more geometries faster. Creates a GeometryCollection or Multi* geometry from a set of geometries.
  • ST_ContainsProperly - Availability: 1.4.0 Tests if every point of B lies in the interior of A
  • ST_GeoHash - Availability: 1.4.0 Return a GeoHash representation of the geometry.
  • ST_IsValidReason - Availability: 1.4 Returns text stating if a geometry is valid, or a reason for invalidity.
  • ST_LineCrossingDirection - Availability: 1.4 Returns a number indicating the crossing behavior of two LineStrings
  • ST_LocateBetweenElevations - Availability: 1.4.0 Returns the portions of a geometry that lie in an elevation (Z) range.
  • ST_MakeLine - Availability: 1.4.0 - ST_MakeLine(geomarray) was introduced. ST_MakeLine aggregate functions was enhanced to handle more points faster. Creates a LineString from Point, MultiPoint, or LineString geometries.
  • ST_MinimumBoundingCircle - Availability: 1.4.0 Returns the smallest circle polygon that contains a geometry.
  • ST_Union - Availability: 1.4.0 - ST_Union was enhanced. ST_Union(geomarray) was introduced and also faster aggregate collection in PostgreSQL. Computes a geometry representing the point-set union of the input geometries.

12.12.14. PostGIS Functions new or enhanced in 1.3

The functions given below are PostGIS functions that were added or enhanced.

Functions new in PostGIS 1.3

  • ST_AsGML - Availability: 1.3.2 Return the geometry as a GML version 2 or 3 element.
  • ST_AsGeoJSON - Availability: 1.3.4 Return a geometry as a GeoJSON element.
  • ST_CurveToLine - Availability: 1.3.0 Converts a geometry containing curves to a linear geometry.
  • ST_LineToCurve - Availability: 1.3.0 Converts a linear geometry to a curved geometry.
  • ST_SimplifyPreserveTopology - Availability: 1.3.3 Returns a simplified and valid version of a geometry, using the Douglas-Peucker algorithm.