PostGIS

Chapter 15. PostGIS Special Functions Index

Prev

Next

15.1. PostGIS Aggregate Functions

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

  • ST_3DExtent - 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 the input geometries into connected sets.

  • ST_ClusterWithin - Aggregate function that clusters the input geometries by separation distance.

  • ST_Collect - Creates a GeometryCollection or Multi* geometry from a set of geometries.

  • 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).

15.2. PostGIS Window Functions

The functions given below are spatial window functions provided with PostGIS that can be used just like any other sql window function such as row_numer(), lead(), lag(). All these require an SQL OVER() clause.

  • ST_ClusterDBSCAN - Window function that returns a cluster id for each input geometry using the DBSCAN algorithm.

  • ST_ClusterKMeans - Window function that returns a cluster id for each input geometry using the K-means algorithm.

15.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. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 8.1, 10.5

  • ST_3DDWithin - Tests if two 3D geometries are within a given 3D distance This method implements the SQL/MM specification. SQL-MM ?

  • ST_3DDifference - Perform 3D difference This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1

  • ST_3DDistance - Returns the 3D cartesian minimum distance (based on spatial ref) between two geometries in projected units. This method implements the SQL/MM specification. SQL-MM ISO/IEC 13249-3

  • ST_3DIntersection - Perform 3D intersection This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1

  • ST_3DIntersects - Tests if two geometries spatially intersect in 3D - only for points, linestrings, polygons, polyhedral surface (area). This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1

  • ST_3DLength - Returns the 3D length of a linear geometry. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 7.1, 10.3

  • ST_3DPerimeter - Returns the 3D perimeter of a polygonal geometry. This method implements the SQL/MM specification. SQL-MM ISO/IEC 13249-3: 8.1, 10.5

  • ST_3DUnion - Perform 3D union. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1

  • ST_AddEdgeModFace - Add a new edge and, if in doing so it splits a face, modify the original face and add a new face. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.13

  • ST_AddEdgeNewFaces - Add a new edge and, if in doing so it splits a face, delete the original face and replace it with two new faces. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.12

  • ST_AddIsoEdge - Adds an isolated edge defined by geometry alinestring to a topology connecting two existing isolated nodes anode and anothernode and returns the edge id of the new edge. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.4

  • ST_AddIsoNode - Adds an isolated node to a face in a topology and returns the nodeid of the new node. If face is null, the node is still created. This method implements the SQL/MM specification. SQL-MM: Topo-Net Routines: X+1.3.1

  • ST_Area - Returns the area of a polygonal geometry. This method implements the SQL/MM specification. SQL-MM 3: 8.1.2, 9.5.3

  • ST_AsBinary - Return the OGC/ISO Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data. This method implements the SQL/MM specification. SQL-MM 3: 5.1.37

  • ST_AsGML - Return the geometry as a GML version 2 or 3 element. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 17.2

  • ST_AsText - Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata. This method implements the SQL/MM specification. SQL-MM 3: 5.1.25

  • ST_Boundary - Returns the boundary of a geometry. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1.17

  • ST_Buffer - Computes a geometry covering all points within a given distance from a geometry. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1.30

  • ST_Centroid - Returns the geometric center of a geometry. This method implements the SQL/MM specification. SQL-MM 3: 8.1.4, 9.5.5

  • ST_ChangeEdgeGeom - Changes the shape of an edge without affecting the topology structure. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details X.3.6

  • ST_Contains - Tests if no points of B lie in the exterior of A, and A and B have at least one interior point in common. This method implements the SQL/MM specification. SQL-MM 3: 5.1.31

  • ST_ConvexHull - Computes the convex hull of a geometry. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1.16

  • ST_CoordDim - Return the coordinate dimension of a geometry. This method implements the SQL/MM specification. SQL-MM 3: 5.1.3

  • ST_CreateTopoGeo - Adds a collection of geometries to a given empty topology and returns a message detailing success. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details — X.3.18

  • ST_Crosses - Tests if two geometries have some, but not all, interior points in common. This method implements the SQL/MM specification. SQL-MM 3: 5.1.29

  • ST_CurveToLine - Converts a geometry containing curves to a linear geometry. This method implements the SQL/MM specification. SQL-MM 3: 7.1.7

  • ST_Difference - Computes a geometry representing the part of geometry A that does not intersect geometry B. This method implements the SQL/MM specification. SQL-MM 3: 5.1.20

  • ST_Dimension - Returns the topological dimension of a geometry. This method implements the SQL/MM specification. SQL-MM 3: 5.1.2

  • ST_Disjoint - Tests if two geometries are disjoint (they have no point in common). This method implements the SQL/MM specification. SQL-MM 3: 5.1.26

  • ST_Distance - Returns the distance between two geometry or geography values. This method implements the SQL/MM specification. SQL-MM 3: 5.1.23

  • ST_EndPoint - Returns the last point of a LineString or CircularLineString. This method implements the SQL/MM specification. SQL-MM 3: 7.1.4

  • ST_Envelope - Returns a geometry representing the bounding box of a geometry. 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. This method implements the SQL/MM specification. SQL-MM 3: 5.1.24

  • ST_ExteriorRing - Returns a LineString representing the exterior ring of a Polygon. This method implements the SQL/MM specification. SQL-MM 3: 8.2.3, 8.3.3

  • ST_GMLToSQL - Return a specified ST_Geometry value from GML representation. This is an alias name for ST_GeomFromGML This method implements the SQL/MM specification. SQL-MM 3: 5.1.50 (except for curves support).

  • ST_GeomCollFromText - Makes a collection Geometry from collection WKT with the given SRID. If SRID is not given, it defaults to 0. This method implements the SQL/MM specification.

  • ST_GeomFromText - Return a specified ST_Geometry value from Well-Known Text representation (WKT). This method implements the SQL/MM specification. SQL-MM 3: 5.1.40

  • ST_GeomFromWKB - Creates a geometry instance from a Well-Known Binary geometry representation (WKB) and optional SRID. This method implements the SQL/MM specification. SQL-MM 3: 5.1.41

  • ST_GeometryFromText - Return a specified ST_Geometry value from Well-Known Text representation (WKT). This is an alias name for ST_GeomFromText This method implements the SQL/MM specification. SQL-MM 3: 5.1.40

  • ST_GeometryN - Return an element of a geometry collection. This method implements the SQL/MM specification. SQL-MM 3: 9.1.5

  • ST_GeometryType - Returns the SQL-MM type of a geometry as text. This method implements the SQL/MM specification. SQL-MM 3: 5.1.4

  • ST_GetFaceEdges - Returns a set of ordered edges that bound aface. This method implements the SQL/MM specification. SQL-MM 3 Topo-Geo and Topo-Net 3: Routine Details: X.3.5

  • ST_GetFaceGeometry - Returns the polygon in the given topology with the specified face id. This method implements the SQL/MM specification. SQL-MM 3 Topo-Geo and Topo-Net 3: Routine Details: X.3.16

  • ST_InitTopoGeo - Creates a new topology schema and registers this new schema in the topology.topology table and details summary of process. This method implements the SQL/MM specification. SQL-MM 3 Topo-Geo and Topo-Net 3: Routine Details: X.3.17

  • ST_InteriorRingN - Returns the Nth interior ring (hole) of a Polygon. This method implements the SQL/MM specification. SQL-MM 3: 8.2.6, 8.3.5

  • ST_Intersection - Computes a geometry representing the shared portion of geometries A and B. This method implements the SQL/MM specification. SQL-MM 3: 5.1.18

  • ST_Intersects - Tests if two geometries intersect (they have at least one point in common). This method implements the SQL/MM specification. SQL-MM 3: 5.1.27

  • ST_IsClosed - Tests if a LineStrings’s start and end points are coincident. For a PolyhedralSurface tests if it is closed (volumetric). This method implements the SQL/MM specification. SQL-MM 3: 7.1.5, 9.3.3

  • ST_IsEmpty - Tests if a geometry is empty. This method implements the SQL/MM specification. SQL-MM 3: 5.1.7

  • ST_IsRing - Tests if a LineString is closed and simple. This method implements the SQL/MM specification. SQL-MM 3: 7.1.6

  • ST_IsSimple - Tests if a geometry has no points of self-intersection or self-tangency. This method implements the SQL/MM specification. SQL-MM 3: 5.1.8

  • ST_IsValid - Tests if a geometry is well-formed in 2D. This method implements the SQL/MM specification. SQL-MM 3: 5.1.9

  • ST_Length - Returns the 2D length of a linear geometry. This method implements the SQL/MM specification. SQL-MM 3: 7.1.2, 9.3.4

  • ST_LineFromText - Makes a Geometry from WKT representation with the given SRID. If SRID is not given, it defaults to

    1. This method implements the SQL/MM specification. SQL-MM 3: 7.2.8

  • ST_LineFromWKB - Makes a LINESTRING from WKB with the given SRID This method implements the SQL/MM specification. SQL-MM 3: 7.2.9

  • ST_LinestringFromWKB - Makes a geometry from WKB with the given SRID. This method implements the SQL/MM specification. SQL-MM 3: 7.2.9

  • ST_LocateAlong - Returns the point(s) on a geometry that match a measure value. 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. This method implements the SQL/MM specification. SQL-MM IEC 13249-3: 5.1

  • ST_M - Returns the M coordinate of a Point. This method implements the SQL/MM specification.

  • ST_MLineFromText - Return a specified ST_MultiLineString value from WKT representation. This method implements the SQL/MM specification.SQL-MM 3: 9.4.4

  • ST_MPointFromText - Makes a Geometry from WKT with the given SRID. If SRID is not given, it defaults to 0. 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. 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. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.9

  • ST_ModEdgeSplit - Split an edge by creating a new node along an existing edge, modifying the original edge and adding a new edge. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.9

  • ST_MoveIsoNode - Moves an isolated node in a topology from one point to another. If new apoint geometry exists as a node an error is thrown. Returns description of move. This method implements the SQL/MM specification. SQL-MM: Topo-Net Routines: X.3.2

  • ST_NewEdgeHeal - 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. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.9

  • ST_NewEdgesSplit - Split an edge by creating a new node along an existing edge, deleting the original edge and replacing it with two new edges. Returns the id of the new node created that joins the new edges. This method implements the SQL/MM specification. SQL-MM: Topo-Net Routines: X.3.8

  • ST_NumGeometries - Returns the number of elements in a geometry collection. This method implements the SQL/MM specification. SQL-MM 3: 9.1.4

  • ST_NumInteriorRings - Returns the number of interior rings (holes) of a Polygon. This method implements the SQL/MM specification. SQL-MM 3: 8.2.5

  • ST_NumPatches - Return the number of faces on a Polyhedral Surface. Will return null for non-polyhedral geometries. This method implements the SQL/MM specification. SQL-MM ISO/IEC 13249-3: 8.5

  • ST_NumPoints - Returns the number of points in a LineString or CircularString. 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. This method implements the SQL/MM specification. SQL-MM 3: 5.1.43

  • ST_Overlaps - Tests if two geometries intersect and have the same dimension, but are not completely contained by each other. This method implements the SQL/MM specification. SQL-MM 3: 5.1.32

  • ST_PatchN - Returns the Nth geometry (face) of a PolyhedralSurface. This method implements the SQL/MM specification. SQL-MM ISO/IEC 13249-3: 8.5

  • ST_Perimeter - Returns the length of the boundary of a polygonal geometry or geography. This method implements the SQL/MM specification. SQL-MM 3: 8.1.3, 9.5.4

  • ST_Point - Creates a Point with X, Y and SRID values. This method implements the SQL/MM specification. SQL-MM 3: 6.1.2

  • ST_PointFromText - Makes a point Geometry from WKT with the given SRID. If SRID is not given, it defaults to unknown. This method implements the SQL/MM specification. SQL-MM 3: 6.1.8

  • ST_PointFromWKB - Makes a geometry from WKB with the given SRID This method implements the SQL/MM specification. SQL-MM 3: 6.1.9

  • ST_PointN - Returns the Nth point in the first LineString or circular LineString in a geometry. This method implements the SQL/MM specification. SQL-MM 3: 7.2.5, 7.3.5

  • ST_PointOnSurface - Computes a point guaranteed to lie in a polygon, or on a geometry. 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.

  • ST_Polygon - Creates a Polygon from a LineString with a specified SRID. This method implements the SQL/MM specification. SQL-MM 3: 8.3.2

  • ST_PolygonFromText - Makes a Geometry from WKT with the given SRID. If SRID is not given, it defaults to 0. 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 This method implements the SQL/MM specification. SQL-MM 3: 5.1.25

  • ST_RemEdgeModFace - Removes an edge and, if the removed edge separated two faces,delete one of the them and modify the other to take the space of both. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.15

  • ST_RemEdgeNewFace - Removes an edge and, if the removed edge separated two faces,delete the original faces and replace them with a new face. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X.3.14

  • ST_RemoveIsoEdge - Removes an isolated edge and returns description of action. If the edge is not isolated, then an exception is thrown. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X+1.3.3

  • ST_RemoveIsoNode - Removes an isolated node and returns description of action. If the node is not isolated (is start or end of an edge), then an exception is thrown. This method implements the SQL/MM specification. SQL-MM: Topo-Geo and Topo-Net 3: Routine Details: X+1.3.3

  • ST_SRID - Returns the spatial reference identifier for a geometry. This method implements the SQL/MM specification. SQL-MM 3: 5.1.5

  • ST_StartPoint - Returns the first point of a LineString. This method implements the SQL/MM specification. SQL-MM 3: 7.1.3

  • ST_SymDifference - Computes a geometry representing the portions of geometries A and B that do not intersect. This method implements the SQL/MM specification. SQL-MM 3: 5.1.21

  • ST_Touches - Tests if two geometries have at least one point in common, but their interiors do not intersect. This method implements the SQL/MM specification. SQL-MM 3: 5.1.28

  • ST_Transform - Return a new geometry with coordinates transformed to a different spatial reference system. This method implements the SQL/MM specification. SQL-MM 3: 5.1.6

  • ST_Union - Computes a geometry representing the point-set union of the input geometries. This method implements the SQL/MM specification. SQL-MM 3: 5.1.19 the z-index (elevation) when polygons are involved.

  • ST_Volume - Computes the volume of a 3D solid. If applied to surface (even closed) geometries will return 0. 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 This method implements the SQL/MM specification. SQL-MM 3: 5.1.36

  • ST_WKTToSQL - Return a specified ST_Geometry value from Well-Known Text representation (WKT). This is an alias name for ST_GeomFromText This method implements the SQL/MM specification. SQL-MM 3: 5.1.34

  • ST_Within - Tests if no points of A lie in the exterior of B, and A and B have at least one interior point in common. This method implements the SQL/MM specification. SQL-MM 3: 5.1.30

  • ST_X - Returns the X coordinate of a Point. This method implements the SQL/MM specification. SQL-MM 3: 6.1.3

  • ST_Y - Returns the Y coordinate of a Point. This method implements the SQL/MM specification. SQL-MM 3: 6.1.4

  • ST_Z - Returns the Z coordinate of a Point. This method implements the SQL/MM specification.

  • TG_ST_SRID - Returns the spatial reference identifier for a topogeometry. This method implements the SQL/MM specification. SQL-MM 3: 14.1.5

