# Functions

## ST_3DDistance¶

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

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

SQL example:

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

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)`

SQL 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)`

SQL example:

``````SELECT ST_Area(polygondf.countyshape)
FROM polygondf
``````

## ST_AreaSpheroid¶

Introduction: Return the geodesic area of A using WGS84 spheroid. Unit is square meter. Works better for large geometries (country level) compared to `ST_Area` + `ST_Transform`. It is equivalent to PostGIS `ST_Area(geography, use_spheroid=true)` function and produces nearly identical results.

Geometry must be in EPSG:4326 (WGS84) projection and must be in lat/lon order. You can use ST_FlipCoordinates to swap lat and lon.

Format: `ST_AreaSpheroid (A:geometry)`

SQL example:

``````SELECT ST_AreaSpheroid(ST_GeomFromWKT('Polygon ((35 34, 30 28, 34 25, 35 34))'))
``````

Output: `201824850811.76245`

## ST_AsBinary¶

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

Format: `ST_AsBinary (A:geometry)`

SQL 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. See ST_SetSRID

Format: `ST_AsEWKB (A:geometry)`

SQL 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)`

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)`

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)`

SQL example:

``````SELECT ST_AsGML(polygondf.countyshape)
FROM polygondf
``````

Introduction: Return the KML string representation of a geometry

Format: `ST_AsKML (A:geometry)`

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)`

SQL 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)`

SQL 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)`

SQL example:

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

Output: `LINESTRING (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)`

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)`

Example:

``````SELECT ST_BuildArea(
ST_GeomFromText('MULTILINESTRING((0 0, 20 0, 20 20, 0 20, 0 0),(2 2, 18 2, 18 18, 2 18, 2 2))')
) AS geom
``````

Result:

``````+----------------------------------------------------------------------------+
|geom                                                                        |
+----------------------------------------------------------------------------+
|POLYGON((0 0,0 20,20 20,20 0,0 0),(2 2,18 2,18 18,2 18,2 2))                |
+----------------------------------------------------------------------------+
``````

## ST_Centroid¶

Introduction: Return the centroid point of A

Format: `ST_Centroid (A:geometry)`

SQL example:

``````SELECT ST_Centroid(polygondf.countyshape)
FROM polygondf
``````

## ST_Collect¶

Introduction: Returns MultiGeometry object based on a geometry column.

Format

`ST_Collect(*geom: geometry)`

Example:

``````SELECT ST_Collect(
tbl.geom) AS geom
``````

Result:

``````+---------------------------------------------------------------+
|geom                                                           |
+---------------------------------------------------------------+
|MULTIPOINT ((21.427834 52.042576573), (45.342524 56.342354355))|
+---------------------------------------------------------------+
``````

Example:

``````SELECT ST_Collect(
Array(
ST_GeomFromText('POINT(21.427834 52.042576573)'),
ST_GeomFromText('POINT(45.342524 56.342354355)')
)
) AS geom
``````

Result:

``````+---------------------------------------------------------------+
|geom                                                           |
+---------------------------------------------------------------+
|MULTIPOINT ((21.427834 52.042576573), (45.342524 56.342354355))|
+---------------------------------------------------------------+
``````

## ST_CollectionExtract¶

Introduction: Returns a homogeneous multi-geometry from a given geometry collection.

The type numbers are: 1. POINT 2. LINESTRING 3. POLYGON

If the type parameter is omitted a multi-geometry of the highest dimension is returned.

Format: `ST_CollectionExtract (A:geometry)`

Format: `ST_CollectionExtract (A:geometry, type:Int)`

Example:

``````WITH test_data as (
ST_GeomFromText(
'GEOMETRYCOLLECTION(POINT(40 10), POLYGON((0 0, 0 5, 5 5, 5 0, 0 0)))'
) as geom
)
SELECT ST_CollectionExtract(geom) as c1, ST_CollectionExtract(geom, 1) as c2
FROM test_data
``````

Result:

``````+----------------------------------------------------------------------------+
|c1                                        |c2                               |
+----------------------------------------------------------------------------+
|MULTIPOLYGON(((0 0, 0 5, 5 5, 5 0, 0 0))) |MULTIPOINT(40 10)                |              |
+----------------------------------------------------------------------------+
``````

## ST_ConcaveHull¶

Introduction: Return the Concave Hull of polgyon A, with alpha set to pctConvex[0, 1] in the Delaunay Triangulation method, the concave hull will not contain a hole unless allowHoles is set to true

Format: `ST_ConcaveHull (A:geometry, pctConvex:float)`

Format: `ST_ConcaveHull (A:geometry, pctConvex:float, allowHoles:Boolean)`

SQL example:

``````SELECT ST_ConcaveHull(polygondf.countyshape, pctConvex)`
FROM polygondf
``````

