Spatial functions

This page lists every Spark SQL spatial function: 1 works on Databasin Sail as in Apache Spark, 2 work with a difference noted below and 2 aren't supported. It also lists the 12 spatial functions Databasin Sail adds, named as in Databricks SQL. Each function shows its status on Sail, its usage, and examples; for Spark's functions, the examples whose results on Sail match Spark's.

Sail keeps spatial values in Spark's GEOMETRY and GEOGRAPHY types. Build one with st_point or st_geomfromtext, measure with st_distance or st_distancesphere, and test with st_contains, st_within and st_intersects. A cluster on an older build gets the Databasin functions when it restarts.

• Coordinates: x is longitude and y is latitude. The SRID is 0 (no coordinate system, the default), 4326 (WGS 84 longitude and latitude) or 3857 (Web Mercator).
• Plane or sphere: st_distance and the predicates work in the plane, in the coordinates' own units, and refuse GEOGRAPHY values. For meters between longitude/latitude points, use st_distancesphere.
• Two dimensions: measures and predicates use x and y only; st_astext keeps Z and M.
• Reading a value: a query result shows a geometry as its SRID and WKB bytes; wrap it in st_astext to read it as text. PySpark gets Geometry objects.
• Joins: a join on st_contains or st_intersects compares every pair of rows. On large tables, narrow both sides first, for example with a longitude/latitude box.

Every supported function also works from PySpark through pyspark.sql.functions when PySpark has a wrapper for it. The Sail SQL reference lists every function page.

Geospatial

st_asbinary · st_astext · st_aswkt · st_contains · st_distance · st_distancesphere · st_geogfromwkb · st_geomfromtext · st_geomfromwkb · st_geomfromwkt · st_intersects · st_point · st_setsrid · st_srid · st_within · st_x · st_y

st_asbinary

Supported, differs from Spark. New in Spark 4.1.0. st_asbinary(geo) works; the endianness argument (st_asbinary(geo, 'XDR'/'NDR')) fails.

st_asbinary(geo[, endianness]): Returns the geospatial value (value of type GEOGRAPHY or GEOMETRY) in WKB format using the specified endianness ('NDR' for little-endian, 'XDR' for big-endian), if provided. Defaults to little-endian encoding.

• geo: A geospatial value, either a GEOGRAPHY or a GEOMETRY.
• endianness: The optional endianness of the output WKB, 'NDR' for little-endian (default) or 'XDR' for big-endian.

