Function

ST_3DDistance

Introduction: Return the 3-dimensional minimum cartesian distance between A and B

Format: ST_3DDistance (A:geometry, B:geometry)

Since: v1.3.0

Example:

SELECT ST_3DDistance(polygondf.countyshape, polygondf.countyshape)
FROM polygondf

ST_AddPoint

Introduction: Return Linestring with additional point at the given index, if position is not available the point will be added at the end of line.

Format: ST_AddPoint(geom: geometry, point: geometry, position: integer)

Format: ST_AddPoint(geom: geometry, point: geometry)

Since: v1.3.0

Example:

SELECT ST_AddPoint(ST_GeomFromText("LINESTRING(0 0, 1 1, 1 0)"), ST_GeomFromText("Point(21 52)"), 1)

SELECT ST_AddPoint(ST_GeomFromText("Linestring(0 0, 1 1, 1 0)"), ST_GeomFromText("Point(21 52)"))

Output:

LINESTRING(0 0, 21 52, 1 1, 1 0)
LINESTRING(0 0, 1 1, 1 0, 21 52)

ST_Area

Introduction: Return the area of A

Format: ST_Area (A:geometry)

Since: v1.3.0

Example:

SELECT ST_Area(polygondf.countyshape)
FROM polygondf

ST_AsBinary

Introduction: Return the Well-Known Binary representation of a geometry

Format: ST_AsBinary (A:geometry)

Since: v1.3.0

Example:

SELECT ST_AsBinary(polygondf.countyshape)
FROM polygondf

ST_AsEWKB

Introduction: Return the Extended Well-Known Binary representation of a geometry. EWKB is an extended version of WKB which includes the SRID of the geometry. The format originated in PostGIS but is supported by many GIS tools. If the geometry is lacking SRID a WKB format is produced.

Format: ST_AsEWKB (A:geometry)

Since: v1.3.0

Example:

SELECT ST_AsEWKB(polygondf.countyshape)
FROM polygondf

ST_AsEWKT

Introduction: Return the Extended Well-Known Text representation of a geometry. EWKT is an extended version of WKT which includes the SRID of the geometry. The format originated in PostGIS but is supported by many GIS tools. If the geometry is lacking SRID a WKT format is produced. See ST_SetSRID

Format: ST_AsEWKT (A:geometry)

Since: v1.2.1

Spark SQL example:

SELECT ST_AsEWKT(polygondf.countyshape)
FROM polygondf

ST_AsGeoJSON

Introduction: Return the GeoJSON string representation of a geometry

Format: ST_AsGeoJSON (A:geometry)

Since: v1.3.0

Spark SQL example:

SELECT ST_AsGeoJSON(polygondf.countyshape)
FROM polygondf

ST_AsGML

Introduction: Return the GML string representation of a geometry

Format: ST_AsGML (A:geometry)

Since: v1.3.0

Spark SQL example:

SELECT ST_AsGML(polygondf.countyshape)
FROM polygondf

ST_AsKML

Introduction: Return the KML string representation of a geometry

Format: ST_AsKML (A:geometry)

Since: v1.3.0

Spark SQL example:

SELECT ST_AsKML(polygondf.countyshape)
FROM polygondf

ST_AsText

Introduction: Return the Well-Known Text string representation of a geometry

Format: ST_AsText (A:geometry)

Since: v1.3.0

Example:

SELECT ST_AsText(polygondf.countyshape)
FROM polygondf

ST_Azimuth

Introduction: Returns Azimuth for two given points in radians null otherwise.

Format: ST_Azimuth(pointA: Point, pointB: Point)

Since: v1.3.0

Example:

SELECT ST_Azimuth(ST_POINT(0.0, 25.0), ST_POINT(0.0, 0.0))

Output: 3.141592653589793

ST_Boundary

Introduction: Returns the closure of the combinatorial boundary of this Geometry.

Format: ST_Boundary(geom: geometry)

Since: v1.3.0

Example:

SELECT ST_Boundary(ST_GeomFromText('POLYGON ((1 1, 0 0, -1 1, 1 1))'))

Output: LINEARRING (1 1, 0 0, -1 1, 1 1)

ST_Buffer

Introduction: Returns a geometry/geography that represents all points whose distance from this Geometry/geography is less than or equal to distance.

Format: ST_Buffer (A:geometry, buffer: Double)

Since: v1.2.0

Spark SQL example:

SELECT ST_Buffer(polygondf.countyshape, 1)
FROM polygondf

ST_BuildArea

Introduction: Returns the areal geometry formed by the constituent linework of the input geometry.

Format: ST_BuildArea (A:geometry)

Since: v1.2.1

Example:

SELECT ST_BuildArea(ST_Collect(smallDf, bigDf)) AS geom
FROM smallDf, bigDf

Input: MULTILINESTRING((0 0, 10 0, 10 10, 0 10, 0 0),(10 10, 20 10, 20 20, 10 20, 10 10))

Output: MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0)),((10 10,10 20,20 20,20 10,10 10)))

ST_Distance

Introduction: Return the Euclidean distance between A and B

Format: ST_Distance (A:geometry, B:geometry)

Since: v1.2.0

Spark SQL example:

SELECT ST_Distance(polygondf.countyshape, polygondf.countyshape)
FROM polygondf

ST_Envelope

Introduction: Return the envelop boundary of A

Format: ST_Envelope (A:geometry)

Since: v1.3.0

Example:

SELECT ST_Envelope(polygondf.countyshape)
FROM polygondf

ST_ExteriorRing

Introduction: Returns a LINESTRING representing the exterior ring (shell) of a POLYGON. Returns NULL if the geometry is not a polygon.

Format: ST_ExteriorRing(A:geometry)

Since: v1.2.1

Examples:

SELECT ST_ExteriorRing(df.geometry)
FROM df

Input: POLYGON ((0 0, 1 1, 2 1, 0 1, 1 -1, 0 0))

Output: LINESTRING (0 0, 1 1, 2 1, 0 1, 1 -1, 0 0)

ST_FlipCoordinates

Introduction: Returns a version of the given geometry with X and Y axis flipped.

Format: ST_FlipCoordinates(A:geometry)

Since: v1.2.0

Spark SQL example:

SELECT ST_FlipCoordinates(df.geometry)
FROM df

Input: POINT (1 2)

Output: POINT (2 1)

ST_Force_2D

Introduction: Forces the geometries into a "2-dimensional mode" so that all output representations will only have the X and Y coordinates

Format: ST_Force_2D (A:geometry)

Since: v1.2.1

Example:

SELECT ST_Force_2D(df.geometry) AS geom
FROM df

Input: POLYGON((0 0 2,0 5 2,5 0 2,0 0 2),(1 1 2,3 1 2,1 3 2,1 1 2))

Output: POLYGON((0 0,0 5,5 0,0 0),(1 1,3 1,1 3,1 1))

ST_GeoHash

Introduction: Returns GeoHash of the geometry with given precision

Format: ST_GeoHash(geom: geometry, precision: int)

Since: v1.2.0

Example:

Query:

SELECT ST_GeoHash(ST_GeomFromText('POINT(21.427834 52.042576573)'), 5) AS geohash

Result:

+-----------------------------+
|geohash                      |
+-----------------------------+
|u3r0p                        |
+-----------------------------+

ST_GeometryN

Introduction: Return the 0-based Nth geometry if the geometry is a GEOMETRYCOLLECTION, (MULTI)POINT, (MULTI)LINESTRING, MULTICURVE or (MULTI)POLYGON. Otherwise, return null

Format: ST_GeometryN(geom: geometry, n: Int)

Since: v1.3.0

Example:

SELECT ST_GeometryN(ST_GeomFromText('MULTIPOINT((1 2), (3 4), (5 6), (8 9))'), 1)

Output: POINT (3 4)

ST_InteriorRingN