## ST_ConvexHull¶

Introduction: Return the Convex Hull of polgyon A

Format: `ST_ConvexHull (A:geometry)`

SQL example:

``````SELECT ST_ConvexHull(polygondf.countyshape)
FROM polygondf
``````

## ST_Difference¶

Introduction: Return the difference between geometry A and B (return part of geometry A that does not intersect geometry B)

Format: `ST_Difference (A:geometry, B:geometry)`

Example:

``````SELECT ST_Difference(ST_GeomFromWKT('POLYGON ((-3 -3, 3 -3, 3 3, -3 3, -3 -3))'), ST_GeomFromWKT('POLYGON ((0 -4, 4 -4, 4 4, 0 4, 0 -4))'))
``````

Result:

``````POLYGON ((0 -3, -3 -3, -3 3, 0 3, 0 -3))
``````

## ST_Distance¶

Introduction: Return the Euclidean distance between A and B

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

SQL example:

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

## ST_DistanceSphere¶

Introduction: Return the haversine / great-circle distance of A using a given earth radius (default radius: 6371008.0). Unit is meter. Compared to `ST_Distance` + `ST_Transform`, it works better for datasets that cover large regions such as continents or the entire planet. It is equivalent to PostGIS `ST_Distance(geography, use_spheroid=false)` and `ST_DistanceSphere` function and produces nearly identical results. It provides faster but less accurate result compared to `ST_DistanceSpheroid`.

Geometry must be in EPSG:4326 (WGS84) projection and must be in lat/lon order. You can use ST_FlipCoordinates to swap lat and lon. For non-point data, we first take the centroids of both geometries and then compute the distance.

Format: `ST_DistanceSphere (A:geometry)`

SQL example 1:

``````SELECT ST_DistanceSphere(ST_GeomFromWKT('POINT (51.3168 -0.56)'), ST_GeomFromWKT('POINT (55.9533 -3.1883)'))
``````

Output: `543796.9506134904`

SQL example 2:

``````SELECT ST_DistanceSphere(ST_GeomFromWKT('POINT (51.3168 -0.56)'), ST_GeomFromWKT('POINT (55.9533 -3.1883)'), 6378137.0)
``````

Output: `544405.4459192449`

## ST_DistanceSpheroid¶

Introduction: Return the geodesic distance of A using WGS84 spheroid. Unit is meter. Compared to `ST_Distance` + `ST_Transform`, it works better for datasets that cover large regions such as continents or the entire planet. It is equivalent to PostGIS `ST_Distance(geography, use_spheroid=true)` and `ST_DistanceSpheroid` function and produces nearly identical results. It provides slower but more accurate result compared to `ST_DistanceSphere`.

Geometry must be in EPSG:4326 (WGS84) projection and must be in lat/lon order. You can use ST_FlipCoordinates to swap lat and lon. For non-point data, we first take the centroids of both geometries and then compute the distance.

Format: `ST_DistanceSpheroid (A:geometry)`

SQL example:

``````SELECT ST_DistanceSpheroid(ST_GeomFromWKT('POINT (51.3168 -0.56)'), ST_GeomFromWKT('POINT (55.9533 -3.1883)'))
``````

Output: `544430.9411996207`

## ST_Dump¶

Introduction: This is an aggregate function that takes a column of of geometries as input, and returns a single GeometryCollection of all these geometries.

Format: `ST_Dump(geom: geometry)`

SQL example:

``````SELECT ST_Dump(tbl.geom)
``````

Output: `GeometryCollection ( (10 40), (40 30), (20 20), (30 10) )`

## ST_DumpPoints¶

Introduction: Returns a MultiPoint geometry which consists of individual points that compose the input line string.

