# Data Lake Analytics的Geospatial分析函数

## 0. 简介

• WKT
• WKB
• GeoJson
• ESRI Geometry Object Json
• ESRI Shape

DLA采用4326坐标系标准，EPSG 4326使用经纬度坐标，属于地理坐标系。GPS采用的就是这个坐标系。

## 1. WKT数据

https://en.wikipedia.org/wiki/Well-known_text
http://www.opengeospatial.org/standards/wkt-crs

• POINT (0 0)
• LINESTRING (0 0, 1 1, 1 2)
• POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1))
• MULTIPOINT (0 0, 1 2)
• MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))
• MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))
• GEOMETRYCOLLECTION (POINT (2 3), LINESTRING (2 3, 3 4))

## 2. WKB数据

WKTWKB
POINT (0 0) 010100000000000000000000000000000000000000
LINESTRING (0 0, 1 1, 1 2) 01020000000300000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F0000000000000040
POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)) 01030000000200000005000000000000000000000000000000000000000000000000001040000000000000000000000000000010400000000000001040000000000000000000000000000010400000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F
MULTIPOINT (0 0, 1 2) 0104000000020000000101000000000000000000000000000000000000000101000000000000000000F03F0000000000000040
MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4)) 01050000000200000001020000000300000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F0000000000000040010200000003000000000000000000004000000000000008400000000000000840000000000000004000000000000014400000000000001040
MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1))) 01060000000200000001030000000200000005000000000000000000000000000000000000000000000000001040000000000000000000000000000010400000000000001040000000000000000000000000000010400000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F01030000000100000005000000000000000000F0BF000000000000F0BF00000000000000C0000000000000F0BF00000000000000C000000000000000C0000000000000F0BF00000000000000C0000000000000F0BF000000000000F0BF
GEOMETRYCOLLECTION (POINT (2 3), LINESTRING (2 3, 3 4)) 0107000000020000000101000000000000000000004000000000000008400102000000020000000000000000000040000000000000084000000000000008400000000000001040

## 3. GeoJson数据

GeoJson的详细描述：http://geojson.org/

## 6. 函数列表

