1. Prepare Data

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

1.1. Prepare the database

pgRouting is installed as extension. This requires:

• Supported PostgreSQL version

• Supported PostGIS version

These requirements are met on OSGeoLive. When the required software is installed, open a terminal window by pressing ctrl-alt-t and follow the instructions. Information about installing OSGeoLive can be found on Installation.

Note

If OSGeoLive is not being used, please refer to the chapter’s appendix to set up the user “user”.

1.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 << EOF

-- add PostGIS functions
CREATE EXTENSION postgis;

-- add pgRouting functions
CREATE EXTENSION pgrouting;

-- Inspect the pgRouting installation
\dx+ pgrouting

-- View pgRouting version
SELECT pgr_version();

EOF

1.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 Firenze city data and is a snapshot of July 2021.

1.2.1. Getting the data

1.2.1.1. Option 1) When using OSGeoLive

OSGeoLive comes with osm data from the city of Firenze.

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

1.2.1.2. Option 2) Download data form OSGeoLive website

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

# TODO Use the /15/osm  instead of data/osm/FIRENZE_IT
CITY="Firenze_IT"
wget -N --progress=dot:mega \
"http://download.osgeo.org/livedvd/15/osm/$CITY.osm.bz2"
bunzip2 -f "$CITY.osm.bz2"

1.2.1.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 July 2021.

CITY="Firenze_IT"
BBOX="11.20,43.76,11.27,43.79"
wget --progress=dot:mega -O "$CITY.osm" "http://www.overpass-api.de/api/xapi?*[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.

1.3. Upload data to the database

The next step is to run osm2pgrouting converter, which is a command line tool that inserts the 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/Firenze_IT.osm data

• with the city_routing database

From a terminal window ctrl-alt-t.

1.3.1. Run the osm2pgrouting converter

/usr/local/bin/osm2pgrouting \
    -f "Firenze_IT.osm" \
    -c "/usr/local/share/osm2pgrouting/mapconfig.xml" \
    -d city_routing \
    -U user \
    -W user \
    --clean

Note

Depending on the osm2pgrouting version -W password is needed

Output:

/usr/local/bin/osm2pgrouting \
    -f "Firenze_IT.osm" \
    -c "/usr/local/share/osm2pgrouting/mapconfig.xml" \
    -d city_routing \
    -U user \
    -W user \
    --clean

1.3.2. 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     | vicky
 public | geometry_columns         | view     | vicky
 public | pointsofinterest         | table    | user
 public | pointsofinterest_pid_seq | sequence | user
 public | spatial_ref_sys          | table    | vicky
 public | ways                     | table    | user
 public | ways_gid_seq             | sequence | user
 public | ways_vertices_pgr        | table    | user
 public | ways_vertices_pgr_id_seq | sequence | user
(11 rows)

1.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