Data Lake Analytics的Geospatial分析函式

weixin_33806914發表於2019-02-21
函式
  1. 簡介

為滿足部分客戶在雲上做Geometry資料的分析需求，阿里雲Data Lake Analytics（以下簡稱：DLA）支援多種格式的地理空間資料處理函式，符合Open Geospatial Consortium’s (OGC) OpenGIS規範，支援的常用資料格式包括：

WKT
WKB
GeoJson
ESRI Geometry Object Json
ESRI Shape
DLA採用4326座標系標準，EPSG 4326使用經緯度座標，屬於地理座標系。GPS採用的就是這個座標系。

  1. WKT資料

詳細描述：
https://en.wikipedia.org/wiki...
http://www.opengeospatial.org...

支援類似如下的WKT相關字串。

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))

  1. WKB資料

詳細描述：https://en.wikipedia.org/wiki...

上述WKT資料和WKB的對應示例：

WKT WKB
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

  1. GeoJson資料

GeoJson的詳細描述：http://geojson.org/

  1. ESRI Geometry Object Json資料

規範說明：http://resources.esri.com/hel...

  1. ESRI Shape二進位制資料

規範說明：http://www.esri.com/LIBRARY/W...

  1. 函式列表

Name Description
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...）矩陣字串指定的條件。
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 僅適用於北半球：用作一個經緯度列和一個固定的座標點的距離計算

  1. 函式定義與示例

ST_asText
ST_asText(GEOMETRY) -> VARCHAR
將Geometry資料轉成WKT格式的字串資料。

示例：

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
輸入WKT格式的Line字串資料，生成Line的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
從座標系的座標值（X, Y），生成對應的Point的Geometry資料。

示例：

select ST_asText(ST_Point(30.2741500000,120.1551500000));

_col0
POINT (30.27415 120.15515)

ST_Polygon
ST_Polygon(VARCHAR) -> GEOMETRY
輸入WKT格式的Polygon字串資料，生成Polygon的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
返回面或多面的面積。對於點、線，返回0.0。對於GeometryCollection，返回所有單個面積的和。

示例：

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
輸入WKT格式的字串資料，生成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
用於返回幾何物件的維度。在這種情況下，維度是指長度和寬度。例如，點既沒有長度也沒有寬度，所以其維度為 0；而線只有長度卻沒有寬度，因此其維度為 1。

示例：

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
判斷Line或者MultiLine是否閉合。

示例：

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
計算Line或者MultiLine的長度。

示例：

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
返回幾何物件在座標系中的最大X座標值。

示例：

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
返回幾何物件在座標系中的最小X座標值。

示例：

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
返回幾何物件在座標系中的最大Y座標值。

示例：

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
返回幾何物件在座標系中的最小Y座標值。

示例：

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
以Polygon作為輸入引數，並返回其內部環數。

示例：

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
以Line作為輸入引數，判斷是否是環（如Line是閉合的）。

示例：

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
用於返回Line的第一個點。

示例：

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
用於返回Line的最後一個點。

示例：

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

_col0
POINT (1 2)

ST_X
ST_X(GEOMETRY) -> DOUBLE
返回Point的X座標。

示例：

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

_col0
0.5

ST_Y
ST_Y(GEOMETRY) -> DOUBLE
返回Point的Y座標。

示例：

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
以Line形式返回面的外部環。

示例：

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))

下圖的陰影部分顯示了對稱差異的結果。對稱差異為包括兩個表面的多表面圖形：其中一個表面包含位於正方形之內、圓形之外的所有點，另外一個表面包含位於圓形之內、正方形之外的所有點。

image.png | left | 223x166

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
以兩個幾何物件作為輸入，如果這兩個物件的交集生成的幾何物件的維度小於兩個源物件中的最大維度，則返回 1。交集物件所包含的點必須在兩個源幾何的內部，並且不等於其中任何一個源物件。否則，返回 0。

示例：

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
比較兩個幾何物件，判斷是否滿足“DE-9IM”模式（https://en.wikipedia.org/wiki...）矩陣字串指定的條件。

示例：

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)'),
'111');

_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
輸入一個幾何物件，然後返回其可識別的二進位制WKB資料。

ST_GeometryFromWKBHexString
ST_GeometryFromWKBHexString(VARCHAR) -> GEOMETRY
輸入WKB的HEX字串資料，返回對應的幾何物件。

示例：

select ST_ASTEXT(ST_GeometryFromWKBHexString('000000000140000000000000004010000000000000'));

_col0
POINT (2 4)

ST_pointFromWKBHexString
ST_pointFromWKBHexString(VARCHAR) -> GEOMETRY
輸入Point的WKB的HEX字串資料，返回對應的Point幾何物件。

示例：

select ST_ASTEXT(ST_pointFromWKBHexString('000000000140000000000000004010000000000000'));

_col0
POINT (2 4)

ST_lineFromWKBHexString
ST_lineFromWKBHexString(VARCHAR) -> GEOMETRY
輸入Line的WKB的HEX字串資料，返回對應的Line幾何物件。