Format: `ST_DumpPoints(geom: geometry)`

SQL example:

``````SELECT ST_DumpPoints(ST_GeomFromText('LINESTRING (0 0, 1 1, 1 0)'))
``````

Output: `MultiPoint ((0 0), (0 1), (1 1), (1 0), (0 0))`

## ST_EndPoint¶

Introduction: Returns last point of given linestring.

Format: `ST_EndPoint(geom: geometry)`

SQL example:

``````SELECT ST_EndPoint(ST_GeomFromText('LINESTRING(100 150,50 60, 70 80, 160 170)'))
``````

Output: `POINT(160 170)`

## ST_Envelope¶

Introduction: Return the envelop boundary of A

Format: `ST_Envelope (A:geometry)`

SQL example:

``````SELECT ST_Envelope(polygondf.countyshape)
FROM polygondf
``````

## ST_ExteriorRing¶

Introduction: Returns a line string representing the exterior ring of the POLYGON geometry. Return NULL if the geometry is not a polygon.

Format: `ST_ExteriorRing(geom: geometry)`

SQL example:

``````SELECT ST_ExteriorRing(ST_GeomFromText('POLYGON((0 0 1, 1 1 1, 1 2 1, 1 1 1, 0 0 1))'))
``````

Output: `LINESTRING (0 0, 1 1, 1 2, 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)`

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)`

Example:

``````SELECT ST_AsText(
ST_Force_2D(ST_GeomFromText('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))'))
) AS geom
``````

Result:

``````+---------------------------------------------------------------+
|geom                                                           |
+---------------------------------------------------------------+
|POLYGON((0 0,0 5,5 0,0 0),(1 1,3 1,1 3,1 1))                   |
+---------------------------------------------------------------+
``````

## ST_Force3D¶

Introduction: Forces the geometry into a 3-dimensional model so that all output representations will have X, Y and Z coordinates. An optionally given zValue is tacked onto the geometry if the geometry is 2-dimensional. Default value of zValue is 0.0 If the given geometry is 3-dimensional, no change is performed on it. If the given geometry is empty, no change is performed on it.

Note

Example output is after calling ST_AsText() on returned geometry, which adds Z for in the WKT for 3D geometries

Format: `ST_Force3D(geometry, zValue)`

SQL Example:

``````SELECT ST_Force3D(geometry) AS geom
``````

Input: `LINESTRING(0 1, 1 2, 2 1)`

Output: `LINESTRING Z(0 1 0, 1 2 0, 2 1 0)`

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 Z((0 0 2,0 5 2,5 0 2,0 0 2),(1 1 2,3 1 2,1 3 2,1 1 2))`

``````SELECT ST_Force3D(geometry, 2.3) AS geom
``````

Input: `LINESTRING(0 1, 1 2, 2 1)`

Output: `LINESTRING Z(0 1 2.3, 1 2 2.3, 2 1 2.3)`

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 Z((0 0 2,0 5 2,5 0 2,0 0 2),(1 1 2,3 1 2,1 3 2,1 1 2))`

Input: `LINESTRING EMPTY`

Output: `LINESTRING EMPTY`

## ST_GeoHash¶

Introduction: Returns GeoHash of the geometry with given precision

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

Example:

Query:

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

Result:

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

## ST_GeometricMedian¶

Introduction: Computes the approximate geometric median of a MultiPoint geometry using the Weiszfeld algorithm. The geometric median provides a centrality measure that is less sensitive to outlier points than the centroid.

The algorithm will iterate until the distance change between successive iterations is less than the supplied `tolerance` parameter. If this condition has not been met after `maxIter` iterations, the function will produce an error and exit, unless `failIfNotConverged` is set to `false`.

If a `tolerance` value is not provided, a default `tolerance` value is `1e-6`.

Format: `ST_GeometricMedian(geom: geometry, tolerance: float, maxIter: integer, failIfNotConverged: boolean)`

Format: `ST_GeometricMedian(geom: geometry, tolerance: float, maxIter: integer)`

Format: `ST_GeometricMedian(geom: geometry, tolerance: float)`

Format: `ST_GeometricMedian(geom: geometry)`

Default parameters: `tolerance: 1e-6, maxIter: 1000, failIfNotConverged: false`

Example:

``````SELECT ST_GeometricMedian(ST_GeomFromWKT('MULTIPOINT((0 0), (1 1), (2 2), (200 200))'))
``````

Output:

``````POINT (1.9761550281255005 1.9761550281255005)
``````

## 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)`