15.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_CoveredBy - Tests if no point in A is outside B

  • ST_Covers - Tests if no point in B is outside 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_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 - Return a modified geometry/geography having no segment longer than the given distance.

  • 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.

15.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 or Lanczos 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 Z 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.

15.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.

15.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).

15.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_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_Shift_Longitude - 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

15.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_AsText - Return the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.

  • 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.

15.10. PostGIS Polyhedral Surface Support Functions

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

  • Box2D - Returns a BOX2D representing the 2D extent of a geometry.

  • Box3D - Returns a BOX3D representing the 3D extent of a geometry.

  • 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_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_3DShortestLine - Returns the 3D shortest line between two geometries

  • ST_3DUnion - Perform 3D union.

  • ST_Affine - Apply a 3D affine transformation to a geometry.

  • ST_ApproximateMedialAxis - Compute the approximate medial axis of an areal geometry.

  • 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_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_AsX3D - Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML

  • ST_CoordDim - Return the coordinate dimension of a geometry.

  • ST_Dimension - Returns the topological dimension of 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_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_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_ForceLHR - Force LHR orientation

  • 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_Force3D - Force the geometries into XYZ mode. This is an alias for ST_Force3DZ.

  • ST_Force3DZ - Force the geometries into XYZ mode.

  • ST_ForceCollection - 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_GeometryN - Return an element of a geometry collection.

  • ST_GeometryType - Returns the SQL-MM type of a geometry as text.

  • = - 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.

  • ~= - Returns TRUE if A’s bounding box is the same as B’s.

  • ST_IsClosed - Tests if a LineStrings’s start and end points are coincident. For a PolyhedralSurface tests if it is closed (volumetric).

  • ST_IsPlanar - Check if a surface is or not planar

  • ST_IsSolid - Test if the geometry is a solid. No validity check is performed.

  • 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_MemSize - Returns the amount of memory space a geometry takes.

  • ST_NPoints - Returns the number of points (vertices) in a geometry.

  • 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_PatchN - Returns the Nth geometry (face) of a PolyhedralSurface.

  • 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_ShiftLongitude - Shifts the longitude coordinates of a geometry between -180..180 and 0..360.

  • ST_StraightSkeleton - Compute a straight skeleton from a geometry

  • 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_Tesselate - Perform surface Tesselation of a polygon or polyhedralsurface and returns as a TIN or collection of TINS

  • ST_Transform - Return a new geometry with coordinates transformed to a different spatial reference system.

  • ST_Volume - Computes the volume of a 3D solid. If applied to surface (even closed) geometries will return 0.

  • ~(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.

  • 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

15.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 matrix_checkmark means the function works with the type or subtype natively.

  • A matrix_transform 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 matrix_autocast means the function works with the type because of a auto-cast to another such as to box3d rather than direct type support.

  • A matrix_sfcgal_required means the function only available if PostGIS compiled with SFCGAL support.

  • A matrix_sfcgal_enhanced 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)

Function geom geog 2.5D Curves SQL MM PS T

Box2D

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

Box3D

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

GeometryType

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

PostGIS_AddBBox

matrix_checkmark

matrix_checkmark

PostGIS_DropBBox

matrix_checkmark

matrix_checkmark

PostGIS_Extensions_Upgrade

PostGIS_Full_Version

PostGIS_GEOS_Version

PostGIS_HasBBox

matrix_checkmark

matrix_checkmark

PostGIS_LibXML_Version

PostGIS_Lib_Build_Date

PostGIS_Lib_Version

PostGIS_Liblwgeom_Version

PostGIS_PROJ_Version

PostGIS_Scripts_Build_Date

PostGIS_Scripts_Installed

PostGIS_Scripts_Released

PostGIS_Version

PostGIS_Wagyu_Version

ST_3DArea

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_3DClosestPoint

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_3DConvexHull

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_3DDifference

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_3DDistance

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_3DExtent

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_3DIntersection

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_3DLength

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_3DLineInterpolatePoint

matrix_checkmark

matrix_checkmark

ST_3DLongestLine

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_3DMakeBox

matrix_checkmark

ST_3DMaxDistance

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_3DPerimeter

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_3DShortestLine

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_3DUnion

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_AddMeasure

matrix_checkmark

matrix_checkmark

ST_AddPoint

matrix_checkmark

matrix_checkmark

ST_Affine

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_AlphaShape

matrix_sfcgal_required

ST_Angle

matrix_checkmark

ST_ApproximateMedialAxis

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_Area

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Azimuth

matrix_checkmark

matrix_checkmark

ST_Boundary

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_BoundingDiagonal

matrix_checkmark

matrix_checkmark

ST_Buffer

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_BuildArea

matrix_checkmark

ST_CPAWithin

matrix_checkmark

matrix_checkmark

ST_Centroid

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_ChaikinSmoothing

matrix_checkmark

ST_ClipByBox2D

matrix_checkmark

ST_ClosestPoint

matrix_checkmark

ST_ClosestPointOfApproach

matrix_checkmark

matrix_checkmark

ST_ClusterDBSCAN

matrix_checkmark

ST_ClusterIntersecting

matrix_checkmark

ST_ClusterKMeans

matrix_checkmark

ST_ClusterWithin

matrix_checkmark

ST_Collect

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_CollectionExtract

matrix_checkmark

ST_CollectionHomogenize

matrix_checkmark

ST_ConcaveHull

matrix_checkmark

matrix_checkmark

ST_ConstrainedDelaunayTriangles

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_ConvexHull

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_CoordDim

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_CurveToLine

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_DelaunayTriangles

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Difference

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Dimension

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Distance

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_DistanceCPA

matrix_checkmark

matrix_checkmark

ST_DistanceSphere

matrix_checkmark

ST_DistanceSpheroid

matrix_checkmark

ST_Dump

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_DumpPoints

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_DumpRings

matrix_checkmark

matrix_checkmark

ST_DumpSegments

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_EndPoint

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Envelope

