资源链接在文章末尾,包含文件:
一个与行政区划边界数据相关的应用,行政区划数据从水经微图下载而来格式为kml,需要把这些数据导入到数据库 GreenPlum,并规范数据格式。源数据包含两类:
- _xx名称.txt(区域名称和中心点坐标)
- _xx边界.txt(区域名称和区域边界点坐标)
数据要转换成两类:
- 点 POINT(longitude,latitude)
- 多边形 POLYGON( POINT, POINT, POINT, ... )
将kml后缀批量修改为xml的脚本:
# 在文件所在文件夹下创建 .txt 文件写入以下内容后修改文件后缀为 .bat ren *.kml *.xml2.1 获取数据并处理
中心点数据是很规范比较容易解析的,但是边界数据的存在多边界和单边界情况,此时的xml标签不一致,所以需要分开进行解析:
难点 1️⃣ 标签路径不统一【必须分开进行解析】:
处理方法【将多边界数据以节点类型传递给下一个 xml文件输入组件】:
xml文件输入组件是可以接收流数据的,但是在提取成节点时会给节点添加标识
难点 2️⃣ 数据格式不好,需要转换:
每行的数据是longitude,latitude,altitude 经度,纬度,海拔 而最终需要的点格式为POINT(longitude,latitude)边界格式为POLYGON( POINT, POINT, POINT, ... ),思路是去掉换行然后对海拔数据,0进行替换,去掉换行用正则表达式:
替换海拔,0用SQL,以下SQL实现了边界数据字符串到多边形数据的格式转换:
POLYGON( '(' || TRIm ( TRAILING ',(' FROM REPLACe ( coordinates, ',0', '),(' ) ) )2.2 数据入库中间表并转换数据格式
使用JOB可以控制SQL的执行顺序,由于用到了数据库的关联查询及数据结构转换所以创建了临时表:
所有使用到的SQL:
-- 存放边界数据 DROP TABLE IF EXISTS "public"."data_address_area"; CREATE TABLE "public"."data_address_area" ( "name" varchar(255) COLLATE "pg_catalog"."default", "zone_name" varchar(255) COLLATE "pg_catalog"."default", "coordinates" varchar(102400) COLLATE "pg_catalog"."default" ); -- 存放区域名称 DROP TABLE IF EXISTS "public"."data_address_point"; CREATE TABLE "public"."data_address_point" ( "name" varchar(255) COLLATE "pg_catalog"."default", "zone_name" varchar(255) COLLATE "pg_catalog"."default", "coordinates" varchar(102400) COLLATE "pg_catalog"."default" ); -- 存放格式化后的数据 DROP TABLE IF EXISTS "public"."data_address_geo"; CREATE TABLE "public"."data_address_geo" ( "id" varchar(16) COLLATE "pg_catalog"."default", "pid" varchar(16) COLLATE "pg_catalog"."default", "deep" varchar(1) COLLATE "pg_catalog"."default", "name" varchar(64) COLLATE "pg_catalog"."default", "ext_path" varchar(32) COLLATE "pg_catalog"."default", "geo" point, "polygon" polygon ); -- 执行数据合并 INSERT INTO data_address_geo SELECT pid.pid || ROW_NUMBER ( ) OVER ( ORDER BY point.NAME DESC ) AS "id", pid.pid AS "pid", '3' AS "deep", point.NAME AS "name", pid.ext_path || ' ' || point.NAME AS "ext_path", point.point, polygon.polygon FROM ( SELECt NAME, zone_name, POINT( REPLACE ( coordinates, ',0', '' ) ) FROM data_address_point ) point, ( SELECt NAME, POLYGON( '(' || TRIm ( TRAILING ',(' FROM REPLACe ( coordinates, ',0', '),(' ) ) ) FROM data_address_area ) polygon, ( SELECt ID AS "pid", NAME, ext_path FROM data_administrative_divisions_geo ) pid WHERe point.NAME = polygon.NAME AND point.zone_name = pid.NAME -- 删除临时表 DROP TABLE "public"."data_address_area"; DROP TABLE "public"."data_address_point";3.结果数据
表字段pid、deep、ext_path是为了和之前的表进行合并形成树形结构,不需要的可以忽略:
4.技术总结ETL 工具 Kettle 的功能是很强大的,读取数据、格式化数据、入库等等操作都是一般操作,有些复杂流程也可以拆分成多个简单流程,从而实现需求。这个实例就可以发现字符串替换操作用了很多次。
【资源连接】
链接:https://pan.baidu.com/s/18mbpnQBtwGiYISOwfkHwPw
提取码:xv0w