Spatial functions

Use spatial functions to program your applications. Learn about the function usage and the factors that are common to all or most spatial functions.

Db2® Spatial Extender follows the OGC Simple Features for SQL specification and the ISO SQL/MM Part 3: Spatial standard in the implementation of spatial functions.

The functions are listed by the following categories:

Construction of geometries from data exchange formats
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 spatial indexes and geometries

Construction of geometries from data exchange formats

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

Table 1. Spatial Extender functions that construct geometries from data exchange formats
Function use	Function name
Construct any geometry from a well-known text, a well-known binary, an ESRI shape, or a Geographic Markup Language (GML) format	ST_Geometry function
Construct a specific geometry from a well-known text, a well-known binary, an ESRI shape, or a GML format	ST_GeomCollection function ST_GeomCollFromTxt 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_Point function
Construct any geometry from a well-known text representation	ST_WKTToSQL function ST_GeomFromText function
Construct a specific geometry from a well-known text representation	ST_GeomCollFromTxt function ST_LineFromText function ST_PointFromText function ST_PolyFromText function ST_MLineFromText function ST_MPointFromText function ST_MPolyFromText function
Construct any geometry from a well-known binary representation	ST_WKBToSQL function ST_GeomFromWKB function
Construct a specific geometry from a well-known binary representation	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 Extender 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 well-known text representation	ST_AsText function (See related ST_WellKnownText transform group)
Convert a geometry into a well-known binary representation	ST_AsBinary function (See related ST_WellKnownBinary transform group)
Convert a geometry into an ESRI shape format	ST_AsShape function (See related ST_Shape transform group)
Convert a geometry into a GML format	ST_AsGML function (See related ST_GML transform group)

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 Extender 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 Extender 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
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 by combining multiple geometries	ST_BuildMBRAggr function ST_GetAggrResult function ST_Union function ST_BuildUnionAggrResult function ST_GetAggrResult 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
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 Extender functions that obtain 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 spatial indexes and geometries

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

Table 6. Spatial Extender functions that return information about spatial indexes and properties of geometries
Function use	Function name
Return information about spatial indexes	ST_GetIndexParms function
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 type	ST_GeometryType function
Return information about a 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_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