1. Prepare Data ¶

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

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 Belém city data and is a snapshot of Sep 2024.

1.2.1. Getting the data ¶

1.2.1.1. Option 1) When using OSGeoLive ¶

OSGeoLive comes with OSM data from the city of Belém.

CITY="BELEM_BR"
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.

CITY="BELEM_BR"
wget -N --progress=dot:mega "https://download.osgeo.org/livedvd/17/osm/BELEM_BR.osm.bz2"
bunzip2 -f "BELEM_BR.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 Sep 2024.

CITY="BELEM_BR"
BBOX="-48.52,-1.49,-48.4,-1.36"
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.

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/BELEM_BR.osm data
with the city_routing database

From a terminal window ctrl-alt-t.

1.3.1. Run the osm2pgrouting converter ¶

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

Note

Depending on the osm2pgrouting version -W password is needed

Output:

Execution starts at: Mon Dec 16 18:21:01 2024

***************************************************
           COMMAND LINE CONFIGURATION             *
***************************************************
Filename = BELEM_BR.osm
Configuration file = /usr/share/osm2pgrouting/mapconfig.xml
host = localhost
port = 5432
dbname = city_routing
username = user
schema= 
prefix = 
suffix = 
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

Dropping tables...
TABLE: ways dropped ... OK.
TABLE: ways_vertices_pgr dropped ... OK.
TABLE: pointsofinterest dropped ... OK.
TABLE: configuration dropped ... OK.
TABLE: osm_nodes dropped ... OK.
TABLE: osm_ways dropped ... OK.
TABLE: osm_relations dropped ... OK.

Creating tables...
TABLE: ways_vertices_pgr created ... OK.
TABLE: ways created ... OK.
TABLE: pointsofinterest created ... OK.
TABLE: configuration created ... OK.
Opening configuration file: /usr/share/osm2pgrouting/mapconfig.xml
    Parsing configuration

Exporting configuration ...
  - Done 
Counting lines ...
  - Done 
Opening data file: BELEM_BR.osm	total lines: 1706680
    Parsing data


End Of file


    Finish Parsing data

Adding auxiliary tables to database...

Export Ways ...
    Processing 119686 ways:

[********|                                          ] (16%) Total processed: 20000	 Vertices inserted: 21229	Split ways inserted 27927

[****************|                                  ] (33%) Total processed: 40000	 Vertices inserted: 1968	Split ways inserted 3014

[*************************|                         ] (50%) Total processed: 60000	 Vertices inserted: 390	Split ways inserted 665

[*********************************|                 ] (66%) Total processed: 80000	 Vertices inserted: 145	Split ways inserted 230

[*****************************************|         ] (83%) Total processed: 100000	 Vertices inserted: 437	Split ways inserted 605

[**************************************************|] (100%) Total processed: 119686	 Vertices inserted: 61	Split ways inserted 146

Creating indexes ...

Processing Points of Interest ...
#########################
size of streets: 119686
Execution started at: Mon Dec 16 18:21:01 2024
Execution ended at:   Mon Dec 16 18:21:10 2024
Elapsed time: 8.909 Seconds.
User CPU time: -> 4.57533 seconds
#########################

psql -c 'DELETE FROM ways WHERE length_m IS NULL;' -d city_routing

DELETE 1

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     | runner
 public | geometry_columns         | view     | runner
 public | pointsofinterest         | table    | user
 public | pointsofinterest_pid_seq | sequence | user
 public | spatial_ref_sys          | table    | runner
 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