Skip to content

Function

ST_Distance

Introduction: Return the Euclidean distance between A and B

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

Since: v1.0.0

Spark SQL example:

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

ST_ConvexHull

Introduction: Return the Convex Hull of polgyon A

Format: ST_ConvexHull (A:geometry)

Since: v1.0.0

Spark SQL example:

SELECT ST_ConvexHull(polygondf.countyshape)
FROM polygondf

ST_Envelope

Introduction: Return the envelop boundary of A

Format: ST_Envelope (A:geometry)

Since: v1.0.0

Spark SQL example:

SELECT ST_Envelope(polygondf.countyshape)
FROM polygondf

ST_Length

Introduction: Return the perimeter of A

Format: ST_Length (A:geometry)

Since: v1.0.0

Spark SQL example:

SELECT ST_Length(polygondf.countyshape)
FROM polygondf

ST_Area

Introduction: Return the area of A

Format: ST_Area (A:geometry)

Since: v1.0.0

Spark SQL example:

SELECT ST_Area(polygondf.countyshape)
FROM polygondf

ST_Centroid

Introduction: Return the centroid point of A

Format: ST_Centroid (A:geometry)

Since: v1.0.0

Spark SQL example:

SELECT ST_Centroid(polygondf.countyshape)
FROM polygondf

ST_Transform

Introduction:

Transform the Spatial Reference System / Coordinate Reference System of A, from SourceCRS to TargetCRS

Note

By default, ST_Transform assumes Longitude/Latitude is your coordinate X/Y. If this is not the case, set UseLongitudeLatitudeOrder as "false".

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] UseLongitudeLatitudeOrder:Boolean, [Optional] DisableError)

Since: v1.0.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',true, false)
FROM polygondf

Note

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

ST_Intersection

Introduction: Return the intersection geometry of A and B

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

Since: v1.1.0

Spark SQL example:

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

ST_IsValid

Introduction: Test if a geometry is well formed

Format: ST_IsValid (A:geometry)

Since: v1.2.0

Spark SQL example:

SELECT ST_IsValid(polygondf.countyshape)
FROM polygondf

ST_MakeValid

Introduction: Given an invalid polygon or multipolygon and removeHoles boolean flag, create a valid representation of the geometry.

Format: ST_MakeValid (A:geometry, removeHoles:Boolean)

Since: v1.2.0

Spark SQL example:

SELECT geometryValid.polygon
FROM table
LATERAL VIEW ST_MakeValid(polygon, false) geometryValid AS polygon

Note

Might return multiple polygons from a only one invalid polygon That's the reason why we need to use the LATERAL VIEW expression

Note

Throws an exception if the geometry isn't polygon or multipolygon

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)

Since: v1.2.0

Spark SQL example:

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

ST_IsSimple

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

Format: ST_IsSimple (A:geometry)

Since: v1.2.0

Spark SQL example:

SELECT ST_IsSimple(polygondf.countyshape)
FROM polygondf

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_AsText

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

Format: ST_AsText (A:geometry)

Since: v1.2.0

Spark SQL example:

SELECT ST_AsText(polygondf.countyshape)
FROM polygondf

ST_AsGeoJSON

Introduction: Return the GeoJSON string representation of a geometry

Format: ST_AsGeoJSON (A:geometry)

Since: v1.3.2

Spark SQL example:

SELECT ST_AsGeoJSON(polygondf.countyshape)
FROM polygondf

ST_NPoints

Introduction: Return points of the geometry

Since: v1.2.1

Format: ST_NPoints (A:geometry)

SELECT ST_NPoints(polygondf.countyshape)
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.

Since: v1.2.1

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

SELECT ST_SimplifyPreserveTopology(polygondf.countyshape, 10.0)
FROM polygondf

ST_GeometryType

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

Format: ST_GeometryType (A:geometry)

Since: v1.2.1

Spark SQL example:

SELECT ST_GeometryType(polygondf.countyshape)
FROM polygondf

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)

Since: v1.3.2

SELECT ST_LineMerge(geometry)
FROM df

ST_Azimuth

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

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

Since: v1.3.2

Spark SQL example:

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

Output: 3.141592653589793

ST_X

Introduction: Returns X Coordinate of given Point null otherwise.

Format: ST_X(pointA: Point)

Since: v1.3.2

Spark SQL example:

SELECT ST_X(ST_POINT(0.0 25.0))
Output: 0.0

ST_Y

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

Format: ST_Y(pointA: Point)

Since: v1.3.2

Spark SQL example:

SELECT ST_Y(ST_POINT(0.0 25.0))

Output: 25.0

ST_StartPoint

Introduction: Returns first point of given linestring.

Format: ST_StartPoint(geom: geometry)

Since: v1.3.2

Spark SQL example:

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

Output: POINT(100 150)

ST_EndPoint

Introduction: Returns last point of given linestring.

Format: ST_EndPoint(geom: geometry)

Since: v1.3.2

Spark SQL example:

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

Output: POINT(160 170)

ST_Boundary

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

Format: ST_Boundary(geom: geometry)

Since: v1.3.2

Spark 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_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)

Since: v1.3.2

Spark 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_GeometryN

Introduction: Return the 1-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.2

Spark SQL 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.2

Spark 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_Dump

Introduction: It expands the geometries. If the geometry is simple (Point, Polygon Linestring etc.) it returns the geometry itself, if the geometry is collection or multi it returns record for each of collection components.

Format: ST_Dump(geom: geometry)

Since: v1.3.2

Spark SQL example:

SELECT ST_Dump(ST_GeomFromText('MULTIPOINT ((10 40), (40 30), (20 20), (30 10))'))

Output: [POINT (10 40), POINT (40 30), POINT (20 20), POINT (30 10)]

ST_DumpPoints

Introduction: Returns list of Points which geometry consists of.

Format: ST_DumpPoints(geom: geometry)

Since: v1.3.2

Spark SQL example:

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

Output: [POINT (0 0), POINT (0 1), POINT (1 1), POINT (1 0), POINT (0 0)]

ST_IsClosed

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

Format: ST_IsClosed(geom: geometry)

Since: v1.3.2

Spark SQL example:

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

Output: false

ST_NumInteriorRings

Introduction: RETURNS number of interior rings of polygon geometries.

Format: ST_NumInteriorRings(geom: geometry)

Since: v1.3.2

Spark 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_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.2

Spark 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_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)

Since: v1.3.2

Spark SQL example:

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

Output: LINESTRING(0 0, 1 0)

ST_IsRing

Introduction: RETURN true if LINESTRING is ST_IsClosed and ST_IsSimple.

Format: ST_IsRing(geom: geometry)

Since: v1.3.2

Spark SQL example:

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

Output: true

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

SELECT ST_NumGeometries(df.geometry)
FROM df