Geospatial Toolkit functions

Use functions provided by the Geospatial Toolkit to process and index spatial data.

Table 1. Functions for any geometry type
Function Input Output Description
ST_Area any1 Double Returns the area of a polygon; returns 0.0 for any other geometry.
ST_AsBinary any1 Binary Exports an object to a well-known binary (WKB) representation.
ST_AsText any1 String Exports an object to well-known text (WKT) representation.
ST_Boundary any1 ST_Geometry Returns the boundary of a geometry.
ST_Buffer any1, Double ST_Geometry Returns a geometry that represents all points whose distance from the input geometry object is less than or equal to the specified distance. Calculations are in the spatial reference system of this geometry object. Because of the limitations of linear interpolation, there will often be a relatively small error in this distance, but it should be near the resolution of the coordinates used.
ST_ContainingGeohash any1, Double String Returns a single geohash that covers a specified geometry, buffered by the specified distance precision. Note that this might result in a much looser cover than ST_GeohashEncodeDistance.
ST_Contains any1,any1 Boolean Returns TRUE if the second geometry is completely contained within the first geometry.
ST_Crosses any1,any1 Boolean Returns TRUE if the two specified geometries cross each other, that is, if both:
  • Their intersection results in a geometry with a dimension that is one less than the maximum dimension of the two source geometries.
  • The intersection set is interior to both source geometries.
