6.20. Geospatial Functions

Presto Geospatial functions that begin with the ST_ prefix support theSQL/MM specification and are compliant with the Open Geospatial Consortium’s(OGC) OpenGIS Specifications. As such, many Presto Geospatial functionsrequire, or more accurately, assume that geometries that are operated on areboth simple and valid. For example, it does not make sense to calculate thearea of a polygon that has a hole defined outside of the polygon, or toconstruct a polygon from a non-simple boundary line.

Presto Geospatial functions support the Well-Known Text (WKT) and Well-KnownBinary (WKB) form of spatial objects:

  • POINT (0 0)
  • LINESTRING (0 0, 1 1, 1 2)
  • POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1))
  • MULTIPOINT (0 0, 1 2)
  • MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))
  • MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))
  • GEOMETRYCOLLECTION (POINT(2 3), LINESTRING (2 3, 3 4))Use ST_GeometryFromText and ST_GeomFromBinary functions to creategeometry objects from WKT or WKB. In WKT/WKB, the coordinate order is(x, y). For spherical/geospatial uses, this implies(longitude, latitude) instead of (latitude, longitude).

The basis for the Geometry type is a plane. The shortest path between twopoints on the plane is a straight line. That means calculations on geometries(areas, distances, lengths, intersections, etc) can be calculated usingcartesian mathematics and straight line vectors.

The SphericalGeography type provides native support for spatial featuresrepresented on “geographic” coordinates (sometimes called “geodetic”coordinates, or “lat/lon”, or “lon/lat”). Geographic coordinates are sphericalcoordinates expressed in angular units (degrees).

The basis for the SphericalGeography type is a sphere. The shortest pathbetween two points on the sphere is a great circle arc. That means thatcalculations on geographies (areas, distances, lengths, intersections, etc)must be calculated on the sphere, using more complicated mathematics. Moreaccurate measurements that take the actual spheroidal shape of the world intoaccount are not supported.

For SphericalGeography objects, values returned by the measurement functionsST_Distance and ST_Length are in the unit of meters; values returned byST_Area are in square meters.

Use to_spherical_geography() function to convert a geometry object togeography object. For example,ST_Distance(ST_Point(-71.0882, 42.3607), ST_Point(-74.1197, 40.6976))returns 3.4577 in the unit of the passed-in values on the euclidean plane,whileST_Distance(to_spherical_geography(ST_Point(-71.0882, 42.3607)), to_spherical_geography(ST_Point(-74.1197, 40.6976)))returns 312822.179 in meters.

Constructors

  • STAsBinary(_Geometry) → varbinary

  • Returns the WKB representation of the geometry.

  • STAsText(_Geometry) → varchar

  • 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'.

  • STGeometryFromText(_varchar) → Geometry

  • Returns a geometry type object from WKT representation.

  • STGeomFromBinary(_varbinary) → Geometry

  • Returns a geometry type object from WKB representation.

  • STLineFromText(_varchar) → LineString

  • Returns a geometry type linestring object from WKT representation.

  • STLineString(_array(Point)) → LineString

  • Returns a LineString formed from an array of points. If there are fewerthan two non-empty points in the input array, an empty LineString will bereturned. Throws an exception if any element in the array is null orempty or same as the previous one. The returned geometry may not besimple, e.g. may self-intersect or may contain duplicate vertexes dependingon the input.

  • STMultiPoint(_array(Point)) → MultiPoint

  • Returns a MultiPoint geometry object formed from the specified points.Return null if input array is empty. Throws an exception if any elementin the array is null or empty. The returned geometry may not be simpleand may contain duplicate points if input array has duplicates.

  • STPoint(_x, y) → Point

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

  • STPolygon(_varchar) → Polygon

  • Returns a geometry type polygon object from WKT representation.

  • tospherical_geography(_Geometry) → SphericalGeography

  • Converts a Geometry object to a SphericalGeography object on the sphere ofthe Earth’s radius. This function is only applicable to POINT,MULTIPOINT, LINESTRING, MULTILINESTRING, POLYGON,MULTIPOLYGON geometries defined in 2D space, or GEOMETRYCOLLECTIONof such geometries. For each point of the input geometry, it verifies thatpoint.x is within [-180.0, 180.0] and point.y is within [-90.0, 90.0], anduses them as (longitude, latitude) degrees to construct the shape of theSphericalGeography result.

  • togeometry(_SphericalGeography) → Geometry

  • Converts a SphericalGeography object to a Geometry object.

