當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Data Lake Analytics的Geospatial分析函数

發(fā)布時間：2025/3/21 编程问答 28 豆豆

生活随笔收集整理的這篇文章主要介紹了 Data Lake Analytics的Geospatial分析函数小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

2019獨角獸企業(yè)重金招聘Python工程師標準>>>

0. 簡介

為滿足部分客戶在云上做Geometry數(shù)據(jù)的分析需求，阿里云Data Lake Analytics（以下簡稱：DLA）支持多種格式的地理空間數(shù)據(jù)處理函數(shù)，符合Open Geospatial Consortium’s (OGC) OpenGIS規(guī)范，支持的常用數(shù)據(jù)格式包括：

WKT
WKB
GeoJson
ESRI Geometry Object Json
ESRI Shape

DLA采用4326坐標系標準，EPSG 4326使用經(jīng)緯度坐標，屬于地理坐標系。GPS采用的就是這個坐標系。

1. WKT數(shù)據(jù)

詳細描述：
https://en.wikipedia.org/wiki/Well-known_text
http://www.opengeospatial.org/standards/wkt-crs

支持類似如下的WKT相關(guān)字符串。

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數(shù)據(jù)

詳細描述：https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary

上述WKT數(shù)據(jù)和WKB的對應(yīng)示例：

WKTWKBPOINT (0 0)010100000000000000000000000000000000000000LINESTRING (0 0, 1 1, 1 2)01020000000300000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F0000000000000040POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1))01030000000200000005000000000000000000000000000000000000000000000000001040000000000000000000000000000010400000000000001040000000000000000000000000000010400000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03FMULTIPOINT (0 0, 1 2)0104000000020000000101000000000000000000000000000000000000000101000000000000000000F03F0000000000000040MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))01050000000200000001020000000300000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F0000000000000040010200000003000000000000000000004000000000000008400000000000000840000000000000004000000000000014400000000000001040MULTIPOLYGON (((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)))01060000000200000001030000000200000005000000000000000000000000000000000000000000000000001040000000000000000000000000000010400000000000001040000000000000000000000000000010400000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F01030000000100000005000000000000000000F0BF000000000000F0BF00000000000000C0000000000000F0BF00000000000000C000000000000000C0000000000000F0BF00000000000000C0000000000000F0BF000000000000F0BFGEOMETRYCOLLECTION (POINT (2 3), LINESTRING (2 3, 3 4))0107000000020000000101000000000000000000004000000000000008400102000000020000000000000000000040000000000000084000000000000008400000000000001040

3. GeoJson數(shù)據(jù)

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

4. ESRI Geometry Object Json數(shù)據(jù)

規(guī)范說明：http://resources.esri.com/help/9.3/arcgisserver/apis/REST/geometry.html

5. ESRI Shape二進制數(shù)據(jù)

規(guī)范說明：http://www.esri.com/LIBRARY/WHITEPAPERS/PDFS/SHAPEFILE.PDF

6. 函數(shù)列表

