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.
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 "https://download.osgeo.org/pgrouting/workshops/$CITY.osm.bz2"
3bunzip2 -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.xmlandmumbai_roads.xmlconfiguration files for osm2pgrouting.~/Desktop/workshop/mumbai.osm- OSM data from the previous stepmumbaidatabase.
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/bin/osm2pgrouting \
2 -f "mumbai.osm" \
3 -c "/usr/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: Sat Mar 30 17:58:36 2024
2
3***************************************************
4 COMMAND LINE CONFIGURATION *
5***************************************************
6Filename = mumbai.osm
7Configuration file = /usr/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/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: Sat Mar 30 17:58:36 2024
67Execution ended at: Sat Mar 30 17:58:36 2024
68Elapsed time: 0.486 Seconds.
69User CPU time: -> 0.244089 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/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: Sat Mar 30 17:58:35 2024
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: Sat Mar 30 17:58:35 2024
67Execution ended at: Sat Mar 30 17:58:36 2024
68Elapsed time: 0.595 Seconds.
69User CPU time: -> 0.263859 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 "https://download.osgeo.org/pgrouting/workshops/$CITY.osm.bz2"
3bunzip2 -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="
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.xmlconfiguration file~/Desktop/workshop/bangladesh.osm- OSM data from the previous stepbangladeshdatabase
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: Sat Mar 30 17:58:37 2024
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: Sat Mar 30 17:58:37 2024
67Execution ended at: Sat Mar 30 17:58:38 2024
68Elapsed time: 1.435 Seconds.
69User CPU time: -> 1.11104 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>