Documentation for osm2pgrouting v2.2

Vicky Vergara edited this page Aug 22, 2017 · 21 revisions

Index of this page:

What's new

  • mapconfig_for_bicycles.xml
  • progress bar
  • Foreign keys to tables
  • No use of pointers
  • table osm_ways_tags that had as columns class_id, way_id is not included anymore due to the fact that the ways table has the column class_id so the table osm_ways_tags is redundant.

Caution

  • Can not process "large" files, continents, big countries, or very large states.
  • Undefined behaviour when: The tables already exist but not all the columns that are needed
    • Use --clean to drop old tables (only drops old routing tables)

Tip

For large files you can remove some attributes, the file size will be reduced.

In the following example, the original file size is approximately 7.7gig, resulting a ~3.6gig file. osm2pgrouting then ran far quicker (~45minutes vs 3+ hours and not complete)

sed -r "s/version=\"[0-9]+\" timestamp=\"[^\"]+\" changeset=\"[0-9]+\" uid=\"[0-9]+\" user=\"[^\"]+\"//g" australia-latest.osm -i.org

Requirements

  • Have a Postgis database
  • When using schemas, have the schema already created
  • Permition for modifying the database
  • pgRouting extension can be added in the database, before or after the data import

Command

Version

$ osm2pgrouting --version
This is osm2pgrouting Version 2.2

Help

$ osm2pgrouting --help

Allowed options:

Help:
  --help                Produce help message for this version.
  -v [ --version ]      Print version string

General:
  -f [ --file ] arg                     REQUIRED: Name of the osm file.
  -c [ --conf ] arg (=/usr/share/osm2pgrouting/mapconfig.xml)
                                        Name of the configuration xml file.
  --schema arg                          Database schema to put tables.
                                          blank: defaults to default schema
                                          dictated by PostgreSQL Search_path.
  --prefix arg                          Prefix added at the beginning of the
                                        table names.
  --suffix arg                          Suffix added at the end of the table names.
  --addnodes                            Import the osm_nodes table.
  --clean                               Drop previously created tables.
               
Database options:
  -d [ --dbname ] arg               Name of your database (Required).
  -U [ --username ] arg (=postgres) Name of the user, which have write access 
                                    to the database.
  -h [ --host ] arg (=localhost)    Host of your postgresql database.
  -p [ --port ] arg (=5432)         db_port of your database.
  -W [ --password ] arg             Password for database access.

Sample Use

To a clean database

  1. Create the database and add postGIS
createdb workshop
psql -c "CREATE EXTENSION postgis" --dbname workshop
  1. Process an osm file (NE)
osm2pgrouting \
   -f workshopNE.osm \
   --dbname workshop \
   -U foo -W bar
  1. Output of the process
Execution starts at: Fri Nov  4 09:20:46 2016

***************************************************
           COMMAND LINE CONFIGURATION             *
***************************************************
Filename = workshopNE.osm
Configuration file = /usr/share/osm2pgrouting/mapconfig.xml                                                                                                                                                                      
host = localhost
port = 5432
dbname = workshop
username = foo
password = bar
schema=        
prefix =
suffix =       
Don't drop tables
Don't add nodes
***************************************************                                                                                                                                                                              
Connecting to the database                                                                                                                                                                                                       
host=localhost user=foo dbname=workshop port=5432 password=bar
connection success
Opening configuration file: /usr/share/osm2pgrouting/mapconfig.xml
    Parsing configuration

    Parsing data (progress line per 100000 elements)

[**************************************|            ] (76%) Total osm elements parsed: 2476000    Finish Parsing data


Creating tables...
Creating 'ways_vertices_pgr': OK
   Adding Geometry: Creating 'ways': OK
   Adding Geometry: Creating 'relations_ways': OK
Creating 'osm_nodes': OK
   Adding Geometry: Creating 'osm_relations': OK
Creating 'osm_way_types': OK
Creating 'osm_way_classes': OK
Adding auxiliary tables to database...

Export Types ...
    Processing 4 way types:      Inserted: 4 in osm_way_types

Export Classes ...
    Processing way's classes:    Inserted: 36 in osm_way_classes

Export Relations ...
    Processing 0 relations:     Inserted: 0 in osm_relations

Export RelationsWays ...
    Processing way's relations:          Inserted: 0 in relations_ways

Export Ways ...
    Processing 110706 ways:
[*************************************************| ] (99%)    Ways Processed: 110706       Split Ways generated: 26435 Vertices inserted 18885 Inserted 26435 split ways
Creating Foreign Keys ...
Foreign keys for osm_way_classes table created
Foreign keys for relations_ways table created
Foreign keys for Ways table created
#########################
size of streets: 110706
Execution started at: Fri Nov  4 09:20:46 2016
Execution ended at:   Fri Nov  4 09:21:03 2016
Elapsed time: 17.002 Seconds.
User CPU time: -> 12.2657 seconds
#########################

To a database that already contains data

  1. Process an osm file (SE)
osm2pgrouting \
   -f workshopSE.osm \
   --dbname workshop \
   -U foo -W bar
  1. Output of the process
Execution starts at: Fri Nov  4 09:24:07 2016

***************************************************
           COMMAND LINE CONFIGURATION             *
***************************************************
Filename = workshopSE.osm
Configuration file = /usr/share/osm2pgrouting/mapconfig.xml
host = localhost
port = 5432
dbname = workshop
username = foo
password = bar
schema= 
prefix = 
suffix = 
Don't drop tables
Don't add nodes
***************************************************
Connecting to the database
host=localhost user=foo dbname=workshop port=5432 password=bar
connection success
Opening configuration file: /usr/share/osm2pgrouting/mapconfig.xml
    Parsing configuration

    Parsing data (progress line per 100000 elements)

[**************|                                    ] (28%) Total osm elements parsed: 2029000    Finish Parsing data


Creating tables...
 Exists: 'ways_vertices_pgr': OK
 Exists: 'ways': OK
 Exists: 'relations_ways': OK
 Exists: 'osm_nodes': OK
 Exists: 'osm_relations': OK
 Exists: 'osm_way_types': OK
 Exists: 'osm_way_classes': OK
Adding auxiliary tables to database...

Export Types ...
    Processing 4 way types:      Inserted: 0 in osm_way_types

Export Classes ...
    Processing way's classes:    Inserted: 0 in osm_way_classes

Export Relations ...
    Processing 1 relations:     Inserted: 1 in osm_relations

Export RelationsWays ...
    Processing way's relations:          Inserted: 2 in relations_ways

Export Ways ...
    Processing 96407 ways:
[*************************************************| ] (99%)    Ways Processed: 96407        Split Ways generated: 27603 Vertices inserted 20735 Inserted 27564 split ways
Creating Foreign Keys ...
Foreign keys for osm_way_classes table created
Foreign keys for relations_ways table created
Foreign keys for Ways table created
#########################
size of streets: 96407
Execution started at: Fri Nov  4 09:24:07 2016
Execution ended at:   Fri Nov  4 09:24:24 2016
Elapsed time: 16.823 Seconds.
User CPU time: -> 10.5931 seconds
#########################

Using the clean flag

  1. Process an osm file (workshopSmall)
osm2pgrouting \
  -f workshopSmall.osm \
  --dbname workshop \
  -U foo -W bar --clean
  1. Output of the process
Execution starts at: Fri Nov  4 09:26:21 2016

***************************************************
           COMMAND LINE CONFIGURATION             *
***************************************************
Filename = workshopSmall.osm
Configuration file = /usr/share/osm2pgrouting/mapconfig.xml
host = localhost
port = 5432
dbname = workshop
username = foo
password = bar
schema= 
prefix = 
suffix = 
Drop tables
Don't add nodes
***************************************************
Connecting to the database
host=localhost user=foo dbname=workshop port=5432 password=bar
connection success
Opening configuration file: /usr/share/osm2pgrouting/mapconfig.xml
    Parsing configuration

    Parsing data (progress line per 100000 elements)

[**|                                                ] (4%) Total osm elements parsed: 304000    Finish Parsing data


Dropping tables...

Creating tables...
Creating 'ways_vertices_pgr': OK
   Adding Geometry: Creating 'ways': OK
   Adding Geometry: Creating 'relations_ways': OK
 Exists: 'osm_nodes': OK
 Exists: 'osm_relations': OK
 Exists: 'osm_way_types': OK
 Exists: 'osm_way_classes': OK
Adding auxiliary tables to database...

Export Types ...
    Processing 4 way types:      Inserted: 0 in osm_way_types

Export Classes ...
    Processing way's classes:    Inserted: 0 in osm_way_classes

Export Relations ...
    Processing 0 relations:     Inserted: 0 in osm_relations