NameDescription
ST_asText 将Geometry数据转成WKT格式的字符串数据。
ST_LineFromText 输入WKT格式的Line字符串数据，生成Line的Geometry数据。
ST_Point 从坐标系的坐标值（X, Y），生成对应的Point的Geometry数据。
ST_Polygon 输入WKT格式的Polygon字符串数据，生成Polygon的Geometry数据。
ST_Area 返回面或多面的面积。对于点、线，返回0.0。对于GeometryCollection，返回所有单个面积的和。
ST_GeometryFromText 输入WKT格式的字符串数据，生成Geometry数据。
ST_Buffer 获取几何对象和距离，然后返回表示围绕源对象的缓冲区的几何对象。
ST_Centroid 获取几何对象的中心点。
ST_CoordDim 返回几何对象的坐标值维度。
ST_Dimension 用于返回几何对象的维度。在这种情况下，维度是指长度和宽度。例如，点既没有长度也没有宽度，所以其维度为 0；而线只有长度却没有宽度，因此其维度为 1。
ST_IsClosed 判断Line或者MultiLine是否闭合。
ST_IsEmpty 判断几何对象是否为空。
ST_Length 计算Line或者MultiLine的长度。
ST_XMax 返回几何对象在坐标系中的最大X坐标值。
ST_XMin 返回几何对象在坐标系中的最小X坐标值。
ST_YMax 返回几何对象在坐标系中的最大Y坐标值。
ST_YMin 返回几何对象在坐标系中的最小Y坐标值。
ST_NumInteriorRing 以Polygon作为输入参数，并返回其内部环数。
ST_NumPoints 用于返回几何对象中的点（折点）数。
ST_IsRing 以Line作为输入参数，判断是否是环（如Line是闭合的）。
ST_StartPoint 用于返回Line的第一个点。
ST_EndPoint 用于返回Line的最后一个点。
ST_X 返回Point的X坐标。
ST_Y 返回Point的Y坐标。
ST_Boundary 输入一个几何对象，然后以几何对象形式返回其组合边界。
ST_Envelope 以多边形的形式返回几何对象的最小边界框。
ST_Difference 输入两个几何对象，然后返回表示两个源对象之差的几何对象。
ST_Distance 用于返回两个几何对象之间的距离。这一距离是两个几何对象的最近折点之间的距离。
ST_ExteriorRing 以Line形式返回面的外部环。
ST_Intersection 以两个几何对象作为输入参数，然后以二维几何对象的形式返回交集。
ST_SymDifference 返回表示两个几何对象间的点集对称差异的几何值对象。
ST_Contains 输入两个几何对象，判断第一个对象是否完全包含第二个对象。
ST_Crosses 以两个几何对象作为输入，如果这两个对象的交集生成的几何对象的维度小于两个源对象中的最大维度，则返回 1。交集对象所包含的点必须在两个源几何的内部，并且不等于其中任何一个源对象。否则，返回 0。
ST_Disjoint 输入两个几何对象，判断两个几何对象的交集是否为空集。
ST_Equals 判断两个几何对象是否完全相同。
ST_Intersects 判断两个几何对象的交集是否不生成空集。
ST_Overlaps 判断两个几何对象的交集生成的几何对象是否维度相同但不等于任一源对象。
ST_Relate 比较两个几何对象，判断是否满足“DE-9IM”模式（https://en.wikipedia.org/wiki/DE-9IM）矩阵字符串指定的条件。
ST_Touches 判断两个几何对象的公共点是否都不与两个几何对象的内部相交。
ST_Within 判断第一个几何对象是否完全位于第二个几何对象的范围内。
ST_asBinary 输入一个几何对象，然后返回其可识别的二进制WKB数据。
ST_GeometryFromWKBHexString 输入WKB的HEX字符串数据，返回对应的几何对象。
ST_pointFromWKBHexString 输入Point的WKB的HEX字符串数据，返回对应的Point几何对象。
ST_lineFromWKBHexString 输入Line的WKB的HEX字符串数据，返回对应的Line几何对象。
ST_polyFromWKBHexString 输入Polygon的WKB的HEX字符串数据，返回对应的Polygon几何对象。
ST_MPointFromWKBHexString 输入MultiPoint的WKB的HEX字符串数据，返回对应的MultiPoint几何对象。
ST_MLineFromWKBHexString 输入MultiLine的WKB的HEX字符串数据，返回对应的MultiLine几何对象。
ST_MPolyFromWKBHexString 输入MultiPolygon的WKB的HEX字符串数据，返回对应的MultiPolygon几何对象。
ST_GeometryFromWKB 输入WKB数据，返回对应的几何对象。
ST_pointFromWKB 输入Point的WKB数据，返回对应的Point几何对象。
ST_lineFromWKB 输入Line的WKB数据，返回对应的Line几何对象。
ST_polyFromWKB 输入Polygon的WKB数据，返回对应的Polygon几何对象。
ST_MPointFromWKB 输入MultiPoint的WKB数据，返回对应的MultiPoint几何对象。
ST_MLineFromWKB 输入MultiLine的WKB数据，返回对应的MultiLine几何对象。
ST_MPolyFromWKB 输入MultiPolygon的WKB数据，返回对应的MultiPolygon几何对象。
ST_GeometryFromGeoJson 输入GeoJson的字符串数据，返回对应的几何对象。
ST_GeometryFromJson 输入ESRI Geometry Object Json的字符串数据，返回对应的几何对象。
ST_asGeoJson 把几何对象转成GeoJson格式输出。
ST_asJson 把几何对象转成ESRI Geometry Object Json格式输出。
ST_GeometryFromEsriShape 输入ESRI Shape的二进制数据，返回对应的几何对象。
UDF_SYS_GEO_IN_CYCLE 仅适用于北半球：做基于地理位置的经纬度画圈
UDF_SYS_GEO_IN_RECTANGLE 仅适用于北半球：用于做基于地理位置的经纬度画矩形
UDF_SYS_GEO_DISTANCE 仅适用于北半球：用作一个经纬度列和一个固定的坐标点的距离计算

