DLA新增函数发布：身份证、星座、拼音、Json数组、经纬度城市查询、互联网坐标系经纬度转换等

概述

本月，Data Lake Analytics（https://et.aliyun.com/bdad/datalake）发布了一批新增的内置函数，涉及身份证地域查询、星座查询、中文拼音转换、Json数组内容字段提取、经纬度城市查询、互联网坐标系经纬度转换等。在云上即可轻松利用这些函数进行分析和数据处理。

说明

1. 空值判断

输入任意类型的数值或者列对象，如果为空，返回true（1）；如果不空，返回false（0）。

is_null(all_type) -> boolean

2. 中文转拼音

输入中文，输出对应的拼音，其他字符不变。

pinyin(string) -> string

示例：

select pinyin('中国');
-> zhongguo

select pinyin('中 国');
-> zhong guo

3. 生成MD5值

输入字符串，输出字符串对应的MD5值。

md5(string) -> string

示例：

select md5('中 国');
-> c939eb06bb2cf2ef5fd3375da2a6bfdd

4. 生成SHA256值

输入字符串，输出字符串对应的SHA256哈希值。

sha256(string) -> string

示例：

select sha256('中国');
-> f0e9521611bb290d7b09b8cd14a63c3fe7cbf9a2f4e0090d8238d22403d35182

5. 星座查询

输入日期，查询对应的星座的英文。

zodiac(date_string | date) -> string

输入日期，查询对应的星座中文。

zodiac_cn(date_string | date) -> string

示例：

select zodiac_cn('2019-08-22');
-> 狮子座

select zodiac_cn('2016-05-26');
-> 双子座

select zodiac('2019-08-22');
-> Leo

select zodiac('2016-05-26');
-> Gemini

6. 经纬度城市查询

根据经、纬度查询中国省、市。经纬度符合GCJ-02（https://baike.baidu.com/item/GCJ-02/1913612）标准。

• city

city(DOUBLE, DOUBLE) -> String

从经、纬度（longitude, latitude），查询对应的城市名称（中文）。

示例：

select city(121.48, 31.22);
+----------------------------+
| city(121.48, 31.22)        |
+----------------------------+
| 上海城区                     |
+----------------------------+

• province

province(DOUBLE, DOUBLE) -> String

从经、纬度（longitude, latitude），查询对应的省、直辖市名称（中文）。

示例：

select province(121.48, 31.22);
+----------------------------+
| province(121.48, 31.22)    |
+----------------------------+
| 上海市                      |
+----------------------------+

7. 互联网坐标系经纬度转换

7.1 当前互联网地图的坐标系现状

地球坐标 (WGS84)

• 国际标准，从GPS 设备中取出的数据的坐标系。
• 国际地图提供商使用的坐标系。
• Data Lake Analytics支持的Geospatial处理函数（https://yq.aliyun.com/articles/669048）就是基于该地球坐标系，如果涉及坐标系经纬度转换，可以用本次发布的函数。
  

火星坐标 (GCJ-02), 也叫国测局坐标系

• 中国标准，从国行移动设备中定位获取的坐标数据使用这个坐标系。
• 国家规定： 国内出版的各种地图系统（包括电子形式），必须至少采用GCJ-02对地理位置进行首次加密。
  

百度坐标 (BD-09)

• 百度标准，百度SDK，百度地图，Geocoding 使用。
• 在火星坐标上来个二次加密。

7.2 开发过程需要注意的事

• 从设备获取经纬度（GPS）坐标

  • 如果使用的是百度SDK那么可以获得百度坐标（BD-09）或者火星坐标（GCJ-02),默认是BD-09
  • 如果使用的是iOS的原生定位库，那么获得的坐标是WGS84
  • 如果使用的是高德SDK,那么获取的坐标是GCJ-02

• 互联网在线地图使用的坐标系

  • 火星坐标系：

    • iOS 地图（其实是高德）
    • Google 地图
    • 搜搜、阿里云、高德地图

  • 百度坐标系：

    • 当然只有百度地图

  • WGS84坐标系：

    • 国际标准，谷歌国外地图、osm地图等国外的地图一般都是这个

7.3 函数说明

