Contrary to Carson Farmer I didn't compile pgRouting from source but installed it from the ubuntugis-unstable repository
sudo apt-get install postgres-8.4-pgrouting postgres-8.4-pgrouting-dd postgres-8.4-pgrouting-tspand followed the instruction from the mentioned blog.
The osm2pgrouting script creates all necessary tables including table "public.ways", which is probably the most important one. The table schema without indexes and constraints looks like:
Table "public.ways" Column | Type | Modifiers --------------+------------------+----------- gid | integer | not null class_id | integer | not null length | double precision | name | character(200) | x1 | double precision | y1 | double precision | x2 | double precision | y2 | double precision | reverse_cost | double precision | rule | text | to_cost | double precision | osm_id | integer | the_geom | geometry | source | integer | target | integer |The relevant columns for the routing are "length","reverse_cost" and "to_cost". The latter two are the forward and backward costs to use a way and are used in directed shortest path calculations. It's necessary to use an algorithm that can handle direction when one-ways have to be taken in account.
Since OpenStreetMap data are in geographic coordinates the length is in degree, but I prefer to have the length of a way in meter. Thankfully PostGIS provides ST_Distance_Spheroid to calculate lengths on the (earth) spheorid:
UPDATE ways SET length = ST_Length_Spheroid(the_geom,'SPHEROID["WGS 84",6378137,298.257223563]')::DOUBLE PRECISION;Next step is to filter the one-ways paths and set the "reverse_cost" to -1 to force pgRouting to choose these ways only in forward direction. Unfortunately only the name and the osm_id are imported from the OpenStreetMap raw data to the database. Thus I needed a list of osm_ids that represents one-ways. Since I also used a PostgreSQL database to store the GRASS attribute tables while working with OpenStreetMap data, I could easily get a comma-separated list of osm_ids with the following query:
psql -A -t -R ',' -d grass -U grass -c "SELECT osm_id FROM osm_net WHERE oneway = 'yes';" > oneways.sqlThen I updated the "reverse_cost" column:
UPDATE ways SET reverse_cost = -1 WHERE osm_id IN(55909972,55909973,28977387, ... ,102306365,27158132)Finally I could start with undirected and directed queries and compare the results.
-- Undirected shortest path query with astar SELECT * FROM astar_sp( 'ways',3365,3412) -- Directed shortest path query with astar SELECT * FROM astar_sp_directed( 'ways',3365,3412,true,true)Verify both calculated shortest paths from node 3365 to node 3412 in QGIS:
Until now I was considering only length and direction of a way. To get a more life-like model I wanted to take the road classes into account, too. I updated table "public.classes" and estimated for each road class an average speed in m/s in column "cost":
psql -A -d osm_routing -U openstreet -c "SELECT id,type_id,TRIM(trailing FROM name) AS name,cost FROM classes LIMIT 8;" id|type_id|name|cost 101|1|motorway|33.3333 102|1|motorway_link|27.7778 103|1|motorway_junction|27.7778 104|1|trunk|27.7778 105|1|trunk_link|27.7778 106|1|primary|13.8889 107|1|primary_link|13.8889 108|1|secondary|11.1111 (8 rows)With these assumed costs for each road class I updated the costs in table "public.ways" and divided the length by the average speed and got the time in seconds that is necessary to pass a certain way.
-- Update to_cost column UPDATE ways SET to_cost=(length/(SELECT "cost" FROM classes WHERE id=class_id))::DOUBLE PRECISION -- Update reverse_cost columns UPDATE ways SET reverse_cost=(length/(SELECT "cost" FROM classes WHERE id=class_id))::DOUBLE PRECISION WHERE reverse_cost <> -1;Now everything is set up and it needs nothing but another rainy weekend to compare pgRouting results with GRASS GIS results.
Last but not least I'd like to share yet another way how to display results of PostGIS queries without using an intermediate (Shape-)file, namely using the very versatile Virtual Format from the OGR library. Create a text file with the following lines and open it as a vector layer in QGIS or any other OGR backed application:
<OGRVRTDataSource> <OGRVRTLayer name="shortest_path"> <SrcDataSource> PG:dbname='osm_routing' user='openstreet' </SrcDataSource> <SrcSQL> SELECT * FROM astar_sp_directed( 'ways',3365,3414,true,true) </SrcSQL> </OGRVRTLayer> </OGRVRTDataSource>