SQL example:

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

Output: `POINT (3 4)`

## ST_GeometryType¶

Introduction: Returns the type of the geometry as a string. EG: 'ST_Linestring', 'ST_Polygon' etc.

Format: `ST_GeometryType (A:geometry)`

SQL example:

``````SELECT ST_GeometryType(polygondf.countyshape)
FROM polygondf
``````

## 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)`

SQL 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: `LINESTRING (1 1, 2 1, 2 2, 1 2, 1 1)`

## ST_Intersection¶

Introduction: Return the intersection geometry of A and B

Format: `ST_Intersection (A:geometry, B:geometry)`

SQL example:

``````SELECT ST_Intersection(polygondf.countyshape, polygondf.countyshape)
FROM polygondf
``````

## ST_IsClosed¶

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

Format: `ST_IsClosed(geom: geometry)`

SQL example:

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

Output: `false`

## ST_IsEmpty¶

Introduction: Test if a geometry is empty geometry

Format: `ST_IsEmpty (A:geometry)`

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)`

SQL 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)`

SQL example:

``````SELECT ST_IsSimple(polygondf.countyshape)
FROM polygondf
``````

## ST_IsValid¶

Introduction: Test if a geometry is well formed

Format: `ST_IsValid (A:geometry)`

SQL example:

``````SELECT ST_IsValid(polygondf.countyshape)
FROM polygondf
``````

## ST_Length¶

Introduction: Return the perimeter of A

Format: ST_Length (A:geometry)

SQL example:

``````SELECT ST_Length(polygondf.countyshape)
FROM polygondf
``````

## ST_LengthSpheroid¶

Introduction: Return the geodesic perimeter of A using WGS84 spheroid. Unit is meter. Works better for large geometries (country level) compared to `ST_Length` + `ST_Transform`. It is equivalent to PostGIS `ST_Length(geography, use_spheroid=true)` and `ST_LengthSpheroid` function and produces nearly identical results.

Geometry must be in EPSG:4326 (WGS84) projection and must be in lat/lon order. You can use ST_FlipCoordinates to swap lat and lon.

Format: `ST_LengthSpheroid (A:geometry)`

SQL example:

``````SELECT ST_LengthSpheroid(ST_GeomFromWKT('Polygon ((0 0, 0 90, 0 0))'))
``````

Output: `20037508.342789244`

## ST_LineFromMultiPoint¶

Introduction: Creates a LineString from a MultiPoint geometry.

Format: `ST_LineFromMultiPoint (A:geometry)`

Example:

``````SELECT ST_AsText(
ST_LineFromMultiPoint(ST_GeomFromText('MULTIPOINT((10 40), (40 30), (20 20), (30 10))'))
) AS geom
``````

Result:

``````+---------------------------------------------------------------+
|geom                                                           |
+---------------------------------------------------------------+
|LINESTRING (10 40, 40 30, 20 20, 30 10)                        |
+---------------------------------------------------------------+
``````

## ST_LineInterpolatePoint¶

Introduction: Returns a point interpolated along a line. First argument must be a LINESTRING. Second argument is a Double between 0 and 1 representing fraction of total linestring length the point has to be located.

Format: `ST_LineInterpolatePoint (geom: geometry, fraction: Double)`

SQL example:

``````SELECT ST_LineInterpolatePoint(ST_GeomFromWKT('LINESTRING(25 50, 100 125, 150 190)'), 0.2) as Interpolated
``````

Output:

``````+-----------------------------------------+
|Interpolated                             |
+-----------------------------------------+
|POINT (51.5974135047432 76.5974135047432)|
+-----------------------------------------+
``````

## ST_LineMerge¶

Introduction: Returns a LineString formed by sewing together the constituent line work of a MULTILINESTRING.

Note

Only works for MULTILINESTRING. Using other geometry will return a GEOMETRYCOLLECTION EMPTY. If the MultiLineString can't be merged, the original MULTILINESTRING is returned.

