1. Data for Sustainable Development Goals

To be able to use pgRouting, data has to be imported into a database. This chapter will use osm2pgrouting to get that the data from OpenStreetMaps(OSM). This data will be used for exercises in further chapters.

1.1. Work Directory for pgRouting data manipulation

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

1.2. Mumbai database

pgRouting is pre-installed as an extension which 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 in OSGeoLive Installation of this workshop.

Note

If you don’t have pgRouting installed. You can find the installation procedure at this link

1.2.1. Create Mumbai database compatible with pgRouting

Use the following command to create mumbai database

createdb mumbai

To connect to the database do the following

psql mumbai

After connecting to the database, first step is to create EXTENSION to enable pgRouting and PostGIS in the database. Then add the SCHEMA for each table.

-- add PostGIS extension
CREATE EXTENSION postgis;

-- add pgRouting extension
CREATE EXTENSION pgrouting;

-- creating schemas for data
CREATE SCHEMA roads;
CREATE SCHEMA buildings;
CREATE EXTENSION hstore;

1.2.2. Get the Mumbai Data

The pgRouting workshop will make use of OpenStreetMap data of an area in Mumbai City. The instructions for downloading the data are given below.

1.2.2.1. Downloading Mumbai data from OSGeo

The following command is used to download the snapshot of the Mumbai area data used in this workshop, using the download service of OSGeo.

Note

The Mumbai data for this workshop depends on this snapshot.

wget -N --progress=dot:mega "https://download.osgeo.org/pgrouting/workshops/mumbai.osm.bz2"
bunzip2 -f "mumbai.osm.bz2"

1.2.2.2. Downloading Mumbai data from OpenStreetMap (OSM)

The following command is used to download the OpenStreetMap data of the area in Mumbai, India.

OpenStreetMap data changes on a day to day basis, therefore if this data is used, the results might change and some queries might need adjustments. The command was used to take the snapshot of the data on June 2021.

CITY="mumbai"
BBOX="72.8263,19.1021,72.8379,19.1166"
wget --progress=dot:mega -O "$CITY.osm" "http://www.overpass-api.de/api/xapi?*[bbox=][@meta]"

1.2.3. Upload Mumbai 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. See Appendix: osm2pgrouting Import Tool for additional information about osm2pgrouting.

For this step the following is used:

• mumbai_buildings.xml and mumbai_roads.xml configuration files for osm2pgrouting.

• ~/Desktop/workshop/mumbai.osm - OSM data from the previous step

• mumbai database.

Contents of the configuration files are given in the Appendix. Create a XML file using these contents and save it into the root directory ~/Desktop/workshop.

Open a terminal window by ctrl-alt-t and move to the workshop directory by cd ~/Desktop/workshop. The following osm2pgrouting command will be used to convert the osm files to pgRouting friendly format which we will use for further exercises.

1.2.3.1. Importing Mumbai Roads

The following osm2pgrouting command will be used to import the Roads from OpenStreetMaps file to pgRouting database which we will use for further exercises.

/usr/bin/osm2pgrouting \
    -f "mumbai.osm" \
    -c "/usr/share/osm2pgrouting/mapconfig.xml" \
    --schema "roads" \
    -d mumbai \
    -U user \
    -W user \
    --prefix "roads_" \
    --tags \
    --clean

Note

Depending on the osm2pgrouting version -W password is needed

Output:

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

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

Dropping tables...
TABLE: roads.roads_ways dropped ... OK.
TABLE: roads.roads_ways_vertices_pgr dropped ... OK.
TABLE: roads.roads_pointsofinterest dropped ... OK.
TABLE: roads.configuration dropped ... OK.
TABLE: roads.osm_nodes dropped ... OK.
TABLE: roads.osm_ways dropped ... OK.
TABLE: roads.osm_relations dropped ... OK.

Creating tables...
TABLE: roads.roads_ways_vertices_pgr created ... OK.
TABLE: roads.roads_ways created ... OK.
TABLE: roads.roads_pointsofinterest created ... OK.
TABLE: roads.configuration created ... OK.
Opening configuration file: /usr/share/osm2pgrouting/mapconfig.xml
    Parsing configuration

