ST geospatial functions

Applies to: Databricks SQL Databricks Runtime 17.1 and above

Preview

This feature is in Public Preview.

ST geospatial functions operate on objects of type GEOGRAPHY and/or GEOMETRY, or allow to construct GEOGRAPHY and GEOMETRY values from standard or popular geospatial formats, or export GEOGRAPHY and GEOMETRY values to standard or popular geospatial formats.

See also:

• Alphabetical list of ST geospatial functions

Import Databricks functions to get ST functions (Databricks Runtime)

No import needed for Databricks SQL and Spark SQL.

To import ST functions for Python or Scala in notebooks, use the following commands:

Python

Python

from pyspark.databricks.sql import functions as dbf

Scala

Scala

import com.databricks.sql.functions._

List of ST geospatial functions (Databricks SQL)

Import

Function	Description
st_geogfromgeojson(geojsonExpr)	Returns a GEOGRAPHY(4326) value from its GeoJSON representation.
st_geogfromtext(wktExpr)	Returns a GEOGRAPHY(4326) value from its WKT representation.
st_geogfromwkb(wktExpr)	Returns a GEOGRAPHY(4326) value from its WKB representation.
st_geogfromwkt(wktExpr)	Returns a GEOGRAPHY(4326) value from its WKT representation.
st_geomfromewkb(wktExpr)	Returns a GEOMETRY(ANY) value from its EWKB representation.
st_geomfromgeohash(geohash)	Returns the geohash grid box corresponding to the input geohash value as a 2D polygon geometry.
st_geomfromgeojson(geojsonExpr)	Returns a GEOMETRY(4326) value from its GeoJSON representation.
st_geomfromtext(wktExpr[, sridExpr])	Returns a GEOMETRY(srid) or GEOMETRY(ANY) value from its WKT representation, and sets its SRID value to the value of sridExpr, or 0 if sridExpr is omitted.
st_geomfromwkb(wkbExpr[, sridExpr])	Returns a GEOMETRY(srid) or GEOMETRY(ANY) value from its WKB representation, and sets its SRID value to the value of sridExpr, or 0 if sridExpr is omitted.
st_geomfromwkt(wktExpr[, sridExpr])	Returns a GEOMETRY(srid) or GEOMETRY(ANY) value from its WKT representation, and sets its SRID value to the value of sridExpr, or 0 if sridExpr is omitted.
st_pointfromgeohash(geohash)	Returns the center of the geohash grid box corresponding to the input geohash value as a 2D point geometry.
to_geography(georepExpr)	Returns a GEOGRAPHY(ANY) value from its GeoJSON, WKB, or WKT representation.
to_geometry(georepExpr)	Returns a GEOMETRY(ANY) value from its EWKB, GeoJSON, WKB, or WKT representation.
try_to_geography(georepExpr)	Returns a GEOGRAPHY(ANY) value from its GeoJSON, WKB, or WKT representation, or NULL if the input representation is invalid.
try_to_geometry(georepExpr)	Returns a GEOMETRY(ANY) value from its EWKB, GeoJSON, WKB, or WKT representation, or NULL if the input representation is invalid.

Export

Function	Description
st_asbinary(geoExpr)	Returns the WKB representation of the input GEOGRAPHY or GEOMETRY value as a BINARY value.
st_asewkb(geoExpr)	Returns the EWKB representation of the input GEOMETRY value as a BINARY value.
st_asgeojson(geoExpr)	Returns the GeoJSON representation of the input GEOGRAPHY or GEOMETRY value as a STRING value.
st_asewkt(geoExpr)	Returns the EWKT representation of the input GEOGRAPHY or GEOMETRY value as a STRING value.
st_astext(geoExpr)	Returns the WKT representation of the input GEOGRAPHY or GEOMETRY value as a STRING value.
st_aswkb(geoExpr)	Returns the WKB representation of the input GEOGRAPHY or GEOMETRY value as a BINARY value.
st_aswkt(geoExpr)	Returns the WKT representation of the input GEOGRAPHY or GEOMETRY value as a STRING value.
st_geohash(geo [, precision])	Returns the geohash of the input geometry at the given precision.

