用于将osm2po输出的pgrouting sql脚本转换为数据文件(csv、json或avro)的实用程序。
pgrsql2data的Python项目详细描述
pgrsql2数据
说明
此工具旨在将osm2po生成的sql文件转换为可在任何位置导入的通用数据集。
支持的数据格式包括csv
、json
和avro
。
长(er)描述
osm2po生成一个pgrouting兼容的sql文件,该文件包含ddl语句,用于创建数据并将数据插入postgresql。这些语句与postgresql/postgis紧密耦合,很难将这些数据导入其他地方(例如google bigquery)。
此工具将SQL语句转换为结构化数据格式(csv
、json
或avro
)。
- 数据模式是从脚本的
CREATE TABLE
语句自动推断出来的几何数据从十六进制WKB转换为WKT
理论上,这个工具可以用于任何包含(单个)CREATE TABLE
和INSERT INTO
语句的sql脚本。
安装
安装时使用pip
:
pip install pgrsql2data
用法
命令行
$ pgrsql2data --input osm_2po_pgr.sql --format csv
示例
sample.sql
-- Created by : osm2po-core-- Version : 5.2.43-- Author (c) : Carsten Moeller - info@osm2po.de-- Date : Sat Jan 12 08:51:32 UTC 2019SETclient_encoding='UTF8';DROPTABLEIFEXISTSnz_2po_4pgr;-- SELECT DropGeometryTable('nz_2po_4pgr');CREATETABLEnz_2po_4pgr(idinteger,osm_idbigint,osm_namecharactervarying,osm_metacharactervarying,osm_source_idbigint,osm_target_idbigint,clazzinteger,flagsinteger,sourceinteger,targetinteger,kmdoubleprecision,kmhinteger,costdoubleprecision,reverse_costdoubleprecision,x1doubleprecision,y1doubleprecision,x2doubleprecision,y2doubleprecision);SELECTAddGeometryColumn('nz_2po_4pgr','geom_way',4326,'LINESTRING',2);INSERTINTOnz_2po_4pgrVALUES(1,2850,'Ketetahi Road',null,691734,1680775132,43,3,1,2,0.9172049,50,0.0183441,0.0183441,175.6671636,-39.065529,175.6641327,-39.0733589,'0102000020E6100...'),(2,2857,'Ngauruhoe Place',null,441520023,2938455973,43,3,3,239248,0.0059348,50,1.187E-4,1.187E-4,175.5417358,-39.1998235,175.5417999,-39.199804,'0102000020E6100...'),(3,2857,'Ngauruhoe Place',null,2938455973,4173847805,43,3,239248,295015,0.0501935,50,0.0010039,0.0010039,175.5417999,-39.199804,175.542348,-39.1996541,'0102000020E6100...');
转换为…
csv
$ pgrsql2data --input sample.sql --format csv $ cat sample.csv "id","osm_id","osm_name","osm_meta","osm_source_id","osm_target_id","clazz","flags","source","target","km","kmh","cost","reverse_cost","x1","y1","x2","y2","geom_way"1,2850,"Ketetahi Road","",691734,1680775132,43,3,1,2,0.9172049,50,0.0183441,0.0183441,175.6671636,-39.065529,175.6641327,-39.0733589,"LINESTRING (175.6671636000000092 -39.0655289999999979, 175.6669300000000078 -39.0660262999999972, 175.6665609000000075 -39.0667807000000025, 175.6664728000000082 -39.0669753999999969, 175.6664277000000141 -39.0671368000000001, 175.6663901000000010 -39.0673267999999965, 175.6663532000000032 -39.0676137000000026, 175.6663232999999877 -39.0679250000000025, 175.6662765000000093 -39.0681926999999973, 175.6662121999999897 -39.0685058999999981, 175.6661235999999917 -39.0688525999999996, 175.6660063999999863 -39.0693205000000034, 175.6659176000000002 -39.0697118000000003, 175.6658382999999901 -39.0700554000000011, 175.6657261999999946 -39.0705236000000014, 175.6656762000000072 -39.0707115000000016, 175.6656149000000084 -39.0709337999999988, 175.6655820000000006 -39.0710349000000008, 175.6655240000000049 -39.0711586999999980, 175.6654651999999999 -39.0712526000000011, 175.6653054999999881 -39.0715093999999965, 175.6650927999999965 -39.0717934999999983, 175.6649300999999923 -39.0720382000000015, 175.6646489999999972 -39.0725479999999976, 175.6643620000000112 -39.0730144999999993, 175.6641327000000103 -39.0733589000000023)"2,2857,"Ngauruhoe Place","",441520023,2938455973,43,3,3,239248,0.0059348,50,0.0001187,0.0001187,175.5417358,-39.1998235,175.5417999,-39.199804,"LINESTRING (175.5417357999999979 -39.1998235000000008, 175.5417999000000009 -39.1998040000000003)"3,2857,"Ngauruhoe Place","",2938455973,4173847805,43,3,239248,295015,0.0501935,50,0.0010039,0.0010039,175.5417999,-39.199804,175.542348,-39.1996541,"LINESTRING (175.5417999000000009 -39.1998040000000003, 175.5422246999999913 -39.1996748999999980, 175.5423480000000040 -39.1996540999999965)"
json
$ pgrsql2data --input sample.sql --format json $ cat sample.json {"id": 1, "osm_id": 2850, "osm_name": "Ketetahi Road", "osm_meta": null, "osm_source_id": 691734, "osm_target_id": 1680775132, "clazz": 43, "flags": 3, "source": 1, "target": 2, "km": 0.9172049, "kmh": 50, "cost": 0.0183441, "reverse_cost": 0.0183441, "x1": 175.6671636, "y1": -39.065529, "x2": 175.6641327, "y2": -39.0733589, "geom_way": "LINESTRING (175.6671636000000092 -39.0655289999999979, 175.6669300000000078 -39.0660262999999972, 175.6665609000000075 -39.0667807000000025, 175.6664728000000082 -39.0669753999999969, 175.6664277000000141 -39.0671368000000001, 175.6663901000000010 -39.0673267999999965, 175.6663532000000032 -39.0676137000000026, 175.6663232999999877 -39.0679250000000025, 175.6662765000000093 -39.0681926999999973, 175.6662121999999897 -39.0685058999999981, 175.6661235999999917 -39.0688525999999996, 175.6660063999999863 -39.0693205000000034, 175.6659176000000002 -39.0697118000000003, 175.6658382999999901 -39.0700554000000011, 175.6657261999999946 -39.0705236000000014, 175.6656762000000072 -39.0707115000000016, 175.6656149000000084 -39.0709337999999988, 175.6655820000000006 -39.0710349000000008, 175.6655240000000049 -39.0711586999999980, 175.6654651999999999 -39.0712526000000011, 175.6653054999999881 -39.0715093999999965, 175.6650927999999965 -39.0717934999999983, 175.6649300999999923 -39.0720382000000015, 175.6646489999999972 -39.0725479999999976, 175.6643620000000112 -39.0730144999999993, 175.6641327000000103 -39.0733589000000023)"}{"id": 2, "osm_id": 2857, "osm_name": "Ngauruhoe Place", "osm_meta": null, "osm_source_id": 441520023, "osm_target_id": 2938455973, "clazz": 43, "flags": 3, "source": 3, "target": 239248, "km": 0.0059348, "kmh": 50, "cost": 0.0001187, "reverse_cost": 0.0001187, "x1": 175.5417358, "y1": -39.1998235, "x2": 175.5417999, "y2": -39.199804, "geom_way": "LINESTRING (175.5417357999999979 -39.1998235000000008, 175.5417999000000009 -39.1998040000000003)"}{"id": 3, "osm_id": 2857, "osm_name": "Ngauruhoe Place", "osm_meta": null, "osm_source_id": 2938455973, "osm_target_id": 4173847805, "clazz": 43, "flags": 3, "source": 239248, "target": 295015, "km": 0.0501935, "kmh": 50, "cost": 0.0010039, "reverse_cost": 0.0010039, "x1": 175.5417999, "y1": -39.199804, "x2": 175.542348, "y2": -39.1996541, "geom_way": "LINESTRING (175.5417999000000009 -39.1998040000000003, 175.5422246999999913 -39.1996748999999980, 175.5423480000000040 -39.1996540999999965)"}
avro
$ pgrsql2data --input sample.sql --format avro $ avrocat sample.avro {"y2": -39.0733589, "x1": 175.6671636, "target": 2, "osm_name": "Ketetahi Road", "km": 0.9172049, "clazz": 43, "x2": 175.6641327, "source": 1, "osm_target_id": 1680775132, "cost": 0.0183441, "flags": 3, "osm_id": 2850, "y1": -39.065529, "osm_meta": null, "reverse_cost": 0.0183441, "kmh": 50, "geom_way": "LINESTRING (175.6671636000000092 -39.0655289999999979, 175.6669300000000078 -39.0660262999999972, 175.6665609000000075 -39.0667807000000025, 175.6664728000000082 -39.0669753999999969, 175.6664277000000141 -39.0671368000000001, 175.6663901000000010 -39.0673267999999965, 175.6663532000000032 -39.0676137000000026, 175.6663232999999877 -39.0679250000000025, 175.6662765000000093 -39.0681926999999973, 175.6662121999999897 -39.0685058999999981, 175.6661235999999917 -39.0688525999999996, 175.6660063999999863 -39.0693205000000034, 175.6659176000000002 -39.0697118000000003, 175.6658382999999901 -39.0700554000000011, 175.6657261999999946 -39.0705236000000014, 175.6656762000000072 -39.0707115000000016, 175.6656149000000084 -39.0709337999999988, 175.6655820000000006 -39.0710349000000008, 175.6655240000000049 -39.0711586999999980, 175.6654651999999999 -39.0712526000000011, 175.6653054999999881 -39.0715093999999965, 175.6650927999999965 -39.0717934999999983, 175.6649300999999923 -39.0720382000000015, 175.6646489999999972 -39.0725479999999976, 175.6643620000000112 -39.0730144999999993, 175.6641327000000103 -39.0733589000000023)", "id": 1, "osm_source_id": 691734}{"y2": -39.199804, "x1": 175.5417358, "target": 239248, "osm_name": "Ngauruhoe Place", "km": 0.0059348, "clazz": 43, "x2": 175.5417999, "source": 3, "osm_target_id": 2938455973, "cost": 0.0001187, "flags": 3, "osm_id": 2857, "y1": -39.1998235, "osm_meta": null, "reverse_cost": 0.0001187, "kmh": 50, "geom_way": "LINESTRING (175.5417357999999979 -39.1998235000000008, 175.5417999000000009 -39.1998040000000003)", "id": 2, "osm_source_id": 441520023}{"y2": -39.1996541, "x1": 175.5417999, "target": 295015, "osm_name": "Ngauruhoe Place", "km": 0.0501935, "clazz": 43, "x2": 175.542348, "source": 239248, "osm_target_id": 4173847805, "cost": 0.0010039, "flags": 3, "osm_id": 2857, "y1": -39.199804, "osm_meta": null, "reverse_cost": 0.0010039, "kmh": 50, "geom_way": "LINESTRING (175.5417999000000009 -39.1998040000000003, 175.5422246999999913 -39.1996748999999980, 175.5423480000000040 -39.1996540999999965)", "id": 3, "osm_source_id": 2938455973}