4. Prepare Data ¶

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

Chapter Contents

Prepare Data

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:

# work on the home folder
cd

# login to postgres
psql -U postgres

-- Create "user"
CREATE ROLE "user" SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN PASSWORD 'user';

-- exit psql
\q

# Add the user to .pgpass
echo :5432:*:user:user >> .pgpass

4.1.1. Create a pgRouting compatible database.¶

Note

Depending on the postgres configureation -U <user> is needed on psql commands

# Create the database
createdb city_routing

# login as user "user"
psql 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 Boston city data and is a snapshot of Jun-2017.

4.2.1. Make a directory for pgRouting data manipulation ¶

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

4.2.2. Getting the data ¶

4.2.2.1. Option 1) When using OSGeo Live ¶

OSGeo Live comes with osm data from the city of Boston.

CITY="Boston_MA"
bzcat ~/data/osm/$CITY.osm.bz2 > $CITY.osm

4.2.2.2. Option 2) Download data form OSGeo Live website ¶

The exact same data can be found on the OSGeo Live download page.

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

4.2.2.3. Option 3) Download using Overpass XAPI.¶

The following downloads the latest OSM data on using the same area. Using this data in the workshop can generate variations on the results, due to changes since Jun-2017.

CITY="Boston_MA"
BBOX="-71.16528,42.31628,-70.99396,42.39493"
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. Upload Data to the database ¶

The next step is to run osm2pgrouting converter, which is a command line tool that inserts your data in the database, “ready” to be used with pgRouting. Additional information about osm2pgrouting can be found at the Appendix: osm2pgrouting Import Tool

For this step:

the osm2pgrouting default mapconfig.xml configuration file is used
and the ~/Desktop/workshop/Boston_MA.osm data.
with the city_routing database

From a terminal window ctrl-alt-t.

4.3.1. Run the osm2pgrouting converter ¶

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

Note

Depending on the osm2pgrouting version -W password is needed

Output:

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

    Parsing data (progress line per 100000 elements)

[*********************************************|     ] (91%) Total osm elements parsed: 1891000    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 8 relations:     Inserted: 8 in osm_relations

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

Export Ways ...
    Processing 97535 ways:
[*************************************************| ] (99%)    Ways Processed: 97535        Split Ways generated: 36126 Vertices inserted 26504 Inserted 36126 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: 97535
#########################

Tables on the database

psql -d city_routing -c "\d"

If everything went well the result should look like this:

Upload Data to the database
  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_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
  (15 rows)