1. Data for Sustainable Development Goals

../_images/prepareData.png

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

 1-- add PostGIS extension
 2CREATE EXTENSION postgis;
 3
 4-- add pgRouting extension
 5CREATE EXTENSION pgrouting;
 6
 7-- creating schemas for data
 8CREATE SCHEMA roads;
 9CREATE SCHEMA buildings;
10CREATE 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.

1CITY="mumbai"
2wget -N --progress=dot:mega \
3"https://download.osgeo.org/pgrouting/workshops/$CITY.osm.bz2"
4bunzip2 -f "$CITY.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.

1CITY="mumbai"
2BBOX="72.8263,19.1021,72.8379,19.1166"
3wget --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.

 1/usr/local/bin/osm2pgrouting \
 2    -f "mumbai.osm" \
 3    -c "/usr/local/share/osm2pgrouting/mapconfig.xml" \
 4    --schema "roads" \
 5    -d mumbai \
 6    -U user \
 7    -W user \
 8    --prefix "roads_" \
 9    --tags \
10    --clean

Note

Depending on the osm2pgrouting version -W password is needed

Output:

 1Execution starts at: Mon Mar 13 13:39:34 2023
 2
 3***************************************************
 4           COMMAND LINE CONFIGURATION             *
 5***************************************************
 6Filename = mumbai.osm
 7Configuration file = /usr/local/share/osm2pgrouting/mapconfig.xml
 8host = localhost
 9port = 5432
10dbname = mumbai
11username = user
12schema= roads
13prefix = roads_
14suffix = 
15Drop tables
16Don't create indexes
17Don't add OSM nodes
18***************************************************
19Testing database connection: mumbai
20database connection successful: mumbai
21Connecting to the database
22connection success
23
24Dropping tables...
25TABLE: roads.roads_ways dropped ... OK.
26TABLE: roads.roads_ways_vertices_pgr dropped ... OK.
27TABLE: roads.roads_pointsofinterest dropped ... OK.
28TABLE: roads.configuration dropped ... OK.
29TABLE: roads.osm_nodes dropped ... OK.
30TABLE: roads.osm_ways dropped ... OK.
31TABLE: roads.osm_relations dropped ... OK.
32
33Creating tables...
34TABLE: roads.roads_ways_vertices_pgr created ... OK.
35TABLE: roads.roads_ways created ... OK.
36TABLE: roads.roads_pointsofinterest created ... OK.
37TABLE: roads.configuration created ... OK.
38Opening configuration file: /usr/local/share/osm2pgrouting/mapconfig.xml
39    Parsing configuration
40
41Exporting configuration ...
42  - Done 
43Counting lines ...
44  - Done 
45Opening data file: mumbai.osm	total lines: 24438
46    Parsing data
47
48
49End Of file
50
51
52    Finish Parsing data
53
54Adding auxiliary tables to database...
55
56Export Ways ...
57    Processing 1309 ways:
58
59[**************************************************|] (100%) Total processed: 1309	 Vertices inserted: 378	Split ways inserted 558
60
61Creating indexes ...
62
63Processing Points of Interest ...
64#########################
65size of streets: 1309
66Execution started at: Mon Mar 13 13:39:34 2023
67Execution ended at:   Mon Mar 13 13:39:34 2023
68Elapsed time: 0.626 Seconds.
69User CPU time: -> 0.27766 seconds
70#########################

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.

 1/usr/local/bin/osm2pgrouting \
 2    -f "mumbai.osm" \
 3    -c "buildings.xml" \
 4    --schema "buildings" \
 5    --prefix "buildings_"  \
 6    --tags \
 7    -d mumbai \
 8    -U user \
 9    -W user \
10    --clean

Note

Depending on the osm2pgrouting version -W password is needed

Output:

 1Execution starts at: Mon Mar 13 13:39:33 2023
 2
 3***************************************************
 4           COMMAND LINE CONFIGURATION             *
 5***************************************************
 6Filename = mumbai.osm
 7Configuration file = buildings.xml
 8host = localhost
 9port = 5432