Introduction: Returns the Nth interior linestring ring of the polygon geometry. Returns NULL if the geometry is not a polygon or the given N is out of range

Format: ST_InteriorRingN(geom: geometry, n: Int)

Since: v1.3.0

Example:

SELECT ST_InteriorRingN(ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1), (1 3, 2 3, 2 4, 1 4, 1 3), (3 3, 4 3, 4 4, 3 4, 3 3))'), 0)

Output: LINEARRING (1 1, 2 1, 2 2, 1 2, 1 1)

ST_IsClosed

Introduction: RETURNS true if the LINESTRING start and end point are the same.

Format: ST_IsClosed(geom: geometry)

Since: v1.3.0

Example:

SELECT ST_IsClosed(ST_GeomFromText('LINESTRING(0 0, 1 1, 1 0)'))

ST_IsEmpty

Introduction: Test if a geometry is empty geometry

Format: ST_IsEmpty (A:geometry)

Since: v1.2.1

Spark SQL example:

SELECT ST_IsEmpty(polygondf.countyshape)
FROM polygondf

ST_IsRing

Introduction: RETURN true if LINESTRING is ST_IsClosed and ST_IsSimple.

Format: ST_IsRing(geom: geometry)

Since: v1.3.0

Example:

SELECT ST_IsRing(ST_GeomFromText("LINESTRING(0 0, 0 1, 1 1, 1 0, 0 0)"))

Output: true

ST_IsSimple

Introduction: Test if geometry's only self-intersections are at boundary points.

Format: ST_IsSimple (A:geometry)

Since: v1.3.0

Example:

SELECT ST_IsSimple(polygondf.countyshape)
FROM polygondf

ST_IsValid

Introduction: Test if a geometry is well formed

Format: ST_IsValid (A:geometry)

Since: v1.3.0

Example:

SELECT ST_IsValid(polygondf.countyshape)
FROM polygondf

ST_Length

Introduction: Return the perimeter of A

Format: ST_Length (A:geometry)

Since: v1.3.0

Example:

SELECT ST_Length(polygondf.countyshape)
FROM polygondf

ST_LineFromMultiPoint

Introduction: Creates a LineString from a MultiPoint geometry.

Format: ST_LineFromMultiPoint (A:geometry)

Since: v1.3.0

Example:

SELECT ST_LineFromMultiPoint(df.geometry) AS geom
FROM df

Input: MULTIPOINT((10 40), (40 30), (20 20), (30 10))

Output: LINESTRING (10 40, 40 30, 20 20, 30 10)

ST_Normalize

Introduction: Returns the input geometry in its normalized form.

Format

ST_Normalize(geom: geometry)

Since: v1.3.0

Example:

SELECT ST_AsEWKT(ST_Normalize(ST_GeomFromWKT('POLYGON((0 1, 1 1, 1 0, 0 0, 0 1))'))) AS geom

Result:

+-----------------------------------+
|geom                               |
+-----------------------------------+
|POLYGON ((0 0, 0 1, 1 1, 1 0, 0 0))|
+-----------------------------------+

ST_NPoints

Introduction: Returns the number of points of the geometry

Since: v1.3.0

Format: ST_NPoints (A:geometry)

Example:

SELECT ST_NPoints(polygondf.countyshape)
FROM polygondf

ST_NDims

Introduction: Returns the coordinate dimension of the geometry. It supports 2 - (x,y) , 3 - (x,y,z). Currently the geometry serializer in sedona-sql does not support M dimension, 4D geometries with ZM coordinates will have their M coordinates dropped and became 3D geometries. We're working on a new geometry serializer to resolve this issue.

Format: ST_NDims(geom: geometry)

Since: v1.3.1

Spark SQL example with z co-rodinate:

SELECT ST_NDims(ST_GeomFromEWKT('POINT(1 1 2)'))

Output: 3

Spark SQL example with x,y co-ordinate:

SELECT ST_NDims(ST_GeomFromText('POINT(1 1)'))

Output: 2

ST_NumGeometries