Format: `ST_LineMerge (A:geometry)`

``````SELECT ST_LineMerge(geometry)
FROM df
``````

## ST_LineSubstring¶

Introduction: Return a linestring being a substring of the input one starting and ending at the given fractions of total 2d length. Second and third arguments are Double values between 0 and 1. This only works with LINESTRINGs.

Format: `ST_LineSubstring (geom: geometry, startfraction: Double, endfraction: Double)`

SQL example:

``````SELECT ST_LineSubstring(ST_GeomFromWKT('LINESTRING(25 50, 100 125, 150 190)'), 0.333, 0.666) as Substring
``````

Output:

``````+------------------------------------------------------------------------------------------------+
|Substring                                                                                       |
+------------------------------------------------------------------------------------------------+
|LINESTRING (69.28469348539744 94.28469348539744, 100 125, 111.70035626068274 140.21046313888758)|
+------------------------------------------------------------------------------------------------+
``````

## ST_MakePolygon¶

Introduction: Function to convert closed linestring to polygon including holes. The holes must be a MultiLinestring.

Format: `ST_MakePolygon(geom: geometry, holes: <geometry>)`

Example:

Query:

``````SELECT
ST_MakePolygon(
ST_GeomFromText('LINESTRING(7 -1, 7 6, 9 6, 9 1, 7 -1)'),
ST_GeomFromText('MultiLINESTRING((6 2, 8 2, 8 1, 6 1, 6 2))')
) AS polygon
``````

Result:

``````+----------------------------------------------------------------+
|polygon                                                         |
+----------------------------------------------------------------+
|POLYGON ((7 -1, 7 6, 9 6, 9 1, 7 -1), (6 2, 8 2, 8 1, 6 1, 6 2))|
+----------------------------------------------------------------+
``````

## ST_MakeValid¶

Introduction: Given an invalid geometry, create a valid representation of the geometry.

Collapsed geometries are either converted to empty (keepCollaped=true) or a valid geometry of lower dimension (keepCollapsed=false). Default is keepCollapsed=false.

Format: `ST_MakeValid (A:geometry)`

Format: `ST_MakeValid (A:geometry, keepCollapsed:Boolean)`

SQL example:

``````WITH linestring AS (
SELECT ST_GeomFromWKT('LINESTRING(1 1, 1 1)') AS geom
) SELECT ST_MakeValid(geom), ST_MakeValid(geom, true) FROM linestring
``````

Result:

``````+------------------+------------------------+
|st_makevalid(geom)|st_makevalid(geom, true)|
+------------------+------------------------+
|  LINESTRING EMPTY|             POINT (1 1)|
+------------------+------------------------+
``````

Note

In Sedona up to and including version 1.2 the behaviour of ST_MakeValid was different. Be sure to check you code when upgrading. The previous implementation only worked for (multi)polygons and had a different interpretation of the second, boolean, argument. It would also sometimes return multiple geometries for a single geomtry input.

## ST_MinimumBoundingCircle¶

Introduction: Returns the smallest circle polygon that contains a geometry.

Format: `ST_MinimumBoundingCircle(geom: geometry, [Optional] quadrantSegments:int)`

SQL example:

``````SELECT ST_MinimumBoundingCircle(ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))'))
``````

Introduction: Returns two columns containing the center point and radius of the smallest circle that contains a geometry.

Format: `ST_MinimumBoundingRadius(geom: geometry)`

SQL example:

``````SELECT ST_MinimumBoundingRadius(ST_GeomFromText('POLYGON((1 1,0 0, -1 1, 1 1))'))
``````

## ST_Multi¶

Introduction: Returns a MultiGeometry object based on the geometry input. ST_Multi is basically an alias for ST_Collect with one geometry.

Format

`ST_Multi(geom: geometry)`

Example:

``````SELECT ST_Multi(
ST_GeomFromText('POINT(1 1)')
) AS geom
``````

Result:

``````+---------------------------------------------------------------+
|geom                                                           |
+---------------------------------------------------------------+
|MULTIPOINT (1 1)                                               |
+---------------------------------------------------------------+
``````

## ST_NDims¶

Introduction: Returns the coordinate dimension of the geometry.

Format: `ST_NDims(geom: geometry)`

SQL example with z co-rodinate:

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

