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 types | image | Example |
|---|---|---|
Point |  | POINT (30 10) |
LineString |  | LINESTRING (30 10, 10 30, 40 40) |
Polygon |  | POLYGON ((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)) | |
MultiPoint |  | MULTIPOINT ((10 40), (40 30), (20 20), (30 10)) |
MULTIPOINT (10 40, 40 30, 20 20, 30 10) | ||
MultiLineString |  | MULTILINESTRING ((10 10, 20 20, 10 40), (40 40, 30 30, 40 20, 30 10)) |
MultiPolygon |  | MULTIPOLYGON (((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.
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.
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 |
+--------------------------------------------------------------------------------------------------+