Relationship Tests

  • STContains(_Geometry, Geometry) → boolean

  • Returns true if and only if no points of the second geometry lie in theexterior of the first geometry, and at least one point of the interior ofthe first geometry lies in the interior of the second geometry.

  • STCrosses(_Geometry, Geometry) → boolean

  • Returns true if the supplied geometries have some, but not all,interior points in common.

  • STDisjoint(_Geometry, Geometry) → boolean

  • Returns true if the give geometries do not spatially intersect – ifthey do not share any space together.

  • STEquals(_Geometry, Geometry) → boolean

  • Returns true if the given geometries represent the same geometry.

  • STIntersects(_Geometry, Geometry) → boolean

  • Returns true if the given geometries spatially intersect in twodimensions (share any portion of space) and false if they do not (theyare disjoint).

  • STOverlaps(_Geometry, Geometry) → boolean

  • Returns true if the given geometries share space, are of the samedimension, but are not completely contained by each other.

  • STRelate(_Geometry, Geometry) → boolean

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

  • STTouches(_Geometry, Geometry) → boolean

  • Returns true if the given geometries have at least one point in common,but their interiors do not intersect.

  • STWithin(_Geometry, Geometry) → boolean

  • Returns true if first geometry is completely inside second geometry.

Operations

  • geometryunion(_array(Geometry)) → Geometry

  • Returns a geometry that represents the point set union of the inputgeometries. Performance of this function, in conjunction witharray_agg() to first aggregate the input geometries, may be betterthan geometry_union_agg(), at the expense of higher memoryutilization.

  • STBoundary(_Geometry) → Geometry

  • Returns the closure of the combinatorial boundary of this geometry.

  • STBuffer(_Geometry, distance) → Geometry

  • Returns the geometry that represents all points whose distance from thespecified geometry is less than or equal to the specified distance. If thepoints of the geometry are extremely close together (delta < 1e-8), thismight return an empty geometry.

  • STDifference(_Geometry, Geometry) → Geometry

  • Returns the geometry value that represents the point set difference of thegiven geometries.

  • STEnvelope(_Geometry) → Geometry

  • Returns the bounding rectangular polygon of a geometry.

  • STEnvelopeAsPts(_Geometry) -> array(Geometry)

  • Returns an array of two points: the lower left and upper right corners ofthe bounding rectangular polygon of a geometry. Returns null if inputgeometry is empty.

  • STExteriorRing(_Geometry) → Geometry

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

  • STIntersection(_Geometry, Geometry) → Geometry

  • Returns the geometry value that represents the point set intersection oftwo geometries.

  • STSymDifference(_Geometry, Geometry) → Geometry

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

  • STUnion(_Geometry, Geometry) → Geometry

  • Returns a geometry that represents the point set union of the inputgeometries.

See also: geometry_union(), geometry_union_agg()

Accessors

  • STArea(_Geometry) → double
  • Returns the 2D Euclidean area of a geometry.