Output: `3`

SQL example with x,y co-ordinate:

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

Output: `2`

## ST_Normalize¶

Introduction: Returns the input geometry in its normalized form.

Format

`ST_Normalize(geom: geometry)`

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: Return points of the geometry

Format: `ST_NPoints (A:geometry)`

``````SELECT ST_NPoints(polygondf.countyshape)
FROM polygondf
``````

## ST_NRings¶

Introduction: Returns the number of rings in a Polygon or MultiPolygon. Contrary to ST_NumInteriorRings, this function also takes into account the number of exterior rings.

This function returns 0 for an empty Polygon or MultiPolygon. If the geometry is not a Polygon or MultiPolygon, an IllegalArgument Exception is thrown.

Format: `ST_NRings(geom: geometry)`

Examples:

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

Output: `1`

Input: `'MULTIPOLYGON (((1 0, 1 6, 6 6, 6 0, 1 0), (2 1, 2 2, 3 2, 3 1, 2 1)), ((10 0, 10 6, 16 6, 16 0, 10 0), (12 1, 12 2, 13 2, 13 1, 12 1)))'`

Output: `4`

Input: `'POLYGON EMPTY'`

Output: `0`

Input: `'LINESTRING (1 0, 1 1, 2 1)'`

Output: `Unsupported geometry type: LineString, only Polygon or MultiPolygon geometries are supported.`

## 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)`

``````SELECT ST_NumGeometries(df.geometry)
FROM df
``````

## ST_NumInteriorRings¶

Introduction: RETURNS number of interior rings of polygon geometries.

Format: `ST_NumInteriorRings(geom: geometry)`

SQL 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_NumPoints¶

Introduction: Returns number of points in a LineString

Format: `ST_NumPoints(geom: geometry)`

Note

If any other geometry is provided as an argument, an IllegalArgumentException is thrown.

SQL Example:

``````SELECT ST_NumPoints(ST_GeomFromWKT('MULTIPOINT ((0 0), (1 1), (0 1), (2 2))'))
``````

Output:

``````IllegalArgumentException: Unsupported geometry type: MultiPoint, only LineString geometry is supported.
``````

SQL Example:

``````SELECT ST_NumPoints(ST_GeomFromText('LINESTRING(0 1, 1 0, 2 0)'))
``````

Output: `3`

## 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(geom: geometry, n: integer)`

SQL example:

``````SELECT ST_PointN(ST_GeomFromText('LINESTRING(0 0, 1 2, 2 4, 3 6)'), 2) AS geom
``````

Result:

``````+---------------------------------------------------------------+
|geom                                                           |
+---------------------------------------------------------------+
|POINT (1 2)                                                    |
+---------------------------------------------------------------+
``````

## ST_PointOnSurface¶

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

Format: `ST_PointOnSurface(A:geometry)`

Examples:

``````SELECT ST_AsText(ST_PointOnSurface(ST_GeomFromText('POINT(0 5)')));
st_astext
------------
POINT(0 5)

SELECT ST_AsText(ST_PointOnSurface(ST_GeomFromText('LINESTRING(0 5, 0 10)')));
st_astext
------------
POINT(0 5)

SELECT ST_AsText(ST_PointOnSurface(ST_GeomFromText('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))')));
st_astext
----------------
POINT(2.5 2.5)

SELECT ST_AsText(ST_PointOnSurface(ST_GeomFromText('LINESTRING(0 5 1, 0 0 1, 0 10 2)')));
st_astext
----------------
POINT Z(0 0 1)
``````

## ST_PrecisionReduce¶

Introduction: Reduce the decimals places in the coordinates of the geometry to the given number of decimal places. The last decimal place will be rounded.

Format: `ST_PrecisionReduce (A:geometry, B:int)`

SQL example:

``````SELECT ST_PrecisionReduce(polygondf.countyshape, 9)
FROM polygondf
``````
The new coordinates will only have 9 decimal places.

## ST_RemovePoint¶