10dbname = mumbai
11username = user
12schema= buildings
13prefix = buildings_
14suffix = 
15Drop tables
16Don't create indexes
17Don't add OSM nodes
18***************************************************
19Testing database connection: mumbai
20database connection successful: mumbai
21Connecting to the database
22connection success
23
24Dropping tables...
25TABLE: buildings.buildings_ways dropped ... OK.
26TABLE: buildings.buildings_ways_vertices_pgr dropped ... OK.
27TABLE: buildings.buildings_pointsofinterest dropped ... OK.
28TABLE: buildings.configuration dropped ... OK.
29TABLE: buildings.osm_nodes dropped ... OK.
30TABLE: buildings.osm_ways dropped ... OK.
31TABLE: buildings.osm_relations dropped ... OK.
32
33Creating tables...
34TABLE: buildings.buildings_ways_vertices_pgr created ... OK.
35TABLE: buildings.buildings_ways created ... OK.
36TABLE: buildings.buildings_pointsofinterest created ... OK.
37TABLE: buildings.configuration created ... OK.
38Opening configuration file: buildings.xml
39    Parsing configuration
40
41Exporting configuration ...
42  - Done 
43Counting lines ...
44  - Done 
45Opening data file: mumbai.osm	total lines: 24438
46    Parsing data
47
48
49End Of file
50
51
52    Finish Parsing data
53
54Adding auxiliary tables to database...
55
56Export Ways ...
57    Processing 1309 ways:
58
59[**************************************************|] (100%) Total processed: 1309	 Vertices inserted: 1063	Split ways inserted 1066
60
61Creating indexes ...
62
63Processing Points of Interest ...
64#########################
65size of streets: 1309
66Execution started at: Mon Mar 13 13:39:33 2023
67Execution ended at:   Mon Mar 13 13:39:34 2023
68Elapsed time: 0.669 Seconds.
69User CPU time: -> 0.312429 seconds
70#########################

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.

1-- add PostGIS extension
2CREATE EXTENSION postgis;
3
4-- add pgRouting extension
5CREATE EXTENSION pgrouting;
6CREATE EXTENSION hstore;
7-- creating schemas for data
8CREATE 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.

1CITY="bangladesh"
2wget -N --progress=dot:mega \
3  "https://download.osgeo.org/pgrouting/workshops/$CITY.osm.bz2"
4bunzip2 -f "$CITY.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.

1CITY="bangladesh"
2BBOX="88.9515,22.2192,89.3806,22.4310"
3wget --progress=dot:mega -O "$CITY.osm" "http://www.overpass-api.de/api/xapi?*[bbox=][@meta]"
4
5osmconvert --drop-author --drop-version bangladesh.osm -o=bangladesh_pass1.osm
6osmfilter bangladesh_pass1.osm -o=bangladesh.osm --drop="highway= building="

See Option 3) Download using Overpass XAPI

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.

 1osm2pgrouting \
 2    -f "bangladesh.osm" \
 3    -c "waterways.xml" \
 4    --schema "waterways" \
 5    --prefix "waterways_"  \
 6    --tags \
 7    -d bangladesh \
 8    -U user \
 9    -W user \
10    --clean

Note

Depending on the osm2pgrouting version -W password is needed

Output:

 1Execution starts at: Mon Mar 13 13:39:35 2023
 2
 3***************************************************
 4           COMMAND LINE CONFIGURATION             *
 5***************************************************
 6Filename = bangladesh.osm
 7Configuration file = waterways.xml
 8host = localhost
 9port = 5432
10dbname = bangladesh
11username = user
12schema= waterways
13prefix = waterways_
14suffix = 
15Drop tables
16Don't create indexes
17Don't add OSM nodes
18***************************************************
19Testing database connection: bangladesh
20database connection successful: bangladesh
21Connecting to the database
22connection success
23
24Dropping tables...
25TABLE: waterways.waterways_ways dropped ... OK.
26TABLE: waterways.waterways_ways_vertices_pgr dropped ... OK.
27TABLE: waterways.waterways_pointsofinterest dropped ... OK.
28TABLE: waterways.configuration dropped ... OK.
29TABLE: waterways.osm_nodes dropped ... OK.
30TABLE: waterways.osm_ways dropped ... OK.
31TABLE: waterways.osm_relations dropped ... OK.
32
33Creating tables...
34TABLE: waterways.waterways_ways_vertices_pgr created ... OK.
35TABLE: waterways.waterways_ways created ... OK.
36TABLE: waterways.waterways_pointsofinterest created ... OK.
37TABLE: waterways.configuration created ... OK.
38Opening configuration file: waterways.xml
39    Parsing configuration
40
41Exporting configuration ...
42  - Done 
43Counting lines ...
44  - Done 
45Opening data file: bangladesh.osm	total lines: 719061
46    Parsing data
47
48
49End Of file
50
51
52    Finish Parsing data
53
54Adding auxiliary tables to database...
55
56Export Ways ...
57    Processing 13256 ways:
58
59[**************************************************|] (100%) Total processed: 13256	 Vertices inserted: 792	Split ways inserted 815
60
61Creating indexes ...
62
63Processing Points of Interest ...
64#########################
65size of streets: 13256
66Execution started at: Mon Mar 13 13:39:35 2023
67Execution ended at:   Mon Mar 13 13:39:37 2023
68Elapsed time: 2.426 Seconds.
69User CPU time: -> 1.99282 seconds
70#########################

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

psql bangladesh

1.4. Appendix