ST_polyFromWKBHexString
ST_polyFromWKBHexString(VARCHAR) -> GEOMETRY
輸入Polygon的WKB的HEX字串資料，返回對應的Polygon幾何物件。

ST_MPointFromWKBHexString
ST_MPointFromWKBHexString(VARCHAR) -> GEOMETRY
輸入MultiPoint的WKB的HEX字串資料，返回對應的MultiPoint幾何物件。

示例：

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

_col0
MULTIPOINT ((0 0), (1 2))

ST_MLineFromWKBHexString
ST_MLineFromWKBHexString(VARCHAR) -> GEOMETRY
輸入MultiLine的WKB的HEX字串資料，返回對應的MultiLine幾何物件。

ST_MPolyFromWKBHexString
ST_MPolyFromWKBHexString(VARCHAR) -> GEOMETRY
輸入MultiPolygon的WKB的HEX字串資料，返回對應的MultiPolygon幾何物件。

ST_GeometryFromWKB
ST_GeometryFromWKB(VARBINARY) -> GEOMETRY
輸入WKB資料，返回對應的幾何物件。

示例：

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

_col0
POINT (2 4)

ST_pointFromWKB
ST_pointFromWKB(VARBINARY) -> GEOMETRY
輸入Point的WKB資料，返回對應的Point幾何物件。

示例：

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

_col0
POINT (2 4)

ST_lineFromWKB
ST_lineFromWKB(VARBINARY) -> GEOMETRY
輸入Line的WKB資料，返回對應的Line幾何物件。

ST_polyFromWKB
ST_polyFromWKB(VARBINARY) -> GEOMETRY
輸入Polygon的WKB資料，返回對應的Polygon幾何物件。

ST_MPointFromWKB
ST_MPointFromWKB(VARBINARY) -> GEOMETRY
輸入MultiPoint的WKB資料，返回對應的MultiPoint幾何物件。

示例：

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

_col0
MULTIPOINT ((0 0), (1 2))

ST_MLineFromWKB
ST_MLineFromWKB(VARBINARY) -> GEOMETRY
輸入MultiLine的WKB資料，返回對應的MultiLine幾何物件。

ST_MPolyFromWKB
ST_MPolyFromWKB(VARBINARY) -> GEOMETRY
輸入MultiPolygon的WKB資料，返回對應的MultiPolygon幾何物件。

ST_GeometryFromGeoJson
ST_GeometryFromGeoJson(VARCHAR) -> GEOMETRY
輸入GeoJson的字串資料，返回對應的幾何物件。

ST_GeometryFromJson
ST_GeometryFromJson(VARCHAR) -> GEOMETRY
輸入ESRI Geometry Object Json的字串資料，返回對應的幾何物件。

ST_asGeoJson
ST_asGeoJson(GEOMETRY) -> VARCHAR
把幾何物件轉成GeoJson格式輸出。

示例：

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
把幾何物件轉成ESRI Geometry Object Json格式輸出。

示例：

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
輸入ESRI Shape的二進位制資料，返回對應的幾何物件。

示例：

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

第一個引數為經度列名稱， 型別FLOAT/DOUBLE
第二個引數為緯度列名稱， 型別FLOAT/DOUBLE
第三個引數為圓圈中心點的位置，格式=>'經度,維度', =>'120.85979,30.011984'
第四個引數為圓圈的半徑，單位米
判斷longitude、latitude的點是否在以中心點point為圓心，半徑為radius的圓內。

示例：

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

第一個引數為經度列名稱， 型別FLOAT/DOUBLE
第二個引數為緯度列名稱， 型別FLOAT/DOUBLE
第三個引數為矩形的左下角座標，格式=>'經度,維度', =>'120.85979,30.011984'
第四個引數為矩形的右上角座標，格式=>'經度,維度', =>'120.88450,31.21011'
判斷longitude、latitude的點是否在以2個斜角點構成的矩形內。

示例：

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

第一個引數為經度列名稱， 型別FLOAT/DOUBLE
第二個引數為緯度列名稱， 型別FLOAT/DOUBLE
第三個引數為固定座標點的經緯度，格式=>'經度,維度', =>'120.85979,30.011984'
求longitude、latitude的點和pointA點的距離，單位：米。

示例：

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

cnt
2857
  1. 更多參考

教程：Data Lake Analytics + OSS資料檔案格式處理大全: https://yq.aliyun.com/article...
使用Data Lake Analytics從OSS清洗資料到AnalyticDB：https://yq.aliyun.com/article...
Data Lake Analytics使用場景：https://help.aliyun.com/docum...
OLAP on TableStore——基於Data Lake Analytics的Serverless SQL大資料分析https://yq.aliyun.com/article...
使用Data Lake Analytics 分析OSS資料：https://help.aliyun.com/docum...
Data Lake Analytics資料庫的連線方式：https://help.aliyun.com/docum...
Data Lake Analytics分析RDS資料：https://yq.aliyun.com/article...
下一代企業級雲上資料分析服務：Data Lake Analytics：https://yq.aliyun.com/article...
DLA相關技術文件：https://yq.aliyun.com/search?...

相關文章