Introduction: RETURN Line 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)`

SQL example:

``````SELECT ST_RemovePoint(ST_GeomFromText('LINESTRING(0 0, 1 1, 1 0)'), 1)
``````

Output: `LINESTRING(0 0, 1 0)`

## ST_Reverse¶

Introduction: Return the geometry with vertex order reversed

Format: `ST_Reverse (A:geometry)`

Example:

``````SELECT ST_AsText(
ST_Reverse(ST_GeomFromText('LINESTRING(0 0, 1 2, 2 4, 3 6)'))
) AS geom
``````

Result:

``````+---------------------------------------------------------------+
|geom                                                           |
+---------------------------------------------------------------+
|LINESTRING (3 6, 2 4, 1 2, 0 0)                                |
+---------------------------------------------------------------+
``````

## ST_S2CellIDs¶

Introduction: Cover the geometry with Google S2 Cells, return the corresponding cell IDs with the given level. The level indicates the size of cells. With a bigger level, the cells will be smaller, the coverage will be more accurate, but the result size will be exponentially increasing.

Format: `ST_S2CellIDs(geom: geometry, level: Int)`

SQL example:

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

Output:

``````+------------------------------------------------------------------------------------------------------------------------------+
|st_s2cellids(st_geomfromtext(LINESTRING(1 3 4, 5 6 7), 0), 6)                                                                 |
+------------------------------------------------------------------------------------------------------------------------------+
|[1159395429071192064, 1159958379024613376, 1160521328978034688, 1161084278931456000, 1170091478186196992, 1170654428139618304]|
+------------------------------------------------------------------------------------------------------------------------------+
``````

## 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)`

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 reference system identifier (SRID) of the geometry.

Format: `ST_SetSRID (A:geometry, srid: Integer)`

SQL example:

``````SELECT ST_SetSRID(polygondf.countyshape, 3021)
FROM polygondf
``````

## ST_SimplifyPreserveTopology¶

Introduction: Simplifies a geometry and ensures that the result is a valid geometry having the same dimension and number of components as the input, and with the components having the same topological relationship.

Format: `ST_SimplifyPreserveTopology (A:geometry, distanceTolerance: Double)`

``````SELECT ST_SimplifyPreserveTopology(polygondf.countyshape, 10.0)
FROM polygondf
``````

## ST_Split¶

Introduction: Split an input geometry by another geometry (called the blade). Linear (LineString or MultiLineString) geometry can be split by a Point, MultiPoint, LineString, MultiLineString, Polygon, or MultiPolygon. Polygonal (Polygon or MultiPolygon) geometry can be split by a LineString, MultiLineString, Polygon, or MultiPolygon. In either case, when a polygonal blade is used then the boundary of the blade is what is actually split by. ST_Split will always return either a MultiLineString or MultiPolygon even if they only contain a single geometry. Homogeneous GeometryCollections are treated as a multi-geometry of the type it contains. For example, if a GeometryCollection of only Point geometries is passed as a blade it is the same as passing a MultiPoint of the same geometries.

Format: `ST_Split (input: geometry, blade: geometry)`

SQL Example:

``````SELECT ST_Split(
ST_GeomFromWKT('LINESTRING (0 0, 1.5 1.5, 2 2)'),
ST_GeomFromWKT('MULTIPOINT (0.5 0.5, 1 1)'))
``````

Output: `MULTILINESTRING ((0 0, 0.5 0.5), (0.5 0.5, 1 1), (1 1, 1.5 1.5, 2 2))`

## ST_SRID¶

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

Format: `ST_SRID (A:geometry)`

SQL example:

``````SELECT ST_SRID(polygondf.countyshape)
FROM polygondf
``````

## ST_StartPoint¶

Introduction: Returns first point of given linestring.

Format: `ST_StartPoint(geom: geometry)`

SQL example:

``````SELECT ST_StartPoint(ST_GeomFromText('LINESTRING(100 150,50 60, 70 80, 160 170)'))
``````

Output: `POINT(100 150)`

## ST_SubDivide¶

Introduction: Returns a multi-geometry divided based of given maximum number of vertices.

Format: `ST_SubDivide(geom: geometry, maxVertices: int)`

SQL example:

``````SELECT Sedona.ST_AsText(Sedona.ST_SubDivide(Sedona.ST_GeomFromText('LINESTRING(0 0, 85 85, 100 100, 120 120, 21 21, 10 10, 5 5)'), 5));
``````

Output:

``````MULTILINESTRING ((0 0, 5 5), (5 5, 10 10), (10 10, 21 21), (21 21, 60 60), (60 60, 85 85), (85 85, 100 100), (100 100, 120 120))
``````

## ST_SubDivideExplode¶

Introduction: It works the same as ST_SubDivide but returns new rows with geometries instead of a multi-geometry.

Format: ```SELECT SEDONA.ST_AsText(GEOM) FROM table(SEDONA.ST_SubDivideExplode(geom: geometry, maxVertices: int))```

Example:

Query:

``````SELECT Sedona.ST_AsText(GEOM)
FROM table(Sedona.ST_SubDivideExplode(Sedona.ST_GeomFromText('LINESTRING(0 0, 85 85, 100 100, 120 120, 21 21, 10 10, 5 5)'), 5));
``````

Result:

``````+-----------------------------+
|geom                         |
+-----------------------------+
|LINESTRING(0 0, 5 5)         |
|LINESTRING(5 5, 10 10)       |
|LINESTRING(10 10, 21 21)     |
|LINESTRING(21 21, 60 60)     |
|LINESTRING(60 60, 85 85)     |
|LINESTRING(85 85, 100 100)   |
|LINESTRING(100 100, 120 120) |
+-----------------------------+
``````

## ST_SymDifference¶

Introduction: Return the symmetrical difference between geometry A and B (return parts of geometries which are in either of the sets, but not in their intersection)

Format: `ST_SymDifference (A:geometry, B:geometry)`

Example:

``````SELECT ST_SymDifference(ST_GeomFromWKT('POLYGON ((-3 -3, 3 -3, 3 3, -3 3, -3 -3))'), ST_GeomFromWKT('POLYGON ((-2 -3, 4 -3, 4 3, -2 3, -2 -3))'))
``````

Result:

``````MULTIPOLYGON (((-2 -3, -3 -3, -3 3, -2 3, -2 -3)), ((3 -3, 3 3, 4 3, 4 -3, 3 -3)))
``````

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

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)`

SQL example (simple):

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

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_Translate¶

Introduction: Returns the input geometry with its X, Y and Z coordinates (if present in the geometry) translated by deltaX, deltaY and deltaZ (if specified)

If the geometry is 2D, and a deltaZ parameter is specified, no change is done to the Z coordinate of the geometry and the resultant geometry is also 2D.

If the geometry is empty, no change is done to it. If the given geometry contains sub-geometries (GEOMETRY COLLECTION, MULTI POLYGON/LINE/POINT), all underlying geometries are individually translated.

Format: `ST_Translate(geometry: geometry, deltaX: deltaX, deltaY: deltaY, deltaZ: deltaZ)`

Example:

Input: `ST_Translate(GEOMETRYCOLLECTION(MULTIPOLYGON (((1 0, 1 1, 2 1, 2 0, 1 0)), ((1 2, 3 4, 3 5, 1 2))), POINT(1, 1, 1), LINESTRING EMPTY), 2, 2, 3)`

Output: `GEOMETRYCOLLECTION(MULTIPOLYGON (((3 2, 3 3, 4 3, 4 2, 3 2)), ((3 4, 5 6, 5 7, 3 4))), POINT(3, 3, 4), LINESTRING EMPTY)`

Input: `ST_Translate(POINT(1, 3, 2), 1, 2)`

Output: `POINT(2, 5, 2)`

## ST_Union¶

Introduction: Return the union of geometry A and B

Format: `ST_Union (A:geometry, B:geometry)`

Example:

``````SELECT ST_Union(ST_GeomFromWKT('POLYGON ((-3 -3, 3 -3, 3 3, -3 3, -3 -3))'), ST_GeomFromWKT('POLYGON ((1 -2, 5 0, 1 2, 1 -2))'))
``````

Result:

``````POLYGON ((3 -1, 3 -3, -3 -3, -3 3, 3 3, 3 1, 5 0, 3 -1))
``````

## ST_X¶

Introduction: Returns X Coordinate of given Point null otherwise.

Format: `ST_X(pointA: Point)`

SQL 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)`

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)`

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)`

SQL 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)`

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)`

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)`

SQL 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)`

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)`

SQL example:

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

Output: `4.0`

Last update: October 17, 2023 01:49:40