SELECT hex(st_asbinary(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040')));
-- 0101000000000000000000F03F0000000000000040

st_astext

Supported. A Databasin Sail function, named as in Databricks SQL; Apache Spark doesn't have it.

st_astext(geo): Returns the Well-Known Text of a GEOMETRY or GEOGRAPHY value, Z and M coordinates included.

• geo: A GEOMETRY value, or WKB as BINARY.

SELECT st_astext(st_point(1.5, -2));
-- POINT(1.5 -2)

st_aswkt

Supported. A Databasin Sail function, named as in Databricks SQL; Apache Spark doesn't have it.

st_aswkt(geo): The same as st_astext.

• geo: A GEOMETRY value, or WKB as BINARY.

SELECT st_aswkt(st_geomfromwkt('LINESTRING(0 0, 3 4)'));
-- LINESTRING(0 0,3 4)

st_contains

Supported. A Databasin Sail function, named as in Databricks SQL; Apache Spark doesn't have it. Works in the plane on GEOMETRY values; both values need the same SRID, and a GEOGRAPHY value is refused. An empty value gives false.

st_contains(geo1, geo2): Returns true if geo1 contains geo2: no point of geo2 lies outside geo1, and at least one lies inside it. A point on the boundary is not contained.

• geo1: A GEOMETRY value.
• geo2: A GEOMETRY value with the same SRID.

SELECT st_contains(st_geomfromtext('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'), st_point(5, 5));
-- true

SELECT st_contains(st_geomfromtext('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'), st_point(10, 5));
-- false

st_distance

Supported. A Databasin Sail function, named as in Databricks SQL; Apache Spark doesn't have it. For meters between longitude/latitude points, use st_distancesphere. A GEOGRAPHY value is refused.

st_distance(geo1, geo2): Returns the shortest distance between two GEOMETRY values in the plane, in the units of their coordinates, or NULL when either is empty.

• geo1: A GEOMETRY value.
• geo2: A GEOMETRY value with the same SRID.

SELECT st_distance(st_point(0, 0), st_point(3, 4));
-- 5.0

SELECT st_distance(st_geomfromtext('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'), st_point(13, 14));
-- 5.0

st_distancesphere

Supported. A Databasin Sail function, named as in Databricks SQL; Apache Spark doesn't have it. Both values must be POINTs with longitude in [-180, 180] and latitude in [-90, 90]. SRID 3857 is refused. A sphere, not the WGS 84 ellipsoid: within about 0.5% of the true distance.

st_distancesphere(geo1, geo2): Returns the distance in meters between two points of longitude and latitude, on a sphere of the earth's mean radius (6,371,008.8 meters).

• geo1: A POINT: longitude, latitude.
• geo2: A POINT: longitude, latitude.

SELECT st_distancesphere(st_point(0, 0), st_point(0, 1));
-- 111195.0802335329

SELECT st_distancesphere(st_point(-94.7139, 39.2976), st_point(-90.37, 38.7487));
-- 380146.3028027733

st_geogfromwkb

Supported. New in Spark 4.1.0. Results match Spark; the auto-generated result column name can differ in some examples.

st_geogfromwkb(wkb): Parses the WKB description of a geography and returns the corresponding GEOGRAPHY value.

• wkb: A BINARY value in WKB format, representing a GEOGRAPHY value.

SELECT hex(st_asbinary(st_geogfromwkb(X'0101000000000000000000F03F0000000000000040')));
-- 0101000000000000000000F03F0000000000000040

st_geomfromtext

Supported. A Databasin Sail function, named as in Databricks SQL; Apache Spark doesn't have it. Refuses non-finite coordinates, a one-point LINESTRING, and a ring that isn't closed or has fewer than 4 points. EMPTY geometries are allowed.

st_geomfromtext(wkt[, srid]): Returns the GEOMETRY that the Well-Known Text wkt describes: a POINT, LINESTRING, POLYGON, their MULTI forms or a GEOMETRYCOLLECTION, with or without Z and M.

• wkt: Well-Known Text, for example 'POINT(1 2)'.
• srid: Optional, a constant: 0 (the default), 3857 or 4326.

SELECT st_astext(st_geomfromtext('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));
-- POLYGON((0 0,10 0,10 10,0 10,0 0))

st_geomfromwkb

Supported, differs from Spark. New in Spark 4.1.0. st_geomfromwkb(wkb) works; the SRID argument (st_geomfromwkb(wkb, srid)) fails, and st_srid is not implemented.

st_geomfromwkb(wkb[, srid]): Parses the WKB description of a geometry and returns the corresponding GEOMETRY value.

• wkb: A BINARY value in WKB format, representing a GEOMETRY value.
• srid: The optional SRID value of the geometry. Default is 0.

SELECT hex(st_asbinary(st_geomfromwkb(X'0101000000000000000000F03F0000000000000040')));
-- 0101000000000000000000F03F0000000000000040

st_geomfromwkt

Supported. A Databasin Sail function, named as in Databricks SQL; Apache Spark doesn't have it.

st_geomfromwkt(wkt[, srid]): The same as st_geomfromtext.

• wkt: Well-Known Text.
• srid: Optional, a constant: 0 (the default), 3857 or 4326.

SELECT st_aswkt(st_geomfromwkt('LINESTRING(0 0, 3 4)'));
-- LINESTRING(0 0,3 4)

st_intersects

Supported. A Databasin Sail function, named as in Databricks SQL; Apache Spark doesn't have it. Works in the plane on GEOMETRY values; both values need the same SRID, and a GEOGRAPHY value is refused. An empty value gives false.

st_intersects(geo1, geo2): Returns true if the two values share at least one point, boundary included.

• geo1: A GEOMETRY value.
• geo2: A GEOMETRY value with the same SRID.

SELECT st_intersects(st_geomfromtext('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'), st_point(10, 5));
-- true

st_point

Supported. A Databasin Sail function, named as in Databricks SQL; Apache Spark doesn't have it. Coordinates must be finite numbers; NULL gives NULL.

st_point(x, y[, srid]): Returns the point at x, y as a GEOMETRY. For longitude and latitude, x is the longitude, y the latitude and srid 4326.

• x: The x coordinate (longitude): a number.
• y: The y coordinate (latitude): a number.
• srid: Optional, a constant: 0 (the default, no coordinate system), 3857 (Web Mercator) or 4326 (WGS 84 longitude and latitude).

SELECT st_astext(st_point(1.5, -2));
-- POINT(1.5 -2)

SELECT st_astext(st_point(-94.7139, 39.2976, 4326));
-- POINT(-94.7139 39.2976)

st_setsrid

Not supported. New in Spark 4.1.0. Not implemented.

st_setsrid(geo, srid): Returns a new GEOGRAPHY or GEOMETRY value whose SRID is the specified SRID value.

• geo: A GEOGRAPHY or GEOMETRY value.
• srid: The new SRID value of the geography or geometry.

st_srid

Not supported. New in Spark 4.1.0. Not implemented.

st_srid(geo): Returns the SRID of the input GEOGRAPHY or GEOMETRY value.

• geo: A GEOGRAPHY or GEOMETRY value.

st_within

Supported. A Databasin Sail function, named as in Databricks SQL; Apache Spark doesn't have it. Works in the plane on GEOMETRY values; both values need the same SRID, and a GEOGRAPHY value is refused. An empty value gives false.

st_within(geo1, geo2): Returns true if geo1 lies within geo2: the same as st_contains(geo2, geo1).

• geo1: A GEOMETRY value.
• geo2: A GEOMETRY value with the same SRID.

SELECT st_within(st_point(5, 5), st_geomfromtext('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));
-- true

st_x

Supported. A Databasin Sail function, named as in Databricks SQL; Apache Spark doesn't have it. Any geometry other than a POINT is an error.

st_x(geo): Returns the x coordinate (longitude) of a POINT, or NULL for an empty point.

• geo: A GEOMETRY value, or WKB as BINARY.

SELECT st_x(st_point(3, 4));
-- 3.0

st_y

Supported. A Databasin Sail function, named as in Databricks SQL; Apache Spark doesn't have it. Any geometry other than a POINT is an error.

st_y(geo): Returns the y coordinate (latitude) of a POINT, or NULL for an empty point.

• geo: A GEOMETRY value, or WKB as BINARY.

SELECT st_y(st_point(3, 4));
-- 4.0

For Spark's functions, function names, usage text, argument descriptions and examples are from the Apache Spark SQL built-in functions documentation, Copyright The Apache Software Foundation, licensed under the Apache License, Version 2.0. Apache Spark is a trademark of the Apache Software Foundation. Sail status and notes, and the text and examples of the functions Databasin adds, are from Databasin.

Where to go next