地理空间函数
空间函数是用于处理和操作三维空间数据的函数集合。
CnosDB 使用 WKT（Well-known text）支持 地理空间类型（Geometry）的数据查询。
支持的类型请参考 地理空间类型
<geometry tag> <wkt data>
<geometry tag> ::= POINT | LINESTRING | POLYGON | MULTIPOINT |
MULTILINESTRING | MULTIPOLYGON | GEOMETRYCOLLECTION
<wkt data> ::= <point> | <linestring> | <polygon> | <multipoint> |
<multilinestring> | <multipolygon> | <geometrycollection>
| 几何类型 | 图片 | 示例 |
|---|---|---|
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
将空间几何对象 Geometry 以 WKB 格式返回。
ST_AsBinary(geometry)
查看 ST_AsBinary 示例
SELECT ST_AsBinary('POINT(0 3)');
+--------------------------------------------+
| st_AsBinary(Utf8("POINT(0 3)")) |
+--------------------------------------------+
| 010100000000000000000000000000000000000840 |
+--------------------------------------------+
ST_GeomFromWKB
把 WKB 格式二进制转为 Geometry 类型
ST_GeomFromWKB(wkb)
查看 ST_GeomFromWKB 示例
SELECT ST_GeomFromWKB(ST_AsBinary('POINT(0 3)'));
+-------------------------------------------------+
| st_GeomFromWKB(st_AsBinary(Utf8("POINT(0 3)"))) |
+-------------------------------------------------+
| POINT(0 3) |
+-------------------------------------------------+
ST_Distance
返回两个几何体的 2D 投影之间的最小欧氏距离。
ST_Distance(geometry1, gemometry2)
查看 ST_Distance 示例
计算两点之间的距离。
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 |
+--------------------------------------------------------------------------+
计算点到线的直线距离。
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 |
+--------------------------------------------------------------------------+
计算平面和平面之间的距离。
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
返回几何对象 2D 投影的笛卡尔面积。面积单位与用于表示输入几何体坐标的单位相同。 对于 Point、LineString、MultiPoint 和 MultiLineString、Line，此函数返回 0。 对于几何体集合，它返回集合中几何体的面积之和。
提示
部分几何图形不支持计算面积，对这些几何体计算面积会返回 0，如：Point、MultiPoint、LineString、MultiLineString、Line。 如果参数内容格式非法，返回值为 NULL。
ST_Area(geometry)
查看 ST_Area 示例
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
比较两个几何体，如果两个几何体完全相同，则返回 true。
提示
ST_Equals(A, B) 等价于 ST_Within(A, B) && ST_Within(B, A)
ST_Equals(A, B)
查看 ST_Area 示例
select ST_Equals('LINESTRING(0 0, 10 10)', 'LINESTRING(0 0, 5 5, 10 10)') st_equals;
+-----------+
| st_equals |
+-----------+
| true |
+-----------+
ST_Contains
如果几何对象A包含几何对象B，返回 true。
ST_Contains(A, B)
查看 ST_Contains 示例
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
如果两个几何对象相交，则返回 true。
ST_Intersects(A, B)
查看 ST_Intersects 示例
select ST_Intersects('LINESTRING(3 2, 7 6)', 'LINESTRING(3 4, 8 4)') st_intersects;
+---------------+
| st_intersects |
+---------------+
| true |
+---------------+
ST_Disjoint
如果两个几何对象不相接，返回 true。
ST_Disjoint(A, B)
查看 ST_Disjoint 示例
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
如果给定的Geometry对象A完全在对象B之内，则返回 true。
ST_Within(A, B)
查看 ST_Within 示例
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 |
+--------------------------------------------------------------------------------------------------+