Measurements

Function	Description
st_area(geoExpr)	Returns the area of the input GEOGRAPHY or GEOMETRY value.
st_distance(geoExpr1, geoExpr2)	Returns the 2D Cartesian distance between the two input GEOMETRIES.
st_distancesphere(geoExpr1, geoExpr2)	Returns the spherical distance (in meters) between two point GEOMETRIES, measured on a sphere whose radius is the mean radius of the WGS84 ellipsoid.
st_distancespheroid(geoExpr1, geoExpr2)	Returns the geodesic distance (in meters) between two point GEOMETRIES on the WGS84 ellipsoid.
st_length(geoExpr)	Returns the length of the input GEOGRAPHY or GEOMETRY value.
st_perimeter(geoExpr)	Returns the perimeter of the input GEOGRAPHY or GEOMETRY value.

Constructors

Function	Description
st_makeline(geoArray)	Returns a linestring GEOMETRY whose points are the non-empty points of the geometries in the input array of geometries, which are expected to be points, linestrings, or multipoints.
st_makepolygon(outer[, innerArray])	Constructs a polygon GEOMETRY from the input outer boundary and optional array of inner boundaries, represented as closed linestrings.
st_point(x, y [, srid])	Returns a point GEOMETRY with the given x and y coordinates and SRID value (if provided).

Accessors

Function	Description
st_dimension(geoExpr)	Returns the topological dimension of the 2D projection of the GEOMETRY.
st_endpoint(geoExpr)	Returns the last point of the input GEOGRAPHY or GEOMETRY value, if the input geospatial value is a non-empty linestring.
st_envelope(geoExpr)	Returns a 2D Cartesian geometry representing the 2D axis-aligned minimum bounding box (envelope) of the input geometry.
st_envelope_agg(geoCol)	Returns the envelope of all the geometries in the column, or NULL if the column has zero rows, or contains only NULL values.
st_exteriorring(geoExpr)	Returns the exterior ring of the input polygon GEOGRAPHY or GEOMETRY as a linestring.
st_geometryn(geo, n)	Returns the 1-based n-th element of the input geometry as a GEOMETRY value.
st_geometrytype(geoExpr)	Returns the type of the input GEOGRAPHY or GEOMETRY value as a string.
st_isempty(geoExpr)	Returns true if the input GEOGRAPHY or GEOMETRY value does not contain any non-empty points.
st_m(geoExpr)	Returns the M coordinate of the input point GEOMETRY, or NULL if the point is empty or does not have an M coordinate.
st_ndims(geoExpr)	Returns the coordinate dimension of the input GEOGRAPHY or GEOMETRY value.
st_npoints(geoExpr)	Returns the number of non-empty points in the input GEOGRAPHY or GEOMETRY value.
st_numgeometries(geoExpr)	Returns the number of geometries in the input GEOMETRY value.
st_pointn(geoExpr, indexExpr)	Returns the n-th point from the input linestring GEOGRAPHY or GEOMETRY.
st_startpoint(geoExpr)	Returns the first point of the input GEOGRAPHY or GEOMETRY value, if the input geospatial value is a non-empty linestring.
st_x(geoExpr)	Returns the X coordinate of the input point GEOMETRY, or NULL if the point is empty.
st_xmax(geoExpr)	Returns the maximum X coordinate of the input GEOMETRY, or NULL if the geometry is empty.
st_xmin(geoExpr)	Returns the minimum X coordinate of the input GEOMETRY, or NULL if the geometry is empty.
st_y(geoExpr)	Returns the Y coordinate of the input point GEOMETRY, or NULL if the point is empty.
st_ymax(geoExpr)	Returns the maximum Y coordinate of the input GEOMETRY, or NULL if the geometry is empty.
st_ymin(geoExpr)	Returns the minimum Y coordinate of the input GEOMETRY, or NULL if the geometry is empty.
st_z(geoExpr)	Returns the Z coordinate of the input point GEOMETRY, or NULL if the point is empty or does not have a Z coordinate.
st_zmax(geoExpr)	Returns the maximum Z coordinate of the input GEOMETRY, or NULL if the geometry is empty or does not have a Z coordinate.
st_zmin(geoExpr)	Returns the minimum Z coordinate of the input GEOMETRY, or NULL if the geometry is empty or does not have a Z coordinate.

