Skip to main content
Join the official 2019 Python Developers SurveyStart the survey!

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

Project description

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}

Project details


Release history Release notifications

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for pgrsql2data, version 19.2.1
Filename, size File type Python version Upload date Hashes
Filename, size pgrsql2data-19.2.1-py2.py3-none-any.whl (7.6 kB) File type Wheel Python version py2.py3 Upload date Hashes View hashes
Filename, size pgrsql2data-19.2.1.tar.gz (8.7 kB) File type Source Python version None Upload date Hashes View hashes

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN SignalFx SignalFx Supporter DigiCert DigiCert EV certificate StatusPage StatusPage Status page