Exporting configuration ...
  - Done 
Counting lines ...
  - Done 
Opening data file: mumbai.osm	total lines: 24438
    Parsing data


End Of file


    Finish Parsing data

Adding auxiliary tables to database...

Export Ways ...
    Processing 1309 ways:

[**************************************************|] (100%) Total processed: 1309	 Vertices inserted: 378	Split ways inserted 558

Creating indexes ...

Processing Points of Interest ...
#########################
size of streets: 1309
Execution started at: Mon Dec 16 18:21:11 2024
Execution ended at:   Mon Dec 16 18:21:11 2024
Elapsed time: 0.491 Seconds.
User CPU time: -> 0.243815 seconds
#########################

1.2.3.2. Importing Mumbai Buildings

Similar to Roads, osm2pgrouting command will be used to import the Buildings from OpenStreetMaps file to pgRouting database which we will use for further exercises.

/usr/bin/osm2pgrouting \
    -f "mumbai.osm" \
    -c "buildings.xml" \
    --schema "buildings" \
    --prefix "buildings_"  \
    --tags \
    -d mumbai \
    -U user \
    -W user \
    --clean

Note

Depending on the osm2pgrouting version -W password is needed

Output:

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

***************************************************
           COMMAND LINE CONFIGURATION             *
***************************************************
Filename = mumbai.osm
Configuration file = buildings.xml
host = localhost
port = 5432
dbname = mumbai
username = user
schema= buildings
prefix = buildings_
suffix = 
Drop tables
Don't create indexes
Don't add OSM nodes
***************************************************
Testing database connection: mumbai
database connection successful: mumbai
Connecting to the database
connection success

Dropping tables...
TABLE: buildings.buildings_ways dropped ... OK.
TABLE: buildings.buildings_ways_vertices_pgr dropped ... OK.
TABLE: buildings.buildings_pointsofinterest dropped ... OK.
TABLE: buildings.configuration dropped ... OK.
TABLE: buildings.osm_nodes dropped ... OK.
TABLE: buildings.osm_ways dropped ... OK.
TABLE: buildings.osm_relations dropped ... OK.

Creating tables...
TABLE: buildings.buildings_ways_vertices_pgr created ... OK.
TABLE: buildings.buildings_ways created ... OK.
TABLE: buildings.buildings_pointsofinterest created ... OK.
TABLE: buildings.configuration created ... OK.
Opening configuration file: buildings.xml
    Parsing configuration

Exporting configuration ...
  - Done 
Counting lines ...
  - Done 
Opening data file: mumbai.osm	total lines: 24438
    Parsing data


End Of file


    Finish Parsing data

Adding auxiliary tables to database...

Export Ways ...
    Processing 1309 ways:

[**************************************************|] (100%) Total processed: 1309	 Vertices inserted: 1063	Split ways inserted 1066

Creating indexes ...

Processing Points of Interest ...
#########################
size of streets: 1309
Execution started at: Mon Dec 16 18:21:10 2024
Execution ended at:   Mon Dec 16 18:21:11 2024
Elapsed time: 0.526 Seconds.
User CPU time: -> 0.250535 seconds
#########################

To connect to the database, type the following in the terminal.

psql mumbai

1.3. Bangladesh database

Now download the data for an area in Bangladesh by following the same steps like that of Mumbai.

1.3.1. Create Bangladesh area database compatible with pgRouting

Use the following command to create bangladesh database

createdb bangladesh

To connect to the database do the following

psql bangladesh

After connecting to the database, first step is to create EXTENSION to enable pgRouting and PostGIS in the database. Then add the SCHEMA for each table.

-- add PostGIS extension
CREATE EXTENSION postgis;

-- add pgRouting extension
CREATE EXTENSION pgrouting;
CREATE EXTENSION hstore;
-- creating schemas for data
CREATE SCHEMA waterways;

1.3.2. Get the Bangladesh Data

1.3.2.1. Downloading Bangladesh data from OSGeo

The following command is used to download the snapshot of the Bangladesh area data used in this workshop, using the download service of OSGeo.

Note