matrix_checkmark

matrix_checkmark

ST_EstimatedExtent

matrix_autocast

matrix_checkmark

ST_Expand

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Extent

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_ExteriorRing

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Extrude

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_FilterByM

matrix_checkmark

ST_FlipCoordinates

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Force2D

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_ForceCurve

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_ForceLHR

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_ForcePolygonCCW

matrix_checkmark

matrix_checkmark

ST_ForcePolygonCW

matrix_checkmark

matrix_checkmark

ST_ForceRHR

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_ForceSFS

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Force3D

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Force3DM

matrix_checkmark

matrix_checkmark

ST_Force3DZ

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Force4D

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_ForceCollection

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_FrechetDistance

matrix_checkmark

ST_GeneratePoints

matrix_checkmark

ST_GeometricMedian

matrix_checkmark

matrix_checkmark

ST_GeometryN

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_GeometryType

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_HasArc

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_HausdorffDistance

matrix_checkmark

ST_Hexagon

matrix_checkmark

ST_HexagonGrid

matrix_checkmark

ST_InteriorRingN

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_InterpolatePoint

matrix_checkmark

matrix_checkmark

ST_Intersection

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_IsClosed

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_IsCollection

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_IsEmpty

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_IsPlanar

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_IsPolygonCCW

matrix_checkmark

matrix_checkmark

ST_IsPolygonCW

matrix_checkmark

matrix_checkmark

ST_IsRing

matrix_checkmark

matrix_checkmark

ST_IsSimple

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_IsSolid

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_IsValid

matrix_checkmark

matrix_checkmark

ST_IsValidDetail

matrix_checkmark

ST_IsValidReason

matrix_checkmark

ST_IsValidTrajectory

matrix_checkmark

matrix_checkmark

ST_Length

matrix_checkmark

matrix_checkmark

matrix_sfcgal_enhanced

ST_Length2D

matrix_checkmark

ST_LengthSpheroid

matrix_checkmark

matrix_checkmark

ST_Letters

matrix_checkmark

ST_LineFromMultiPoint

matrix_checkmark

matrix_checkmark

ST_LineInterpolatePoint

matrix_checkmark

matrix_checkmark

ST_LineInterpolatePoints

matrix_checkmark

matrix_checkmark

ST_LineLocatePoint

matrix_checkmark

ST_LineMerge

matrix_checkmark

ST_LineSubstring

matrix_checkmark

matrix_checkmark

ST_LineToCurve

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_LocateAlong

matrix_checkmark

matrix_checkmark

ST_LocateBetween

matrix_checkmark

matrix_checkmark

ST_LocateBetweenElevations

matrix_checkmark

matrix_checkmark

ST_LongestLine

matrix_checkmark

ST_M

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_MakeBox2D

matrix_checkmark

ST_MakeEnvelope

matrix_checkmark

ST_MakeLine

matrix_checkmark

matrix_checkmark

ST_MakePoint

matrix_checkmark

matrix_checkmark

ST_MakePointM

matrix_checkmark

ST_MakePolygon

matrix_checkmark

matrix_checkmark

ST_MakeSolid

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_MakeValid

matrix_checkmark

matrix_checkmark

ST_MaxDistance

matrix_checkmark

ST_MaximumInscribedCircle

matrix_checkmark

ST_MemSize

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_MemUnion

matrix_checkmark

matrix_checkmark

ST_MinimumBoundingCircle

matrix_checkmark

ST_MinimumBoundingRadius

matrix_checkmark

ST_MinimumClearance

matrix_checkmark

ST_MinimumClearanceLine

matrix_checkmark

ST_MinkowskiSum

matrix_sfcgal_required

ST_Multi

matrix_checkmark

ST_NDims

matrix_checkmark

matrix_checkmark

ST_NPoints

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_NRings

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Node

matrix_checkmark

matrix_checkmark

ST_Normalize

matrix_checkmark

ST_NumGeometries

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_NumInteriorRing

matrix_checkmark

ST_NumInteriorRings

matrix_checkmark

matrix_checkmark

ST_NumPatches

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_NumPoints

matrix_checkmark

matrix_checkmark

ST_OffsetCurve

matrix_checkmark

ST_OptimalAlphaShape

matrix_sfcgal_required

ST_Orientation

matrix_sfcgal_required

matrix_sfcgal_required

ST_OrientedEnvelope

matrix_checkmark

ST_PatchN

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Perimeter

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Perimeter2D

matrix_checkmark

ST_Point

matrix_checkmark

matrix_checkmark

ST_PointM

matrix_checkmark

ST_PointN

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_PointOnSurface

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_PointZ

matrix_checkmark

ST_PointZM

matrix_checkmark

ST_Points

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Polygon

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Polygonize

matrix_checkmark

ST_Project

matrix_checkmark

ST_QuantizeCoordinates

matrix_checkmark

ST_ReducePrecision

matrix_checkmark

ST_RemovePoint

matrix_checkmark

matrix_checkmark

ST_RemoveRepeatedPoints

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Reverse

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Rotate

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_RotateX

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_RotateY

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_RotateZ

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_SRID

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Scale

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Scroll

matrix_checkmark

matrix_checkmark

ST_Segmentize

matrix_checkmark

matrix_checkmark

ST_SetEffectiveArea

matrix_checkmark

ST_SetPoint

matrix_checkmark

matrix_checkmark

ST_SetSRID

matrix_checkmark

matrix_checkmark

ST_SharedPaths

matrix_checkmark

ST_ShiftLongitude

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_ShortestLine

matrix_checkmark

ST_Simplify

matrix_checkmark

ST_SimplifyPolygonHull

matrix_checkmark

ST_SimplifyPreserveTopology

matrix_checkmark

ST_SimplifyVW

matrix_checkmark

ST_Snap

matrix_checkmark

ST_SnapToGrid

matrix_checkmark

matrix_checkmark

ST_Split

matrix_checkmark

ST_Square

matrix_checkmark

ST_SquareGrid

matrix_checkmark

ST_StartPoint

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_StraightSkeleton

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_Subdivide

matrix_checkmark

ST_Summary

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_SwapOrdinates

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_SymDifference

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Tesselate

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_TileEnvelope

matrix_checkmark

ST_TransScale

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Transform

matrix_checkmark

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Translate

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_TriangulatePolygon

matrix_checkmark

matrix_checkmark

ST_UnaryUnion

matrix_checkmark

matrix_checkmark

ST_Union

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_Volume

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

ST_VoronoiLines

matrix_checkmark

ST_VoronoiPolygons

matrix_checkmark

ST_WrapX

matrix_checkmark

matrix_checkmark

ST_X

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_XMax

matrix_autocast

matrix_checkmark

matrix_checkmark

ST_XMin

matrix_autocast

matrix_checkmark

matrix_checkmark

ST_Y

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_YMax

matrix_autocast

matrix_checkmark

matrix_checkmark

ST_YMin

matrix_autocast

matrix_checkmark

matrix_checkmark

ST_Z

matrix_checkmark

matrix_checkmark

matrix_checkmark

ST_ZMax

matrix_autocast

matrix_checkmark

matrix_checkmark

ST_ZMin

matrix_autocast

matrix_checkmark

matrix_checkmark

ST_Zmflag

matrix_checkmark

matrix_checkmark

matrix_checkmark

postgis.backend

postgis.enable_outdb_rasters

postgis.gdal_datapath

postgis.gdal_enabled_drivers

postgis.gdal_vsi_options

postgis_sfcgal_full_version

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

postgis_sfcgal_version

matrix_sfcgal_required

matrix_sfcgal_required

matrix_sfcgal_required

15.12.1. 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

  • RemoveUnusedPrimitives - Availability: 3.3.0 Removes topology primitives which not needed to define existing TopoGeometry objects.

  • 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 a possible concave geometry using the CGAL Alpha Shapes algorithm.

  • 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 a possible concave geometry using the CGAL Alpha Shapes algorithm after have computed the "optimal" alpha value.

  • ST_SimplifyPolygonHull - Availability: 3.3.0 - requires GEOS >= 3.11.0 Computes a simplifed topology-preserving outer or inner hull of a polygonal geometry.

  • ST_TriangulatePolygon - Availability: 3.3.0 - requires GEOS >= 3.11.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 encloses all input geometry vertices

  • ST_LineMerge - Enhanced: 3.3.0 accept a directed parameter - requires GEOS >= 3.11.0 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 latest available version.

15.12.2. 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

  • FindLayer - Availability: 3.2.0 Returns a topology.layer record by different means.

  • FindTopology - Availability: 3.2.0 Returns a topology record by different means.

  • GetFaceContainingPoint - Availability: 3.2.0 Finds the face containing a point.

  • ST_AsFlatGeobuf - Availability: 3.2.0 Return a FlatGeobuf representation of a set of rows.

  • ST_Contour - Availability: 3.2.0 Generates a set of vector contours from the provided raster band, using the GDAL contouring algorithm.

  • 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_InterpolateRaster - Availability: 3.2.0 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_SRID - Availability: 3.2.0 Returns the spatial reference identifier for a topogeometry.

  • ST_Scroll - Availability: 3.2.0 Change start point of a closed LineString.

  • ST_SetM - Availability: 3.2.0 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_SetZ - Availability: 3.2.0 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.

  • TopoGeom_addTopoGeom - Availability: 3.2 Adds element of a TopoGeometry to the definition of another TopoGeometry.

  • ValidateTopologyRelation - Availability: 3.2.0 Returns info about invalid topology relation records

  • 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

  • GetFaceByPoint - Enhanced: 3.2.0 more efficient implementation and clearer contract, stops working with invalid topologies. Finds face intersecting a given point.

  • 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_MoveIsoNode - Enhanced: 3.2.0 ensures the nod cannot be moved in a different face 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.

  • ST_PixelAsCentroid - Enhanced: 3.2.0 Faster now implemented in C. Returns the centroid (point geometry) of the area represented by a pixel.

  • ST_PixelAsCentroids - Enhanced: 3.2.0 Faster now implemented in C. 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_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.

  • ST_Value - Enhanced: 3.2.0 resample optional argument was added. 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.

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.

  • ValidateTopology - Changed: 3.2.0 added optional bbox parameter, perform face labeling and edge linking checks. Returns a set of validatetopology_returntype objects detailing issues with topology.