NameDescriptionST_asText將Geometry數(shù)據(jù)轉(zhuǎn)成WKT格式的字符串數(shù)據(jù)。ST_LineFromText輸入WKT格式的Line字符串數(shù)據(jù)，生成Line的Geometry數(shù)據(jù)。ST_Point從坐標系的坐標值（X, Y），生成對應(yīng)的Point的Geometry數(shù)據(jù)。ST_Polygon輸入WKT格式的Polygon字符串數(shù)據(jù)，生成Polygon的Geometry數(shù)據(jù)。ST_Area返回面或多面的面積。對于點、線，返回0.0。對于GeometryCollection，返回所有單個面積的和。ST_GeometryFromText輸入WKT格式的字符串數(shù)據(jù)，生成Geometry數(shù)據(jù)。ST_Buffer獲取幾何對象和距離，然后返回表示圍繞源對象的緩沖區(qū)的幾何對象。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作為輸入?yún)?shù)，并返回其內(nèi)部環(huán)數(shù)。ST_NumPoints用于返回幾何對象中的點（折點）數(shù)。ST_IsRing以Line作為輸入?yún)?shù)，判斷是否是環(huán)（如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形式返回面的外部環(huán)。ST_Intersection以兩個幾何對象作為輸入?yún)?shù)，然后以二維幾何對象的形式返回交集。ST_SymDifference返回表示兩個幾何對象間的點集對稱差異的幾何值對象。ST_Contains輸入兩個幾何對象，判斷第一個對象是否完全包含第二個對象。ST_Crosses以兩個幾何對象作為輸入，如果這兩個對象的交集生成的幾何對象的維度小于兩個源對象中的最大維度，則返回 1。交集對象所包含的點必須在兩個源幾何的內(nèi)部，并且不等于其中任何一個源對象。否則，返回 0。ST_Disjoint輸入兩個幾何對象，判斷兩個幾何對象的交集是否為空集。ST_Equals判斷兩個幾何對象是否完全相同。ST_Intersects判斷兩個幾何對象的交集是否不生成空集。ST_Overlaps判斷兩個幾何對象的交集生成的幾何對象是否維度相同但不等于任一源對象。ST_Relate比較兩個幾何對象，判斷是否滿足“DE-9IM”模式（https://en.wikipedia.org/wiki/DE-9IM）矩陣字符串指定的條件。ST_Touches判斷兩個幾何對象的公共點是否都不與兩個幾何對象的內(nèi)部相交。ST_Within判斷第一個幾何對象是否完全位于第二個幾何對象的范圍內(nèi)。ST_asBinary輸入一個幾何對象，然后返回其可識別的二進制WKB數(shù)據(jù)。ST_GeometryFromWKBHexString輸入WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的幾何對象。ST_pointFromWKBHexString輸入Point的WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的Point幾何對象。ST_lineFromWKBHexString輸入Line的WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的Line幾何對象。ST_polyFromWKBHexString輸入Polygon的WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的Polygon幾何對象。ST_MPointFromWKBHexString輸入MultiPoint的WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的MultiPoint幾何對象。ST_MLineFromWKBHexString輸入MultiLine的WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的MultiLine幾何對象。ST_MPolyFromWKBHexString輸入MultiPolygon的WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的MultiPolygon幾何對象。ST_GeometryFromWKB輸入WKB數(shù)據(jù)，返回對應(yīng)的幾何對象。ST_pointFromWKB輸入Point的WKB數(shù)據(jù)，返回對應(yīng)的Point幾何對象。ST_lineFromWKB輸入Line的WKB數(shù)據(jù)，返回對應(yīng)的Line幾何對象。ST_polyFromWKB輸入Polygon的WKB數(shù)據(jù)，返回對應(yīng)的Polygon幾何對象。ST_MPointFromWKB輸入MultiPoint的WKB數(shù)據(jù)，返回對應(yīng)的MultiPoint幾何對象。ST_MLineFromWKB輸入MultiLine的WKB數(shù)據(jù)，返回對應(yīng)的MultiLine幾何對象。ST_MPolyFromWKB輸入MultiPolygon的WKB數(shù)據(jù)，返回對應(yīng)的MultiPolygon幾何對象。ST_GeometryFromGeoJson輸入GeoJson的字符串數(shù)據(jù)，返回對應(yīng)的幾何對象。ST_GeometryFromJson輸入ESRI Geometry Object Json的字符串數(shù)據(jù)，返回對應(yīng)的幾何對象。ST_asGeoJson把幾何對象轉(zhuǎn)成GeoJson格式輸出。ST_asJson把幾何對象轉(zhuǎn)成ESRI Geometry Object Json格式輸出。ST_GeometryFromEsriShape輸入ESRI Shape的二進制數(shù)據(jù)，返回對應(yīng)的幾何對象。UDF_SYS_GEO_IN_CYCLE僅適用于北半球：做基于地理位置的經(jīng)緯度畫圈UDF_SYS_GEO_IN_RECTANGLE僅適用于北半球：用于做基于地理位置的經(jīng)緯度畫矩形UDF_SYS_GEO_DISTANCE僅適用于北半球：用作一個經(jīng)緯度列和一個固定的坐標點的距離計算

7. 函數(shù)定義與示例

ST_asText

ST_asText(GEOMETRY) -> VARCHAR

將Geometry數(shù)據(jù)轉(zhuǎn)成WKT格式的字符串數(shù)據(jù)。