1.4.1. Configuration information for Buildings

  1<?xml version="1.0" encoding="UTF-8"?>
  2<configuration>
  3  <tag_name name="building" id="1">
  4    <!-- Buildigs are grouped based on the population density in each category -->
  5
  6    <!-- Negligible -->
  7    <tag_value name="terrace" id="1" />
  8    <tag_value name="shrine" id="2" />
  9    <tag_value name="service" id="3" />
 10    <tag_value name="transformer_tower" id="4" />
 11    <tag_value name="water_tower" id="5" />
 12    <tag_value name="military" id="6" />
 13    <tag_value name="ruins" id="7" />
 14    <tag_value name="tree_house" id="8" />
 15    <tag_value name="hangar" id="9" />
 16    <tag_value name="digester" id="10" />
 17    <tag_value name="barn" id="11" />
 18    <tag_value name="farm_auxiliary" id="12" />
 19    <tag_value name="slurry_tank" id="13" />
 20    <tag_value name="stable" id="14" />
 21    <tag_value name="sty" id="15" />
 22    <tag_value name="grandstand" id="16" />
 23    <tag_value name="pavilion" id="17" />
 24    <tag_value name="riding_hall" id="18" />
 25    <tag_value name="sports_hall" id="19" />
 26    <tag_value name="stadium" id="20" />
 27    <tag_value name="parking" id="21" />
 28    <tag_value name="greenhouse" id="22" />
 29    <tag_value name="kiosk" id="23" />
 30    <tag_value name="carport" id="24" />
 31    <tag_value name="garage" id="25" />
 32    <tag_value name="garages" id="26" />
 33    <tag_value name="container" id="27" />
 34    <tag_value name="roof" id="28" />
 35    <tag_value name="user defined" id="29" />
 36    <tag_value name="toilets" id="30" />
 37    <tag_value name="cowshed" id="31" />
 38
 39    <!-- Very Sparse -->
 40    <tag_value name="farm" id="101" />
 41    <tag_value name="warehouse" id="102" />
 42    <tag_value name="conservatory" id="103" />
 43    <tag_value name="train_station" id="104" />
 44    <tag_value name="transportation" id="105" />
 45    <tag_value name="houseboat" id="106" />
 46    <tag_value name="industrial" id="107" />
 47    <tag_value name="temple" id="108" />
 48    <tag_value name="chapel" id="109" />
 49    <tag_value name="monastery" id="110" />
 50    <tag_value name="presbytery" id="111" />
 51    <tag_value name="religious" id="112" />
 52    <tag_value name="synagogue" id="113" />
 53    <tag_value name="cathedral" id="114" />
 54    <tag_value name="church" id="115" />
 55    <tag_value name="mosque" id="116" />
 56    <tag_value name="bakehouse" id="117" />
 57    <tag_value name="office" id="118" />
 58    <tag_value name="retail" id="119" />
 59    <tag_value name="public" id="120" />
 60    <tag_value name="kindergarten" id="121" />
 61    <tag_value name="school" id="122" />
 62    <tag_value name="government" id="123" />
 63    <tag_value name="commercial" id="124" />
 64    <tag_value name="civic" id="125" />
 65    <tag_value name="bridge" id="126" />
 66
 67    <!-- Sparse -->
 68    <tag_value name="university" id="201" />
 69    <tag_value name="college" id="202" />
 70
 71    <!-- Moderate -->
 72    <tag_value name="tent" id="301" />
 73    <tag_value name="cabin" id="302" />
 74    <tag_value name="detached" id="303" />
 75    <tag_value name="ger" id="304" />
 76    <tag_value name="semidetached_house" id="305" />
 77    <tag_value name="static_caravan" id="306" />
 78    <tag_value name="fire_station" id="307" />
 79    <tag_value name="hut" id="308" />
 80    <tag_value name="shed" id="309" />
 81    <tag_value name="bunker" id="310" />
 82    <tag_value name="construction" id="311" />
 83    <tag_value name="gatehouse" id="312" />
 84    <tag_value name="bungalow" id="313" />
 85    <tag_value name="hotel" id="314" />
 86    <tag_value name="house" id="315" />
 87    <tag_value name="dormitory" id="316" />
 88    <tag_value name="supermarket" id="317" />
 89    <tag_value name="hospital" id="318" />
 90
 91  
 92    <!-- Dense -->
 93    <tag_value name="residential" id="401" />
 94    <tag_value name="yes" id="402" />
 95
 96
 97    <!-- Very Dense -->
 98    <tag_value name="apartments" id="501" />
 99  </tag_name>
100 
101</configuration>

1.4.2. Configuration information for Waterways

 1<?xml version="1.0" encoding="UTF-8"?>
 2<configuration>
 3  <tag_name name="waterway" id="1">
 4    <tag_value name="river" id="101" />
 5    <tag_value name="riverbank" id="102" />
 6    <tag_value name="stream" id="103" />
 7    <tag_value name="tidal_channel" id="104" />
 8    <tag_value name="canal" id="105" />    
 9    <tag_value name="fairway" id="106" />
10  </tag_name> 
11</configuration>