15.12.3. 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 - requires GEOS >= 3.9.0. Computes the largest circle contained within a geometry.

  • ST_ReducePrecision - Availability: 3.1.0

    • requires GEOS >= 3.9.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 - requires GEOS >= 3.9.0 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 - requires GEOS >= 3.9.0 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, requires GEOS >= 3.9.0 to use this new feature. Computes a rectilinear subdivision of a geometry.

  • ST_SymDifference - Enhanced: 3.1.0 accept a gridSize parameter - requires GEOS >= 3.9.0 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 - requires GEOS >= 3.9.0 Computes the union of the components of a single geometry.

  • ST_Union - Enhanced: 3.1.0 accept a gridSize parameter - requires GEOS >= 3.9.0 Computes a geometry representing the point-set union of the input geometries.

Functions changed in PostGIS 3.1

  • ST_Count - Changed: 3.1.0 - The ST_Count(rastertable, rastercolumn, …​) variants removed. Use instead. 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_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.

  • ST_Histogram - Changed: 3.1.0 Removed ST_Histogram(table_name, column_name) variant. 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_Quantile - Changed: 3.1.0 Removed ST_Quantile(table_name, column_name) variant. 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_SummaryStats - Changed: 3.1.0 ST_SummaryStats(rastertable, rastercolumn, …​) variants are removed. Use instead. 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.

15.12.4. 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 no points of B lie in the exterior of A, and A and B have at least one interior point in common.

  • ST_ContainsProperly - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if B intersects the interior of A but not the boundary or exterior.

  • ST_CoveredBy - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if no point in A is outside B

  • ST_Covers - Enhanced: 3.0.0 enabled support for GEOMETRYCOLLECTION Tests if no point in B is outside 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 are disjoint (they have no point 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 intersect and have the same dimension, but are not completely contained by each 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 uses equal length segments Return a modified geometry/geography having no segment longer than the 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 no points of A lie in the exterior of B, and A and B have at least one interior 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 latest available 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.

15.12.5. 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 latest available version.

  • ST_Angle - Availability: 2.5.0 Returns the angle between two vectors defined by 3 or 4 points, or 2 lines.

  • ST_AsHexWKB - Availability: 2.5.0 Return the Well-Known Binary (WKB) in Hex representation of the raster.

  • ST_BandFileSize - Availability: 2.5.0 Returns the file size of a band stored in file system. If no bandnum specified, 1 is assumed.

  • ST_BandFileTimestamp - Availability: 2.5.0 Returns the file timestamp of a band stored in file system. If no bandnum specified, 1 is assumed.

  • 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_Grayscale - Availability: 2.5.0 Creates a new one-8BUI band raster from the source raster and specified bands representing Red, Green and Blue

  • 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.

  • ST_RastFromHexWKB - Availability: 2.5.0 Return a raster value from a Hex representation of Well-Known Binary (WKB) raster.

  • ST_RastFromWKB - Availability: 2.5.0 Return a raster value from a Well-Known Binary (WKB) raster.

  • ST_SetBandIndex - Availability: 2.5.0 Update the external band number of an out-db band

  • ST_SetBandPath - Availability: 2.5.0 Update the external path and band number of an out-db band

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_AsBinary/ST_AsWKB - Enhanced: 2.5.0 Addition of ST_AsWKB Return the Well-Known Binary (WKB) representation of the raster.

  • 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_BandMetaData - Enhanced: 2.5.0 to include outdbbandnum, filesize and filetimestamp for outdb rasters. Returns basic meta data for a specific raster band. band num 1 is assumed if none-specified.

  • 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.

Functions changed in PostGIS 2.5

  • ST_GDALDrivers - Changed: 2.5.0 - add can_read and can_write columns. Returns a list of raster formats supported by PostGIS through GDAL. Only those formats with can_write=True can be used by ST_AsGDALRaster

15.12.6. 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.

  • ST_MakeEmptyCoverage - Availability: 2.4.0 Cover georeferenced area with a grid of empty raster tiles.

Functions enhanced in PostGIS 2.4

All aggregates now marked as parallel safe which should allow them to be used in plans that can employ parallelism.

PostGIS 2.4.1 postgis_tiger_geocoder set to load Tiger 2017 data. Can optionally load zip code 5-digit tabulation (zcta) as part of the Loader_Generate_Nation_Script.

  • Loader_Generate_Nation_Script

    • Enhanced: 2.4.1 zip code 5 tabulation area (zcta5) load step was fixed and when enabled, zcta5 data is loaded as a single table called zcta5_all as part of the nation script load. Generates a shell script for the specified platform that loads in the county and state lookup tables.

  • Normalize_Address - Enhanced: 2.4.0 norm_addy object includes additional fields zip4 and address_alphanumeric. Given a textual street address, returns a composite norm_addy type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This function will work with just the lookup data packaged with the tiger_geocoder (no need for tiger census data).

  • Pagc_Normalize_Address - Enhanced: 2.4.0 norm_addy object includes additional fields zip4 and address_alphanumeric. Given a textual street address, returns a composite norm_addy type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This function will work with just the lookup data packaged with the tiger_geocoder (no need for tiger census data). Requires address_standardizer extension.

  • Reverse_Geocode - Enhanced: 2.4.1 if optional zcta5 dataset is loaded, the reverse_geocode function can resolve to state and zip even if the specific state data is not loaded. Refer to for details on loading zcta5 data. Takes a geometry point in a known spatial ref sys and returns a record containing an array of theoretically possible addresses and an array of cross streets. If include_strnum_range = true, includes the street range in the cross streets.

  • ST_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 no point in B is outside 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

All PostGIS aggregates now marked as parallel safe. This will force a drop and recreate of aggregates during upgrade which may fail if any user views or sql functions rely on PostGIS aggregates.

  • = - 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.

15.12.7. PostGIS Functions new or enhanced in 2.3

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

Note

PostGIS 2.3.0: PostgreSQL 9.6+ support for parallel queries.

Note

PostGIS 2.3.0: PostGIS extension, all functions schema qualified to reduce issues in database restore.

Note

PostGIS 2.3.0: PostgreSQL 9.4+ support for BRIN indexes. Refer to Section 4.9.2, “BRIN Indexes”.

Note

PostGIS 2.3.0: Tiger Geocoder upgraded to work with TIGER 2016 data.

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).

  • Populate_Topology_Layer - Availability: 2.3.0 Adds missing entries to topology.layer table by reading metadata from topo tables.

  • 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_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.

  • TopoGeom_addElement - Availability: 2.3 Adds an element to the definition of a TopoGeometry.

  • TopoGeom_remElement - Availability: 2.3 Removes an element from the definition of a TopoGeometry.

  • ~(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).

The functions given below are PostGIS functions that are 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.

  • 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.

  • ST_Expand - Enhanced: 2.3.0 support was added to expand a box by different amounts in different dimensions.

  • 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.

  • ST_Segmentize - Enhanced: 2.3.0 Segmentize geography now uses equal length segments

  • ST_Transform - Enhanced: 2.3.0 support for direct PROJ.4 text was introduced.

  • 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.

15.12.8. PostGIS Functions new or enhanced in 2.2

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

Note

postgis_sfcgal now can be installed as an extension using CREATE EXTENSION postgis_sfcgal;

Note

PostGIS 2.2.0: Tiger Geocoder upgraded to work with TIGER 2015 data.

Note

address_standardizer, address_standardizer_data_us extensions for standardizing address data refer to Section 14.1, “Address Standardizer” for details.

Note

Many functions in topology rewritten as C functions for increased performance.

Functions new in PostGIS 2.2

  • PostGIS - 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 the input geometries into connected sets.

  • ST_ClusterWithin - Availability: 2.2.0 Aggregate function that clusters the input geometries by separation distance.

  • ST_CountAgg - Availability: 2.2.0 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_CreateOverview - Availability: 2.2.0 Create an reduced resolution version of a given raster coverage.

  • 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_MapAlgebra (callback function version)

    • Availability: 2.2.0: Ability to add a mask Callback function version - Returns a one-band raster given one or more input rasters, band indexes and one user-specified callback function.

  • ST_MemSize - Availability: 2.2.0 Returns the amount of space (in bytes) the raster takes.

  • ST_RemoveRepeatedPoints - Availability: 2.2.0 Returns a version of a geometry with duplicate points removed.

  • ST_Retile - Availability: 2.2.0 Return a set of configured tiles from an arbitrarily tiled raster coverage.

  • 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_SummaryStatsAgg - Availability: 2.2.0 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_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.

  • parse_address - Availability: 2.2.0 Takes a 1 line address and breaks into parts

  • 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.

  • standardize_address - Availability: 2.2.0 Returns an stdaddr form of an input address utilizing lex, gaz, and rule tables.

  • |=| - 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.

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

  • AsTopoJSON - Enhanced: 2.2.1 added support for puntal inputs

  • 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.

  • ST_AsX3D - Enhanced: 2.2.0: Support for GeoCoordinates and axis (x/y, long/lat) flipping. Look at options for details.

  • 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.

  • 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.

  • ST_Scale - Enhanced: 2.2.0 support for scaling all dimension (factor parameter) was introduced.

  • ST_Split - Enhanced: 2.2.0 support for splitting a line by a multiline, a multipoint or (multi)polygon boundary was introduced.

  • ST_Summary - Enhanced: 2.2.0 Added support for TIN and Curves

  • <→ - 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.

15.12.9. PostGIS functions breaking changes in 2.2

The functions given below are PostGIS functions that have possibly breaking changes in PostGIS 2.2. If you use any of these, you may need to check your existing code.

  • Get_Geocode_Setting - Changed: 2.2.0 : default settings are now kept in a table called geocode_settings_default. Use customized settingsa are in geocode_settings and only contain those that have been set by user.

  • 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.

  • ST_3DDistance - Changed: 2.2.0 - In case of 2D and 3D, Z is no longer assumed to be 0 for missing Z.

  • 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.

  • ST_3DMaxDistance - Changed: 2.2.0 - In case of 2D and 3D, Z is no longer assumed to be 0 for missing Z.

  • 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.

  • ST_DistanceSphere - Changed: 2.2.0 In prior versions this used to be called ST_Distance_Sphere

  • ST_DistanceSpheroid - Changed: 2.2.0 In prior versions this was called ST_Distance_Spheroid

  • ST_Equals - Changed: 2.2.0 Returns true even for invalid geometries if they are binary equal

  • ST_LengthSpheroid - Changed: 2.2.0 In prior versions this was called ST_Length_Spheroid and had the alias ST_3DLength_Spheroid

  • ST_MemSize - Changed: 2.2.0 name changed to ST_MemSize to follow naming convention.

  • ST_PointInsideCircle - Changed: 2.2.0 In prior versions this was called ST_Point_Inside_Circle

  • ValidateTopology - Changed: 2.2.0 values for id1 and id2 were swapped for 'edge crosses node' to be consistent with error description.

  • <→ - 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.

