Spatial functions¶

ST_Point()¶

Returns a point with the specified longitude and latitude.

Null behavior: default

Function signature¶

ST_Point(longitude Numeric, latitude Numeric) -> Geography(Point)

Examples¶

RETURN ST_Point(37.7749, -12.4194) AS point1

Return:

+-------------------------+
| point1                  |
+-------------------------+
| POINT(37.7749 -12.4194) |
+-------------------------+

ST_GeogFromText() / ST_GeogFromWKT()¶

Returns a spatial value from a Well-Known Text (WKT) string representation.

Null behavior: default

Function signature¶

ST_GeogFromText(wkt String) -> Geography(Any)

or

ST_GeogFromWKT(wkt String) -> Geography(Any)

Null behavior: default

Examples¶

Return a point¶

RETURN ST_GeogFromText('POINT(37.7749 -12.4194)') AS point1

Return:

+-------------------------+
| point1                  |
+-------------------------+
| POINT(37.7749 -12.4194) |
+-------------------------+

Return a linestring¶

RETURN ST_GeogFromText('LINESTRING(1 2, 2 4, 4 8, 8 16)') AS linestring1

Return:

+-------------------------------------------------+
| linestring1                                     |
+-------------------------------------------------+
| LINESTRING(1.0 2.0, 2.0 4.0, 4.0 8.0, 8.0 16.0) |
+-------------------------------------------------+

Return a polygon¶

RETURN ST_GeogFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))') AS polygon1

Return:

+------------------------------------------------------------+
| polygon1                                                   |
+------------------------------------------------------------+
| POLYGON((0.0 0.0, 0.0 10.0, 10.0 10.0, 10.0 0.0, 0.0 0.0)) |
+------------------------------------------------------------+

ST_ASText()¶

Returns the Well-Known Text (WKT) string representation of a spatial value.

Null behavior: default

Function signature¶

ST_ASText(g Geography) -> String

Examples¶

RETURN ST_ASText(ST_Point(37.7749, -12.4194)) AS string_point

Return:

+-------------------------+
| string_point            |
+-------------------------+
| POINT(37.7749 -12.4194) |
+-------------------------+

ST_Centroid()¶

Returns the centroid of a spatial value, which is the geometric center of the shape.

Null behavior: default

Function signature¶

ST_Centroid(g Geography) -> Geography(Point)

Examples¶

Return the centroid of a point¶

RETURN ST_Centroid(ST_GeogFromText('POINT(37.7749 -12.4194)')) AS centroid1

Return:

+-------------------------+
| centroid1               |
+-------------------------+
| POINT(37.7749 -12.4194) |
+-------------------------+

Return the centroid of a linestring¶

RETURN ST_Centroid(ST_GeogFromText('LINESTRING(1 2, 2 4, 4 8, 8 16)')) AS centroid1

Return:

+--------------------------------------------+
| centroid1                                  |
+--------------------------------------------+
| POINT(4.462127805439562 9.001796271028539) |
+--------------------------------------------+

Return the centroid of a polygon¶

RETURN ST_Centroid(ST_GeogFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) AS centroid1

Return:

+--------------------------------------------+
| centroid1                                  |
+--------------------------------------------+
| POINT(5.000000000000015 5.005958633534428) |
+--------------------------------------------+

ST_ISValid()¶

Returns true if a spatial value is valid. Otherwise, it returns false.

Null behavior: default

Function signature¶

ST_ISValid(g Geography) -> Boolean

Examples¶

RETURN ST_ISValid(ST_Point(37.7749, -124.4194)) AS isValidPoint

Return:

+--------------+
| isValidPoint |
+--------------+
| false        |
+--------------+

ST_Intersects()¶

Returns true if two given spatial values intersect (sharing at least one common point). Otherwise, it returns false.

Null behavior: default

Function signature¶

ST_Intersects(g1 Geography, g2 Geography) -> Boolean

Examples¶

