osm2pgrouting is a convenient tool, and its focus to work on OpenStreetMap data. There are several cases where osm2pgrouting can’t be used. Some network data already comes with a network topology that can be used with pgRouting out-of-the-box. Often network data is stored in Shape file format (.shp) and we can use PostGIS’ shape2postgresql converter to import the data into a PostgreSQL database.
But what to do then?
In this chapter you will learn how to create a basic Routing Network Topology from a network data that does not have a routing Topology create the minimum attributes needed the Routing Network Topology.
At first we will load OpenStreetMap sample data with osm2pgsql.
CITY="BONN_DE" cd ~/Desktop/workshop cp ~/data/osm/$CITY.osm.bz2 . createdb -U user osm_data psql -U user -d osm_data -c "CREATE EXTENSION postgis;" psql -U user -d osm_data -c "CREATE EXTENSION pgrouting;" osm2pgsql -U user -c -d osm_data --latlong --cache 5 --cache-strategy sparse $CITY.osm.bz2
Let’s see which tables have been created:
Run: psql -U user -d osm_data -c "\d"
The table containing the road network data has the name planet_osm_roads. It consists of large amount of attributes.
Run: psql -U user -d osm_data -c "\d planet_osm_roads"
It is common that road network data provides at least the following information:
This allows to display the road network as a PostGIS layer in GIS software, for example in QGIS. Though it is not sufficient for routing, because it doesn’t contain network topology information.
The next steps will use the PostgreSQL command line tool.
psql -U user osm_data
Having your data imported into a PostgreSQL database might require one more step for pgRouting.
Make sure that your data provides a correct Routing Network Topology, which consists of information about source and target identifiers for each road link. The results above, show that the network topology does not have any source and target information.
Creation of the Routing Network Topology is necessary.
PostGIS topology is not suitable for Routing.
pgRouting provides a general way for creating the Routing Network Topology with the pgr_createTopology function.
pgr_createTopology('<table>', <tolerance>, '<geometry column>', '<gid>')
For additional information see pgr_createTopology.
First add source and target column, then run the pgr_createTopology function ... and wait.
The dimension of the tolerance parameter depends on your data projection. Usually it’s either “degrees” or “meters”. In our example the geometry data projection to determine the tolerance:
SELECT find_srid('public','planet_osm_roads','way'); find_srid ----------- 4326 (1 row)
Based on this result the tolerance will be 0.00001
-- Add "source" and "target" column ALTER TABLE planet_osm_roads ADD COLUMN "source" integer; ALTER TABLE planet_osm_roads ADD COLUMN "target" integer; -- Run topology function SELECT pgr_createTopology('planet_osm_roads', 0.00001, 'way', 'osm_id');
To verify that there is a basic Routing Network Topology:
Also a new table containing the vertices information was created:
Now we are ready for our first routing query with Dijkstra algorithm!
Analyzing the topology with pgr_analyzeGraph:
SELECT pgr_analyzeGraph('planet_osm_roads', 0.000001, the_geom := 'way', id := 'osm_id');
Adjusting the topology is not an easy task:
Depending on the application some adjustments need to be made.
Some topology manipulation functions help to detect and fix some of the topological errors in the data.