# 地理函数

#### 函数说明

ST_POINT(latitude, longitude)

ST_POINT(1.12012, 1.23401)

ST_LINE(array[point1...pointN])

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_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(ST_POINT(1.0, 1.0), 1.234)

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

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

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

WGS84_TO_BD09(geometry)

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

WGS84_TO_CJ02(geometry)

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

BD09_TO_WGS84(geometry)

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

BD09_TO_CJ02(geometry)

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

CJ02_TO_WGS84(geometry)

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

CJ02_TO_BD09(geometry)

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

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

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

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地理函数

IP_TO_COUNTRY

STRING

IP_TO_PROVINCE

STRING

• IP_TO_PROVINCE(STRING ip)：以中文形式，返回IP地址所在的省份。
• IP_TO_PROVINCE(STRING ip, STRING lang)：以指定语言返回IP地址所在的省份。如果lang为EN，则返回英文名称；如果为CN或者其他，则返回中文名称。
说明：
• 当IP无法被解析到省份时，返回该IP所属的国家。当IP无法被解析时，返回“未知”。
• 函数返回的省份中文或者英文名称均为简称，和政府官网一致。

中文参考如下链接：http://www.gov.cn/guoqing/2005-09/13/content_5043917.htm

英文参考如下链接：http://english.gov.cn/archive/

IP_TO_CITY

STRING

IP_TO_CITY_GEO

STRING

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