Export RelationsWays ...
    Processing way's relations:          Inserted: 0 in relations_ways

Export Ways ...
    Processing 14354 ways:
[*************************************************| ] (98%)    Ways Processed: 14354        Split Ways generated: 3810  Vertices inserted 3050 Inserted 3810 split ways
Creating Foreign Keys ...
Foreign keys for osm_way_classes table created
Foreign keys for relations_ways table created
Foreign keys for Ways table created
#########################
size of streets: 14354
Execution started at: Fri Nov  4 09:26:21 2016
Execution ended at:   Fri Nov  4 09:26:24 2016
Elapsed time: 2.897 Seconds.
User CPU time: -> 1.54912 seconds
#########################

Table structure

createdb tablestructure
psql -c "CREATE EXTENSION postgis" --dbname tablestructure 
psql -c "CREATE SCHEMA the_schema" --dbname tablestructure
osm2pgrouting -f workshopSmall.osm
    --prefix pre_
    --suffix _suf
    --schema the_schema
    --addnodes
    -U foo -W bar 
     --dbname tablestructure

In the example:

  • the prefix has an underscore at the end. "pre_"
  • the suffix has an underscore at the beginning. "_suf"
psql tablestructure
\dt+ the_schema.*
                                 List of relations
   Schema   |           Name            | Type  | Owner |    Size    | Description 
------------+---------------------------+-------+-------+------------+-------------
 the_schema | osm_nodes                 | table | foo   | 9040 kB    | 
 the_schema | osm_relations             | table | foo   | 8192 bytes | 
 the_schema | osm_way_classes           | table | foo   | 16 kB      | 
 the_schema | osm_way_types             | table | foo   | 16 kB      | 
 the_schema | pre_relations_ways_suf    | table | foo   | 0 bytes    | 
 the_schema | pre_ways_suf              | table | foo   | 1192 kB    | 
 the_schema | pre_ways_suf_vertices_pgr | table | foo   | 336 kB     | 
(7 rows)
  • all tables are created on the same schema
  • 7 tables are created and the prefix and suffix is used in the routing tables

In the following outputs, the "| Storage | Stats target | Description" are eliminated manually for readability.

the ways table

\dS+ the_schema.pre_ways_suf
                                                              Table "the_schema.pre_ways_suf"
     Column       |           Type            |                               Modifiers
-------------------+---------------------------+-----------------------------------------------------------------------
gid               | bigint                    | not null default nextval('the_schema.pre_ways_suf_gid_seq'::regclass) 
class_id          | integer                   | not null
length            | double precision          |
length_m          | double precision          |
name              | text                      |
source            | bigint                    |
target            | bigint                    |
x1                | double precision          |
y1                | double precision          | 
x2                | double precision          |
y2                | double precision          | 
cost              | double precision          |      
reverse_cost      | double precision          |     
cost_s            | double precision          | 
reverse_cost_s    | double precision          |   
rule              | text                      | 
one_way           | integer                   |   
maxspeed_forward  | integer                   |
maxspeed_backward | integer                   |  
osm_id            | bigint                    |        
source_osm        | bigint                    | 
target_osm        | bigint                    | 
priority          | double precision          | default 1
the_geom          | geometry(LineString,4326) | 
Indexes:
  "pre_ways_suf_pkey" PRIMARY KEY, btree (gid)
  "pre_ways_suf_gdx" gist (the_geom)
  "pre_ways_suf_source_idx" btree (source)
  "pre_ways_suf_source_osm_idx" btree (source_osm)
  "pre_ways_suf_target_idx" btree (target)
  "pre_ways_suf_target_osm_idx" btree (target_osm)
Foreign-key constraints:
  "pre_ways_suf_class_id_fkey" FOREIGN KEY (class_id) REFERENCES the_schema.osm_way_classes(class_id)
  "pre_ways_suf_source_fkey" FOREIGN KEY (source) REFERENCES the_schema.pre_ways_suf_vertices_pgr(id)
  "pre_ways_suf_target_fkey" FOREIGN KEY (target) REFERENCES the_schema.pre_ways_suf_vertices_pgr(id)

the vertices table

\dS+ the_schema.pre_ways_suf_vertices_pgr
                                                        Table "the_schema.pre_ways_suf_vertices_pgr"
  Column  |         Type         |                                     Modifiers
