To be able to use pgRouting, data has to be imported into a database.
Chapter Contents
pgRouting is installed as extension. This requires:
These requirements are met on OSGeoLive, then open a terminal window ctrl-alt-t and
follow the instructions.
注釈
If OSGeo Live is not being used.
OSGeo Live’s account name on the database is "user". To easily use the workshop when not using
OSGeo Live this extra steps are needed:
Workshop FOSS4G Dar Es Salaam# 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
注釈
Depending on the postgres configureation -U <user> is needed on psql commands
# Create the database
createdb city_routing
# login as user "user"
psql city_routing
-- add PostGIS functions
CREATE EXTENSION postgis;
-- add pgRouting functions
CREATE EXTENSION pgrouting;
-- Inspect the pgRouting installation
\dx+ pgrouting
-- View pgRouting version
SELECT pgr_version();
-- exit psql
\q
The pgRouting workshop will make use of OpenStreetMap data, which is already
available on OSGeo Live. This workshop will use the
Dar Es Salaam city data and is a snapshot of August-2018.
mkdir ~/Desktop/workshop
cd ~/Desktop/workshop
OSGeo Live comes with osm data from the city of Dar Es Salaam.
CITY="DS_TZ"
bzcat ~/data/osm/$CITY.osm.bz2 > $CITY.osm
The exact same data can be found on the OSGeo Live download page.
CITY="DS_TZ"
wget -N --progress=dot:mega \
"http://download.osgeo.org/livedvd/data/osm/$CITY/$CITY.osm.bz2"
bunzip2 $CITY.osm.bz2
The following downloads the latest OSM data on using the same area. Using this data in the workshop can generate variations on the results, due to changes since Jun-2017.
CITY="DS_TZ"
BBOX="39.251,-6.8275,39.302,-6.792"
wget --progress=dot:mega -O "$CITY.osm" "http://www.overpass-api.de/api/xapi?*[bbox=${BBOX}][@meta]"
More information how to download OpenStreetMap information can be found in http://wiki.openstreetmap.org/wiki/Downloading_data
An alternative for very large areas is to use the download services of Geofabrik.
The next step is to run osm2pgrouting converter, which is a command line
tool that inserts your 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:
mapconfig.xml configuration file is used~/Desktop/workshop/DS_TS.osm data.city_routing databaseFrom a terminal window ctrl-alt-t.
cd ~/Desktop/workshop
osm2pgrouting \
-f DS_TZ.osm \
-d city_routing \
-U user
注釈
Depending on the osm2pgrouting version -W password is needed
Output:
Execution starts at: Tue Sep 25 02:15:40 2018
***************************************************
COMMAND LINE CONFIGURATION *
***************************************************
Filename = DS_TZ.osm
Configuration file = /usr/share/osm2pgrouting/mapconfig.xml
host = localhost
port = 5432
dbname = city_routing
username = user
password =
schema=
prefix =
suffix =
Don't 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
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: DS_TZ.osm total lines: 724406
Parsing data
End Of file
Finish Parsing data
Adding auxiliary tables to database...
Export Ways ...
Processing 33232 ways:
[******************************| ] (60%) Total processed: 20000 Vertices inserted: 2774 Split ways inserted 3284
[**************************************************|] (100%) Total processed: 33232 Vertices inserted: 1188 Split ways inserted 2366
Creating indexes ...
Processing Points of Interest ...
#########################
size of streets: 33232
Execution started at: Tue Sep 25 02:15:40 2018
Execution ended at: Tue Sep 25 02:15:44 2018
Elapsed time: 4.06 Seconds.
User CPU time: -> 2.41657 seconds
#########################
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 | user
public | geometry_columns | view | user
public | pointsofinterest | table | user
public | pointsofinterest_pid_seq | sequence | user
public | raster_columns | view | user
public | raster_overviews | view | user
public | spatial_ref_sys | table | user
public | ways | table | user
public | ways_gid_seq | sequence | user
public | ways_vertices_pgr | table | user
public | ways_vertices_pgr_id_seq | sequence | user
(13 rows)