Spatial functions

You can use spatial functions to process spatial data.

The implementation of these spatial functions follows the OGC Simple Features for SQL specification and parts of the ISO SQL/MM Part 3: Spatial standard.

The spatial functions fall into the following categories:

Construction of geometries from data exchange formats or coordinate data
Conversion of a geometry into a data exchange format
Comparison of geometries and discovery of relations between geometries
Construction of new geometries from existing geometries
Retrieval of geometries that are part of a geometry
Information about geometries

Construction of geometries from data exchange formats or coordinate data

The following functions create a geometry of the ST_Geometry type or one of its subtypes from coordinate data, or from a well-known text (WKT), well-known binary (WKB), ESRI shape, or Geography Markup Language (GML) data exchange format:

Table 1. Spatial functions that construct geometries from data exchange formats or coordinate data
Function use	Function name
Construct any geometry from a WKT, WKB, ESRI shape, or GML objectConstruct any geometry from a WKT, WKB, ESRI shape, GML object, or GeoJSON	ST_Geometry function
Construct any geometry from a WKT object	ST_WKTToSQL function
Construct any geometry from a WKT or GML objectConstruct any geometry from a WKT, GeoJSON, or GML object	ST_GeomFromText function
Construct any geometry from a WKB object	ST_WKBToSQL function ST_GeomFromWKB function
Construct a specific geometry from a WKT, WKB, ESRI shape, or GML objectConstruct a specific geometry from a WKT, WKB, ESRI shape, GML object, or GeoJSON	ST_GeomCollection function ST_LineString function ST_MultiLineString function ST_MultiPoint function ST_MultiPolygon function ST_Point function ST_Polygon function
Construct a specific geometry from coordinates	ST_Ellipse function ST_Point function
Construct a specific geometry from a WKT or GML objectConstruct a specific geometry from a WKT, GeoJSON, or GML object	ST_GeomCollFromTxt function ST_LineFromText function ST_PointFromText function ST_PolyFromText function ST_MLineFromText function ST_MPointFromText function ST_MPolyFromText function
Construct a specific geometry from a WKB object	ST_GeomCollFromWKB function ST_LineFromWKB function ST_PointFromWKB function ST_PolyFromWKB function ST_MLineFromWKB function ST_MPointFromWKB function ST_MPolyFromWKB function

For more information about supported data exchange formats, see Supported data formats

Conversion of a geometry into a data exchange format

The following functions convert a geometry of the ST_Geometry type or one of its subtypes into a data exchange format:

Table 2. Spatial functions that convert a geometry of the ST_Geometry type or one of its subtypes into a data exchange format
Function use	Function name
Convert a geometry into a WKT object	ST_AsText function
Convert a geometry into a WKB object	ST_AsBinary function
Convert a geometry into an ESRI shape object	ST_AsShape function
Convert a geometry into a GML object	ST_AsGML function
Convert a geometry into a GeoJSON object	ST_AsGeoJSON function

For more information about supported data exchange formats, see Supported data formats

Comparison of geometries and discovery of relations between geometries

These spatial functions return information that is the result of a comparison between geometries, between definitions of coordinate systems, or between spatial reference systems. They return information about ways in which geographic features relate to one another or compare with one another.

Table 3. Spatial functions that compare geometries and find relations between geometries
Function use	Function name
Check whether two items geometries are identical	ST_Equals function
Determine the distance between geometries	ST_Distance function
Determine whether geometries intersect	ST_Crosses function ST_Intersects function ST_Overlaps function ST_Touches function ST_Disjoint function
Determine whether a geometry contains another one	ST_Contains function ST_Within function
Compare geometry envelopes	ST_EnvIntersects function ST_MBRIntersects function
Compare two geometries and determine whether they meet the conditions of the DE-9IM pattern matrix string	ST_Relate function

Construction of new geometries from existing geometries

The following functions modify properties of a geometry of type ST_Geometry or one of its subtypes to construct a new geometry:

Table 4. Spatial functions that construct new geometries from existing geometries
Function use	Function name
Convert geometries between coordinate systems	ST_Transform function
Create modified forms of existing geometries	ST_Generalize function ST_M function ST_X function ST_Y function ST_Z function
Create new geometries with different space configurations	ST_Buffer function ST_Centroid function ST_ConvexHull function ST_Difference function ST_Intersection function ST_SymDifference function ST_Union function ST_Union function
Construct envelopes and boundaries	ST_Boundary function ST_Envelope function ST_ExteriorRing function ST_MBR function
Create new geometry based on existing geometry measures	ST_FindMeasure or ST_LocateAlong function ST_LocateBetween or ST_MeasureBetween function
Create a new geometry from a curve	ST_DistanceToPoint function ST_AppendPoint function ST_ChangePoint function ST_RemovePoint function
Create a new geometry from a curve or multi-curve	ST_NearestPoints function ST_PerpPoints function ST_PointAtDistance function
Create a new geometry by combining multiple geometries	ST_Union aggregation function ST_GrandMBR aggregation function
Convert geometries of a supertype into corresponding geometries of a subtype.	ST_ToGeomColl function ST_ToLineString function ST_ToMultiLine function ST_ToMultiPoint function ST_ToMultiPolygon function ST_ToPoint function ST_ToPolygon function

Retrieval of geometries that are part of a geometry

The following functions that obtain specified geometries from collections of geometry objects:

Table 5. Spatial functions that retrieve a geometry that is part of a geometry
Function use	Function name
Obtain a geometry from within an existing geometry by using an index	ST_GeometryN function ST_InteriorRingN function ST_LineStringN function ST_PointN function ST_PolygonN function
Obtain points from a curve	ST_Endpoint function ST_MidPoint function ST_StartPoint function

Information about geometries

The following functions return information about geometric properties such as coordinates, measures, and boundaries:

Table 6. Spatial functions that return information about properties of geometries
Function use	Function name
Return information about spatial reference systems	ST_SrsId or ST_SRID function ST_SrsName function
Return information to indicate whether geometry properties are identical	ST_EqualCoordsys function ST_EqualSRS function
Return information about geometry types	ST_GeometryType function
Return information about geometry dimensions	ST_Area function ST_Dimension function ST_Length function ST_Perimeter function
Return information about boundaries, envelopes, rings, and geometries in a geometry	ST_NumGeometries function ST_NumInteriorRing function ST_NumLineStrings function ST_NumPoints function ST_NumPolygons function
Return information to indicate whether envelopes or bounding boxes intersect	ST_EnvIntersects function ST_MBRIntersects function
Return information about coordinates and measures	ST_CoordDim function ST_Is3d function ST_IsFormat function ST_IsMeasured function ST_IsValid function ST_MaxM function ST_MaxX function ST_MaxY function ST_MaxZ function ST_M function ST_MinM function ST_MinX function ST_MinY function ST_MinZ function ST_X function ST_Y function ST_Z function
Return information to indicate whether a geometry is closed, empty, or simple	ST_IsClosed function ST_IsEmpty function ST_IsSimple function