Editors

Function	Description
st_addpoint(geo1Expr, geo2Expr[, indexExpr])	Adds a new point to the n-th position in the input linestring GEOGRAPHY or GEOMETRY.
st_flipcoordinates(geoExpr)	Swaps X and Y coordinates of the input geometry.
st_multi(geoExpr)	Returns the input GEOGRAPHY or GEOMETRY value as an equivalent multi-geospatial value.
st_removepoint(geoExpr, indexExpr)	Removes the n-th point from the input linestring GEOGRAPHY or GEOMETRY.
st_reverse(geoExpr)	Reverses the input GEOGRAPHY or GEOMETRY value.
st_setpoint(geo1Expr, indexExpr, geo2Expr)	Sets the n-th point of the input linestring GEOGRAPHY or GEOMETRY.

Geometry Validation

Function	Description
st_isvalid(geoExpr)	Returns true if the input GEOMETRY is a valid geometry in the OGC sense.

Spatial Reference System Functions

Function	Description
st_setsrid(geo, srid)	Returns a new GEOMETRY value whose SRID is the specified SRID value.
st_srid(geoExpr)	Returns the SRID of the input GEOGRAPHY or GEOMETRY value.
st_transform(geo, srid)	Transforms the X and Y coordinates of the input GEOMETRY from the current coordinate reference system to the coordinate reference system described by the provided SRID value.

Distance Relationships

Function	Description
st_dwithin(geo1, geo2, d)	Returns true if the 2D Cartesian distance between the two input geometries is smaller than or equal to the input distance.

Topological Relationships

Function	Description
st_contains(geo1, geo2)	Returns true if the first GEOMETRY contains the second GEOMETRY.
st_covers(geoExpr1, geoExpr2)	Returns true if the first GEOMETRY covers the second GEOMETRY.
st_disjoint(geoExpr1, geoExpr2)	Returns true if the two GEOMETRIES are disjoint.
st_equals(geo1, geo2)	Returns true if the two geometries are geometrically equal, that is if they correspond to the same point set.
st_intersects(geo1, geo2)	Returns true if the two geometries intersect.
st_touches(geo1, geo2)	Returns true if the two geometries touch each other.
st_within(geo1, geo2)	Returns true if the first GEOMETRY is within the second GEOMETRY.

Overlay Functions

Function	Description
st_difference(geoExpr1, geoExpr2)	Returns the point-set difference of the two input GEOMETRIES as a 2D GEOMETRY.
st_intersection(geo1, geo2)	Returns the point-set intersection of the two input geometries as a 2D geometry.
st_union(geo1, geo2)	Returns the point-set union of the two input geometries as a 2D geometry.
st_union_agg(geoCol)	Returns the point-wise union of all the geometries in the column, or NULL if the column is zero rows, or contains only NULL values.

Affine Transformations

Function	Description
st_rotate(geoExpr, rotationAngle)	Rotates the input GEOMETRY around the Z axis by the given rotation angle (in radians).
st_scale(geoExpr, xfactor, yfactor[, zfactor])	Scales the input GEOMETRY in the X, Y, and, if specified, Z directions using the provided scaling factors.
st_translate(geoExpr, xoffset, yoffset[, zoffset])	Translates the input GEOMETRY in the X, Y, and, if specified, Z directions using the provided offsets.

Geometry Processing

Function	Description
st_buffer(geoExpr, radiusExpr)	Returns the buffer of the input GEOMETRY using the specified radius.
st_centroid(geoExpr)	Returns the centroid of the input GEOMETRY as a 2D point GEOMETRY.
st_concavehull(geoExpr, lengthRatioExpr[, allowHolesExpr])	Returns the concave hull of the input GEOMETRY as a GEOMETRY using the specified length ratio.
st_convexhull(geoExpr)	Returns the convex hull of the input GEOMETRY as a GEOMETRY.
st_simplify(geo, tolerance)	Simplifies the input GEOMETRY using the Douglas-Peucker algorithm.