In my last post I covered the steps needed to set up a PostGIS database on Mac OS X together with pgRouting. Now, pgRouting solves my geospatial routing problems quite well, but I have run into a rather annoying problem: pgRouting returns the edges as they are stored in the database and not as traversed, so on some edges the direction (from-to or to-from) needs to be flipped around.
The following QGIS screenshot illustrates the problem (click for bigger version):
You can see clearly that the line arrows do not all point in the same direction.
But don’t despair! My SQL skills are admittedly a bit rusty, but I managed to write a PL/pgSQL script that fixes the problem.
-- Wrapper for shortest_path() function to find the shortest route between two points CREATE OR REPLACE FUNCTION dijkstra( geom_table varchar, source int4, target int4) RETURNS SETOF GEOMS AS $$ DECLARE r record; path_result record; v_id integer; e_id integer; geom geoms; id integer; BEGIN id := 0; FOR path_result IN EXECUTE 'SELECT gid,the_geom FROM ' || 'shortest_path(''SELECT gid AS id, start_id::integer AS source, end_id::integer AS target, ' || 'length(the_geom)::double precision AS cost FROM ' || quote_ident(geom_table) || ''', ' || quote_literal(source) || ' , ' || quote_literal(target) || ' , false, false), ' || quote_ident(geom_table) || ' WHERE edge_id = gid ' LOOP geom.gid := path_result.gid; geom.the_geom := path_result.the_geom; id := id + 1; geom.id := id; RETURN NEXT geom; END LOOP; RETURN; END; $$ LANGUAGE 'plpgsql' VOLATILE STRICT; -- Returns records for the route from start to end with correct directions CREATE OR REPLACE FUNCTION calc_route( geom_table varchar, start_id int, end_id int) RETURNS SETOF record AS $$ DECLARE r record; id int; prev int; i int; BEGIN prev := 0; id := start_id; FOR r IN EXECUTE 'SELECT start_id, end_id, route.* ' || 'FROM ' || quote_ident(geom_table) || ' JOIN ' || '(SELECT * FROM dijkstra(' || quote_literal(geom_table) || ',' || start_id || ', ' || end_id || ')) AS route ' || 'ON ' || quote_ident(geom_table) || '.gid = route.gid ORDER BY route.id; ' LOOP IF (r.start_id = id AND r.end_id <> prev) THEN RETURN NEXT r; ELSIF (r.end_id = id AND r.start_id <> prev) THEN i := r.end_id; r.end_id := r.start_id; r.start_id := i; r.the_geom := ST_Reverse(r.the_geom); RETURN NEXT r; ELSE RAISE NOTICE 'error: record % % %', r.start_id, r.end_id, r.id; RETURN; END IF; prev := r.start_id; id := r.end_id; END LOOP; RETURN; END; $$ LANGUAGE 'plpgsql' VOLATILE STRICT;
[SyntaxHL]DROP TABLE IF EXISTS result;
SELECT start_id, end_id, gid, the_geom INTO result FROM calc_route(‘network’, 22353, 21587)
AS (start_id int, end_id int, id int, gid int, the_geom geometry);[/SyntaxHL]
As a result, here is a QGIS screenshot with all segments in the correct direction (again, click for bigger version)
A note about line 59: this script assumes that pgRouting returns the segments in the correct order, which means that either the condition from line 50 or from line 52 evaluates to true. So, line 59 should never be executed. However, some people have reported on the pgrouting-users mailing list that the segments returned from pgRouting are not in the right order – in which case you would see the message from line 59.