6. osm2pgrouting Import Tool

osm2pgrouting is a command line tool that allows to import OpenStreetMap data into a pgRouting database. It builds the routing network topology automatically and creates tables for feature types and road classes. osm2pgrouting was primarily written by Daniel Wendt and is currently hosted on the pgRouting project site: http://www.pgrouting.org/docs/tools/osm2pgrouting.html

Note

There are some limitations, especially regarding the network size. The current version of osm2pgrouting needs to load all data into memory, which makes it fast but also requires a lot or memory for large datasets. An alternative tool to osm2pgrouting without the network size limitation is osm2po (http://osm2po.de). It’s available under “Freeware License”.

Raw OpenStreetMap data contains much more features and information than need for routing. Also the format is not suitable for pgRouting out-of-the-box. An .osm XML file consists of three major feature types:

  • nodes
  • ways
  • relations

The data of sampledata.osm for example looks like this:

<?xml version='1.0' encoding='UTF-8'?>
<osm version='0.6' generator='xapi: OSM Extended API 2.0' ... >
  ...	
  <node id='255405560' lat='41.4917468' lon='2.0257695' version='1' 
  		changeset='19117' user='efrainlarrea' uid='32823' visible='true' 
  		timestamp='2008-04-02T17:40:07Z'>
  </node>
  <node id='255405551' lat='41.4866740' lon='2.0302842' version='3' 
  		changeset='248452' user='efrainlarrea' uid='32823' visible='true' 
  		timestamp='2008-04-24T15:56:08Z'>
  </node>
  <node id='255405552' lat='41.4868540' lon='2.0297863' version='1' 
  		changeset='19117' user='efrainlarrea' uid='32823' visible='true' 
  		timestamp='2008-04-02T17:40:07Z'>
  </node>
  ...
  <way id='35419222' visible='true' timestamp='2009-06-03T21:49:11Z' 
  		version='1' changeset='1416898' user='Yodeima' uid='115931'>
    <nd ref='415466914'/>
    <nd ref='415466915'/>
    <tag k='highway' v='unclassified'/>
    <tag k='lanes' v='1'/>
    <tag k='name' v='Carrer del Progrés'/>
    <tag k='oneway' v='no'/>
  </way>
  <way id='35419227' visible='true' timestamp='2009-06-14T20:37:55Z' 
  		version='2' changeset='1518775' user='Yodeima' uid='115931'>
    <nd ref='415472085'/>
    <nd ref='415472086'/>
    <nd ref='415472087'/>
    <tag k='highway' v='unclassified'/>
    <tag k='lanes' v='1'/>
    <tag k='name' v='carrer de la mecanica'/>
    <tag k='oneway' v='no'/>
  </way>
  ...
  <relation id='903432' visible='true' timestamp='2010-05-06T08:36:54Z' 
  		version='1' changeset='4619553' user='ivansanchez' uid='5265'>
    <member type='way' ref='56426179' role='outer'/>
    <member type='way' ref='56426173' role='inner'/>
    <tag k='layer' v='0'/>
    <tag k='leisure' v='common'/>
    <tag k='name' v='Plaça Can Suris'/>
    <tag k='source' v='WMS shagrat.icc.cat'/>
    <tag k='type' v='multipolygon'/>
  </relation>
  ...
</osm>

Detailed description of all possible OpenStretMap types and classes can be found here: http://wiki.openstreetmap.org/index.php/Map_features.

When using osm2pgrouting, we take only nodes and ways of types and classes specified in mapconfig.xml file that will be imported into the routing database:

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <type name="highway" id="1">
    <class name="motorway" id="101" />
    <class name="motorway_link" id="102" />
    <class name="motorway_junction" id="103" />
    ...
    <class name="road" id="100" />
  </type>    
  <type name="junction" id="4">
    <class name="roundabout" id="401" />
  </type>  
</configuration> 

The default mapconfig.xml is installed in /usr/share/osm2pgrouting/.

6.1. Create routing database

Before we can run osm2pgrouting we have to create a database and load PostGIS and pgRouting functions into this database. If you have installed the template databases as described in the previous chapter, creating a pgRouting-ready database is done with a single command. Open a terminal window and run:

# Optional: Drop database
dropdb -U postgres pgrouting-workshop

# Create a new routing database
createdb -U postgres pgrouting-workshop
psql -U postgres -d pgrouting-workshop -c "CREATE EXTENSION postgis;"
psql -U postgres -d pgrouting-workshop -c "CREATE EXTENSION pgrouting;"

... and you’re done.

Alternativly you can use PgAdmin III and SQL commands. Start PgAdmin III (available on the LiveDVD), connect to any database and open the SQL Editor and then run the following SQL command:

-- create pgrouting-workshop database
CREATE DATABASE "pgrouting-workshop";
\c pgrouting-workshop

CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;

6.2. Run osm2pgrouting

The next step is to run osm2pgrouting converter, which is a command line tool, so you need to open a terminal window.

We take the default mapconfig.xml configuration file and the pgrouting-workshop database we created before. Furthermore we take ~/Desktop/pgrouting-workshop/data/sampledata.osm as raw data. This file contains only OSM data from Nottingham to speed up data processing time.

The workshop data is available as compressed file, which needs to be extracted first either using file manager or with this command:

cd ~/Desktop/pgrouting-workshop/
tar -xvzf data.tar.gz

Then run the converter:

osm2pgrouting -file "data/sampledata.osm" \
                          -conf "/usr/share/osm2pgrouting/mapconfig.xml" \
                          -dbname pgrouting-workshop \
                          -user postgres \
                          -clean

List of all possible parameters:

Parameter Value Description Required
-file <file> name of your osm xml file yes
-dbname <dbname> name of your database yes
-user <user> name of the user, which have write access to the database yes
-conf <file> name of your configuration xml file yes
-host <host> host of your postgresql database (default: 127.0.0.1) no
-port <port> port of your database (default: 5432) no
-passwd <passwd> password for database access no
-prefixtables <prefix> add at the beginning of table names no
-skipnodes   don’t import the nodes table no
-clean   drop peviously created tables no

Note

  • There might be an updated version of osm2pgrouting available. To update the package run:
sudo apt-get update
sudo apt-get install --only-upgrade osm2pgrouting
  • If you get permission denied error for postgres users you can set connection method to trust in /etc/postgresql/9.1/main/pg_hba.conf and restart PostgreSQL server with sudo service postgresql restart.

Depending on the size of your network the calculation and import may take a while. After it’s finished connect to your database and check the tables that should have been created:

Run: psql -U postgres -d routing -c "\d"

If everything went well the result should look like this:

                 List of relations
 Schema |        Name         |   Type   |  Owner
--------+---------------------+----------+----------
 public | classes             | table    | postgres
 public | geography_columns   | view     | postgres
 public | geometry_columns    | view     | postgres
 public | nodes               | table    | postgres
 public | raster_columns      | view     | postgres
 public | raster_overviews    | view     | postgres
 public | relation_ways       | table    | postgres
 public | relations           | table    | postgres
 public | spatial_ref_sys     | table    | postgres
 public | types               | table    | postgres
 public | vertices_tmp        | table    | postgres
 public | vertices_tmp_id_seq | sequence | postgres
 public | way_tag             | table    | postgres
 public | ways                | table    | postgres
(14 rows)

Note

osm2pgrouting creates more tables and imports more attributes than we will use in this workshop. Some of them have been just added recently and are still lacking proper documentation.

Table Of Contents

Previous topic

5. pgRouting Algorithms

Next topic

7. Advanced Routing Queries

License

This work is licensed under a Creative Commons Attribution-Share Alike 3.0 License.

Creative Commons License