Skip to main content
Version: latest

Geospatial Functions

Geospatial functions are a collection of functions used to handle and manipulate three-dimensional spatial data.

CnosDB uses WKT (Well-known text) to support geospatial type (Geometry) data queries.

Please refer to Geospatial Types for supported types

<geometry tag> <wkt data>

<geometry tag> ::= POINT | LINESTRING | POLYGON | MULTIPOINT | 
                   MULTILINESTRING | MULTIPOLYGON | GEOMETRYCOLLECTION
                   
<wkt data> ::= <point> | <linestring> | <polygon> | <multipoint> | 
               <multilinestring> | <multipolygon> | <geometrycollection>
Geometry typesimageExample
PointPOINT (30 10)
LineStringLINESTRING (30 10, 10 30, 40 40)
PolygonPOLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))
POLYGON ((35 10, 45 45, 15 40, 10 20, 35 10), (20 30, 35 35, 30 20, 20 30))
MultiPointMULTIPOINT ((10 40), (40 30), (20 20), (30 10))
MULTIPOINT (10 40, 40 30, 20 20, 30 10)
MultiLineStringMULTILINESTRING ((10 10, 20 20, 10 40), (40 40, 30 30, 40 20, 30 10))
MultiPolygonMULTIPOLYGON (((30 20, 45 40, 10 40, 30 20)), ((15 5, 40 10, 10 20, 5 10, 15 5)))
MULTIPOLYGON (((40 40, 20 45, 45 30, 40 40)), ((20 35, 10 30, 10 10, 30 5, 45 20, 20 35), (30 20, 20 15, 20 25, 30 20)))
GeometryCollection GEOMETRYCOLLECTION (POINT (40 10), LINESTRING (10 10, 20 20, 10 40), POLYGON ((40 40, 20 45, 45 30, 40 40)))

ST_AsBinary​

Return the spatial geometry object Geometry in WKB format.

ST_AsBinary(geometry)
View ST_AsBinary Example
SELECT ST_AsBinary('POINT(0 3)');
+--------------------------------------------+
| st_AsBinary(Utf8("POINT(0 3)"))            |
+--------------------------------------------+
| 010100000000000000000000000000000000000840 |
+--------------------------------------------+

ST_GeomFromWKB​

Convert Well-known Binary (WKB) format binary to Geometry type

ST_GeomFromWKB(wkb)
View ST_GeomFromWKB Example
SELECT ST_GeomFromWKB(ST_AsBinary('POINT(0 3)'));
+-------------------------------------------------+
| st_GeomFromWKB(st_AsBinary(Utf8("POINT(0 3)"))) |
+-------------------------------------------------+
| POINT(0 3)                                      |
+-------------------------------------------------+

ST_Distance​

Returns the minimum Euclidean distance between the 2D projections of two geometric shapes.

ST_Distance(geometry1, gemometry2)
View ST_Distance Example

Calculate the distance between two points.

SELECT ST_Distance('POINT(0 0)', 'LINESTRING (30 10, 10 30, 40 40)');
+--------------------------------------------------------------------------+
| ST_Distance(Utf8("POINT(0 0)"),Utf8("LINESTRING (30 10, 10 30, 40 40)")) |
+--------------------------------------------------------------------------+
| 28.284271247461902                                                       |
+--------------------------------------------------------------------------+

Calculate the straight-line distance from a point to a line.**

SELECT ST_Distance('POINT(0 0)', 'LINESTRING (30 10, 10 30, 40 40)');
+--------------------------------------------------------------------------+
| st_distance(Utf8("POINT(0 0)"),Utf8("LINESTRING (30 10, 10 30, 40 40)")) |
+--------------------------------------------------------------------------+
| 28.284271247461902                                                       |
+--------------------------------------------------------------------------+

Calculate the distance between planes.

SELECT ST_Distance('POLYGON((0 2,1 1,0 -1,0 2))', 'POLYGON((-1 -3,-2 -1,0 -3,-1 -3))');
+--------------------------------------------------------------------------------------------+
| st_distance(Utf8("POLYGON((0 2,1 1,0 -1,0 2))"),Utf8("POLYGON((-1 -3,-2 -1,0 -3,-1 -3))")) |
+--------------------------------------------------------------------------------------------+
| 1.4142135623730951                                                                         |
+--------------------------------------------------------------------------------------------+

ST_Area​

Returns the Cartesian area of the 2D projection of the geometric object.The unit of area is the same as the unit used to represent the coordinates of the input geometry. For Point, LineString, MultiPoint, and MultiLineString, Line, this function returns 0. For a collection of geometric shapes, it returns the sum of the areas of the shapes in the collection.

tip

Some geometric shapes do not support area calculation, calculating the area of these geometric objects will return 0, such as: Point, MultiPoint, LineString, MultiLineString, Line. If the parameter content format is invalid, the return value is NULL.

ST_Area(geometry)
View ST_Area Example
SELECT ST_Area('POLYGON ((40 40, 20 45, 45 30, 40 40))');
+---------------------------------------------------------+
| ST_Area(Utf8("POLYGON ((40 40, 20 45, 45 30, 40 40))")) |
+---------------------------------------------------------+
| 87.5                                                    |
+---------------------------------------------------------+

ST_Equals​

Compare two geometric shapes, if two geometric shapes are exactly the same, return true.

tip

ST_Equals(A, B) is equivalent to ST_Within(A, B) && ST_Within(B, A)

ST_Equals(A, B)
View ST_Area Example
select ST_Equals('LINESTRING(0 0, 10 10)', 'LINESTRING(0 0, 5 5, 10 10)') st_equals;
+-----------+
| st_equals |
+-----------+
| true      |
+-----------+

ST_Contains​

If geometric object A contains geometric object B, return true.

ST_Contains(A, B)
View ST_Contains Example
select ST_Contains('POLYGON((0 0,0 3,3 0,0 0))', 'POLYGON((0 0,0 1,1 0,0 0))') st_contains;
+-------------+
| st_contains |
+-------------+
| true        |
+-------------+

ST_Intersects​

If two geometric objects intersect, return true.

ST_Intersects(A, B)

View ST_Intersects Example
select ST_Intersects('LINESTRING(3 2, 7 6)', 'LINESTRING(3 4, 8 4)') st_intersects;
+---------------+
| st_intersects |
+---------------+
| true          |
+---------------+

ST_Disjoint​

If two geometric objects do not intersect, return true.

ST_Disjoint(A, B)

View ST_Disjoint Example
select ST_Disjoint('LINESTRING(0 0,-3 -3)', 'LINESTRING(0 1,1 0)');
+------------------------------------------------------------------------+
| ST_Disjoint(Utf8("LINESTRING(0 0,-3 -3)"),Utf8("LINESTRING(0 1,1 0)")) |
+------------------------------------------------------------------------+
| true                                                                   |
+------------------------------------------------------------------------+

ST_Within​

Returns true if the given Geometry object A is completely inside object B.

ST_Within(A, B)
View ST_Within Example
select ST_Within('POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))', 'POLYGON((0 0, 0 3, 3 3, 3 0, 0 0))');
+--------------------------------------------------------------------------------------------------+
| ST_Within(Utf8("POLYGON((1 1, 1 2, 2 2, 2 1, 1 1))"),Utf8("POLYGON((0 0, 0 3, 3 3, 3 0, 0 0))")) |
+--------------------------------------------------------------------------------------------------+
| true                                                                                             |
+--------------------------------------------------------------------------------------------------+