15.12.10. PostGIS Functions new or enhanced in 2.1

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

Note

More Topology performance Improvements. Please refer to Chapter 10, Topology for more details.

Note

Bug fixes (particularly with handling of out-of-band rasters), many new functions (often shortening code you have to write to accomplish a common task) and massive speed improvements to raster functionality. Refer to Chapter 12, Raster Reference for more details.

Note

PostGIS 2.1.0: Tiger Geocoder upgraded to work with TIGER 2012 census data. geocode_settings added for debugging and tweaking rating preferences, loader made less greedy, now only downloads tables to be loaded. PostGIS 2.1.1: Tiger Geocoder upgraded to work with TIGER 2013 data. Please refer to Section 14.2, “Tiger Geocoder” for more details.

Functions new in PostGIS 2.1

  • AsTopoJSON - Availability: 2.1.0 Returns the TopoJSON representation of a topogeometry.

  • Drop_Nation_Tables_Generate_Script

    • Availability: 2.1.0 Generates a script that drops all tables in the specified schema that start with county_all, state_all or state code followed by county or state.

  • Get_Geocode_Setting - Availability: 2.1.0 Returns value of specific setting stored in tiger.geocode_settings table.

  • Loader_Generate_Nation_Script

    • Availability: 2.1.0 Generates a shell script for the specified platform that loads in the county and state lookup tables.

  • Pagc_Normalize_Address - Availability: 2.1.0 Given a textual street address, returns a composite norm_addy type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This function will work with just the lookup data packaged with the tiger_geocoder (no need for tiger census data). Requires address_standardizer extension.

  • 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_ColorMap - Availability: 2.1.0 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 - Availability: 2.1.0 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 - Availability: 2.1.0 Return true if rastB intersects the interior of rastA but not the boundary or exterior of rastA.

  • ST_CoveredBy - Availability: 2.1.0 Return true if no points of raster rastA lie outside raster rastB.

  • ST_Covers - Availability: 2.1.0 Return true if no points of raster rastB lie outside raster rastA.

  • ST_DFullyWithin - Availability: 2.1.0 Return true if rasters rastA and rastB are fully within the specified distance of each other.

  • ST_DWithin - Availability: 2.1.0 Return true if rasters rastA and rastB are within the specified distance of each other.

  • ST_DelaunayTriangles - Availability: 2.1.0 Returns the Delaunay triangulation of the vertices of a geometry.

  • ST_Disjoint - Availability: 2.1.0 Return true if raster rastA does not spatially intersect rastB.

  • ST_DumpValues - Availability: 2.1.0 Get the values of the specified band as a 2-dimension array.

  • ST_Extrude - Availability: 2.1.0 Extrude a surface to a related volume

  • ST_ForceLHR - Availability: 2.1.0 Force LHR orientation

  • ST_FromGDALRaster - Availability: 2.1.0 Returns a raster from a supported GDAL raster file.

  • ST_GeomFromGeoHash - Availability: 2.1.0 Return a geometry from a GeoHash string.

  • ST_InvDistWeight4ma - Availability: 2.1.0 Raster processing function that interpolates a pixel’s value from the pixel’s neighborhood.

  • ST_MapAlgebra (callback function version)

    • Availability: 2.1.0 Callback function version - Returns a one-band raster given one or more input rasters, band indexes and one user-specified callback function.

  • ST_MapAlgebra (expression version) - Availability: 2.1.0 Expression version - Returns a one-band raster given one or two input rasters, band indexes and one or more user-specified SQL expressions.

  • ST_MinConvexHull - Availability: 2.1.0 Return the convex hull geometry of the raster excluding NODATA pixels.

  • ST_MinDist4ma - Availability: 2.1.0 Raster processing function that returns the minimum distance (in number of pixels) between the pixel of interest and a neighboring pixel with value.

  • ST_MinkowskiSum - Availability: 2.1.0 Performs Minkowski sum

  • ST_NearestValue - Availability: 2.1.0 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 - Availability: 2.1.0 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 - Availability: 2.1.0 Returns text stating if rasters are aligned and if not aligned, a reason why.

  • ST_Orientation - Availability: 2.1.0 Determine surface orientation

  • ST_Overlaps - Availability: 2.1.0 Return true if raster rastA and rastB intersect but one does not completely contain the other.

  • ST_PixelAsCentroid - Availability: 2.1.0 Returns the centroid (point geometry) of the area represented by a pixel.

  • ST_PixelAsCentroids - Availability: 2.1.0 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 - Availability: 2.1.0 Returns a point geometry of the pixel’s upper-left corner.

  • ST_PixelAsPoints - Availability: 2.1.0 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_PixelOfValue - Availability: 2.1.0 Get the columnx, rowy coordinates of the pixel whose value equals the search value.

  • ST_PointFromGeoHash - Availability: 2.1.0 Return a point from a GeoHash string.

  • ST_RasterToWorldCoord - Availability: 2.1.0 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_Resize - Availability: 2.1.0 Requires GDAL 1.6.1+ Resize a raster to a new width/height

  • ST_Roughness - Availability: 2.1.0 Returns a raster with the calculated "roughness" of a DEM.

  • ST_SetValues - Availability: 2.1.0 Returns modified raster resulting from setting the values of a given band.

  • ST_Simplify - Availability: 2.1.0 Returns a "simplified" geometry version of the given TopoGeometry using the Douglas-Peucker algorithm.

  • ST_StraightSkeleton - Availability: 2.1.0 Compute a straight skeleton from a geometry

  • ST_Summary - Availability: 2.1.0 Returns a text summary of the contents of the raster.

  • ST_TPI - Availability: 2.1.0 Returns a raster with the calculated Topographic Position Index.

  • ST_TRI - Availability: 2.1.0 Returns a raster with the calculated Terrain Ruggedness Index.

  • ST_Tesselate - Availability: 2.1.0 Perform surface Tesselation of a polygon or polyhedralsurface and returns as a TIN or collection of TINS

  • ST_Tile - Availability: 2.1.0 Returns a set of rasters resulting from the split of the input raster based upon the desired dimensions of the output rasters.

  • ST_Touches - Availability: 2.1.0 Return true if raster rastA and rastB have at least one point in common but their interiors do not intersect.

  • ST_Union - Availability: 2.1.0 ST_Union(rast, unionarg) variant was introduced. Returns the union of a set of raster tiles into a single raster composed of 1 or more bands.

  • ST_Within - Availability: 2.1.0 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 - Availability: 2.1.0 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.

  • Set_Geocode_Setting - Availability: 2.1.0 Sets a setting that affects behavior of geocoder functions.

  • UpdateRasterSRID - Availability: 2.1.0 Change the SRID of all rasters in the user-specified column and table.

  • clearTopoGeom - Availability: 2.1 Clears the content of a topo geometry.

  • 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

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

  • ST_AddBand - Enhanced: 2.1.0 support for addbandarg added.

  • ST_AddBand - Enhanced: 2.1.0 support for new out-db bands added.

  • ST_AsBinary/ST_AsWKB - Enhanced: 2.1.0 Addition of outasin

  • ST_Aspect - Enhanced: 2.1.0 Uses ST_MapAlgebra() and added optional interpolate_nodata function parameter

  • ST_Clip - Enhanced: 2.1.0 Rewritten in C

  • ST_Distinct4ma - Enhanced: 2.1.0 Addition of Variant 2

  • ST_HillShade - Enhanced: 2.1.0 Uses ST_MapAlgebra() and added optional interpolate_nodata function parameter

  • ST_Max4ma - Enhanced: 2.1.0 Addition of Variant 2

  • ST_Mean4ma - Enhanced: 2.1.0 Addition of Variant 2

  • ST_Min4ma - Enhanced: 2.1.0 Addition of Variant 2

  • ST_PixelAsPolygons - Enhanced: 2.1.0 exclude_nodata_value optional argument was added.

  • ST_Polygon - Enhanced: 2.1.0 Improved Speed (fully C-Based) and the returning multipolygon is ensured to be valid.

  • ST_Range4ma - Enhanced: 2.1.0 Addition of Variant 2

  • ST_SameAlignment - Enhanced: 2.1.0 addition of Aggegrate variant

  • ST_SetGeoReference - Enhanced: 2.1.0 Addition of ST_SetGeoReference(raster, double precision, …​) variant

  • ST_SetValue - Enhanced: 2.1.0 Geometry variant of ST_SetValue() now supports any geometry type, not just point. The geometry variant is a wrapper around the geomval[] variant of ST_SetValues()

  • ST_Slope - Enhanced: 2.1.0 Uses ST_MapAlgebra() and added optional units, scale, interpolate_nodata function parameters

  • ST_StdDev4ma - Enhanced: 2.1.0 Addition of Variant 2

  • ST_Sum4ma - Enhanced: 2.1.0 Addition of Variant 2

  • ST_Transform - Enhanced: 2.1.0 Addition of ST_Transform(rast, alignto) variant

  • ST_Union - Enhanced: 2.1.0 Improved Speed (fully C-Based).

  • ST_Union - Enhanced: 2.1.0 ST_Union(rast) (variant 1) unions all bands of all input rasters. Prior versions of PostGIS assumed the first band.

  • ST_Union - Enhanced: 2.1.0 ST_Union(rast, uniontype) (variant 4) unions all bands of all input rasters.

  • ST_AsGML - Enhanced: 2.1.0 id support was introduced, for GML 3.

  • ST_Boundary - Enhanced: 2.1.0 support for Triangle was introduced

  • ST_DWithin - Enhanced: 2.1.0 improved speed for geography. See Making Geography faster for details.

  • ST_DWithin - Enhanced: 2.1.0 support for curved geometries was introduced.

  • ST_Distance - Enhanced: 2.1.0 improved speed for geography. See Making Geography faster for details.

  • ST_Distance - Enhanced: 2.1.0 - support for curved geometries was introduced.

  • ST_DumpPoints - Enhanced: 2.1.0 Faster speed. Reimplemented as native-C.

  • ST_MakeValid - Enhanced: 2.1.0, added support for GEOMETRYCOLLECTION and MULTIPOINT.

  • ST_Segmentize - Enhanced: 2.1.0 support for geography was introduced.

  • ST_Summary - Enhanced: 2.1.0 S flag to denote if has a known spatial reference system

  • toTopoGeom - Enhanced: 2.1.0 adds the version taking an existing TopoGeometry.

15.12.11. PostGIS functions breaking changes in 2.1