Introduction: Returns the number of Geometries. If geometry is a GEOMETRYCOLLECTION (or MULTI*) return the number of geometries, for single geometries will return 1.

Format: ST_NumGeometries (A:geometry)

Since: v1.3.0

Example:

SELECT ST_NumGeometries(df.geometry)
FROM df

ST_NumInteriorRings

Introduction: Returns number of interior rings of polygon geometries.

Format: ST_NumInteriorRings(geom: geometry)

Since: v1.3.0

Example:

SELECT ST_NumInteriorRings(ST_GeomFromText('POLYGON ((0 0, 0 5, 5 5, 5 0, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1))'))

Output: 1

ST_PointN

Introduction: Return the Nth point in a single linestring or circular linestring in the geometry. Negative values are counted backwards from the end of the LineString, so that -1 is the last point. Returns NULL if there is no linestring in the geometry.

Format: ST_PointN(A:geometry, B:integer)

Since: v1.2.1

Examples:

SELECT ST_PointN(df.geometry, 2)
FROM df

Input: LINESTRING(0 0, 1 2, 2 4, 3 6), 2

Output: POINT (1 2)

Input: LINESTRING(0 0, 1 2, 2 4, 3 6), -2

Output: POINT (2 4)

Input: CIRCULARSTRING(1 1, 1 2, 2 4, 3 6, 1 2, 1 1), -1

Output: POINT (1 1)

ST_PointOnSurface

Introduction: Returns a POINT guaranteed to lie on the surface.

Format: ST_PointOnSurface(A:geometry)

Since: v1.2.1

Examples:

SELECT ST_PointOnSurface(df.geometry)
FROM df

1. Input: POINT (0 5)

   Output: POINT (0 5)

2. Input: LINESTRING(0 5, 0 10)

   Output: POINT (0 5)

3. Input: POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))

   Output: POINT (2.5 2.5)

4. Input: LINESTRING(0 5 1, 0 0 1, 0 10 2)

   Output: POINT Z(0 0 1)

ST_Reverse

Introduction: Return the geometry with vertex order reversed

Format: ST_Reverse (A:geometry)

Since: v1.2.1

Example:

SELECT ST_Reverse(df.geometry) AS geom
FROM df

Input: POLYGON ((-0.5 -0.5, -0.5 0.5, 0.5 0.5, 0.5 -0.5, -0.5 -0.5))

Output: POLYGON ((-0.5 -0.5, 0.5 -0.5, 0.5 0.5, -0.5 0.5, -0.5 -0.5))

ST_RemovePoint

Introduction: Return Linestring with removed point at given index, position can be omitted and then last one will be removed.

Format: ST_RemovePoint(geom: geometry, position: integer)

Format: ST_RemovePoint(geom: geometry)

Since: v1.3.0

Example:

SELECT ST_RemovePoint(ST_GeomFromText("LINESTRING(0 0, 1 1, 1 0)"), 1)

Output: LINESTRING(0 0, 1 0)

ST_SetPoint

Introduction: Replace Nth point of linestring with given point. Index is 0-based. Negative index are counted backwards, e.g., -1 is last point.

Format: ST_SetPoint (linestring: geometry, index: integer, point: geometry)

Since: v1.3.0

Example:

SELECT ST_SetPoint(ST_GeomFromText('LINESTRING (0 0, 0 1, 1 1)'), 2, ST_GeomFromText('POINT (1 0)')) AS geom

Result:

+--------------------------------+
|                           geom |
+--------------------------------+
|     LINESTRING (0 0, 0 1, 1 0) |
+--------------------------------+

ST_SetSRID

Introduction: Sets the spatial refence system identifier (SRID) of the geometry.

Format: ST_SetSRID (A:geometry, srid: integer)

Since: v1.3.0

Example:

SELECT ST_SetSRID(polygondf.countyshape, 3021)
FROM polygondf

ST_SRID

Introduction: Return the spatial refence system identifier (SRID) of the geometry.

