The previous post looked at implementing the basic pgRouting workshop OpenLayers3 examples in EPSG:27700. This post adds reverse costs to the mix to take advantage of the road routing information (RRI) supplied with Ordnance Survey's ITN dataset.

The changes to the two functions I created are minor - I simply add in the reverse cost field to the pgr_dijkstra function and change the two boolean operators ("use directed graph?" and "use reverse costs?") from "false" to "true".

First function:

``````-- Function: routing.pgr_dijkstra_rcost(character varying, integer, integer)
-- DROP FUNCTION routing.pgr_dijkstra_rcost(character varying, integer, integer);

CREATE OR REPLACE FUNCTION routing.pgr_dijkstra_rcost(IN tbl character varying, IN source integer, IN target integer, OUT seq integer, OUT gid integer, OUT geom geometry)
RETURNS SETOF record AS
\$BODY\$
DECLARE
sql     text;
rec     record;
BEGIN
seq     := 0;
sql     := 'SELECT gid,geometry FROM ' ||
'pgr_dijkstra(''SELECT gid as id, source::int, target::int, '
|| 'cost_time::float AS cost, rcost_time::float AS reverse_cost FROM '
|| quote_ident(tbl) || ''', '
|| quote_literal(source) || ', '
|| quote_literal(target) || ' , true, true), '
|| quote_ident(tbl) || ' WHERE id2 = gid ORDER BY seq';

FOR rec IN EXECUTE sql
LOOP
seq     := seq + 1;
gid     := rec.gid;
geom    := rec.geometry;
RETURN NEXT;
END LOOP;
RETURN;
END;
\$BODY\$
LANGUAGE plpgsql VOLATILE STRICT
COST 100
ROWS 1000;
ALTER FUNCTION routing.pgr_dijkstra_rcost(character varying, integer, integer)
OWNER TO postgres;
COMMENT ON FUNCTION routing.pgr_dijkstra_rcost(character varying, integer, integer) IS 'OL3 shortest path with reverse cost';
``````

Second function:

``````-- Function: routing.pgr_fromatob_rcost(character varying, double precision, double precision, double precision, double precision)
-- DROP FUNCTION routing.pgr_fromatob_rcost(character varying, double precision, double precision, double precision, double precision);

CREATE OR REPLACE FUNCTION routing.pgr_fromatob_rcost(IN tbl character varying, IN x1 double precision, IN y1 double precision, IN x2 double precision, IN y2 double precision, OUT seq integer, OUT gid integer, OUT name text, OUT heading double precision, OUT cost double precision, OUT geom geometry)
RETURNS SETOF record AS
\$BODY\$
DECLARE
sql     text;
rec     record;
source	integer;
target	integer;
point	integer;

BEGIN
-- Find nearest node
EXECUTE 'SELECT id::integer FROM itn_network_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x1 || ' ' || y1 || ')'',27700) LIMIT 1' INTO rec;
source := rec.id;

EXECUTE 'SELECT id::integer FROM itn_network_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x2 || ' ' || y2 || ')'',27700) LIMIT 1' INTO rec;
target := rec.id;

-- Shortest path query (TODO: limit extent by BBOX)
seq := 0;
sql := 'SELECT gid, geometry, cost, source, target,
ST_Reverse(geometry) AS flip_geom FROM ' ||
'pgr_dijkstra(''SELECT gid as id, source::int, target::int, '
|| 'cost_time::float AS cost, rcost_time::float AS reverse_cost FROM '
|| quote_ident(tbl) || ''', '
|| source || ', ' || target
|| ' , true, true), '
|| quote_ident(tbl) || ' WHERE id2 = gid ORDER BY seq';

-- Remember start point
point := source;

FOR rec IN EXECUTE sql
LOOP
-- Flip geometry (if required)
IF ( point != rec.source ) THEN
rec.geometry := rec.flip_geom;
point := rec.source;
ELSE
point := rec.target;
END IF;

EXECUTE 'SELECT degrees( ST_Azimuth(
ST_StartPoint(''' || rec.geometry::text || '''),
ST_EndPoint(''' || rec.geometry::text || ''') ) )'

-- Return record
seq     := seq + 1;
gid     := rec.gid;
--name    := rec.name;
cost    := rec.cost;
geom    := rec.geometry;
RETURN NEXT;
END LOOP;
RETURN;
END;
\$BODY\$
LANGUAGE plpgsql VOLATILE STRICT
COST 100
ROWS 1000;
ALTER FUNCTION routing.pgr_fromatob_rcost(character varying, double precision, double precision, double precision, double precision)
OWNER TO postgres;
``````

If you now create a new layer in the same store we created in the last post (Ch. 9 of the pgRouting workshop) but change the SQL slightly you should get a different route from the same source and target nodes than from the function without reverse costs:

``````SELECT ST_MakeLine(route.geom) FROM (
SELECT geom FROM pgr_fromAtoB_rcost('itn_network', %x1%, %y1%, %x2%, %y2%
) ORDER BY seq) AS route``````