The functions given below are PostGIS functions that have possibly breaking changes in PostGIS 2.1. If you use any of these, you may need to check your existing code.

  • ST_Aspect - Changed: 2.1.0 In prior versions, return values were in radians. Now, return values default to degrees

  • ST_HillShade - Changed: 2.1.0 In prior versions, azimuth and altitude were expressed in radians. Now, azimuth and altitude are expressed in degrees

  • ST_Intersects - Changed: 2.1.0 The behavior of the ST_Intersects(raster, geometry) variants changed to match that of ST_Intersects(geometry, raster).

  • ST_PixelAsCentroids - Changed: 2.1.1 Changed behavior of exclude_nodata_value.

  • ST_PixelAsPoints - Changed: 2.1.1 Changed behavior of exclude_nodata_value.

  • ST_PixelAsPolygons - Changed: 2.1.1 Changed behavior of exclude_nodata_value.

  • ST_Polygon - Changed: 2.1.0 In prior versions would sometimes return a polygon, changed to always return multipolygon.

  • ST_RasterToWorldCoordX - Changed: 2.1.0 In prior versions, this was called ST_Raster2WorldCoordX

  • ST_RasterToWorldCoordY - Changed: 2.1.0 In prior versions, this was called ST_Raster2WorldCoordY

  • ST_Resample - Changed: 2.1.0 Parameter srid removed. Variants with a reference raster no longer applies the reference raster’s SRID. Use ST_Transform() to reproject raster. Works on rasters with no SRID.

  • ST_Rescale - Changed: 2.1.0 Works on rasters with no SRID

  • ST_Reskew - Changed: 2.1.0 Works on rasters with no SRID

  • ST_Slope - Changed: 2.1.0 In prior versions, return values were in radians. Now, return values default to degrees

  • ST_SnapToGrid - Changed: 2.1.0 Works on rasters with no SRID

  • ST_WorldToRasterCoordX - Changed: 2.1.0 In prior versions, this was called ST_World2RasterCoordX

  • ST_WorldToRasterCoordY - Changed: 2.1.0 In prior versions, this was called ST_World2RasterCoordY

  • ST_EstimatedExtent - Changed: 2.1.0. Up to 2.0.x this was called ST_Estimated_Extent.

  • ST_Force2D - Changed: 2.1.0. Up to 2.0.x this was called ST_Force_2D.

  • ST_Force3D - Changed: 2.1.0. Up to 2.0.x this was called ST_Force_3D.

  • ST_Force3DM - Changed: 2.1.0. Up to 2.0.x this was called ST_Force_3DM.

  • ST_Force3DZ - Changed: 2.1.0. Up to 2.0.x this was called ST_Force_3DZ.

  • ST_Force4D - Changed: 2.1.0. Up to 2.0.x this was called ST_Force_4D.

  • ST_ForceCollection - Changed: 2.1.0. Up to 2.0.x this was called ST_Force_Collection.

  • ST_LineInterpolatePoint - Changed: 2.1.0. Up to 2.0.x this was called ST_Line_Interpolate_Point.

  • ST_LineLocatePoint - Changed: 2.1.0. Up to 2.0.x this was called ST_Line_Locate_Point.

  • ST_LineSubstring - Changed: 2.1.0. Up to 2.0.x this was called ST_Line_Substring.

  • ST_Segmentize - Changed: 2.1.0 As a result of the introduction of geography support: The construct SELECT ST_Segmentize('LINESTRING(1 2, 3 4)',0.5); will result in ambiguous function error. You need to have properly typed object e.g. a geometry/geography column, use ST_GeomFromText, ST_GeogFromText or SELECT ST_Segmentize('LINESTRING(1 2, 3 4)'::geometry,0.5);

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

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

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

Note

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

Note

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