示例：

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字符串數(shù)據(jù)，生成Line的Geometry數(shù)據(jù)。

示例：

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），生成對應(yīng)的Point的Geometry數(shù)據(jù)。

示例：

select ST_asText(ST_Point(30.2741500000,120.1551500000));+----------------------------+ | _col0 | +----------------------------+ | POINT (30.27415 120.15515) | +----------------------------+

ST_Polygon

ST_Polygon(VARCHAR) -> GEOMETRY

輸入WKT格式的Polygon字符串數(shù)據(jù)，生成Polygon的Geometry數(shù)據(jù)。

示例：

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格式的字符串數(shù)據(jù)，生成Geometry數(shù)據(jù)。

ST_Buffer

ST_Buffer(GEOMETRY, DOUBLE) -> GEOMETRY

獲取幾何對象和距離，然后返回表示圍繞源對象的緩沖區(qū)的幾何對象。

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作為輸入?yún)?shù)，并返回其內(nèi)部環(huán)數(shù)。

示例：

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

用于返回幾何對象中的點（折點）數(shù)。

示例：

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作為輸入?yún)?shù)，判斷是否是環(huán)（如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形式返回面的外部環(huán)。

示例：

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

以兩個幾何對象作為輸入?yún)?shù)，然后以二維幾何對象的形式返回交集。

示例：

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

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

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。交集對象所包含的點必須在兩個源幾何的內(nèi)部，并且不等于其中任何一個源對象。否則，返回 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/DE-9IM）矩陣字符串指定的條件。

示例：

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

判斷兩個幾何對象的公共點是否都不與兩個幾何對象的內(nèi)部相交。

示例：

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

判斷第一個幾何對象是否完全位于第二個幾何對象的范圍內(nèi)。

示例：

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數(shù)據(jù)。

ST_GeometryFromWKBHexString

ST_GeometryFromWKBHexString(VARCHAR) -> GEOMETRY

輸入WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的幾何對象。

示例：

select ST_ASTEXT(ST_GeometryFromWKBHexString('000000000140000000000000004010000000000000'));+-------------+ | _col0 | +-------------+ | POINT (2 4) | +-------------+

ST_pointFromWKBHexString

ST_pointFromWKBHexString(VARCHAR) -> GEOMETRY

輸入Point的WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的Point幾何對象。

示例：

select ST_ASTEXT(ST_pointFromWKBHexString('000000000140000000000000004010000000000000'));+-------------+ | _col0 | +-------------+ | POINT (2 4) | +-------------+

ST_lineFromWKBHexString

ST_lineFromWKBHexString(VARCHAR) -> GEOMETRY

輸入Line的WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的Line幾何對象。

ST_polyFromWKBHexString

ST_polyFromWKBHexString(VARCHAR) -> GEOMETRY

輸入Polygon的WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的Polygon幾何對象。

ST_MPointFromWKBHexString

ST_MPointFromWKBHexString(VARCHAR) -> GEOMETRY

輸入MultiPoint的WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的MultiPoint幾何對象。

示例：

SELECT ST_asText(ST_MPointFromWKBHexString('0104000000020000000101000000000000000000000000000000000000000101000000000000000000F03F0000000000000040'));+---------------------------+ | _col0 | +---------------------------+ | MULTIPOINT ((0 0), (1 2)) | +---------------------------+

ST_MLineFromWKBHexString

ST_MLineFromWKBHexString(VARCHAR) -> GEOMETRY

輸入MultiLine的WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的MultiLine幾何對象。

ST_MPolyFromWKBHexString

ST_MPolyFromWKBHexString(VARCHAR) -> GEOMETRY

輸入MultiPolygon的WKB的HEX字符串數(shù)據(jù)，返回對應(yīng)的MultiPolygon幾何對象。

ST_GeometryFromWKB

ST_GeometryFromWKB(VARBINARY) -> GEOMETRY

輸入WKB數(shù)據(jù)，返回對應(yīng)的幾何對象。

示例：

select ST_ASTEXT(ST_GeometryFromWKB(from_hex('000000000140000000000000004010000000000000')));+-------------+ | _col0 | +-------------+ | POINT (2 4) | +-------------+

ST_pointFromWKB

ST_pointFromWKB(VARBINARY) -> GEOMETRY

