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

🔗 Official Documentation

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

🔗 Official Documentation

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)

🔗 Official Documentation

bing_tile_polygon

Returns the polygon representation of a given Bing tile.

Supported Signatures

function bing_tile_polygon(bingtile) returns geometry

🔗 Official Documentation

bing_tile_quadkey

Returns the quadkey of a given Bing tile.

Supported Signatures

function bing_tile_quadkey(bingtile) returns varchar

🔗 Official Documentation

bing_tile_zoom_level

Returns the zoom level of a given Bing tile.

Supported Signatures

function bing_tile_zoom_level(bingtile) returns tinyint

🔗 Official Documentation

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>

🔗 Official Documentation

convex_hull_agg

Returns the minimum convex geometry that encloses all input geometries.

Supported Signatures

function convex_hull_agg(geometry) returns geometry

🔗 Official Documentation

from_encoded_polyline

Decodes a polyline to a linestring.

Supported Signatures

function from_encoded_polyline(varchar) returns geometry

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

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)

🔗 Official Documentation

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>

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

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>

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

ST_Area

Returns the 2D Euclidean area of a geometry.

Supported Signatures

function ST_Area(geometry) returns double
function ST_Area(sphericalgeography) returns double

🔗 Official Documentation

ST_AsBinary

Returns the WKB representation of the geometry.

Supported Signatures

function ST_AsBinary(geometry) returns varbinary

🔗 Official Documentation

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

🔗 Official Documentation

ST_Boundary

Returns the closure of the combinatorial boundary of this geometry.

Supported Signatures

function ST_Boundary(geometry) returns geometry

🔗 Official Documentation

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

🔗 Official Documentation

ST_Centroid

Returns the point value that is the mathematical centroid of a geometry.

Supported Signatures

function ST_Centroid(geometry) returns geometry

🔗 Official Documentation

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

🔗 Official Documentation

ST_ConvexHull

Returns the minimum convex geometry that encloses all input geometries.

Supported Signatures

function ST_ConvexHull(geometry) returns geometry

🔗 Official Documentation

ST_CoordDim

Returns the coordinate dimension of the geometry.

Supported Signatures

function ST_CoordDim(geometry) returns tinyint

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

ST_Envelope

Returns the bounding rectangular polygon of a geometry.

Supported Signatures

function ST_Envelope(geometry) returns geometry

🔗 Official Documentation

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>

🔗 Official Documentation

ST_Equals

Returns true if the given geometries represent the same geometry.

Supported Signatures

function ST_Equals(geometry, geometry) returns boolean

🔗 Official Documentation

ST_ExteriorRing

Returns a line string representing the exterior ring of the input polygon.

Supported Signatures

function ST_ExteriorRing(geometry) returns geometry

🔗 Official Documentation

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>

🔗 Official Documentation

ST_GeometryFromText

Returns a geometry type object from WKT representation.

Supported Signatures

function ST_GeometryFromText(varchar) returns geometry

🔗 Official Documentation

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

🔗 Official Documentation

ST_GeometryType

Returns the type of the geometry.

Supported Signatures

function ST_GeometryType(geometry) returns varchar

🔗 Official Documentation

ST_GeomFromBinary

Returns a geometry type object from WKB representation.

Supported Signatures

function ST_GeomFromBinary(varbinary) returns geometry

🔗 Official Documentation

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

🔗 Official Documentation

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>

🔗 Official Documentation

ST_Intersection

Returns the geometry value that represents the point set intersection of two geometries.

Supported Signatures

function ST_Intersection(geometry, geometry) returns geometry

🔗 Official Documentation

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

🔗 Official Documentation

ST_IsClosed

Returns true if the linestring’s start and end points are coincident.

Supported Signatures

function ST_IsClosed(geometry) returns boolean

🔗 Official Documentation

ST_IsEmpty

Returns true if this Geometry is an empty geometrycollection, polygon, point etc.

Supported Signatures

function ST_IsEmpty(geometry) returns boolean

🔗 Official Documentation

ST_IsRing

Returns true if and only if the line is closed and simple.

Supported Signatures

function ST_IsRing(geometry) returns boolean

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

ST_LineFromText

Returns a geometry type linestring object from WKT representation.

Supported Signatures

function ST_LineFromText(varchar) returns geometry

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

ST_NumInteriorRing

Returns the cardinality of the collection of interior rings of a polygon.

Supported Signatures

function ST_NumInteriorRing(geometry) returns bigint

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

ST_Point

Returns a geometry type point object with the given coordinate values.

Supported Signatures

function ST_Point(double, double) returns geometry

🔗 Official Documentation

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

🔗 Official Documentation

ST_Points

Returns an array of points in a linestring.

Supported Signatures

function ST_Points(geometry) returns array<geometry>

🔗 Official Documentation

ST_Polygon

Returns a geometry type polygon object from WKT representation.

Supported Signatures

function ST_Polygon(varchar) returns geometry

🔗 Official Documentation

ST_Relate

Returns true if first geometry is spatially related to second geometry.

Supported Signatures

function ST_Relate(geometry, geometry, varchar) returns boolean

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

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

🔗 Official Documentation

ST_Union

Returns a geometry that represents the point set union of the input geometries.

Supported Signatures

function ST_Union(geometry, geometry) returns geometry

🔗 Official Documentation

ST_Within

Returns true if first geometry is completely inside second geometry.

Supported Signatures

function ST_Within(geometry, geometry) returns boolean

🔗 Official Documentation

ST_X

Returns the X coordinate of the point.

Supported Signatures

function ST_X(geometry) returns double

🔗 Official Documentation

ST_XMax

Returns X maxima of a bounding box of a geometry.

Supported Signatures

function ST_XMax(geometry) returns double

🔗 Official Documentation

ST_XMin

Returns X minima of a bounding box of a geometry.

Supported Signatures

function ST_XMin(geometry) returns double

🔗 Official Documentation

ST_Y

Returns the Y coordinate of the point.

Supported Signatures

function ST_Y(geometry) returns double

🔗 Official Documentation

ST_YMax

Returns Y maxima of a bounding box of a geometry.

Supported Signatures

function ST_YMax(geometry) returns double

🔗 Official Documentation

ST_YMin

Returns Y minima of a bounding box of a geometry.

Supported Signatures

function ST_YMin(geometry) returns double

🔗 Official Documentation

to_encoded_polyline

Encodes a linestring or multipoint to a polyline.

Supported Signatures

function to_encoded_polyline(geometry) returns varchar

🔗 Official Documentation

to_geojson_geometry

Returns the GeoJSON encoded defined by the input spherical geography.

Supported Signatures

function to_geojson_geometry(sphericalgeography) returns varchar

🔗 Official Documentation

to_geometry

Converts a SphericalGeography object to a Geometry object.

Supported Signatures

function to_geometry(sphericalgeography) returns geometry

🔗 Official Documentation

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

🔗 Official Documentation