Note

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

  • && - Availability: 2.0.0 Returns TRUE if A’s bounding box intersects B’s bounding box.

  • &&& - 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.

  • AddEdge - Availability: 2.0.0 Adds a linestring edge to the edge table and associated start and end points to the point nodes table of the specified topology schema using the specified linestring geometry and returns the edgeid of the new (or existing) edge.

  • AddFace - Availability: 2.0.0 Registers a face primitive to a topology and gets its identifier.

  • AddNode - Availability: 2.0.0 Adds a point node to the node table in the specified topology schema and returns the nodeid of new node. If point already exists as node, the existing nodeid is returned.

  • AddOverviewConstraints - Availability: 2.0.0 Tag a raster column as being an overview of another.

  • AddRasterConstraints - Availability: 2.0.0 Adds raster constraints to a loaded raster table for a specific column that constrains spatial ref, scaling, blocksize, alignment, bands, band type and a flag to denote if raster column is regularly blocked. The table must be loaded with data for the constraints to be inferred. Returns true if the constraint setting was accomplished and issues a notice otherwise.

  • AsGML - Availability: 2.0.0 Returns the GML representation of a topogeometry.

  • CopyTopology - Availability: 2.0.0 Makes a copy of a topology structure (nodes, edges, faces, layers and TopoGeometries).

  • DropOverviewConstraints - Availability: 2.0.0 Untag a raster column from being an overview of another.

  • DropRasterConstraints - Availability: 2.0.0 Drops PostGIS raster constraints that refer to a raster table column. Useful if you need to reload data or update your raster column data.

  • Drop_Indexes_Generate_Script - Availability: 2.0.0 Generates a script that drops all non-primary key and non-unique indexes on tiger schema and user specified schema. Defaults schema to tiger_data if no schema is specified.

  • Drop_State_Tables_Generate_Script

    • Availability: 2.0.0 Generates a script that drops all tables in the specified schema that are prefixed with the state abbreviation. Defaults schema to tiger_data if no schema is specified.

  • Geocode_Intersection - Availability: 2.0.0 Takes in 2 streets that intersect and a state, city, zip, and outputs a set of possible locations on the first cross street that is at the intersection, also includes a geomout as the point location in NAD 83 long lat, a normalized_address (addy) for each location, and the rating. The lower the rating the more likely the match. Results are sorted by lowest rating first. Can optionally pass in maximum results, defaults to 10. Uses Tiger data (edges, faces, addr), PostgreSQL fuzzy string matching (soundex, levenshtein).

  • GetEdgeByPoint - Availability: 2.0.0 Finds the edge-id of an edge that intersects a given point.

  • GetFaceByPoint - Availability: 2.0.0 Finds face intersecting a given point.

  • GetNodeByPoint - Availability: 2.0.0 Finds the node-id of a node at a point location.

  • GetNodeEdges - Availability: 2.0 Returns an ordered set of edges incident to the given node.

  • GetRingEdges - Availability: 2.0.0 Returns the ordered set of signed edge identifiers met by walking on ana given edge side.

  • GetTopoGeomElements - Availability: 2.0.0 Returns a set of topoelement objects containing the topological element_id,element_type of the given TopoGeometry (primitive elements).

  • GetTopologySRID - Availability: 2.0.0 Returns the SRID of a topology in the topology.topology table given the name of the topology.

  • Get_Tract - Availability: 2.0.0 Returns census tract or field from tract table of where the geometry is located. Default to returning short name of tract.

  • Install_Missing_Indexes - Availability: 2.0.0 Finds all tables with key columns used in geocoder joins and filter conditions that are missing used indexes on those columns and will add them.

  • Loader_Generate_Census_Script

    • Availability: 2.0.0 Generates a shell script for the specified platform for the specified states that will download Tiger census state tract, bg, and tabblocks data tables, stage and load into tiger_data schema. Each state script is returned as a separate record.

  • Loader_Generate_Script - Availability: 2.0.0 to support Tiger 2010 structured data and load census tract (tract), block groups (bg), and blocks (tabblocks) tables . Generates a shell script for the specified platform for the specified states that will download Tiger data, stage and load into tiger_data schema. Each state script is returned as a separate record. Latest version supports Tiger 2010 structural changes and also loads census tract, block groups, and blocks tables.

  • Missing_Indexes_Generate_Script

    • Availability: 2.0.0 Finds all tables with key columns used in geocoder joins that are missing indexes on those columns and will output the SQL DDL to define the index for those tables.

  • Polygonize - Availability: 2.0.0 Finds and registers all faces defined by topology edges.

  • Reverse_Geocode - Availability: 2.0.0 Takes a geometry point in a known spatial ref sys and returns a record containing an array of theoretically possible addresses and an array of cross streets. If include_strnum_range = true, includes the street range in the cross streets.

  • ST_3DClosestPoint - Availability: 2.0.0 Returns the 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_AddEdgeModFace - Availability: 2.0 Add a new edge and, if in doing so it splits a face, modify the original face and add a new face.

  • ST_AddEdgeNewFaces - Availability: 2.0 Add a new edge and, if in doing so it splits a face, delete the original face and replace it with two new faces.

  • ST_AsGDALRaster - Availability: 2.0.0 - requires GDAL >= 1.6.0. Return the raster tile in the designated GDAL Raster format. Raster formats are one of those supported by your compiled library. Use ST_GDALDrivers() to get a list of formats supported by your library.

  • ST_AsJPEG - Availability: 2.0.0 - requires GDAL >= 1.6.0. Return the raster tile selected bands as a single Joint Photographic Exports Group (JPEG) image (byte array). If no band is specified and 1 or more than 3 bands, then only the first band is used. If only 3 bands then all 3 bands are used and mapped to RGB.

  • ST_AsLatLonText - Availability: 2.0 Return the Degrees, Minutes, Seconds representation of the given point.

  • ST_AsPNG - Availability: 2.0.0 - requires GDAL >= 1.6.0. Return the raster tile selected bands as a single portable network graphics (PNG) image (byte array). If 1, 3, or 4 bands in raster and no bands are specified, then all bands are used. If more 2 or more than 4 bands and no bands specified, then only band 1 is used. Bands are mapped to RGB or RGBA space.

  • ST_AsRaster - Availability: 2.0.0 - requires GDAL >= 1.6.0. Converts a PostGIS geometry to a PostGIS raster.

  • ST_AsTIFF - Availability: 2.0.0 - requires GDAL >= 1.6.0. Return the raster selected bands as a single TIFF image (byte array). If no band is specified or any of specified bands does not exist in the raster, then will try to use all bands.

  • ST_AsX3D - Availability: 2.0.0: ISO-IEC-19776-1.2-X3DEncodings-XML Returns a Geometry in X3D xml node element format: ISO-IEC-19776-1.2-X3DEncodings-XML

  • ST_Aspect - Availability: 2.0.0 Returns the aspect (in degrees by default) of an elevation raster band. Useful for analyzing terrain.

  • ST_Band - Availability: 2.0.0 Returns one or more bands of an existing raster as a new raster. Useful for building new rasters from existing rasters.

  • ST_BandIsNoData - Availability: 2.0.0 Returns true if the band is filled with only nodata values.

  • ST_Clip - Availability: 2.0.0 Returns the raster clipped by the input geometry. If band number not is specified, all bands are processed. If crop is not specified or TRUE, the output raster is cropped.

  • 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 encloses all input geometry vertices

  • ST_Count - Availability: 2.0.0 Returns the number of pixels in a given band of a raster or raster coverage. If no band is specified defaults to band 1. If exclude_nodata_value is set to true, will only count pixels that are not equal to the nodata value.

  • ST_CreateTopoGeo - Availability: 2.0 Adds a collection of geometries to a given empty topology and returns a message detailing success.

  • ST_Distinct4ma - Availability: 2.0.0 Raster processing function that calculates the number of unique pixel values in a neighborhood.

  • ST_FlipCoordinates - Availability: 2.0.0 Returns a version of a geometry with X and Y axis flipped.

  • ST_GDALDrivers - Availability: 2.0.0 - requires GDAL >= 1.6.0. Returns a list of raster formats supported by PostGIS through GDAL. Only those formats with can_write=True can be used by ST_AsGDALRaster

  • ST_GeomFromGeoJSON - Availability: 2.0.0 requires - JSON-C >= 0.9 Takes as input a geojson representation of a geometry and outputs a PostGIS geometry object

  • ST_GetFaceEdges - Availability: 2.0 Returns a set of ordered edges that bound aface.

  • ST_HasNoBand - Availability: 2.0.0 Returns true if there is no band with given band number. If no band number is specified, then band number 1 is assumed.

  • ST_HillShade - Availability: 2.0.0 Returns the hypothetical illumination of an elevation raster band using provided azimuth, altitude, brightness and scale inputs.

  • ST_Histogram - Availability: 2.0.0 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_InterpolatePoint - Availability: 2.0.0 Returns the interpolated measure of a geometry closest to a point.

  • ST_IsEmpty - Availability: 2.0.0 Returns true if the raster is empty (width = 0 and height = 0). Otherwise, returns false.

  • 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_MapAlgebraExpr - Availability: 2.0.0 1 raster band version: Creates a new one band raster formed by applying a valid PostgreSQL algebraic operation on the input raster band and of pixeltype provided. Band 1 is assumed if no band is specified.

  • ST_MapAlgebraExpr - Availability: 2.0.0 2 raster band version: Creates a new one band raster formed by applying a valid PostgreSQL algebraic operation on the two input raster bands and of pixeltype provided. band 1 of each raster is assumed if no band numbers are specified. The resulting raster will be aligned (scale, skew and pixel corners) on the grid defined by the first raster and have its extent defined by the "extenttype" parameter. Values for "extenttype" can be: INTERSECTION, UNION, FIRST, SECOND.

  • ST_MapAlgebraFct - Availability: 2.0.0 1 band version - Creates a new one band raster formed by applying a valid PostgreSQL function on the input raster band and of pixeltype prodived. Band 1 is assumed if no band is specified.

  • ST_MapAlgebraFct - Availability: 2.0.0 2 band version - Creates a new one band raster formed by applying a valid PostgreSQL function on the 2 input raster bands and of pixeltype prodived. Band 1 is assumed if no band is specified. Extent type defaults to INTERSECTION if not specified.

  • ST_MapAlgebraFctNgb - Availability: 2.0.0 1-band version: Map Algebra Nearest Neighbor using user-defined PostgreSQL function. Return a raster which values are the result of a PLPGSQL user function involving a neighborhood of values from the input raster band.

  • ST_Max4ma - Availability: 2.0.0 Raster processing function that calculates the maximum pixel value in a neighborhood.

  • ST_Mean4ma - Availability: 2.0.0 Raster processing function that calculates the mean pixel value in a neighborhood.

  • ST_Min4ma - Availability: 2.0.0 Raster processing function that calculates the minimum pixel value in a neighborhood.

  • ST_ModEdgeHeal - Availability: 2.0 Heals two edges by deleting the node connecting them, modifying the first edgeand deleting the second edge. Returns the id of the deleted node.

  • ST_MoveIsoNode - Availability: 2.0.0 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.

  • ST_NewEdgeHeal - Availability: 2.0 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.

  • 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_PixelAsPolygon - Availability: 2.0.0 Returns the polygon geometry that bounds the pixel for a particular row and column.

  • ST_PixelAsPolygons - Availability: 2.0.0 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_Project - Availability: 2.0.0 Returns a point projected from a start point by a distance and bearing (azimuth).

  • ST_Quantile - Availability: 2.0.0 Compute quantiles for a raster or raster table coverage in the context of the sample or population. Thus, a value could be examined to be at the raster’s 25%, 50%, 75% percentile.

  • ST_Range4ma - Availability: 2.0.0 Raster processing function that calculates the range of pixel values in a neighborhood.

  • ST_Reclass - Availability: 2.0.0 Creates a new raster composed of band types reclassified from original. The nband is the band to be changed. If nband is not specified assumed to be 1. All other bands are returned unchanged. Use case: convert a 16BUI band to a 8BUI and so forth for simpler rendering as viewable formats.

  • ST_RelateMatch - Availability: 2.0.0 Tests if a DE-9IM Intersection Matrix matches an Intersection Matrix pattern

  • ST_RemEdgeModFace - Availability: 2.0 Removes an edge and, if the removed edge separated two faces,delete one of the them and modify the other to take the space of both.

  • ST_RemEdgeNewFace - Availability: 2.0 Removes an edge and, if the removed edge separated two faces,delete the original faces and replace them with a new face.

  • ST_Resample - Availability: 2.0.0 Requires GDAL 1.6.1+ Resample a raster using a specified resampling algorithm, new dimensions, an arbitrary grid corner and a set of raster georeferencing attributes defined or borrowed from another raster.

  • ST_Rescale - Availability: 2.0.0 Requires GDAL 1.6.1+ Resample a raster by adjusting only its scale (or pixel size). New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor.

  • ST_Reskew - Availability: 2.0.0 Requires GDAL 1.6.1+ Resample a raster by adjusting only its skew (or rotation parameters). New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor.

  • ST_SameAlignment - Availability: 2.0.0 Returns true if rasters have same skew, scale, spatial ref, and offset (pixels can be put on same grid without cutting into pixels) and false if they don’t with notice detailing issue.

  • ST_SetBandIsNoData - Availability: 2.0.0 Sets the isnodata flag of the band to TRUE.

  • ST_SharedPaths - Availability: 2.0.0 Returns a collection containing paths shared by the two input linestrings/multilinestrings.

  • ST_Slope - Availability: 2.0.0 Returns the slope (in degrees by default) of an elevation raster band. Useful for analyzing terrain.

  • ST_Snap - Availability: 2.0.0 Snap segments and vertices of input geometry to vertices of a reference geometry.

  • ST_SnapToGrid - Availability: 2.0.0 Requires GDAL 1.6.1+ Resample a raster by snapping it to a grid. New pixel values are computed using the NearestNeighbor (english or american spelling), Bilinear, Cubic, CubicSpline or Lanczos resampling algorithm. Default is NearestNeighbor.

  • ST_Split - Availability: 2.0.0 requires GEOS Returns a collection of geometries created by splitting a geometry by another geometry.

  • ST_StdDev4ma - Availability: 2.0.0 Raster processing function that calculates the standard deviation of pixel values in a neighborhood.

  • ST_Sum4ma - Availability: 2.0.0 Raster processing function that calculates the sum of all pixel values in a neighborhood.

  • ST_SummaryStats - Availability: 2.0.0 Returns 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_Transform - Availability: 2.0.0 Requires GDAL 1.6.1+ Reprojects a raster in a known spatial reference system to another known spatial reference system using specified resampling algorithm. Options are NearestNeighbor, Bilinear, Cubic, CubicSpline, Lanczos defaulting to NearestNeighbor.

  • ST_UnaryUnion - Availability: 2.0.0 Computes the union of the components of a single geometry.

  • ST_Union - Availability: 2.0.0 Returns the union of a set of raster tiles into a single raster composed of 1 or more bands.

  • ST_ValueCount - Availability: 2.0.0 Returns a set of records containing a pixel band value and count of the number of pixels in a given band of a raster (or a raster coverage) that have a given set of values. If no band is specified defaults to band 1. By default nodata value pixels are not counted. and all other values in the pixel are output and pixel band values are rounded to the nearest integer.

  • TopoElementArray_Agg - Availability: 2.0.0 Returns a topoelementarray for a set of element_id, type arrays (topoelements).

  • TopoGeo_AddLineString - Availability: 2.0.0 Adds a linestring to an existing topology using a tolerance and possibly splitting existing edges/faces. Returns edge identifiers.

  • TopoGeo_AddPoint - Availability: 2.0.0 Adds a point to an existing topology using a tolerance and possibly splitting an existing edge.

  • TopoGeo_AddPolygon - Availability: 2.0.0 Adds a polygon to an existing topology using a tolerance and possibly splitting existing edges/faces. Returns face identifiers.

  • TopologySummary - Availability: 2.0.0 Takes a topology name and provides summary totals of types of objects in topology.

  • Topology_Load_Tiger - Availability: 2.0.0 Loads a defined region of tiger data into a PostGIS Topology and transforming the tiger data to spatial reference of the topology and snapping to the precision tolerance of the topology.

  • toTopoGeom - Availability: 2.0 Converts a simple Geometry into a topo geometry.

  • ~= - Availability: 2.0.0 Returns TRUE if A’s bounding box is the same as B’s.

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

  • AddGeometryColumn - Enhanced: 2.0.0 use_typmod argument introduced. Defaults to creating typmod geometry column instead of constraint-based.

  • Box2D - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • Box3D - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • Geocode - Enhanced: 2.0.0 to support Tiger 2010 structured data and revised some logic to improve speed, accuracy of geocoding, and to offset point from centerline to side of street address is located on. The new parameter max_results useful for specifying number of best results or just returning the best result.

  • GeometryType - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • Populate_Geometry_Columns - Enhanced: 2.0.0 use_typmod optional argument was introduced that allows controlling if columns are created with typmodifiers or with check constraints.

  • ST_Intersection - Enhanced: 2.0.0 - Intersection in the raster space was introduced. In earlier pre-2.0.0 versions, only intersection performed in vector space were supported.

  • ST_Intersects - Enhanced: 2.0.0 support raster/raster intersects was introduced.

  • ST_Value - Enhanced: 2.0.0 exclude_nodata_value optional argument was added.

  • ST_3DExtent - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_Affine - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_Area - Enhanced: 2.0.0 - support for 2D polyhedral surfaces was introduced.

  • ST_AsBinary - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_AsBinary - Enhanced: 2.0.0 support for higher coordinate dimensions was introduced.

  • ST_AsBinary - Enhanced: 2.0.0 support for specifying endian with geography was introduced.

  • ST_AsEWKB - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_AsEWKT - Enhanced: 2.0.0 support for Geography, Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_AsGML - Enhanced: 2.0.0 prefix support was introduced. Option 4 for GML3 was introduced to allow using LineString instead of Curve tag for lines. GML3 Support for Polyhedral surfaces and TINS was introduced. Option 32 was introduced to output the box.

  • ST_AsKML - Enhanced: 2.0.0 - Add prefix namespace, use default and named args

  • ST_Azimuth - Enhanced: 2.0.0 support for geography was introduced.

  • ST_ChangeEdgeGeom - Enhanced: 2.0.0 adds topological consistency enforcement

  • ST_Dimension - Enhanced: 2.0.0 support for Polyhedral surfaces and TINs was introduced. No longer throws an exception if given empty geometry.

  • ST_Dump - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_DumpPoints - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_Expand - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_Extent - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_Force2D - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.

  • ST_ForceRHR - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.

  • ST_Force3D - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.

  • ST_Force3DZ - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.

  • ST_ForceCollection - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.

  • ST_GMLToSQL - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced.

  • ST_GMLToSQL - Enhanced: 2.0.0 default srid optional parameter added.

  • ST_GeomFromEWKB - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced.

  • ST_GeomFromEWKT - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced.

  • ST_GeomFromGML - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced.

  • ST_GeomFromGML - Enhanced: 2.0.0 default srid optional parameter added.

  • ST_GeometryN - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_GeometryType - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.

  • ST_IsClosed - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.

  • ST_MakeEnvelope - Enhanced: 2.0: Ability to specify an envelope without specifying an SRID was introduced.

  • ST_MakeValid - Enhanced: 2.0.1, speed improvements

  • ST_NPoints - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.

  • ST_NumGeometries - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_Relate - Enhanced: 2.0.0 - added support for specifying boundary node rule.

  • ST_Rotate - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_Rotate - Enhanced: 2.0.0 additional parameters for specifying the origin of rotation were added.

  • ST_RotateX - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_RotateY - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_RotateZ - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_Scale - Enhanced: 2.0.0 support for Polyhedral surfaces, Triangles and TIN was introduced.

  • ST_ShiftLongitude - Enhanced: 2.0.0 support for Polyhedral surfaces and TIN was introduced.

  • ST_Summary - Enhanced: 2.0.0 added support for geography

  • ST_Transform - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.

  • ValidateTopology - Enhanced: 2.0.0 more efficient edge crossing detection and fixes for false positives that were existent in prior versions.

  • && - Enhanced: 2.0.0 support for Polyhedral surfaces was introduced.

