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 ¶

pgRouting is installed as extension. This requires:

PostgreSQL 9.4 or higher
PostGIS 2.x installed as extension

These requirements are met on OSGeoLive. Once done, open a terminal window by pressing ctrl-alt-t and follow the instructions.

Note

If OSGeoLive is not being used, please refer to Chapter: Appendix.

4.1.1. Create a pgRouting compatible database.¶

Note

Depending on the postgres configuration -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 OSGeoLive. This workshop will use the Bucharesti city data and is a snapshot of August-2018.

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 OSGeoLive ¶

OSGeoLive comes with osm data from the city of Bucharesti.

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

4.2.2.2. Option 2) Download data form OSGeoLive website ¶

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

CITY="Bucaresti_RO"
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 in the results, due to changes since Jun-2017.

CITY="Bucaresti_RO"
BBOX="26.0535,44.4058,26.1468,44.4566"
wget --progress=dot:mega -O "$CITY.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"

More information about how to download OpenStreetMap data can be found in https://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/Bucaresti_RO.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 Bucaresti_RO.osm \
    -d city_routing \
    -U user

Note

Depending on the osm2pgrouting version -W password is needed

Output:

***************************************************
           COMMAND LINE CONFIGURATION             *
***************************************************
Filename = Bucaresti_RO.osm
Configuration file = /usr/local/share/osm2pgrouting/mapconfig.xml
host = localhost
port = 5432
dbname = city_routing
username =
password =
schema=
prefix =
suffix =
Don't drop tables
Don't create indexes
Don't add OSM nodes
***************************************************
Testing database connection: city_routing
database connection successful: city_routing
Connecting to the database
connection success

Creating tables...
TABLE: ways_vertices_pgr already exists.
TABLE: ways_vertices_pgr already exists.
TABLE: ways_vertices_pgr already exists.
TABLE: ways_vertices_pgr already exists.
Opening configuration file: /usr/local/share/osm2pgrouting/mapconfig.xml
    Parsing configuration

Exporting configuration ...
  - Done
Counting lines ...
  - Done
Opening data file: Bucaresti_RO.osm     total lines: 1215318
    Parsing data


End Of file


    Finish Parsing data

Adding auxiliary tables to database...

Export Ways ...
    Processing 38897 ways:
[*************************|                         ] (51%) Total processed: 20000       Vertices inserted: 12609       Split ways inserted 14553
[**************************************************|] (100%) Total processed: 38897      Vertices inserted: 4530        Split ways inserted 8758

Creating indexes ...

Processing Points of Interest ...
#########################
size of streets: 38897
#########################

Tables on the database

psql -d city_routing -c "\d"

If everything went well the result should look like this:

               List of relations
Schema |           Name           |   Type   | Owner
--------+--------------------------+----------+-------
public | configuration            | table    | user
public | configuration_id_seq     | sequence | user
public | geography_columns        | view     | user
public | geometry_columns         | view     | user
public | pointsofinterest         | table    | user
public | pointsofinterest_pid_seq | sequence | user
public | raster_columns           | view     | user
public | raster_overviews         | view     | 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
(13 rows)

4.4. Chapter: Appendix ¶

OSGeoLive’s account name on the database is "user". To easily use the workshop when not using OSGeoLive 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