Geospatial Functions
bing_tile
Creates a Bing tile object from XY coordinates and a zoom level. Zoom levels from 1 to 23 are supported.
Supported Signatures
function bing_tile(bigint, bigint, bigint) returns bingtile
function bing_tile(varchar) returns bingtile
bing_tile_at
Returns a Bing tile at a given zoom level containing a point at a given latitude and longitude. Latitude must be within [-85.05112878, 85.05112878] range. Longitude must be within [-180, 180] range. Zoom levels from 1 to 23 are supported.
Supported Signatures
function bing_tile_at(double, double, bigint) returns bingtile
bing_tile_coordinates
Returns the XY coordinates of a given Bing tile.
Supported Signatures
function bing_tile_coordinates(bingtile) returns row(x bigint, y bigint)
bing_tile_polygon
Returns the polygon representation of a given Bing tile.
Supported Signatures
function bing_tile_polygon(bingtile) returns geometry
bing_tile_quadkey
Returns the quadkey of a given Bing tile.
Supported Signatures
function bing_tile_quadkey(bingtile) returns varchar
bing_tile_zoom_level
Returns the zoom level of a given Bing tile.
Supported Signatures
function bing_tile_zoom_level(bingtile) returns tinyint
bing_tiles_around
Returns a collection of Bing tiles that surround the point specified by the latitude and longitude arguments at a given zoom level.
Supported Signatures
function bing_tiles_around(double, double, bigint) returns array<bingtile>
function bing_tiles_around(double, double, bigint, double) returns array<bingtile>
convex_hull_agg
Returns the minimum convex geometry that encloses all input geometries.
Supported Signatures
function convex_hull_agg(geometry) returns geometry
from_encoded_polyline
Decodes a polyline to a linestring.
Supported Signatures
function from_encoded_polyline(varchar) returns geometry
from_geojson_geometry
Returns the spherical geography type object from the GeoJSON representation stripping non geometry key/values. Feature and FeatureCollection are not supported.
Supported Signatures
function from_geojson_geometry(varchar) returns sphericalgeography
geometry_from_hadoop_shape
Returns a geometry type object from Spatial Framework for Hadoop representation.
Supported Signatures
function geometry_from_hadoop_shape(varbinary) returns geometry
geometry_invalid_reason
Returns the reason for why the input geometry is not valid. Returns NULL if the input is valid.
Supported Signatures
function geometry_invalid_reason(geometry) returns varchar
geometry_nearest_points
Returns the points on each geometry nearest the other. If either geometry is empty, return NULL. Otherwise, return a row of two Points that have the minimum distance of any two points on the geometries. The first Point will be from the first Geometry argument, the second from the second Geometry argument. If there are multiple pairs with the minimum distance, one pair is chosen arbitrarily.
Supported Signatures
function geometry_nearest_points(geometry, geometry) returns row(c0 geometry, c1 geometry)
geometry_to_bing_tiles
Returns the minimum set of Bing tiles that fully covers a given geometry at a given zoom level. Zoom levels from 1 to 23 are supported.
Supported Signatures
function geometry_to_bing_tiles(geometry, bigint) returns array<bingtile>
geometry_union
Returns a geometry that represents the point set union of the input geometries. Performance of this function, in conjunction with array_agg() to first aggregate the input geometries, may be better than geometry_union_agg(), at the expense of higher memory utilization.
Supported Signatures
function geometry_union(array<geometry>) returns geometry
geometry_union_agg
Returns a geometry that represents the point set union of all input geometries.
Supported Signatures
function geometry_union_agg(geometry) returns geometry
great_circle_distance
Returns the great-circle distance between two points on Earth’s surface in kilometers.
Supported Signatures
function great_circle_distance(double, double, double, double) returns double
line_interpolate_point
Returns a Point interpolated along a LineString at the fraction given. The fraction must be between 0 and 1, inclusive.
Supported Signatures
function line_interpolate_point(geometry, double) returns geometry
line_interpolate_points
Returns an array of Points interpolated along a LineString. The fraction must be between 0 and 1, inclusive.
Supported Signatures
function line_interpolate_points(geometry, double) returns array<geometry>
line_locate_point
Returns a float between 0 and 1 representing the location of the closest point on the LineString to the given Point, as a fraction of total 2d line length.
Supported Signatures
function line_locate_point(geometry, geometry) returns double
simplify_geometry
Returns a “simplified” version of the input geometry using the Douglas-Peucker algorithm. Will avoid creating derived geometries (polygons in particular) that are invalid.
Supported Signatures
function simplify_geometry(geometry, double) returns geometry
ST_Area
Returns the 2D Euclidean area of a geometry.
Supported Signatures
function ST_Area(geometry) returns double
function ST_Area(sphericalgeography) returns double
ST_AsBinary
Returns the WKB representation of the geometry.
Supported Signatures
function ST_AsBinary(geometry) returns varbinary
ST_AsText
Returns the WKT representation of the geometry. For empty geometries, ST_AsText(ST_LineFromText(‘LINESTRING EMPTY’)) will produce ‘MULTILINESTRING EMPTY’ and ST_AsText(ST_Polygon(‘POLYGON EMPTY’)) will produce ‘MULTIPOLYGON EMPTY’.
Supported Signatures
function ST_AsText(geometry) returns varchar
ST_Boundary
Returns the closure of the combinatorial boundary of this geometry.
Supported Signatures
function ST_Boundary(geometry) returns geometry
ST_Buffer
Returns the geometry that represents all points whose distance from the specified geometry is less than or equal to the specified distance. If the points of the geometry are extremely close together (delta < 1e-8), this might return an empty geometry.
Supported Signatures
function ST_Buffer(geometry, double) returns geometry
ST_Centroid
Returns the point value that is the mathematical centroid of a geometry.
Supported Signatures
function ST_Centroid(geometry) returns geometry
ST_Contains
Returns true if and only if no points of the second geometry lie in the exterior of the first geometry, and at least one point of the interior of the first geometry lies in the interior of the second geometry.
Supported Signatures
function ST_Contains(geometry, geometry) returns boolean
ST_ConvexHull
Returns the minimum convex geometry that encloses all input geometries.
Supported Signatures
function ST_ConvexHull(geometry) returns geometry
ST_CoordDim
Returns the coordinate dimension of the geometry.
Supported Signatures
function ST_CoordDim(geometry) returns tinyint
ST_Crosses
Returns true if the supplied geometries have some, but not all, interior points in common.
Supported Signatures
function ST_Crosses(geometry, geometry) returns boolean
ST_Difference
Returns the geometry value that represents the point set difference of the given geometries.
Supported Signatures
function ST_Difference(geometry, geometry) returns geometry
ST_Dimension
Returns the inherent dimension of this geometry object, which must be less than or equal to the coordinate dimension.
Supported Signatures
function ST_Dimension(geometry) returns tinyint
ST_Disjoint
Returns true if the give geometries do not spatially intersect – if they do not share any space together.
Supported Signatures
function ST_Disjoint(geometry, geometry) returns boolean
ST_Distance
Returns the great-circle distance in meters between two SphericalGeography points.
Supported Signatures
function ST_Distance(geometry, geometry) returns double
function ST_Distance(sphericalgeography, sphericalgeography) returns double
ST_EndPoint
Returns the last point of a LineString geometry as a Point. This is a shortcut for ST_PointN(geometry, ST_NumPoints(geometry)).
Supported Signatures
function ST_EndPoint(geometry) returns geometry
ST_Envelope
Returns the bounding rectangular polygon of a geometry.
Supported Signatures
function ST_Envelope(geometry) returns geometry
ST_EnvelopeAsPts
Returns an array of two points. the lower left and upper right corners of the bounding rectangular polygon of a geometry. Returns NULL if input geometry is empty.
Supported Signatures
function ST_EnvelopeAsPts(geometry) returns array<geometry>
ST_Equals
Returns true if the given geometries represent the same geometry.
Supported Signatures
function ST_Equals(geometry, geometry) returns boolean
ST_ExteriorRing
Returns a line string representing the exterior ring of the input polygon.
Supported Signatures
function ST_ExteriorRing(geometry) returns geometry
ST_Geometries
Returns an array of geometries in the specified collection. Returns a one-element array if the input geometry is not a multi-geometry. Returns NULL if input geometry is empty.
Supported Signatures
function ST_Geometries(geometry) returns array<geometry>
ST_GeometryFromText
Returns a geometry type object from WKT representation.
Supported Signatures
function ST_GeometryFromText(varchar) returns geometry
ST_GeometryN
Returns the geometry element at a given index (indices start at 1). If the geometry is a collection of geometries (e.g., GEOMETRYCOLLECTION or MULTI*), returns the geometry at a given index. If the given index is less than 1 or greater than the total number of elements in the collection, returns NULL. Use ST_NumGeometries() to find out the total number of elements. Singular geometries (e.g., POINT, LINESTRING, POLYGON), are treated as collections of one element. Empty geometries are treated as empty collections.
Supported Signatures
function ST_GeometryN(geometry, bigint) returns geometry
ST_GeometryType
Returns the type of the geometry.
Supported Signatures
function ST_GeometryType(geometry) returns varchar
ST_GeomFromBinary
Returns a geometry type object from WKB representation.
Supported Signatures
function ST_GeomFromBinary(varbinary) returns geometry
ST_InteriorRingN
Returns the interior ring element at the specified index (indices start at 1). If the given index is less than 1 or greater than the total number of interior rings in the input geometry, returns NULL. The input geometry must be a polygon. Use ST_NumInteriorRing() to find out the total number of elements.
Supported Signatures
function ST_InteriorRingN(geometry, bigint) returns geometry
ST_InteriorRings
Returns an array of all interior rings found in the input geometry, or an empty array if the polygon has no interior rings. Returns NULL if the input geometry is empty. The input geometry must be a polygon.
Supported Signatures
function ST_InteriorRings(geometry) returns array<geometry>
ST_Intersection
Returns the geometry value that represents the point set intersection of two geometries.
Supported Signatures
function ST_Intersection(geometry, geometry) returns geometry
ST_Intersects
Returns true if the given geometries spatially intersect in two dimensions (share any portion of space) and false if they do not (they are disjoint).
Supported Signatures
function ST_Intersects(geometry, geometry) returns boolean
ST_IsClosed
Returns true if the linestring’s start and end points are coincident.
Supported Signatures
function ST_IsClosed(geometry) returns boolean
ST_IsEmpty
Returns true if this Geometry is an empty geometrycollection, polygon, point etc.
Supported Signatures
function ST_IsEmpty(geometry) returns boolean
ST_IsRing
Returns true if and only if the line is closed and simple.
Supported Signatures
function ST_IsRing(geometry) returns boolean
ST_IsSimple
Returns true if this Geometry has no anomalous geometric points, such as self intersection or self tangency.
Supported Signatures
function ST_IsSimple(geometry) returns boolean
ST_IsValid
Returns true if and only if the input geometry is well formed. Use geometry_invalid_reason() to determine why the geometry is not well formed.
Supported Signatures
function ST_IsValid(geometry) returns boolean
ST_Length
Returns the length of a linestring or multi-linestring using Euclidean measurement on a two dimensional plane (based on spatial ref) in projected units.
Supported Signatures
function ST_Length(geometry) returns double
function ST_Length(sphericalgeography) returns double
ST_LineFromText
Returns a geometry type linestring object from WKT representation.
Supported Signatures
function ST_LineFromText(varchar) returns geometry
ST_LineString
Returns a LineString formed from an array of points. If there are fewer than two non-empty points in the input array, an empty LineString will be returned. Array elements must not be NULL or the same as the previous element. The returned geometry may not be simple, e.g. may self-intersect or may contain duplicate vertexes depending on the input.
Supported Signatures
function ST_LineString(array<geometry>) returns geometry
ST_MultiPoint
Returns a MultiPoint geometry object formed from the specified points. Returns NULL if input array is empty. Array elements must not be NULL or empty. The returned geometry may not be simple and may contain duplicate points if input array has duplicates.
Supported Signatures
function ST_MultiPoint(array<geometry>) returns geometry
ST_NumGeometries
Returns the number of geometries in the collection. If the geometry is a collection of geometries (e.g., GEOMETRYCOLLECTION or MULTI*), returns the number of geometries, for single geometries returns 1, for empty geometries returns 0.
Supported Signatures
function ST_NumGeometries(geometry) returns bigint
ST_NumInteriorRing
Returns the cardinality of the collection of interior rings of a polygon.
Supported Signatures
function ST_NumInteriorRing(geometry) returns bigint
ST_NumPoints
Returns the number of points in a geometry. This is an extension to the SQL/MM ST_NumPoints function which only applies to point and linestring.
Supported Signatures
function ST_NumPoints(geometry) returns bigint
ST_Overlaps
Returns true if the given geometries share space, are of the same dimension, but are not completely contained by each other.
Supported Signatures
function ST_Overlaps(geometry, geometry) returns boolean
ST_Point
Returns a geometry type point object with the given coordinate values.
Supported Signatures
function ST_Point(double, double) returns geometry
ST_PointN
Returns the vertex of a linestring at a given index (indices start at 1). If the given index is less than 1 or greater than the total number of elements in the collection, returns NULL. Use ST_NumPoints() to find out the total number of elements.
Supported Signatures
function ST_PointN(geometry, bigint) returns geometry
ST_Points
Returns an array of points in a linestring.
Supported Signatures
function ST_Points(geometry) returns array<geometry>
ST_Polygon
Returns a geometry type polygon object from WKT representation.
Supported Signatures
function ST_Polygon(varchar) returns geometry
ST_Relate
Returns true if first geometry is spatially related to second geometry.
Supported Signatures
function ST_Relate(geometry, geometry, varchar) returns boolean
ST_StartPoint
Returns the first point of a LineString geometry as a Point. This is a shortcut for ST_PointN(geometry, 1).
Supported Signatures
function ST_StartPoint(geometry) returns geometry
ST_SymDifference
Returns the geometry value that represents the point set symmetric difference of two geometries.
Supported Signatures
function ST_SymDifference(geometry, geometry) returns geometry
ST_Touches
Returns true if the given geometries have at least one point in common, but their interiors do not intersect.
Supported Signatures
function ST_Touches(geometry, geometry) returns boolean
ST_Union
Returns a geometry that represents the point set union of the input geometries.
Supported Signatures
function ST_Union(geometry, geometry) returns geometry
ST_Within
Returns true if first geometry is completely inside second geometry.
Supported Signatures
function ST_Within(geometry, geometry) returns boolean
ST_X
Returns the X coordinate of the point.
Supported Signatures
function ST_X(geometry) returns double
ST_XMax
Returns X maxima of a bounding box of a geometry.
Supported Signatures
function ST_XMax(geometry) returns double
ST_XMin
Returns X minima of a bounding box of a geometry.
Supported Signatures
function ST_XMin(geometry) returns double
ST_Y
Returns the Y coordinate of the point.
Supported Signatures
function ST_Y(geometry) returns double
ST_YMax
Returns Y maxima of a bounding box of a geometry.
Supported Signatures
function ST_YMax(geometry) returns double
ST_YMin
Returns Y minima of a bounding box of a geometry.
Supported Signatures
function ST_YMin(geometry) returns double
to_encoded_polyline
Encodes a linestring or multipoint to a polyline.
Supported Signatures
function to_encoded_polyline(geometry) returns varchar
to_geojson_geometry
Returns the GeoJSON encoded defined by the input spherical geography.
Supported Signatures
function to_geojson_geometry(sphericalgeography) returns varchar
to_geometry
Converts a SphericalGeography object to a Geometry object.
Supported Signatures
function to_geometry(sphericalgeography) returns geometry
to_spherical_geography
Converts a Geometry object to a SphericalGeography object on the sphere of the Earth’s radius. This function is only applicable to POINT, MULTIPOINT, LINESTRING, MULTILINESTRING, POLYGON, MULTIPOLYGON geometries defined in 2D space, or GEOMETRYCOLLECTION of such geometries. For each point of the input geometry, it verifies that point.x is within [-180.0, 180.0] and point.y is within [-90.0, 90.0], and uses them as (longitude, latitude) degrees to construct the shape of the SphericalGeography result.
Supported Signatures
function to_spherical_geography(geometry) returns sphericalgeography