4. Prepare Data¶

To be able to use pgRouting, data has to be imported into a database.

This chapter covers:

Prepare the database
Get the Workshop Data
Run osm2pgrouting

4.1. Prepare the database¶

Since version 2.0 pgRouting functions are installed as extension. This requires:

PostgreSQL 9.1 or higher
PostGIS 2.x installed as extension

These requirements are met, then open a terminal window ctrl-alt-t and follow the instructions:

If OSGeo Live is not being used.

OSGeo Live’s account is user. To easily use the workshop when not using OSGeo Live this extra steps are needed:

# login to postgres
psql -U postgres

# Create "user"
CREATE ROLE "user" SUPERUSER LOGIN;

# exit psql
\q

Create a pgRouting compatible database.

# login as user "user"
psql -U user

-- create routing database
CREATE DATABASE city_routing;
\c city_routing

-- add PostGIS functions
CREATE EXTENSION postgis;

-- add pgRouting functions
CREATE EXTENSION pgrouting;

-- Inspect the pgRouting installation
\dx+ pgRouting

-- View pgRouting version
SELECT pgr_version();

# exit psql
\q

4.2. Get the Workshop Data¶

The pgRouting workshop will make use of OpenStreetMap data, which is already available on OSGeo Live. This workshop will use the Bonn city data.

Make a directory for pgRouting data manipulation

mkdir ~/Desktop/workshop
cd ~/Desktop/workshop

When using OSGeo Live

CITY="BONN_DE"
cp ~/data/osm/$CITY.osm.bz2 .
bunzip2 $CITY.osm.bz2

Download data form OSGeo Live website

CITY="BONN_DE"
wget -N --progress=dot:mega \
    "http://download.osgeo.org/livedvd/data/osm/$CITY/$CITY.osm.bz2"
bunzip2 $CITY.osm.bz2

Download using Overpass XAPI.

CITY="BONN_DE"
BBOX="7.097,50.6999,7.1778,50.7721"
wget --progress=dot:mega -O "$CITY.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"

More information how to download OpenStreetMap information can be found in http://wiki.openstreetmap.org/wiki/Downloading_data

An alternative for very large areas is to use the download services of Geofabrik.

4.3. Run osm2pgrouting¶

The next step is to run osm2pgrouting converter, which is a command line tool that inserts your data into your database.

For this workshop:

Use the osm2pgrouting default mapconfig.xml configuration file
Use city_routing database installed above.
Use ~/Desktop/workshop/BONN_DE.osm (see: Get the Workshop Data)

From a terminal window ctrl-alt-t.

Run the converter:

cd ~/Desktop/workshop
    osm2pgrouting \
    -f BONN_DE.osm \
    -d city_routing \
    -U user

Output:

Execution starts at: Sun Jul  3 17:45:12 2016

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

Opening data file: BONN_DE.osm
    Parsing data

Spliting ways

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_tags': OK
Creating 'osm_way_types': OK
Creating 'osm_way_classes': OK
Adding auxiliary tables to database...
    Processing 4 way types:  Inserted 4 way types
    Processing way's classes:  Inserted 36 way's classes
    Processing way's relations:  Inserted: 83874way's relations
    Processing way's tags:  Inserted 22055 way's tags
    Processing 21086 ways:
Vertices inserted 16412    Ways inserted: 21086
Creating topology...
#########################
size of streets: 10340
size of split ways : 21086
Execution started at: Sun Jul  3 17:45:12 2016
Execution ended at:   Sun Jul  3 17:45:16 2016
Elapsed time: 4.611 Seconds.
User CPU time: -> 1.64268

Run: psql -U user -d city_routing -c "\d"

If everything went well the result should look like this:

List of relations
Schema |           Name           |   Type   | Owner
--------+--------------------------+----------+-------
public | geography_columns        | view     | user
public | geometry_columns         | view     | user
public | osm_nodes                | table    | user
public | osm_nodes_node_id_seq    | sequence | user
public | osm_relations            | table    | user
public | osm_way_classes          | table    | user
public | osm_way_tags             | table    | user
public | osm_way_types            | table    | user
public | raster_columns           | view     | user
public | raster_overviews         | view     | user
public | relations_ways           | table    | user
public | spatial_ref_sys          | table    | user
public | ways                     | table    | user
public | ways_gid_seq             | sequence | user
public | ways_vertices_pgr        | table    | user
public | ways_vertices_pgr_id_seq | sequence | user
(16 rows)