ST_Difference any1,any1 ST_Geometry Returns a geometry object that represents the point set difference of the two specified geometry objects.
ST_Dimension any1 Integer Returns the inherent dimension of the input geometry, which must be less than or equal to the coordinate dimension. In non-homogeneous collections, this returns the largest topological dimension of the contained objects.
ST_Disjoint any1,any1 Boolean Returns TRUE if the two specified geometries are disjoint, that is, if they do not intersect one another.
ST_Distance any1,any1 Double Returns the shortest distance between any two points in the two geometries as calculated in their spatial reference system. Because the geometries are closed, it is possible to find a point on each geometry object involved such that the distance between these 2 points is the returned distance between their geometries.
ST_Envelope any1 ST_BoundingBox Returns the minimum bounding box for the input geometry.
EnvelopesIntersect any1, Double, Double, Double, Double Boolean Determines whether the envelope of the specified geometry intersects the specified envelope or bounding box. The envelope or bounding box is determined by its lower corner (longitude and latitude specified by the second and third parameters) and upper corner (longitude and latitude specified by the fourth and fifth parameters).
ST_Equals any1,any1 Boolean Returns TRUE if the two specified geometries are equal, that is, if they have matching X and Y coordinates.
ST_GeohashEncode any1, Integer String[] Returns a collection of no more than 6 geohashes that cover the specified geometry. If the geometry can be covered by geohashes whose bit depth is larger than the specified bit depth, the geohashes are truncated to that bit depth. A point geometry always produces a single geohash.
ST_GeohashEncodeDistance any1, Double String[] Returns a collection of no more than 6 geohashes that cover the specified geometry, buffered by the specified distance precision.
ST_GeoJSONFromGeom any1 String Converts an SQL geometry into a GeoJSON string.
ST_GeomCollection any1[] ST_GeomCollection Constructor for a geometry collection.
ST_GeometryType any1 String Returns, as a string, the name of the instantiable subtype of the geometry of which this object is an instantiable member.
ST_Intersection any1,any1 ST_Geometry Returns a geometry object that represents the point set intersection of the two specified geometry objects.
ST_Intersects any1,any1 Boolean Returns TRUE if the two specified geometries intersect each other.
ST_IntersectsInterior any1, any1 Boolean Returns TRUE if the first geometry intersects the interior of the second geometry.
ST_IsDegenerate any1 Boolean Returns TRUE if the specified geometry is degenerate to a point.
ST_IsEmpty any1 Boolean Returns TRUE if the specified geometry is the empty geometry, that is, if the geometry represents the empty point set ∅ for the coordinate space.
ST_IsMeasured any1 Boolean Returns TRUE if the specified geometry has measure (M) coordinate values. Because this database does not support M values, this function always returns FALSE.
ST_IsSimple any1 Boolean Returns TRUE if the specified geometry has no anomalous geometric points, such as self-intersection or self-tangency. The description of each instantiable geometric class includes the specific conditions that caused an instance of that class to be classified as not simple.
ST_IsValid any1 Boolean Returns TRUE if the specified geometry is valid.
ST_Is3D any1 Boolean Returns TRUE if the specified geometry has Z coordinate values. Because this database does not support Z values, this function always returns FALSE.
ST_Overlaps any1,any1 Boolean Returns TRUE if the two specified geometries overlap, that is, if their intersection set results in a geometry that is different from both of them but of the same dimension.
ST_SRID any1 Integer Returns the spatial reference system ID for this object. The default ID is 4326 -- WGS84.
ST_SymDifference any1,any1 ST_Geometry Returns a geometry object that represents the point set symmetric difference of the two specified geometry objects.
ST_Touches any1,any1 Boolean Returns TRUE if the two specified geometries touch each other, that is, if their boundaries intersect or if the interior of one geometry intersects the boundary of the other.
ST_Within any1,any1 Boolean Returns TRUE if the first geometry is completely within the second geometry.
ST_Union any1,any1 ST_Geometry Returns a geometry object that represents the point set union of the two specified geometry objects.
1 The input geometry type can be any of the types listed here.
Table 2. Functions for points
SQL Function Input Output Description
ST_Azimuth ST_Point, ST_Point Double Returns the azimuth, in radians, on a spheroid and with respect to true North, measured clockwise from the vertical from the first point to the second point.
ST_DestinationPoint ST_Point, Double, Double ST_Point Returns the point on a spheroid that lies at the specified azimuth (second parameter) and distance (third parameter) from the specified starting point (first parameter).
ST_GeodesicAzimuth ST_Point, ST_Point Double Returns the azimuth, in radians, on an ellipsoid and with respect to true North, measured clockwise from the vertical from the first point to the second point.
ST_GeodesicDestinationPoint ST_Point, Double, Double ST_Point Returns the point on an ellipsoid that lies at the specified azimuth (second parameter) and distance (third parameter) from the specified starting point (first parameter).
ST_Point Double, Double ST_Point Returns the point with the specified longitude and latitude values in degrees. The coordinate system must be an ellipsoidal coordinate system; the default is WGS84.
ST_X ST_Point Double Returns the x-coordinate (longitude) of the specified point.
ST_Y ST_Point Double Returns the y-coordinate (latitude) of the specified point.
Table 3. Functions for curves
SQL Function Input Output Description
ST_EndPoint ST_Curve ST_Point Returns the end point of the specified curve.
ST_Length ST_Curve Double Returns the length of the specified curve.
ST_StartPoint ST_Curve ST_Point Returns the start point of the specified curve.
Table 4. Functions for line strings
SQL Function Input Output Description
ST_LineString ST_Point[] ST_LineString Returns a linestring constructed from the specified array of points.
ST_NumPoints ST_LineString Integer Returns the number of points in the specified linestring.
ST_PointN ST_LineString ST_Point Returns the Nth point in the specified linestring.
Table 5. Functions for polygons and bounding boxes
SQL Function Input Output Description
ST_BoundingBox Double, Double, Double, Double ST_BoundingBox Returns an SQL geometry bounding box with the specified southwest longitude, southwest latitude, northeast longitude, and northeast latitude.
ST_BoundingBoxCenter ST_BoundingBox ST_Point Returns the center of the specified bounding box.
ST_ExteriorRing ST_Polygon or ST_BoundingBox ST_LineString Returns the exterior ring of the specified polygon or bounding box.
ST_InteriorRingN ST_Polygon or ST_BoundingBox ST_LineString Returns the Nth interior ring of the specified polygon or bounding box.
ST_InteriorRings ST_Polygon or ST_BoundingBox ST_LineString[] Returns an array of the interior rings of the specified polygon or bounding box.
ST_NorthEastCorner ST_BoundingBox ST_Point Returns the northeast corner of the specified bounding box.
ST_NumInteriorRing ST_Polygon or ST_BoundingBox Integer Returns the number of interior rings of the specified polygon or bounding box.
ST_PointOnSurface ST_Polygon or ST_BoundingBox ST_Point Returns a point that is on the specified polygon or bounding box.
ST_SouthWestCorner ST_BoundingBox ST_Point Returns the southwest corner of the specified bounding box.
Table 6. Functions for geometry collections
SQL Function Input Output Description
ST_GeometryN ST_GeomCollection ST_Geometry Returns the Nth geometry from the specified collection.
ST_NumGeometries ST_GeomCollection Integer Returns the number of geometries in the specified collection.
Table 7. Functions for non-geometry data types
SQL Function Input Output Description
ST_GeohashDecode Varchar ST_Geometry Decodes a geohash, specified as a character string of binary digits, to a bounding box (if the bit depth of the geohash is 63 or less) or to a point (otherwise). For example, the string '110010110' is decoded to a bounding box with the following coordinates:
67.5 11.25, 78.74999999999999 22.499999999999996
ST_GeoJSONToSQL Varchar ST_Geometry Converts a GeoJSON character string into an SQL geometry.
ST_RangeDiff Bigint, Bigint, Bigint, Bigint Bigint Returns the overlap, in milliseconds, between two time ranges. The first time range is determined by the values of the first two input parameters; the second time range by the values of the last two input parameters. Each input parameter is a number of milliseconds measured from the start of the UNIX epoch (01 January 1970). For example, the timestamp 2007-02-14 12:15:06.123 is equivalent to 1171451706123 milliseconds. In each time range, the first parameter (start time) must be smaller than the second parameter (end time).
ST_TimeShift Bigint, Bigint Bigint Returns a time that is calculated by adding a time shift in milliseconds (second parameter) to an input time (first parameter). The input time is a number of milliseconds measured from the start of the UNIX epoch (01 January 1970). For example, the timestamp 2007-02-14 12:15:06.123 is equivalent to 1171451706123 milliseconds. The time shift can be negative, in which case the output time will be earlier than the input time.
ST_WKBToSQL Binary ST_Geometry Converts a well-known binary (WKB) representation of a geometry to a geometry object.
ST_WKTToSQL Varchar ST_Geometry Constructs geometry objects from in input character string that contains well-known text (WKT) representations of geometries.