更新时间:2022-12-07 GMT+08:00

地理函数

函数说明

基本地理空间几何元素介绍说明如表1所示。

表1 基本地理空间几何元素表

地理空间几何元素(统称geometry)

说明

举例

ST_POINT(latitude, longitude)

地理点,包含经度和维度两个信息。

ST_POINT(1.12012, 1.23401)

ST_LINE(array[point1...pointN])

地理线,由多个地理点(ST_POINT)按顺序连接成的折线或直线。

ST_LINE(ARRAY[ST_POINT(1.12, 2.23), ST_POINT(1.13, 2.44), ST_POINT(1.13, 2.44)])

ST_POLYGON(array[point1...point1])

地理多边形,由首尾相同的多个地理点(ST_POINT)按顺序连线围成的封闭多边形区域。

ST_POLYGON(ARRAY[ST_POINT(1.0, 1.0), ST_POINT(2.0, 1.0), ST_POINT(2.0, 2.0), ST_POINT(1.0, 1.0)])

ST_CIRCLE(point, radius)

地理圆形,由圆心地理点(ST_POINT)和半径构成的地理圆形区域。

ST_CIRCLE(ST_POINT(1.0, 1.0), 1.234)

用户可以以基本地理空间几何元素为基础,构造复杂的地理空间几何元素,具体的变换方法见表2

表2 基于基本地理空间几何元素构造复杂几何元素的变换表

变换方法

说明

举例

ST_BUFFER(geometry, distance)

创建一个环绕包含给定地理空间几何元素的多边形,并以给定距离作为环绕距离,通常使用该函数构造一定宽度的公路范围用于偏航检测。

ST_BUFFER(ST_LINE(ARRAY[ST_POINT(1.12, 2.23), ST_POINT(1.13, 2.44), ST_POINT(1.13, 2.44)]),1.0)

ST_INTERSECTION(geometry, geometry)

创建一个多边形,其范围为给定的两个地理空间几何元素的交叠区域。

ST_INTERSECTION(ST_CIRCLE(ST_POINT(1.0, 1.0), 2.0), ST_CIRCLE(ST_POINT(3.0, 1.0), 1.234))

ST_ENVELOPE(geometry)

创建一个包含给定的地理空间几何元素的最小矩形。

ST_ENVELOPE(ST_CIRCLE(ST_POINT(1.0, 1.0), 2.0))

DLI提供丰富的对地理空间几何元素的操作和位置判断函数,具体的SQL标量函数介绍说明见表3

表3 SQL标量函数表

函数

返回值

说明

ST_DISTANCE(point_1, point_2)

DOUBLE

计算两个地理点之间的欧几里得距离。

示例如下:

Select ST_DISTANCE(ST_POINT(x1, y1), ST_POINT(x2, y2)) FROM input

ST_GEODESIC_DISTANCE(point_1, point_2)

DOUBLE

计算两个地理点之间的测地距离,即两个地理点之间地表最短路径距离。

示例如下:

Select ST_GEODESIC_DISTANCE(ST_POINT(x1, y1), ST_POINT(x2, y2)) FROM input

ST_PERIMETER(polygon)

DOUBLE

计算多边形的周长。

示例如下:

Select ST_PERIMETER(ST_POLYGON(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12), ST_POINT(x11, y11)]) FROM input

ST_AREA(polygon)

DOUBLE

计算多边形区域的面积。

示例如下:

Select ST_AREA(ST_POLYGON(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12), ST_POINT(x11, y11)]) FROM input

ST_OVERLAPS(polygon_1, polygon_2)

BOOLEAN

判断一个多边形是否与另一个多边形有重叠区域。

示例如下:

SELECT ST_OVERLAPS(ST_POLYGON(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12), ST_POINT(x11, y11)]), ST_POLYGON(ARRAY[ST_POINT(x21, y21), ST_POINT(x22, y22), ST_POINT(x23, y23), ST_POINT(x21, y21)])) FROM input