----------+----------------------+-----------------------------------------------------------------------------------+
 id       | bigint               | not null default nextval('the_schema.pre_ways_suf_vertices_pgr_id_seq'::regclass)
 osm_id   | bigint               |
 cnt      | integer              |
 chk      | integer              |
 ein      | integer              |
 eout     | integer              |
 lon      | numeric(11,8)        |
 lat      | numeric(11,8)        |
 the_geom | geometry(Point,4326) |
Indexes:
    "pre_ways_suf_vertices_pgr_pkey" PRIMARY KEY, btree (id)
    "vertex_id" UNIQUE CONSTRAINT, btree (osm_id)
    "pre_ways_suf_vertices_pgr_gdx" gist (the_geom)
    "pre_ways_suf_vertices_pgr_osm_id_idx" btree (osm_id)
Referenced by:
    TABLE "the_schema.pre_ways_suf" CONSTRAINT "pre_ways_suf_source_fkey" FOREIGN KEY (source) REFERENCES the_schema.pre_ways_suf_vertices_pgr(id)
    TABLE "the_schema.pre_ways_suf" CONSTRAINT "pre_ways_suf_target_fkey" FOREIGN KEY (target) REFERENCES the_schema.pre_ways_suf_vertices_pgr(id)

Auxiliary tables

They are mainly related to the configuration

the_schema.osm_way_types

SELECT * FROM the_schema.osm_way_types ;
 type_id |   name    
---------+-----------
       2 | cycleway
       1 | highway
       4 | junction
       3 | tracktype
(4 rows)

the_schema.osm_way_classes

SELECT * FROM the_schema.osm_way_classes;
 class_id | type_id |       name        | priority | default_maxspeed 
----------+---------+-------------------+----------+------------------
      201 |       2 | lane              |        0 |               50
      204 |       2 | opposite          |        0 |               50
      203 |       2 | opposite_lane     |        0 |               50
      202 |       2 | track             |        0 |               50
      120 |       1 | bridleway         |        0 |               50
...

the_schema.osm_relations

SELECT * FROM the_schema.osm_relations;
 relation_id | type_id | class_id | name 
-------------+---------+----------+------
(0 rows)

the_schema.osm_nodes

SELECT  node_id, osm_id, lon, lat, numofuse, ST_AsText(the_geom) FROM the_schema.osm_nodes LIMIT 5;
 node_id |  osm_id  |      lon      |     lat     | numofuse |           st_astext            
---------+----------+---------------+-------------+----------+--------------------------------
       1 | 36757745 | -122.62153000 | 45.41327120 |        2 | POINT(-122.62153 45.4132712)
       2 | 36757748 | -122.62093520 | 45.41285660 |        1 | POINT(-122.6209352 45.4128566)
       3 | 36757755 | -122.62127960 | 45.41257250 |        1 | POINT(-122.6212796 45.4125725)
       4 | 36758157 | -122.60811920 | 45.40168840 |        1 | POINT(-122.6081192 45.4016884)
       5 | 37011329 | -122.61641730 | 45.40751060 |        3 | POINT(-122.6164173 45.4075106)
(5 rows)

Data

The following sample data used in this documentation:

BBOX="-122.8,45.4,-122.5,45.6"
wget --progress=dot:mega -O "workshop.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"
BBOX="-122.65,45.5, -122.5,45.6"
wget --progress=dot:mega -O "workshopNE.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"
BBOX="-122.65,45.4, -122.5,45.5"
wget --progress=dot:mega -O "workshopSE.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"
BBOX="-122.8,45.5, -122.65,45.6"
wget --progress=dot:mega -O "workshopNW.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"
BBOX="-122.8,45.4, -122.65,45.5"
wget --progress=dot:mega -O "workshopSW.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"
BBOX="-122.65,45.4, -122.6,45.45"
wget --progress=dot:mega -O "workshopSmall.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"

Contributors

Version 2.2 contributors in alphabetical order:

  • Adrien Pavie - added mapconfig_for_bycicles.xml
  • Luís de Sousa - added foreign keys
  • Mark Dimond, - testing
  • Virginia Vergara - eliminate the use of pointers, progress bar, bugs fixing

History of Contributors

Adrien Pavie, Anton Patrushev, Daniel Kastl, Daniel Wendt, Jordan Anderson, J Kishore Kumar, Ko Nagase, Luís de Sousa, Mark Dimond, Regina Obe, Sarthak Agarwal, Virginia Vergara

You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.