计算WGS84两个经纬度坐标之间的距离，单位：米。注意，lat是纬度，lng是经度，参数顺序不要搞反。

wgs_distance(double lat1, double lng1, double lat2, double lng2) -> double

示例：

select wgs_distance(28, 120, 28.1, 120.1);
-> 14830.55281889834

GCJ-02(火星坐标系)转为BD-09(百度坐标系)，谷歌、高德 -> 百度。注意，lat是纬度，lng是经度，参数顺序不要搞反。

gcj_to_bd(double lat, double lng) -> json

示例：

select gcj_to_bd(120, 28);
-> {"lng":28.00667999996849,"lat":120.005957999865}

BD-09(百度坐标系)转为GCJ-02(火星坐标系)，百度 -> 谷歌、高德。注意，lat是纬度，lng是经度，参数顺序不要搞反。

bd_to_gcj(double lat, double lng) -> json

示例：

select bd_to_gcj(120, 28);
-> {\"lng\":27.993435802597258,\"lat\":119.99402132293964}

WGS84(地球坐标系)转为GCJ02(火星坐标系)。注意，lat是纬度，lng是经度，参数顺序不要搞反。

wgs_to_gcj(double lat, double lng) -> json

示例：

select wgs_to_gcj(120, 28);
-> {"lng":28.0,"lat":120.0}

GCJ02(火星坐标系)转成WGS84(地球坐标系)，误差在1~2米内。注意，lat是纬度，lng是经度，参数顺序不要搞反。

gcj_to_wgs(double lat, double lng) -> json

示例：

select gcj_to_wgs(120, 28);
-> {"lng":28.0,"lat":120.0}

GCJ02(火星坐标系)转成WGS84(地球坐标系)，误差在0.5米内，性能比gcj_to_wgs要慢。注意，lat是纬度，lng是经度，参数顺序不要搞反。

gcj_extract_wgs(double lat, double lng) -> json

示例：

select gcj_extract_wgs(52.2, 14.32);
-> {"lng":14.32,"lat":52.2}

8. 中国身份证地域查询

查询身份证所属省份。

id_card_province(string) -> string

查询身份证所属城市。

id_card_city(string) -> string

查询身份证所属区域。

id_card_area(string) -> string

查询身份证的出身年月日，格式例如：19900101

id_card_birthday(string) -> string

查询身份证的性别。

id_card_gender(string) -> string

校验身份证格式是否合法。

is_valid_id_card(string) -> boolean

输出身份证地域信息，JSON格式。

id_card_info(string) -> json

示例：

select id_card_province('430403199001010011');
-> 湖南省

select id_card_city('430403199001010011');
-> 衡阳市

select id_card_area('430403199001010011');
-> 雁峰区

select id_card_birthday('430403199001010011');
-> 19900101

select id_card_gender('430403199001010011');
-> 男

select is_valid_id_card('430403199001010011');
-> 1

select id_card_info('430403199001010011');
-> {"area":"雁峰区","valid":true,"province":"湖南省","gender":"男","city":"衡阳市"}

9. JSON数组内容字段提取

通过JSON PATH提取数组内容字段，输出为JSON格式数组。

json_array_extract(json, jsonPath) -> array(varchar)

通过JSON PATH提取数组内容字段，输出是字段为字符串形式的数组（字段值是原始字符串值，不是JSON形式）。

json_array_extract_scalar(json, jsonPath) -> array(varchar)

示例：

select json_array_extract(arr1, '$.book.id') as a 
from (values ('[{"book":{"id":"12"}}, {"book":{"id":"14"}}]')) t(arr1);
-> ["12", "14"]

select json_array_extract_scalar(arr1, '$.book.id') as a 
from (values ('[{"book":{"id":"12"}}, {"book":{"id":"14"}}]')) t(arr1);
-> [12, 14]

Data Lake Analytics首购和流量包优惠

首购用户1元10TB，流量包阶梯折扣优惠：https://et.aliyun.com/bdad/datalake
产品详情：https://www.aliyun.com/product/datalakeanalytics
云栖社区：https://yq.aliyun.com/teams/396
知乎社区：https://zhuanlan.zhihu.com/data-lake-analytics