輸入Point的WKB數(shù)據(jù)，返回對應(yīng)的Point幾何對象。

示例：

select ST_ASTEXT(ST_pointFromWKB(from_hex('000000000140000000000000004010000000000000')));+-------------+ | _col0 | +-------------+ | POINT (2 4) | +-------------+

ST_lineFromWKB

ST_lineFromWKB(VARBINARY) -> GEOMETRY

輸入Line的WKB數(shù)據(jù)，返回對應(yīng)的Line幾何對象。

ST_polyFromWKB

ST_polyFromWKB(VARBINARY) -> GEOMETRY

輸入Polygon的WKB數(shù)據(jù)，返回對應(yīng)的Polygon幾何對象。

ST_MPointFromWKB

ST_MPointFromWKB(VARBINARY) -> GEOMETRY

輸入MultiPoint的WKB數(shù)據(jù)，返回對應(yīng)的MultiPoint幾何對象。

示例：

SELECT ST_asText(ST_MPointFromWKB(from_hex('0104000000020000000101000000000000000000000000000000000000000101000000000000000000F03F0000000000000040')));+---------------------------+ | _col0 | +---------------------------+ | MULTIPOINT ((0 0), (1 2)) | +---------------------------+

ST_MLineFromWKB

ST_MLineFromWKB(VARBINARY) -> GEOMETRY

輸入MultiLine的WKB數(shù)據(jù)，返回對應(yīng)的MultiLine幾何對象。

ST_MPolyFromWKB

ST_MPolyFromWKB(VARBINARY) -> GEOMETRY

輸入MultiPolygon的WKB數(shù)據(jù)，返回對應(yīng)的MultiPolygon幾何對象。

ST_GeometryFromGeoJson

ST_GeometryFromGeoJson(VARCHAR) -> GEOMETRY

輸入GeoJson的字符串數(shù)據(jù)，返回對應(yīng)的幾何對象。

ST_GeometryFromJson

ST_GeometryFromJson(VARCHAR) -> GEOMETRY

輸入ESRI Geometry Object Json的字符串數(shù)據(jù)，返回對應(yīng)的幾何對象。

ST_asGeoJson

ST_asGeoJson(GEOMETRY) -> VARCHAR

把幾何對象轉(zhuǎn)成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

把幾何對象轉(zhuǎn)成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的二進制數(shù)據(jù)，返回對應(yīng)的幾何對象。

示例：

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第一個參數(shù)為經(jīng)度列名稱，類型FLOAT/DOUBLE 第二個參數(shù)為緯度列名稱，類型FLOAT/DOUBLE 第三個參數(shù)為圓圈中心點的位置，格式=>'經(jīng)度,維度', =>'120.85979,30.011984' 第四個參數(shù)為圓圈的半徑，單位米

判斷l(xiāng)ongitude、latitude的點是否在以中心點point為圓心，半徑為radius的圓內(nèi)。

示例：

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第一個參數(shù)為經(jīng)度列名稱，類型FLOAT/DOUBLE 第二個參數(shù)為緯度列名稱，類型FLOAT/DOUBLE 第三個參數(shù)為矩形的左下角坐標，格式=>'經(jīng)度,維度', =>'120.85979,30.011984' 第四個參數(shù)為矩形的右上角坐標，格式=>'經(jīng)度,維度', =>'120.88450,31.21011'

判斷l(xiāng)ongitude、latitude的點是否在以2個斜角點構(gòu)成的矩形內(nèi)。

示例：

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第一個參數(shù)為經(jīng)度列名稱，類型FLOAT/DOUBLE 第二個參數(shù)為緯度列名稱，類型FLOAT/DOUBLE 第三個參數(shù)為固定坐標點的經(jīng)緯度，格式=>'經(jīng)度,維度', =>'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 | +------+

原文鏈接

本文為云棲社區(qū)原創(chuàng)內(nèi)容，未經(jīng)允許不得轉(zhuǎn)載。

轉(zhuǎn)載于:https://my.oschina.net/u/3889140/blog/3013117

總結(jié)

以上是生活随笔為你收集整理的Data Lake Analytics的Geospatial分析函数的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

上一篇： css禁止点击事件
下一篇： OpenJ_Bailian 4148 生