RETURN ST_Intersects(ST_GeogFromText('POINT(0 1)'), ST_GeogFromText('POLYGON((0 1, 1 2, 2 3, 0 1))')) AS v

Return:

+------+
| v    |
+------+
| true |
+------+

ST_IntersectionPoints¶

Returns a list of points where the boundaries of two spatial values intersect.

Null behavior: default

Function signature¶

ST_IntersectionPoints(g1 Geography, g2 Geography) -> LIST<Geography(Point)>

Examples¶

RETURN ST_IntersectionPoints(ST_GeogFromText("LineString(0 1,1 0)"),ST_GeogFromText("LineString(0 0,1 1)")) AS v

Return:

+------------------------------------------------+
| v                                              |
+------------------------------------------------+
| [POINT(0.4999999999999999 0.5000571197534015)] |
+------------------------------------------------+

ST_Covers()¶

Given two spatial values g1 and g2, returns true if g1 completely contains g2 (i.e., all points of g2 lie within or on the boundary of g1). Otherwise, it returns false.

Null behavior: default

Function signature¶

ST_Covers(g1 Geography, g2 Geography) -> Boolean

Examples¶

RETURN ST_Covers(ST_GeogFromText('POLYGON((0 1, 1 2, 3 0, 0 1))'), ST_GeogFromText('POINT(1 1)')) AS v

Return:

+------+
| v    |
+------+
| true |
+------+

ST_CoveredBy¶

Given two spatial values g1 and g2, returns true if g2 completely contains g1 (i.e., all points of g1 lie within or on the boundary of g2). Otherwise, it returns false.

Null behavior: default

Function signature¶

ST_CoveredBy(g1 Geography, g2 Geography) -> Boolean

Examples¶

RETURN ST_CoveredBy(ST_GeogFromText('POLYGON((0 1, 1 2, 3 0, 0 1))'), ST_GeogFromText('POINT(1 1)')) AS v

Return:

+-------+
| v     |
+-------+
| false |
+-------+

ST_DWithin¶

Given two spatial values g1 and g2 and a distance threshold in meters, returns true if the distance between at least one point in g1 and at least one point in g2 is less than or equal to the threshold. Otherwise, it returns false.

Null behavior: default

Function signature¶

ST_DWithin(g1 Geography, g2 Geography, distance DOUBLE) -> Boolean

Examples¶

RETURN ST_DWithin(ST_GeogFromText("Point(0 0)"),ST_GeogFromText("Point(10 10)"),20000000000) AS v

Return:

+------+
| v    |
+------+
| true |
+------+

ST_Distance¶

Returns the smallest possible distance (measured by meters) between two spatial values.

Null behavior: default

Function signature¶

ST_Distance(g1 Geography, g2 Geography) -> DOUBLE

Examples¶

RETURN ST_Distance(ST_GeogFromText("Point(0 0)"),ST_GeogFromText("Point(10 10)")) AS distance

Return:

+--------------------+
| distance           |
+--------------------+
| 1568523.0187677438 |
+--------------------+

S2_CellIdFromPoint¶

Returns the S2 Cell ID that covers a point.

Null behavior: default

Function signature¶

S2_CellIdFromPoint(g Geography(Point)) -> UINT64

Examples¶

RETURN S2_CellIdFromPoint(ST_GeogFromText("Point(1 1)")) AS v

Return:

+---------------------+
| v                   |
+---------------------+
| 1153277837650709461 |
+---------------------+

S2_CoveringCellIds¶

Returns a list of the S2 Cell IDs that cover a spatial value.

Null behavior: default

Function signature¶

S2_CoveringCellIds(g Geography) -> LIST<UINT64>

Examples¶

RETURN S2_CoveringCellIds(ST_GeogFromText("POLYGON((0 1, 1 2, 2 3, 0 1))")) AS v

Return:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| v                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [1152391494368201343,1153466862374223872,1153554823304445952,1153836298281156608,1153959443583467520,1154240918560178176,1160503736791990272,1160591697722212352] |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+