## 7. 函数定义与示例

• ST_asText
ST_asText(GEOMETRY) -> VARCHAR

select ST_asText(ST_LineFromText('LINESTRING (0 0, 1 1, 1 2)'));

+----------------------------+
| _col0                      |
+----------------------------+
| LINESTRING (0 0, 1 1, 1 2) |
+----------------------------+
• ST_LineFromText
ST_LineFromText(VARCHAR) -> GEOMETRY

select ST_asText(ST_LineFromText('LINESTRING (0 0, 1 1, 1 2)'));

+----------------------------+
| _col0                      |
+----------------------------+
| LINESTRING (0 0, 1 1, 1 2) |
+----------------------------+
• ST_Point
ST_Point(DOUBLE, DOUBLE) -> GEOMETRY

select ST_asText(ST_Point(30.2741500000,120.1551500000));

+----------------------------+
| _col0                      |
+----------------------------+
| POINT (30.27415 120.15515) |
+----------------------------+
• ST_Polygon
ST_Polygon(VARCHAR) -> GEOMETRY

select ST_asText(ST_Polygon('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));

+----------------------------------------------------------------+
| _col0                                                          |
+----------------------------------------------------------------+
| POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1)) |
+----------------------------------------------------------------+
• ST_Area
ST_Area(Geometry) -> DOUBLE

select ST_Area(ST_Polygon('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));

+-------+
| _col0 |
+-------+
|  15.0 |
+-------+

select ST_Area(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));

+-------+
| _col0 |
+-------+
|  16.0 |
+-------+
• ST_GeometryFromText
ST_GeometryFromText(VARCHAR) -> GEOMETRY

• ST_Buffer
ST_Buffer(GEOMETRY, DOUBLE) -> GEOMETRY

• ST_Centroid
ST_Centroid(GEOMETRY) -> GEOMETRY

select ST_asText(ST_Centroid(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));

+-----------------------+
| _col0                 |
+-----------------------+
| POINT (1.8125 1.8125) |
+-----------------------+

select ST_asText(ST_Centroid(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));

+---------------+
| _col0         |
+---------------+
| POINT (0.5 1) |
+---------------+

select ST_asText(ST_Centroid(ST_GeometryFromText('POINT (0.5 1)')));

+---------------+
| _col0         |
+---------------+
| POINT (0.5 1) |
+---------------+

select ST_asText(ST_Centroid(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))')));

+---------------------------------------------+
| _col0                                       |
+---------------------------------------------+
| POINT (2.033333333333333 2.033333333333333) |
+---------------------------------------------+
• ST_CoordDim
ST_CoordDim(GEOMETRY) -> BIGINT

select ST_CoordDim(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));

+-------+
| _col0 |
+-------+
|     2 |
+-------+

select ST_CoordDim(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));

+-------+
| _col0 |
+-------+
|     2 |
+-------+

select st_coorddim(st_geometryfromtext('point Z (60.567222 -140.404 5959)'));

+-------+
| _col0 |
+-------+
|     3 |
+-------+

select st_coorddim(st_geometryfromtext('point M (60.567222 -140.404 5250)'));

+-------+
| _col0 |
+-------+
|     3 |
+-------+

select st_coorddim(st_geometryfromtext('point ZM (60.567222 -140.404 5959 5250)'));

+-------+
| _col0 |
+-------+
|     4 |
+-------+
• ST_Dimension
ST_Dimension(GEOMETRY) -> BIGINT

select ST_Dimension(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));

+-------+
| _col0 |
+-------+
|     2 |
+-------+
• ST_IsClosed
ST_IsClosed(GEOMETRY) -> BOOLEAN

