pgrsql2data

Release 19.2.1

Utility for converting PgRouting SQL scripts output by osm2po to data files (CSV, JSON or Avro).

Homepage Repository PyPI Python

Keywords
osm2po, pgrouting, openstreetmap
License
MIT
Install
pip install pgrsql2data==19.2.1

Documentation

PgrSQL2Data

PyPI version Build status Coverage Python versions Github license

Description

This tool aims to convert the SQL files generated by osm2po into generic datasets that can be imported anywhere.

Supported data formats include csv, json and avro.

Long(er) description

osm2po generates a PgRouting-compatible SQL file that contains DDL statements to create and insert data into PostgreSQL. These statements are tightly coupled to PostgreSQL/PostGIS and make it difficult to import this data elsewhere (e.g. Google BigQuery).

This tool converts SQL statements into a structured data format (csv, json or avro).

  • Data schema is automatically inferred from the script's CREATE TABLE statement
  • Geometry data is converted from hexidecimal WKB to WKT

This tool can theoretically be used on any SQL script that contains a (single) CREATE TABLE and INSERT INTO statements.

Installation

Install with pip:

pip install pgrsql2data

Usage

Command line

$ pgrsql2data --input osm_2po_pgr.sql --format csv

Example

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 2019

SET client_encoding = 'UTF8';

DROP TABLE IF EXISTS nz_2po_4pgr;
-- SELECT DropGeometryTable('nz_2po_4pgr');

CREATE TABLE nz_2po_4pgr(id integer, osm_id bigint, osm_name character varying, osm_meta character varying, osm_source_id bigint, osm_target_id bigint, clazz integer, flags integer, source integer, target integer, km double precision, kmh integer, cost double precision, reverse_cost double precision, x1 double precision, y1 double precision, x2 double precision, y2 double precision);
SELECT AddGeometryColumn('nz_2po_4pgr', 'geom_way', 4326, 'LINESTRING', 2);

INSERT INTO nz_2po_4pgr VALUES 
(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...');

Converts to...

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}

Stats

Dependencies
2
Dependent packages
0
Dependent repositories
0
Total releases
1
Latest release
First release
Stars
0
Forks
0
Watchers
1
Contributors
1
Repository size
1000 Bytes
SourceRank
7

Development practices

Source repo 2FA enabled
TEXT!
Package manager 2FA enabled
TEXT!
Is security responsive
TEXT!
Dependencies are managed
TEXT!
Issue-free release available
TEXT!
Succession plan available
TEXT!
Package manager 2FA enabled
TEXT!

The Tidelift Subscription provides access to a continuously curated stream of human-researched and maintainer-verified data on open source packages and their licenses, releases, vulnerabilities, and development practices.

Learn more →

Releases

19.2.1

Contributors

Chris


See all contributors

Something wrong with this page? Make a suggestion

Export .ABOUT file for this package

Last synced: 2021-02-19 09:34:52 UTC

Login to resync this project