ST_INTERSECT(line1, line2)

BOOLEAN

检查两条线段是否相互交叉,而非线条所在的直线是否交叉。

示例如下:

SELECT ST_INTERSECT(ST_LINE(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12)]), ST_LINE(ARRAY[ST_POINT(x21, y21), ST_POINT(x22, y22), ST_POINT(x23, y23)])) FROM input

ST_WITHIN(point, polygon)

BOOLEAN

一个点是否包含在几何体(多边形或圆形)内。

示例如下:

SELECT ST_WITHIN(ST_POINT(x11, y11), ST_POLYGON(ARRAY[ST_POINT(x21, y21), ST_POINT(x22, y22), ST_POINT(x23, y23), ST_POINT(x21, y21)])) FROM input

ST_CONTAINS(polygon_1, polygon_2)

BOOLEAN

判断第一个几何体是否包含第二个几何体。

示例如下:

SELECT ST_CONTAINS(ST_POLYGON(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12), ST_POINT(x11, y11)]), ST_POLYGON(ARRAY[ST_POINT(x21, y21), ST_POINT(x22, y22), ST_POINT(x23, y23), ST_POINT(x21, y21)])) FROM input

ST_COVERS(polygon_1, polygon_2)

BOOLEAN

第一个几何体是否覆盖第二个几何体。与ST_CONTAINS相似,但在边界重叠情况下ST_COVER判断为TRUE,ST_CONTAINS判断为FALSE。

示例如下:

SELECT ST_COVERS(ST_POLYGON(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12), ST_POINT(x11, y11)]), ST_POLYGON([ST_POINT(x21, y21), ST_POINT(x22, y22), ST_POINT(x23, y23), ST_POINT(x21, y21)])) FROM input

ST_DISJOINT(polygon_1, polygon_2)

BOOLEAN

判断一个多边形是否与另一个多边形不相交(不重叠)。

示例如下:

SELECT ST_DISJOINT(ST_POLYGON(ARRAY[ST_POINT(x11, y11), ST_POINT(x12, y12), ST_POINT(x11, y11)]), ST_POLYGON(ARRAY[ST_POINT(x21, y21), ST_POINT(x22, y22), ST_POINT(x23, y23), ST_POINT(x21, y21)])) FROM input

地理函数的基准坐标系标准为全球通用的GPS坐标系标准WGS84,GPS坐标不能直接在百度地图(BD09标准)或者google地图(GCJ02标准)上使用,会有偏移现象,为了在不同地理坐标系之间切换,DLI提供了坐标系转换的一系列函数,并且还提供地理距离与米之间的转换函数。详见表4

表4 地理坐标系转换函数与距离单位转换函数表

函数

返回值

说明

WGS84_TO_BD09(geometry)

对应的百度地图坐标系地理空间几何元素

将GPS坐标系下的地理空间几何元素转换成百度地图坐标系下对应的地理空间几何元素。示例如下:

WGS84_TO_BD09(ST_CIRCLE(ST_POINT(x, y), r))

WGS84_TO_CJ02(geometry)

对应的Google地图坐标系地理空间几何元素

将GPS坐标系下的地理空间几何元素转换成Google地图坐标系下对应的地理空间几何元素。示例如下:

WGS84_TO_CJ02(ST_CIRCLE(ST_POINT(x, y), r))

BD09_TO_WGS84(geometry)

对应的GPS坐标系地理空间几何元素

将百度地图坐标系下的地理空间几何元素转换成GPS坐标系下对应的地理空间几何元素。示例如下:

BD09_TO_WGS84(ST_CIRCLE(ST_POINT(x, y), r))

BD09_TO_CJ02(geometry)

对应的Google地图坐标系地理空间几何元素

将百度地图坐标系下的地理空间几何元素转换成Google地图坐标系下对应的地理空间几何元素。示例如下:

BD09_TO_CJ02(ST_CIRCLE(ST_POINT(x, y), r))

CJ02_TO_WGS84(geometry)

对应的GPS坐标系地理空间几何元素

将Google地图坐标系下的地理空间几何元素转换成GPS坐标系下对应的地理空间几何元素。示例如下:

CJ02_TO_WGS84(ST_CIRCLE(ST_POINT(x, y), r))

CJ02_TO_BD09(geometry)

对应的百度地图坐标系地理空间几何元素

将Google地图坐标系下的地理空间几何元素转换成百度地图坐标系下对应的地理空间几何元素。示例如下:

CJ02_TO_BD09(ST_CIRCLE(ST_POINT(x, y), r))

DEGREE_TO_METER(distance)

DOUBLE

将地理函数的距离数值转换成以“米”为单位的数值。示例如下(以米为单位计算地理三边形周长):

DEGREE_TO_METER(ST_PERIMETER(ST_POLYGON(ARRAY[ST_POINT(x1,y1), ST_POINT(x2,y2), ST_POINT(x3,y3), ST_POINT(x1,y1)])))

METER_TO_DEGREE(numerical_value)

DOUBLE

将以“米”为单位的数值转换成地理函数可计算的距离单位数值。示例如下(画出以指定地理点为圆心,半径1公里的圆):

ST_CIRCLE(ST_POINT(x,y), METER_TO_DEGREE(1000))

DLI还提供了基于窗口的SQL地理聚合函数用于SQL逻辑涉及窗口和聚合的场景。详见表5的介绍说明。

表5 时间相关SQL地理聚合函数表

函数

说明

举例

AGG_DISTANCE(point)

距离聚合函数,用于计算窗口内所有相邻地理点的距离总和。

SELECT AGG_DISTANCE(ST_POINT(x,y)) FROM input GROUP BY HOP(rowtime, INTERVAL '1' HOUR, INTERVAL '1' DAY)

AVG_SPEED(point)

平均速度聚合函数,用于计算窗口内所有地理点组成的移动轨迹的平均速度,单位为“米/秒”。

SELECT AVG_SPEED(ST_POINT(x,y)) FROM input GROUP BY TUMBLE(proctime, INTERVAL '1' DAY)

注意事项

无。

示例

偏航检测样例:

1
2
3
4
INSERT INTO yaw_warning
SELECT "The car is yawing"
FROM driver_behavior
WHERE NOT ST_WITHIN(ST_POINT(cast(Longitude as DOUBLE), cast(Latitude as DOUBLE)), ST_BUFFER(ST_LINE(ARRAY[ST_POINT(34.585555,105.725221),ST_POINT(34.586729,105.735974),ST_POINT(34.586492,105.740538),ST_POINT(34.586388,105.741651),ST_POINT(34.586135,105.748712),ST_POINT(34.588691,105.74997)]),0.001));

IP地理函数

当前仅支持IPV4的IP地址。

表6 IP地理函数表

函数

返回值

说明

IP_TO_COUNTRY

STRING

获取IP地址所在的国家名称。

IP_TO_PROVINCE

STRING

获取IP地址所在的省份。

用法说明:

  • IP_TO_PROVINCE(STRING ip):返回IP地址所在的省份。
  • IP_TO_PROVINCE(STRING ip, STRING lang):以指定语言返回IP地址所在的省份。
    说明:
    • 当IP无法被解析到省份时,返回该IP所属的国家。当IP无法被解析时,返回“未知”。
    • 函数返回的省份名称均为简称。

IP_TO_CITY

STRING

获取IP地址所在的城市名称。

说明:

当IP无法被解析到城市时,返回该IP所属的省份或者国家。当IP无法被解析时,返回“未知”。

IP_TO_CITY_GEO

STRING

获取IP地址所在城市的经纬度,格式为“纬度,经度”。

用法说明:

IP_TO_CITY_GEO(STRING ip):返回IP所在城市的经纬度。