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

上一篇:一个助Hadoop集群数据快速上云工具


下一篇:平台自定义资源权限控制使用方法