The following SQL functions are available for SedonaDB.
You can query data directly from files and URLs by treating them like database tables. This feature supports formats like Parquet, CSV, and JSON.
To query a file, place its path or URL in single quotes within the FROM clause.
# Query a remote Parquet file directly "SELECT * FROM 'https://raw.githubusercontent.com/geoarrow/geoarrow-data/v0.2.0/natural-earth/files/natural-earth_cities_geo.parquet'").show()
Return the statistics of geometries for the input geometry.
ST_Analyze_Aggr (A: Geometry)
SELECT ST_Analyze_Aggr(ST_GeomFromText('MULTIPOINT(1.1 101.1,2.1 102.1,3.1 103.1,4.1 104.1,5.1 105.1,6.1 106.1,7.1 107.1,8.1 108.1,9.1 109.1,10.1 110.1)'))
Return the area of a geometry.
ST_Area (A: Geometry)
SELECT ST_Area(ST_GeomFromWKT('POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))'));
Return the Well-Known Binary representation of a geometry or geography. This function also has the alias ST_AsWKB.
ST_AsBinary (A: Geometry)
SELECT ST_AsBinary(ST_Point(1.0, 2.0));
Return the Well-Known Text string representation of a geometry or geography.
ST_AsText (A: Geometry)
SELECT ST_AsText(ST_Point(1.0, 2.0))
Returns a geometry that represents all points whose distance from the input geometry is less than or equal to a specified distance.
ST_Buffer (A: Geometry, distance: Double)
SELECT ST_Buffer(ST_GeomFromText('POLYGON ((10 10, 11 10, 10 11, 10 10))'), 1.0);
Returns the centroid of geom.
ST_Centroid (A: Geometry)
SELECT ST_AsText(ST_Centroid(ST_GeomFromWKT('POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))')));
Aggregates a set of geometries into a single GeometryCollection, MultiPoint, MultiLineString, or MultiPolygon. If all input geometries are of the same type (e.g., all points), it creates a multi-geometry of that type. If the geometries are of mixed types, it returns a GeometryCollection.
ST_Collect (geom: Geometry)
SELECT ST_Collect(ST_GeomFromWKT('MULTIPOINT (0 1, 10 11)'))
Return true if geomA contains geomB.
ST_Contains (A: Geometry, B: Geometry)
SELECT ST_Contains(ST_Point(0.25 0.25), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Return true if geomA is covered by geomB.
ST_CoveredBy (A: Geometry, B: Geometry)
SELECT ST_CoveredBy(ST_Point(0.25 0.25), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Return true if geomA covers geomB.
ST_Covers (A: Geometry, B: Geometry)
SELECT ST_Covers(ST_Point(0.25 0.25), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Computes the difference between geomA and geomB.
ST_Difference (A: Geometry, B: Geometry)
SELECT ST_Difference(ST_GeomFromText('POLYGON ((1 1, 11 1, 1 11, 0 0))'), ST_GeomFromText('POLYGON ((0 0, 10 0, 0 10, 0 0))')) AS val
Return the dimension of the geometry.
ST_Dimension (A: Geometry)
SELECT ST_Dimension(ST_GeomFromWKT('POLYGON ((0 0, 1 0, 0 1, 0 0))'));
Return true if geomA is disjoint from geomB.
ST_Disjoint (A: Geometry, B: Geometry)
SELECT ST_Disjoint(ST_Point(0.25 0.25), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Calculates the distance between geomA and geomB.
ST_Distance (A: Geometry, B: Geometry)
SELECT ST_Distance(ST_GeomFromText('POLYGON ((10 10, 11 10, 10 11, 10 10))'), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Calculates the spherical distance between geomA and geomB.
ST_DistanceSphere (A: Geometry, B: Geometry)
SELECT ST_DistanceSphere(ST_GeomFromText('POLYGON ((10 10, 11 10, 10 11, 10 10))'), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Calculates the spheroidal (ellipsoidal) distance between geomA and geomB.
ST_DistanceSpheroid (A: Geometry, B: Geometry)
SELECT ST_DistanceSpheroid(ST_GeomFromText('POLYGON ((10 10, 11 10, 10 11, 10 10))'), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Returns true if two geometries are within a specified distance of each other.
ST_DWithin (A: Geometry, B: Geometry, distance: Double)
SELECT ST_DWithin(ST_Point(0.25, 0.25), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))'), 0.5);
Returns the bounding box (envelope) of a geometry as a new geometry. The resulting geometry represents the minimum bounding rectangle that encloses the input geometry. Depending on the input, the output can be a Point, LineString, or Polygon.
ST_Envelope (A: Geometry)
SELECT ST_Envelope(ST_Point(1.0, 2.0))
An aggregate function that returns the collective bounding box (envelope) of a set of geometries.
ST_Envelope_Aggr (geom: Geometry)
-- Create a table with geometries and calculate the aggregate envelope WITH shapes(geom) AS ( VALUES (ST_GeomFromWKT('POINT (0 1)')), (ST_GeomFromWKT('POINT (10 11)')) ) SELECT ST_AsText(ST_Envelope_Aggr(geom)) FROM shapes; -- Returns: POLYGON ((0 1, 0 11, 10 11, 10 1, 0 1))
Return true if geomA equals geomB.
ST_Equals (A: Geometry, B: Geometry)
SELECT ST_Equals(ST_Point(0.25 0.25), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Returns a new geometry with the X and Y coordinates of each vertex swapped. This is useful for correcting geometries that have been created with longitude and latitude in the wrong order.
ST_FlipCoordinates (A: geometry)
SELECT ST_FlipCoordinates(df.geometry)
Calculates the Frechet distance between geomA and geomB.
ST_FrechetDistance (A: Geometry, B: Geometry)
SELECT ST_FrechetDistance(ST_GeomFromText('POLYGON ((10 10, 11 10, 10 11, 10 10))'), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Construct a Geometry from WKB.
ST_GeomFromWKB (Wkb: Binary)
-- Creates a POINT(1 2) geometry from its WKB representation SELECT ST_AsText(ST_GeomFromWKB(FROM_HEX('0101000000000000000000F03F0000000000000040')));
Construct a Geometry from WKT. This function also has the alias ST_GeomFromText.
ST_GeomFromWKT (Wkt: String)
SELECT ST_AsText(ST_GeomFromWKT('POINT (30 10)'));
Return the type of a geometry.
ST_GeometryType (A: Geometry)
SELECT ST_GeometryType(ST_GeomFromWKT('POLYGON ((0 0, 1 0, 0 1, 0 0))'))
Return true if the geometry has a M dimension.
ST_HasM (A: Geometry)
SELECT ST_HasM(ST_GeomFromWKT('POLYGON ((0 0, 1 0, 0 1, 0 0))'))
Return true if the geometry has a Z dimension.
ST_HasZ (A: Geometry)
SELECT ST_HasZ(ST_GeomFromWKT('POLYGON ((0 0, 1 0, 0 1, 0 0))'))
Calculates the Hausdorff distance between geomA and geomB.
ST_HausdorffDistance (A: Geometry, B: Geometry)
SELECT ST_HausdorffDistance(ST_GeomFromText('POLYGON ((10 10, 11 10, 10 11, 10 10))'), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Computes the intersection between geomA and geomB.
ST_Intersection (A: Geometry, B: Geometry)
SELECT ST_Intersection(ST_GeomFromText('POLYGON ((1 1, 11 1, 1 11, 0 0))'), ST_GeomFromText('POLYGON ((0 0, 10 0, 0 10, 0 0))')) AS val
An aggregate function that returns the geometric intersection of all geometries in a set.
ST_Intersection_Aggr (geom: Geometry)
-- Create a table with overlapping polygons and find their common intersection WITH shapes(geom) AS ( VALUES (ST_GeomFromWKT('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))')), (ST_GeomFromWKT('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))')) ) SELECT ST_AsText(ST_Intersection_Aggr(geom)) FROM shapes; -- Returns: POLYGON ((1 1, 1 2, 2 2, 2 1, 1 1))
Return true if geomA intersects geomB.
ST_Intersects (A: Geometry, B: Geometry)
SELECT ST_Intersects(ST_Point(0.25 0.25), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Return true if the geometry is empty.
ST_IsEmpty (A: Geometry)
SELECT ST_IsEmpty(ST_GeomFromWKT('POLYGON EMPTY'));
Return true if geomA finds k nearest neighbors from geomB.
ST_KNN (A: Geometry, B: Geometry, k: Integer, use_spheroid: Boolean)
SELECT * FROM table1 a JOIN table2 b ON ST_KNN(a.geom, b.geom, 5, false)
Returns the length of geom. This function only supports LineString, MultiLineString, and GeometryCollections containing linear geometries. Use ST_Perimeter for polygons.
ST_Length (A: Geometry)
SELECT ST_Length(ST_GeomFromWKT('LINESTRING(0 0, 10 0)'));
Returns the M (measure) coordinate of a Point geometry. If the geometry does not have an M value, it returns NULL.
ST_M (A: Point)
SELECT ST_M(ST_Point(1.0, 2.0))
Creates a LineString from two or more input Point, MultiPoint, or LineString geometries. The function connects the input geometries in the order they are provided to form a single continuous line.
ST_MakeLine (g1: Geometry or Geography, g2: Geometry or Geography)
Point, MultiPoint, or LineString geometry or geography.Point, MultiPoint, or LineString geometry or geography.SELECT ST_MakeLine(ST_Point(0, 1), ST_Point(2, 3)) as geom
Calculates the maximum distance between geomA and geomB.
ST_MaxDistance (A: Geometry, B: Geometry)
SELECT ST_MaxDistance(ST_GeomFromText('POLYGON ((10 10, 11 10, 10 11, 10 10))'), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Returns the maximum M (measure) value from a geometry's bounding box.
ST_MMax (A: Geometry)
SELECT ST_MMax(ST_GeomFromWKT('POLYGON ((0 0, 1 0, 0 1, 0 0))'))
Returns the minimum M-coordinate (measure) of a geometry's bounding box.
ST_MMin (A: Geometry)
SELECT ST_MMin(ST_GeomFromWKT('LINESTRING ZM (1 2 3 4, 5 6 7 8)')); -- Returns: 4
This function calculates the 2D perimeter of a given geometry. It supports Polygon, MultiPolygon, and GeometryCollection geometries (as long as the GeometryCollection contains polygonal geometries). For other types, it returns 0. To measure lines, use ST_Length.
To get the perimeter in meters, set use_spheroid to true. This calculates the geodesic perimeter using the WGS84 spheroid. When using use_spheroid, the lenient parameter defaults to true, assuming the geometry uses EPSG:4326. To throw an exception instead, set lenient to false.
ST_Perimeter(geom: Geometry) ST_Perimeter(geom: Geometry, use_spheroid: Boolean) ST_Perimeter(geom: Geometry, use_spheroid: Boolean, lenient: Boolean = True)
SELECT ST_Perimeter(ST_GeomFromWKT('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));
Construct a Point Geometry from X and Y.
ST_Point (x: Double, y: Double)
SELECT ST_AsText(ST_Point(-74.0060, 40.7128));
Constructs a Point with an M (measure) coordinate from X, Y, and M values.
ST_PointM (x: Double, y: Double, m: Double)
SELECT ST_PointM(-64.36, 45.09, 50.0)
Constructs a Point with a Z (elevation) coordinate from X, Y, and Z values.
ST_PointZ (x: Double, y: Double, z: Double)
SELECT ST_PointZ(-64.36, 45.09, 100.0)
Constructs a Point with both Z (elevation) and M (measure) coordinates from X, Y, Z, and M values.
ST_PointZM (x: Double, y: Double, z: Double, m: Double)
SELECT ST_PointZM(-64.36, 45.09, 100.0, 50.0)
Sets the spatial reference system identifier (SRID) of a geometry. This only changes the metadata; it does not transform the coordinates.
ST_SetSRID (geom: Geometry, srid: Integer)
SELECT ST_SetSRID(ST_GeomFromWKT('POINT (-64.363049 45.091501)'), 4326);
Returns the Spatial Reference System Identifier (SRID) of a geometry. If the geometry does not have an SRID, it returns 0.
ST_SRID (geom: Geometry)
SELECT ST_SRID(polygon)
Computes the symmetric difference between geomA and geomB.
ST_SymDifference (A: Geometry, B: Geometry)
SELECT ST_SymDifference(ST_GeomFromText('POLYGON ((1 1, 11 1, 1 11, 0 0))'), ST_GeomFromText('POLYGON ((0 0, 10 0, 0 10, 0 0))')) AS val
Return true if geomA touches geomB.
ST_Touches (A: Geometry, B: Geometry)
SELECT ST_Touches(ST_Point(0.25 0.25), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Transforms the coordinates of a geometry from a source Coordinate Reference System (CRS) to a target CRS.
If the source CRS is not specified, it will be read from the geometry's metadata. Sedona ensures that coordinates are handled in longitude/latitude order for geographic CRS transformations.
ST_Transform (A: Geometry, TargetCRS: String) ST_Transform (A: Geometry, SourceCRS: String, TargetCRS: String)
-- Transform a WGS84 polygon to UTM zone 49N SELECT ST_Transform(ST_SetSRID(ST_GeomFromWkt('POLYGON((170 50,170 72,-130 72,-130 50,170 50))'), 4326), 'EPSG:32649');
Computes the union between geomA and geomB.
ST_Union (A: Geometry, B: Geometry)
SELECT ST_Union(ST_GeomFromText('POLYGON ((1 1, 11 1, 1 11, 0 0))'), ST_GeomFromText('POLYGON ((0 0, 10 0, 0 10, 0 0))')) AS val
An aggregate function that returns the geometric union of all geometries in a set.
ST_Union_Aggr (geom: Geometry)
-- Create a table with two separate polygons and unite them into a single multipolygon WITH shapes(geom) AS ( VALUES (ST_GeomFromWKT('POLYGON ((0 0, 1 0, 1 1, 0 1, 0 0))')), (ST_GeomFromWKT('POLYGON ((2 2, 3 2, 3 3, 2 3, 2 2))')) ) SELECT ST_AsText(ST_Union_Aggr(geom)) FROM shapes; -- Returns: MULTIPOLYGON (((2 2, 3 2, 3 3, 2 3, 2 2)), ((0 0, 1 0, 1 1, 0 1, 0 0)))
Return true if geomA is fully contained by geomB.
ST_Within (A: Geometry, B: Geometry)
SELECT ST_Within(ST_Point(0.25 0.25), ST_GeomFromText('POLYGON ((0 0, 1 0, 0 1, 0 0))')) AS val
Return the X component of a point geometry or geography.
ST_X(A: Point)
SELECT ST_X(ST_Point(1.0, 2.0))
Returns the maximum X-coordinate of a geometry's bounding box.
ST_XMax (A: Geometry)
SELECT ST_XMax(ST_GeomFromWKT('LINESTRING(1 5, 10 15)')); -- Returns: 10
Returns the minimum X-coordinate of a geometry's bounding box.
ST_XMin (A: Geometry)
SELECT ST_XMin(ST_GeomFromWKT('LINESTRING(1 5, 10 15)')); -- Returns: 1
Return the Y component of a point geometry or geography.
ST_Y(A: Point)
SELECT ST_Y(ST_Point(1.0, 2.0))
Returns the maximum Y-coordinate of a geometry's bounding box.
ST_YMax (A: Geometry)
SELECT ST_YMax(ST_GeomFromWKT('LINESTRING(1 5, 10 15)')); -- Returns: 15
Returns the minimum Y-coordinate of a geometry's bounding box.
ST_YMin (A: Geometry)
SELECT ST_YMin(ST_GeomFromWKT('LINESTRING(1 5, 10 15)')); -- Returns: 5
Return the Z component of a point geometry or geography.
ST_Z(A: Point)
SELECT ST_Z(ST_Point(1.0, 2.0))
Returns the maximum Z-coordinate of a geometry's bounding box.
ST_ZMax (A: Geometry)
SELECT ST_ZMax(ST_GeomFromWKT('LINESTRING ZM (1 2 3 4, 5 6 7 8)')); -- Returns: 7
Returns the minimum Z-coordinate of a geometry's bounding box.
ST_ZMin (A: Geometry)
SELECT ST_ZMin(ST_GeomFromWKT('LINESTRING ZM (1 2 3 4, 5 6 7 8)')); -- Returns: 3