For Point and LineString types, returns 0.0.For GeometryCollection types, returns the sum of the areas of the individualgeometries.

  • STArea(_SphericalGeography) → double

  • Returns the area of a polygon or multi-polygon in square meters using a spherical model for Earth.

  • STCentroid(_Geometry) → Point

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

  • STConvexHull(_Geometry) → Geometry

  • Returns the minimum convex geometry that encloses all input geometries.

  • STCoordDim(_Geometry) → bigint

  • Return the coordinate dimension of the geometry.

  • STDimension(_Geometry) → bigint

  • Returns the inherent dimension of this geometry object, which must beless than or equal to the coordinate dimension.

  • STDistance(_Geometry, Geometry) → double

  • Returns the 2-dimensional cartesian minimum distance (based on spatial ref)between two geometries in projected units.

  • STDistance(_SphericalGeography, SphericalGeography) → double

  • Returns the great-circle distance in meters between two SphericalGeography points.

  • STGeometryN(_Geometry, index) → Geometry

  • 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 :func: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.

  • STInteriorRingN(_Geometry, index) → Geometry

  • Returns the interior ring element at the specified index (indices start at 1). Ifthe given index is less than 1 or greater than the total number of interior ringsin the input geometry, returns NULL. Throws an error if the input geometry isnot a polygon.Use :func:ST_NumInteriorRing to find out the total number of elements.

  • STGeometryType(_Geometry) → varchar

  • Returns the type of the geometry.

  • STIsClosed(_Geometry) → boolean

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

  • STIsEmpty(_Geometry) → boolean

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

  • STIsSimple(_Geometry) → boolean

  • Returns true if this Geometry has no anomalous geometric points, such as self intersection or self tangency.

  • STIsRing(_Geometry) → boolean

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

  • STIsValid(_Geometry) → boolean

  • 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.

  • STLength(_Geometry) → double

  • Returns the length of a linestring or multi-linestring using Euclidean measurement on atwo dimensional plane (based on spatial ref) in projected units.

  • STLength(_SphericalGeography) → double

  • Returns the length of a linestring or multi-linestring on a spherical model of the Earth.This is equivalent to the sum of great-circle distances between adjacent points on the linestring.

  • STPointN(_LineString, index) → Point

  • 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 :func:ST_NumPoints to find out the total number of elements.

  • STPoints(_Geometry) -> array(Point)

  • Returns an array of points in a linestring.

  • STXMax(_Geometry) → double

  • Returns the X maximum of the geometry’s bounding box.

  • STYMax(_Geometry) → double

  • Returns the Y maximum of the geometry’s bounding box.

  • STXMin(_Geometry) → double

  • Returns the X minimum of the geometry’s bounding box.

  • STYMin(_Geometry) → double

  • Returns the Y minimum of the geometry’s bounding box.

  • STStartPoint(_Geometry) → point

  • Returns the first point of a LineString geometry as a Point.This is a shortcut for ST_PointN(geometry, 1).

  • STEndPoint(_Geometry) → point

  • Returns the last point of a LineString geometry as a Point.This is a shortcut for ST_PointN(geometry, ST_NumPoints(geometry)).

  • STX(_Point) → double

  • Return the X coordinate of the point.

  • STY(_Point) → double

  • Return the Y coordinate of the point.

  • STInteriorRings(_Geometry) -> array(Geometry)

  • Returns an array of all interior rings found in the input geometry, or an emptyarray if the polygon has no interior rings. Returns null if the input geometryis empty. Throws an error if the input geometry is not a polygon.

  • STNumGeometries(_Geometry) → bigint

  • Returns the number of geometries in the collection.If the geometry is a collection of geometries (e.g., GEOMETRYCOLLECTION orMULTI*), returns the number of geometries, for single geometries returns 1,for empty geometries returns 0. Note that empty geometries inside of aGEOMETRYCOLLECTION will count as a geometry; egST_NumGeometries(ST_GeometryFromText('GEOMETRYCOLLECTION(MULTIPOINT EMPTY)'))will evaluate to 1.

  • STGeometries(_Geometry) -> array(Geometry)

  • Returns an array of geometries in the specified collection. Returns a one-element arrayif the input geometry is not a multi-geometry. Returns null if input geometry is empty.

  • STNumPoints(_Geometry) → bigint

  • Returns the number of points in a geometry. This is an extension to the SQL/MMST_NumPoints function which only applies to point and linestring.

  • STNumInteriorRing(_Geometry) → bigint

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

  • simplifygeometry(_Geometry, double) → 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.

  • linelocate_point(_LineString, Point) → double

  • Returns a float between 0 and 1 representing the location of the closest point onthe LineString to the given Point, as a fraction of total 2d line length.

Returns null if a LineString or a Point is empty or null.

  • lineinterpolate_point(_LineString, double) → Geometry
  • Returns the Point on the LineString at a fractional distance given by thedouble argument. Throws an exception if the distance is not between 0 and 1.

Returns an empty Point if the LineString is empty. Returns null ifeither the LineString or double is null.

  • geometryinvalid_reason(_Geometry) → varchar

  • Returns the reason for why the input geometry is not valid.Returns null if the input is valid.

  • greatcircle_distance(_latitude1, longitude1, latitude2, longitude2) → double

  • Returns the great-circle distance between two points on Earth’s surface in kilometers.

Aggregations

  • convexhull_agg(_Geometry) → Geometry

  • Returns the minimum convex geometry that encloses all input geometries.

  • geometryunion_agg(_Geometry) → Geometry

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

Bing Tiles

These functions convert between geometries andBing tiles. ForBing tiles, x and y refer to tile_x and tile_y.

  • bingtile(_x, y, zoom_level) → BingTile

  • Creates a Bing tile object from XY coordinates and a zoom level.Zoom levels from 1 to 23 are supported.

  • bingtile(_quadKey) → BingTile

  • Creates a Bing tile object from a quadkey.

  • bingtile_at(_latitude, longitude, zoom_level) → BingTile

  • Returns a Bing tile at a given zoom level containing a point at a given latitudeand 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.

  • bingtiles_around(_latitude, longitude, zoom_level) -> array(BingTile)

  • Returns a collection of Bing tiles that surround the point specifiedby the latitude and longitude arguments at a given zoom level.

  • bingtiles_around(_latitude, longitude, zoom_level, radius_in_km) -> array(BingTile)

  • Returns a minimum set of Bing tiles at specified zoom level that cover a circle of specifiedradius in km around a specified (latitude, longitude) point.

  • bingtile_coordinates(_tile) → row

  • Returns the XY coordinates of a given Bing tile.
  • bingtile_polygon(_tile) → Geometry

  • Returns the polygon representation of a given Bing tile.

  • bingtile_quadkey(_tile) → varchar

  • Returns the quadkey of a given Bing tile.

  • bingtile_zoom_level(_tile) → tinyint

  • Returns the zoom level of a given Bing tile.

  • geometryto_bing_tiles(_geometry, zoom_level) -> array(BingTile)

  • Returns the minimum set of Bing tiles that fully covers a given geometry ata given zoom level. Zoom levels from 1 to 23 are supported.