The Bangladesh data for this workshop depends on this snapshot.

wget -N --progress=dot:mega "https://download.osgeo.org/pgrouting/workshops/bangladesh.osm.bz2"
bunzip2 -f "bangladesh.osm.bz2"

1.3.2.2. Downloading Bangladesh data from OpenStreetMap

The following command is used to download the OSM data of the area in Munshigang, Bangladesh.

CITY="bangladesh"
BBOX="88.9515,22.2192,89.3806,22.4310"
wget --progress=dot:mega -O "$CITY.osm" "http://www.overpass-api.de/api/xapi?*[bbox=][@meta]"

osmconvert --drop-author --drop-version bangladesh.osm -o=bangladesh_pass1.osm
osmfilter bangladesh_pass1.osm -o=bangladesh.osm --drop="highway= building="

1.3.3. Upload Bangladesh 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. See Appendix: osm2pgrouting Import Tool for additional information about osm2pgrouting.

For this step the following is used:

• waterways.xml configuration file

• ~/Desktop/workshop/bangladesh.osm - OSM data from the previous step

• bangladesh database

Contents of the configuration files are given in the Appendix. Create a XML file using these contents and save it into the root directory ~/Desktop/workshop.

Open a terminal window by ctrl-alt-t and move to the workshop directory by cd ~/Desktop/workshop.

1.3.3.1. Importing Bangladesh Waterways

The following osm2pgrouting command will be used to import the Waterways from OpenStreetMaps file to pgRouting database which we will use for further exercises.

osm2pgrouting \
    -f "bangladesh.osm" \
    -c "waterways.xml" \
    --schema "waterways" \
    --prefix "waterways_"  \
    --tags \
    -d bangladesh \
    -U user \
    -W user \
    --clean

Note

Depending on the osm2pgrouting version -W password is needed

Output:

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

***************************************************
           COMMAND LINE CONFIGURATION             *
***************************************************
Filename = bangladesh.osm
Configuration file = waterways.xml
host = localhost
port = 5432
dbname = bangladesh
username = user
schema= waterways
prefix = waterways_
suffix = 
Drop tables
Don't create indexes
Don't add OSM nodes
***************************************************
Testing database connection: bangladesh
database connection successful: bangladesh
Connecting to the database
connection success

Dropping tables...
TABLE: waterways.waterways_ways dropped ... OK.
TABLE: waterways.waterways_ways_vertices_pgr dropped ... OK.
TABLE: waterways.waterways_pointsofinterest dropped ... OK.
TABLE: waterways.configuration dropped ... OK.
TABLE: waterways.osm_nodes dropped ... OK.
TABLE: waterways.osm_ways dropped ... OK.
TABLE: waterways.osm_relations dropped ... OK.

Creating tables...
TABLE: waterways.waterways_ways_vertices_pgr created ... OK.
TABLE: waterways.waterways_ways created ... OK.
TABLE: waterways.waterways_pointsofinterest created ... OK.
TABLE: waterways.configuration created ... OK.
Opening configuration file: waterways.xml
    Parsing configuration

Exporting configuration ...
  - Done 
Counting lines ...
  - Done 
Opening data file: bangladesh.osm	total lines: 719061
    Parsing data


End Of file


    Finish Parsing data

Adding auxiliary tables to database...

Export Ways ...
    Processing 13256 ways:

[**************************************************|] (100%) Total processed: 13256	 Vertices inserted: 792	Split ways inserted 815

Creating indexes ...

Processing Points of Interest ...
#########################
size of streets: 13256
Execution started at: Mon Dec 16 18:21:12 2024
Execution ended at:   Mon Dec 16 18:21:13 2024
Elapsed time: 1.453 Seconds.
User CPU time: -> 1.12316 seconds
#########################

To connect to the database, type the following in the terminal.

psql bangladesh

1.4. Appendix