15.12.13. PostGIS Functions changed behavior in 2.0

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

Note

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

Note

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

Note

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

  • AddGeometryColumn - Changed: 2.0.0 This function no longer updates geometry_columns since geometry_columns is a view that reads from system catalogs. It by default also does not create constraints, but instead uses the built in type modifier behavior of PostgreSQL. So for example building a wgs84 POINT column with this function is now equivalent to: ALTER TABLE some_table ADD COLUMN geom geometry(Point,4326);

  • AddGeometryColumn - Changed: 2.0.0 If you require the old behavior of constraints use the default use_typmod, but set it to false.

  • AddGeometryColumn - Changed: 2.0.0 Views can no longer be manually registered in geometry_columns, however views built against geometry typmod tables geometries and used without wrapper functions will register themselves correctly because they inherit the typmod behavior of their parent table column. Views that use geometry functions that output other geometries will need to be cast to typmod geometries for these view geometry columns to be registered correctly in geometry_columns. Refer to .

  • DropGeometryColumn - Changed: 2.0.0 This function is provided for backward compatibility. Now that since geometry_columns is now a view against the system catalogs, you can drop a geometry column like any other table column using ALTER TABLE

  • DropGeometryTable - Changed: 2.0.0 This function is provided for backward compatibility. Now that since geometry_columns is now a view against the system catalogs, you can drop a table with geometry columns like any other table using DROP TABLE

  • Populate_Geometry_Columns - Changed: 2.0.0 By default, now uses type modifiers instead of check constraints to constrain geometry types. You can still use check constraint behavior instead by using the new use_typmod and setting it to false.

  • Box3D - Changed: 2.0.0 In pre-2.0 versions, there used to be a box2d instead of box3d. Since box2d is a deprecated type, this was changed to box3d.

  • ST_GDALDrivers - Changed: 2.0.6, 2.1.3 - by default no drivers are enabled, unless GUC or Environment variable gdal_enabled_drivers is set.

  • ST_ScaleX - Changed: 2.0.0. In WKTRaster versions this was called ST_PixelSizeX.

  • ST_ScaleY - Changed: 2.0.0. In WKTRaster versions this was called ST_PixelSizeY.

  • ST_SetScale - Changed: 2.0.0 In WKTRaster versions this was called ST_SetPixelSize. This was changed in 2.0.0.

  • ST_3DExtent - Changed: 2.0.0 In prior versions this used to be called ST_Extent3D

  • ST_3DLength - Changed: 2.0.0 In prior versions this used to be called ST_Length3D

  • ST_3DMakeBox - Changed: 2.0.0 In prior versions this used to be called ST_MakeBox3D

  • ST_3DPerimeter - Changed: 2.0.0 In prior versions this used to be called ST_Perimeter3D

  • ST_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.

  • ST_AsGML - Changed: 2.0.0 use default named args

  • ST_AsGeoJSON - Changed: 2.0.0 support default args and named args.

  • ST_AsSVG - Changed: 2.0.0 to use default args and support named args

  • ST_EndPoint - Changed: 2.0.0 no longer works with single geometry MultiLineStrings. In older versions of PostGIS a single-line MultiLineString would work 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_GeomFromText - Changed: 2.0.0 In prior versions of PostGIS ST_GeomFromText('GEOMETRYCOLLECTION(EMPTY)') was allowed. This is now illegal in PostGIS 2.0.0 to better conform with SQL/MM standards. This should now be written as ST_GeomFromText('GEOMETRYCOLLECTION EMPTY')

  • ST_GeometryN - Changed: 2.0.0 Prior versions would return NULL for singular geometries. This was changed to return the geometry for ST_GeometryN(..,1) case.

  • ST_IsEmpty - Changed: 2.0.0 In prior versions of PostGIS ST_GeomFromText('GEOMETRYCOLLECTION(EMPTY)') was allowed. This is now illegal in PostGIS 2.0.0 to better conform with SQL/MM standards

  • ST_Length - Changed: 2.0.0 Breaking change — in prior versions applying this to a MULTI/POLYGON of type geography would give you the perimeter of the POLYGON/MULTIPOLYGON. In 2.0.0 this was changed to return 0 to be in line with geometry behavior. Please use ST_Perimeter if you want the perimeter of a polygon

  • ST_LocateAlong - Changed: 2.0.0 in prior versions this used to be called ST_Locate_Along_Measure.

  • ST_LocateBetween - Changed: 2.0.0 - in prior versions this used to be called ST_Locate_Between_Measures.

  • ST_ModEdgeSplit - Changed: 2.0 - In prior versions, this was misnamed ST_ModEdgesSplit

  • ST_NumGeometries - Changed: 2.0.0 In prior versions this would return NULL if the geometry was not a collection/MULTI type. 2.0.0+ now returns 1 for single geometries e.g POLYGON, LINESTRING, POINT.

  • ST_NumInteriorRings - Changed: 2.0.0 - in prior versions it would allow passing a MULTIPOLYGON, returning the number of interior rings of first 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.

  • 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.

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

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

  • PostGIS_LibXML_Version - Availability: 1.5 Returns the version number of the libxml2 library.

  • ST_AddMeasure - Availability: 1.5.0 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 no point in B is outside 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

  • ~= - Availability: 1.5.0 changed behavior Returns TRUE if A’s bounding box is the same as B’s.

  • ST_HausdorffDistance - Availability: 1.5.0 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 support for geography was introduced. Returns TRUE if A’s 2D bounding box intersects B’s 2D bounding box.

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

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

  • Populate_Geometry_Columns - Ensures geometry columns are defined with type modifiers or have appropriate spatial constraints. Availability: 1.4.0

  • ST_AsSVG - Returns SVG path data for a geometry. Availability: 1.2.2. Availability: 1.4.0 Changed in PostGIS 1.4.0 to include L command in absolute path to conform to http://www.w3.org/TR/SVG/paths.html#PathDataBNF

  • ST_Collect - Creates a GeometryCollection or Multi* geometry from a set of geometries. Availability: 1.4.0 - ST_Collect(geomarray) was introduced. ST_Collect was enhanced to handle more geometries faster.

  • ST_ContainsProperly - Tests if B intersects the interior of A but not the boundary or exterior. Availability: 1.4.0

  • ST_GeoHash - Return a GeoHash representation of the geometry. Availability: 1.4.0

  • ST_IsValidReason - Returns text stating if a geometry is valid, or a reason for invalidity. Availability: 1.4

  • ST_LineCrossingDirection - Returns a number indicating the crossing behavior of two LineStrings. Availability: 1.4

  • ST_LocateBetweenElevations - Returns the portions of a geometry that lie in an elevation (Z) range. Availability: 1.4.0

  • ST_MakeLine - Creates a LineString from Point, MultiPoint, or LineString geometries. Availability: 1.4.0 - ST_MakeLine(geomarray) was introduced. ST_MakeLine aggregate functions was enhanced to handle more points faster.

  • ST_MinimumBoundingCircle - Returns the smallest circle polygon that contains a geometry. Availability: 1.4.0

  • ST_Union - Computes a geometry representing the point-set union of the input geometries. Availability: 1.4.0 - ST_Union was enhanced. ST_Union(geomarray) was introduced and also faster aggregate collection in PostgreSQL.

15.12.16. PostGIS Functions new in 1.3

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

  • ST_AsGML - Return the geometry as a GML version 2 or 3 element. Availability: 1.3.2

  • ST_AsGeoJSON - Return a geometry as a GeoJSON element. Availability: 1.3.4

  • ST_CurveToLine - Converts a geometry containing curves to a linear geometry. Availability: 1.3.0

  • ST_LineToCurve - Converts a linear geometry to a curved geometry. Availability: 1.3.0

  • ST_SimplifyPreserveTopology - Returns a simplified and valid version of a geometry, using the Douglas-Peucker algorithm. Availability: 1.3.3

Prev Next

Set_Geocode_Setting

Home

Chapter 16. Reporting Problems