select ST_IsClosed(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+

select ST_IsClosed(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+

select ST_IsClosed(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+
• ST_IsEmpty
ST_IsEmpty(GEOMETRY) -> BOOLEAN

select ST_IsEmpty(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+

select ST_IsEmpty(null);

+-------+
| _col0 |
+-------+
|  NULL |
+-------+

SELECT ST_IsEmpty(ST_GeometryFromText('GEOMETRYCOLLECTION EMPTY'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+

SELECT ST_IsEmpty(ST_GeometryFromText('POLYGON EMPTY'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+
• ST_Length
ST_Length(GEOMETRY) -> DOUBLE

SELECT ST_Length(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));

+-------------------+
| _col0             |
+-------------------+
| 4.650281539872885 |
+-------------------+

SELECT ST_Length(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));

+-------------------+
| _col0             |
+-------------------+
| 6.656854249492381 |
+-------------------+
• ST_XMax
ST_XMax(GEOMETRY) -> DOUBLE

SELECT ST_XMax(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));

+-------+
| _col0 |
+-------+
|   5.0 |
+-------+

SELECT ST_XMax(ST_GeometryFromText('POINT (0.5 1)'));

+-------+
| _col0 |
+-------+
|   0.5 |
+-------+
• ST_XMin
ST_XMin(GEOMETRY) -> DOUBLE

SELECT ST_XMin(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));

+-------+
| _col0 |
+-------+
|   0.0 |
+-------+

SELECT ST_XMin(ST_GeometryFromText('POINT (0.5 1)'));

+-------+
| _col0 |
+-------+
|   0.5 |
+-------+
• ST_YMax
ST_YMax(GEOMETRY) -> DOUBLE

SELECT ST_YMax(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));

+-------+
| _col0 |
+-------+
|   4.0 |
+-------+

SELECT ST_YMax(ST_GeometryFromText('POINT (0.5 1)'));

+-------+
| _col0 |
+-------+
|   1.0 |
+-------+
• ST_YMin
ST_YMin(GEOMETRY) -> DOUBLE

SELECT ST_YMin(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));

+-------+
| _col0 |
+-------+
|   0.0 |
+-------+

SELECT ST_YMin(ST_GeometryFromText('POINT (0.5 1)'));

+-------+
| _col0 |
+-------+
|   1.0 |
+-------+
• ST_NumInteriorRing
ST_NumInteriorRing(GEOMETRY) -> BIGINT

SELECT ST_NumInteriorRing(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+
• ST_NumPoints
ST_NumPoints(GEOMETRY) -> BIGINT

SELECT ST_NumPoints(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));

+-------+
| _col0 |
+-------+
|     8 |
+-------+

SELECT ST_NumPoints(ST_GeometryFromText('POLYGON EMPTY'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+

SELECT ST_NumPoints(ST_GeometryFromText('POINT (0.5 1)'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+
• ST_IsRing
ST_IsRing(GEOMETRY) -> BOOLEAN

select ST_IsRing(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+

select ST_IsRing(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+
• ST_StartPoint
ST_StartPoint(GEOMETRY) -> GEOMETRY

select ST_asText(ST_StartPoint(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)')));

+-------------+
| _col0       |
+-------------+
| POINT (0 0) |
+-------------+
• ST_EndPoint
ST_EndPoint(GEOMETRY) -> GEOMETRY

select ST_asText(ST_EndPoint(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));

+-------------+
| _col0       |
+-------------+
| POINT (1 2) |
+-------------+
• ST_X
ST_X(GEOMETRY) -> DOUBLE

select ST_X(ST_GeometryFromText('POINT (0.5 1)'));

+-------+
| _col0 |
+-------+
|   0.5 |
+-------+
• ST_Y
ST_Y(GEOMETRY) -> DOUBLE

select ST_Y(ST_GeometryFromText('POINT (0.5 1)'));

+-------+
| _col0 |
+-------+
|   1.0 |
+-------+
• ST_Boundary
ST_Boundary(GEOMETRY) -> GEOMETRY

select ST_asText(ST_Boundary(ST_GeometryFromText('POINT (0.5 1)')));

+-------------+
| _col0       |
+-------------+
| POINT EMPTY |
+-------------+

select ST_asText(ST_Boundary(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));

+---------------------------+
| _col0                     |
+---------------------------+
| MULTIPOINT ((0 0), (1 2)) |
+---------------------------+

select ST_asText(ST_Boundary(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));

+-------------------------------------------------------------------------------------------------------------+
| _col0                                                                                                       |
+-------------------------------------------------------------------------------------------------------------+
| MULTILINESTRING ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1), (-1 -1, -2 -1, -2 -2, -1 -2, -1 -1)) |
+-------------------------------------------------------------------------------------------------------------+
• ST_Envelope
ST_Envelope(GEOMETRY) -> GEOMETRY

select ST_asText(ST_Envelope(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));

+-------------------------------------------+
| _col0                                     |
+-------------------------------------------+
| POLYGON ((-2 -2, 4 -2, 4 4, -2 4, -2 -2)) |
+-------------------------------------------+
• ST_Difference
ST_Difference(GEOMETRY, GEOMETRY) -> GEOMETRY

select ST_asText(ST_Difference(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTILINESTRING ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1), (-1 -1, -2 -1, -2 -2, -1 -2, -1 -1))')));

+--------------------------------------------------------------------------------------------------------------+
| _col0                                                                                                        |
+--------------------------------------------------------------------------------------------------------------+
| MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1)), ((-1 -1, -2 -1, -2 -2, -1 -2, -1 -1))) |
+--------------------------------------------------------------------------------------------------------------+

select ST_asText(ST_Difference(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));

+--------------------+
| _col0              |
+--------------------+
| MULTIPOLYGON EMPTY |
+--------------------+
• ST_Distance
ST_Distance(GEOMETRY, GEOMETRY) -> DOUBLE

select ST_Distance(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));

+-------+
| _col0 |
+-------+
|   0.0 |
+-------+

select ST_Distance(
ST_GeometryFromText('POINT(0 0)'),
ST_GeometryFromText('POINT(1 1)'));

+--------------------+
| _col0              |
+--------------------+
| 1.4142135623730951 |
+--------------------+
• ST_ExteriorRing
ST_ExteriorRing(GEOMETRY) -> GEOMETRY

select ST_asText(ST_ExteriorRing(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))')));

+--------------------------------------+
| _col0                                |
+--------------------------------------+
| LINESTRING (0 0, 4 0, 4 4, 0 4, 0 0) |
+--------------------------------------+
• ST_Intersection
ST_Intersection(GEOMETRY, GEOMETRY) -> GEOMETRY

select ST_asText(ST_Intersection(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));

+--------------------------------------------------------------------------------------------------------------+
| _col0                                                                                                        |
+--------------------------------------------------------------------------------------------------------------+
| MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1)), ((-1 -1, -2 -1, -2 -2, -1 -2, -1 -1))) |
+--------------------------------------------------------------------------------------------------------------+