Format: ST_SRID (A:geometry)

Since: v1.3.0

Example:

SELECT ST_SRID(polygondf.countyshape)
FROM polygondf

ST_Transform

Introduction:

Transform the Spatial Reference System / Coordinate Reference System of A, from SourceCRS to TargetCRS For SourceCRS and TargetCRS, WKT format is also available since v1.3.1.

Note

By default, this function uses lat/lon order. You can use ST_FlipCoordinates to swap X and Y.

Note

If ST_Transform throws an Exception called "Bursa wolf parameters required", you need to disable the error notification in ST_Transform. You can append a boolean value at the end.

Format: ST_Transform (A:geometry, SourceCRS:string, TargetCRS:string ,[Optional] DisableError)

Since: v1.2.0

Spark SQL example (simple):

SELECT ST_Transform(polygondf.countyshape, 'epsg:4326','epsg:3857') 
FROM polygondf

Spark SQL example (with optional parameters):

SELECT ST_Transform(polygondf.countyshape, 'epsg:4326','epsg:3857', false)
FROM polygondf

Note

The detailed EPSG information can be searched on EPSG.io.

ST_X

Introduction: Returns X Coordinate of given Point, null otherwise.

Format: ST_X(pointA: Point)

Since: v1.3.0

Example:

SELECT ST_X(ST_POINT(0.0 25.0))

Output: 0.0

ST_XMax

Introduction: Returns the maximum X coordinate of a geometry

Format: ST_XMax (A:geometry)

Since: v1.2.1

Example:

SELECT ST_XMax(df.geometry) AS xmax
FROM df

Input: POLYGON ((-1 -11, 0 10, 1 11, 2 12, -1 -11))

Output: 2

ST_XMin

Introduction: Returns the minimum X coordinate of a geometry

Format: ST_XMin (A:geometry)

Since: v1.2.1

Example:

SELECT ST_XMin(df.geometry) AS xmin
FROM df

Input: POLYGON ((-1 -11, 0 10, 1 11, 2 12, -1 -11))

Output: -1

ST_Y

Introduction: Returns Y Coordinate of given Point, null otherwise.

Format: ST_Y(pointA: Point)

Since: v1.3.0

Example:

SELECT ST_Y(ST_POINT(0.0 25.0))

Output: 25.0

ST_YMax

Introduction: Return the minimum Y coordinate of A

Format: ST_YMax (A:geometry)

Since: v1.2.1

Spark SQL example:

SELECT ST_YMax(ST_GeomFromText('POLYGON((0 0 1, 1 1 1, 1 2 1, 1 1 1, 0 0 1))'))

Output : 2

ST_YMin

Introduction: Return the minimum Y coordinate of A

Format: ST_Y_Min (A:geometry)

Since: v1.2.1

Spark SQL example:

SELECT ST_YMin(ST_GeomFromText('POLYGON((0 0 1, 1 1 1, 1 2 1, 1 1 1, 0 0 1))'))

Output : 0

ST_Z

Introduction: Returns Z Coordinate of given Point, null otherwise.

Format: ST_Z(pointA: Point)

Since: v1.3.0

Example:

SELECT ST_Z(ST_POINT(0.0 25.0 11.0))

Output: 11.0

ST_ZMax

Introduction: Returns Z maxima of the given geometry or null if there is no Z coordinate.

Format: ST_ZMax(geom: geometry)

Since: v1.3.1

Spark SQL example:

SELECT ST_ZMax(ST_GeomFromText('POLYGON((0 0 1, 1 1 1, 1 2 1, 1 1 1, 0 0 1))'))

Output: 1.0

ST_ZMin

Introduction: Returns Z minima of the given geometry or null if there is no Z coordinate.

Format: ST_ZMin(geom: geometry)

Since: v1.3.1

Spark SQL example:

SELECT ST_ZMin(ST_GeomFromText('LINESTRING(1 3 4, 5 6 7)'))

Output: 4.0

---

Last update: December 3, 2022 19:27:54