1.4.1. Configuration information for Buildings

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <tag_name name="building" id="1">
    <!-- Buildigs are grouped based on the population density in each category -->

    <!-- Negligible -->
    <tag_value name="terrace" id="1" />
    <tag_value name="shrine" id="2" />
    <tag_value name="service" id="3" />
    <tag_value name="transformer_tower" id="4" />
    <tag_value name="water_tower" id="5" />
    <tag_value name="military" id="6" />
    <tag_value name="ruins" id="7" />
    <tag_value name="tree_house" id="8" />
    <tag_value name="hangar" id="9" />
    <tag_value name="digester" id="10" />
    <tag_value name="barn" id="11" />
    <tag_value name="farm_auxiliary" id="12" />
    <tag_value name="slurry_tank" id="13" />
    <tag_value name="stable" id="14" />
    <tag_value name="sty" id="15" />
    <tag_value name="grandstand" id="16" />
    <tag_value name="pavilion" id="17" />
    <tag_value name="riding_hall" id="18" />
    <tag_value name="sports_hall" id="19" />
    <tag_value name="stadium" id="20" />
    <tag_value name="parking" id="21" />
    <tag_value name="greenhouse" id="22" />
    <tag_value name="kiosk" id="23" />
    <tag_value name="carport" id="24" />
    <tag_value name="garage" id="25" />
    <tag_value name="garages" id="26" />
    <tag_value name="container" id="27" />
    <tag_value name="roof" id="28" />
    <tag_value name="user defined" id="29" />
    <tag_value name="toilets" id="30" />
    <tag_value name="cowshed" id="31" />

    <!-- Very Sparse -->
    <tag_value name="farm" id="101" />
    <tag_value name="warehouse" id="102" />
    <tag_value name="conservatory" id="103" />
    <tag_value name="train_station" id="104" />
    <tag_value name="transportation" id="105" />
    <tag_value name="houseboat" id="106" />
    <tag_value name="industrial" id="107" />
    <tag_value name="temple" id="108" />
    <tag_value name="chapel" id="109" />
    <tag_value name="monastery" id="110" />
    <tag_value name="presbytery" id="111" />
    <tag_value name="religious" id="112" />
    <tag_value name="synagogue" id="113" />
    <tag_value name="cathedral" id="114" />
    <tag_value name="church" id="115" />
    <tag_value name="mosque" id="116" />
    <tag_value name="bakehouse" id="117" />
    <tag_value name="office" id="118" />
    <tag_value name="retail" id="119" />
    <tag_value name="public" id="120" />
    <tag_value name="kindergarten" id="121" />
    <tag_value name="school" id="122" />
    <tag_value name="government" id="123" />
    <tag_value name="commercial" id="124" />
    <tag_value name="civic" id="125" />
    <tag_value name="bridge" id="126" />

    <!-- Sparse -->
    <tag_value name="university" id="201" />
    <tag_value name="college" id="202" />

    <!-- Moderate -->
    <tag_value name="tent" id="301" />
    <tag_value name="cabin" id="302" />
    <tag_value name="detached" id="303" />
    <tag_value name="ger" id="304" />
    <tag_value name="semidetached_house" id="305" />
    <tag_value name="static_caravan" id="306" />
    <tag_value name="fire_station" id="307" />
    <tag_value name="hut" id="308" />
    <tag_value name="shed" id="309" />
    <tag_value name="bunker" id="310" />
    <tag_value name="construction" id="311" />
    <tag_value name="gatehouse" id="312" />
    <tag_value name="bungalow" id="313" />
    <tag_value name="hotel" id="314" />
    <tag_value name="house" id="315" />
    <tag_value name="dormitory" id="316" />
    <tag_value name="supermarket" id="317" />
    <tag_value name="hospital" id="318" />

  
    <!-- Dense -->
    <tag_value name="residential" id="401" />
    <tag_value name="yes" id="402" />


    <!-- Very Dense -->
    <tag_value name="apartments" id="501" />
  </tag_name>
 
</configuration>

1.4.2. Configuration information for Waterways

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <tag_name name="waterway" id="1">
    <tag_value name="river" id="101" />
    <tag_value name="riverbank" id="102" />
    <tag_value name="stream" id="103" />
    <tag_value name="tidal_channel" id="104" />
    <tag_value name="canal" id="105" />    
    <tag_value name="fairway" id="106" />
  </tag_name> 
</configuration>