select ST_asText(ST_Intersection(
ST_GeometryFromText('POINT(0 0)'),
ST_GeometryFromText('POINT(1 1)')));

+--------------------+
| _col0              |
+--------------------+
| MULTIPOLYGON EMPTY |
+--------------------+
• ST_SymDifference
ST_SymDifference(GEOMETRY, GEOMETRY) -> GEOMETRY

select ST_asText(ST_SymDifference(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));

+--------------------+
| _col0              |
+--------------------+
| MULTIPOLYGON EMPTY |
+--------------------+

select ST_asText(ST_SymDifference(
ST_GeometryFromText('POINT(0 0)'),
ST_GeometryFromText('POINT(1 1)')));

+---------------------------+
| _col0                     |
+---------------------------+
| MULTIPOINT ((0 0), (1 1)) |
+---------------------------+

• ST_Contains
ST_Contains(GEOMETRY, GEOMETRY) -> BOOLEAN

select ST_Contains(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+

select ST_Contains(
ST_GeometryFromText('POINT(0 0)'),
ST_GeometryFromText('POINT(1 1)'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+
• ST_Crosses
ST_Crosses(GEOMETRY, GEOMETRY) -> BOOLEAN

select ST_Crosses(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+

select ST_Crosses(
ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
ST_GeometryFromText('LINESTRING(0 2, 2 0)'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+
• ST_Disjoint
ST_Disjoint(GEOMETRY, GEOMETRY) -> BOOLEAN

select ST_Disjoint(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+

select ST_Disjoint(
ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
ST_GeometryFromText('LINESTRING(0 2, 2 4)'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+
• ST_Equals
ST_Equals(GEOMETRY, GEOMETRY) -> BOOLEAN

select ST_Equals(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+

select ST_Equals(
ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
ST_GeometryFromText('LINESTRING(0 2, 2 4)'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+
• ST_Intersects
ST_Intersects(GEOMETRY, GEOMETRY) -> BOOLEAN

select ST_Intersects(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+

select ST_Intersects(
ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
ST_GeometryFromText('LINESTRING(0 2, 2 4)'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+
• ST_Overlaps
ST_Overlaps(GEOMETRY, GEOMETRY) -> BOOLEAN

select ST_Overlaps(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+

select ST_Overlaps(
ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
ST_GeometryFromText('LINESTRING(0 0, 1.5 1.5)'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+
• ST_Relate
ST_Relate(GEOMETRY, GEOMETRY, VARCHAR) -> BOOLEAN

select ST_Relate(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
'102101FF2');

+-------+
| _col0 |
+-------+
|     0 |
+-------+

select ST_Relate(
ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
ST_GeometryFromText('LINESTRING(0 0, 1.5 1.5)'),
'1*1***1**');

+-------+
| _col0 |
+-------+
|     1 |
+-------+
• ST_Touches
ST_Touches(GEOMETRY, GEOMETRY) -> BOOLEAN

select ST_Touches(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+

select ST_Touches(
ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
ST_GeometryFromText('LINESTRING(0 0, 1 1)'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+
• ST_Within
ST_Within(GEOMETRY, GEOMETRY) -> BOOLEAN

select ST_Within(
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));

+-------+
| _col0 |
+-------+
|     1 |
+-------+

select ST_Within(
ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
ST_GeometryFromText('LINESTRING(0 0, 1 1)'));

+-------+
| _col0 |
+-------+
|     0 |
+-------+
• ST_asBinary
ST_asBinary(GEOMETRY) -> VARBINARY

• ST_GeometryFromWKBHexString
ST_GeometryFromWKBHexString(VARCHAR) -> GEOMETRY

select ST_ASTEXT(ST_GeometryFromWKBHexString('000000000140000000000000004010000000000000'));

+-------------+
| _col0       |
+-------------+
| POINT (2 4) |
+-------------+
• ST_pointFromWKBHexString
ST_pointFromWKBHexString(VARCHAR) -> GEOMETRY

select ST_ASTEXT(ST_pointFromWKBHexString('000000000140000000000000004010000000000000'));

+-------------+
| _col0       |
+-------------+
| POINT (2 4) |
+-------------+
• ST_lineFromWKBHexString
ST_lineFromWKBHexString(VARCHAR) -> GEOMETRY

• ST_polyFromWKBHexString
ST_polyFromWKBHexString(VARCHAR) -> GEOMETRY

• ST_MPointFromWKBHexString
ST_MPointFromWKBHexString(VARCHAR) -> GEOMETRY

SELECT ST_asText(ST_MPointFromWKBHexString('0104000000020000000101000000000000000000000000000000000000000101000000000000000000F03F0000000000000040'));

+---------------------------+
| _col0                     |
+---------------------------+
| MULTIPOINT ((0 0), (1 2)) |
+---------------------------+
• ST_MLineFromWKBHexString
ST_MLineFromWKBHexString(VARCHAR) -> GEOMETRY

• ST_MPolyFromWKBHexString
ST_MPolyFromWKBHexString(VARCHAR) -> GEOMETRY

• ST_GeometryFromWKB
ST_GeometryFromWKB(VARBINARY) -> GEOMETRY

select ST_ASTEXT(ST_GeometryFromWKB(from_hex('000000000140000000000000004010000000000000')));

+-------------+
| _col0       |
+-------------+
| POINT (2 4) |
+-------------+
• ST_pointFromWKB
ST_pointFromWKB(VARBINARY) -> GEOMETRY

select ST_ASTEXT(ST_pointFromWKB(from_hex('000000000140000000000000004010000000000000')));

+-------------+
| _col0       |
+-------------+
| POINT (2 4) |
+-------------+
• ST_lineFromWKB
ST_lineFromWKB(VARBINARY) -> GEOMETRY

• ST_polyFromWKB
ST_polyFromWKB(VARBINARY) -> GEOMETRY

• ST_MPointFromWKB
ST_MPointFromWKB(VARBINARY) -> GEOMETRY

SELECT ST_asText(ST_MPointFromWKB(from_hex('0104000000020000000101000000000000000000000000000000000000000101000000000000000000F03F0000000000000040')));

+---------------------------+
| _col0                     |
+---------------------------+
| MULTIPOINT ((0 0), (1 2)) |
+---------------------------+
• ST_MLineFromWKB
ST_MLineFromWKB(VARBINARY) -> GEOMETRY

• ST_MPolyFromWKB
ST_MPolyFromWKB(VARBINARY) -> GEOMETRY

• ST_GeometryFromGeoJson
ST_GeometryFromGeoJson(VARCHAR) -> GEOMETRY

• ST_GeometryFromJson
ST_GeometryFromJson(VARCHAR) -> GEOMETRY

• ST_asGeoJson
ST_asGeoJson(GEOMETRY) -> VARCHAR

SELECT ST_asGeoJson(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));

+-----------------------------------------------------------------------------------------------+
| _col0                                                                                         |
+-----------------------------------------------------------------------------------------------+
| {"type":"MultiLineString","coordinates":[[[0,0],[1,1],[1,2]],[[2,3],[3,2],[5,4]]],"crs":null} |
+-----------------------------------------------------------------------------------------------+
• ST_asJson
ST_asJson(GEOMETRY) -> VARCHAR

SELECT ST_asJson(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));

+-----------------------------------------------------+
| _col0                                               |
+-----------------------------------------------------+
| {"paths":[[[0,0],[1,1],[1,2]],[[2,3],[3,2],[5,4]]]} |
+-----------------------------------------------------+
• ST_GeometryFromEsriShape
ST_GeometryFromEsriShape(VARBINARY) -> GEOMETRY

SELECT california_counties.name,
COUNT(*) cnt
FROM california_counties
CROSS JOIN earthquakes
WHERE ST_CONTAINS (ST_GeometryFromesrishape(california_counties.boundaryshape), ST_POINT(earthquakes.longitude, earthquakes.latitude))
GROUP BY  california_counties.name
ORDER BY  cnt DESC, california_counties.name;

+-----------------+------+
| name            | cnt  |
+-----------------+------+
| San Benito      |    8 |
| San Bernardino  |    7 |
| Riverside       |    6 |
| Inyo            |    5 |
| Imperial        |    3 |
| San Diego       |    2 |
| Kern            |    1 |
| Kings           |    1 |
| Monterey        |    1 |
| San Luis Obispo |    1 |
| Santa Clara     |    1 |
| Ventura         |    1 |
+-----------------+------+
• UDF_SYS_GEO_IN_CYCLE
UDF_SYS_GEO_IN_CYCLE(longitude, latitude, point, radius) -> BOOLEAN

SELECT count(*) as cnt FROM earthquakes
WHERE UDF_SYS_GEO_IN_CYCLE(longitude,latitude, '120.85979,30.011984', 5000000000000) = true;

+------+
| cnt  |
+------+
| 2858 |
+------+
• UDF_SYS_GEO_IN_RECTANGLE
UDF_SYS_GEO_IN_RECTANGLE(longitude, latitude, pointA, pointB) -> BOOLEAN

SELECT count(*) as cnt FROM earthquakes
WHERE UDF_SYS_GEO_IN_RECTANGLE(longitude, latitude, '69.037,36.5759', '142.018,67.8713')=true;

+------+
| cnt  |
+------+
|   55 |
+------+
• UDF_SYS_GEO_DISTANCE
UDF_SYS_GEO_DISTANCE(longitude, latitude, pointA) -> INTEGER

SELECT count(*) as cnt FROM earthquakes
WHERE UDF_SYS_GEO_DISTANCE(longitude, latitude, '69.037,36.5759') > 10000;

+------+
| cnt  |
+------+
| 2857 |
+------+

posted @ 2019-02-21 15:19  阿里云云栖社区  阅读(...)  评论(...编辑  收藏