Hi List
I have been doing some work getting the pgRouting workshop tutorial working with Ordnance Survey’s ITN dataset and OpenLayers3 using British National Grid (EPSG:27700). I have successfully got it working using pgr_dijkstra but I would like it to take into account the turn restriction information that comes with ITN using the pgr_trsp function.
This query returns the correct result from the network:
SELECT gid,geometry
FROM pgr_trsp(‘SELECT gid as id, source::integer, target::integer, cost_len::float AS cost, rcost_len::float AS reverse_cost FROM itn_network’::text,
31365,31360, true, true, ‘SELECT to_cost, teid AS target_id, feid||COALESCE(’‘,’‘||via,’‘’‘) AS via_path FROM routing.itn_turn_restrictions’::text) AS route
JOIN itn_network ON itn_network.gid = route.id2
ORDER BY seq;
Adding this query to my function replacing the pgr_dijkstra function from http://workshop.pgrouting.org/chapters/wrapper.html#simplified-input-parameters-and-geometry-output makes my function look like this (if the formatting goes then you can see it here https://gist.github.com/mixedbredie/ee3abdb247be6840b400):
– Function: routing.pgr_trsp_rcost(character varying, integer, integer)
– DROP FUNCTION routing.pgr_trsp_rcost(character varying, integer, integer);
CREATE OR REPLACE FUNCTION routing.pgr_trsp_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_trsp(’‘SELECT gid as id, source::integer, target::integer, cost_len::float AS cost, rcost_len::float AS reverse_cost FROM ’ || quote_ident(tbl) || ‘’’::text’ || ‘,’
source::integer || ‘,’
target::integer || ‘, true, true, ‘‘SELECT to_cost, teid AS target_id, feid||COALESCE(’’’,‘’‘||via,’‘’‘) AS via_path FROM itn_turn_restrictions’'::text) AS route JOIN ’
quote_ident(tbl) || ’ ON itn_network.gid = route.id2 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_trsp_rcost(character varying, integer, integer)
OWNER TO postgisadmin;
COMMENT ON FUNCTION routing.pgr_trsp_rcost(character varying, integer, integer) IS ‘OL3 turn restricted shortest path with reverse cost’;
Running the function returns the following error:
SELECT * FROM pgr_trsp_rcost(‘itn_network’,31365,31360);
ERROR: query "SELECT ‘SELECT gid,geometry FROM pgr_trsp(’‘SELECT gid as id, source::integer, target::integer, cost_len::float AS cost, rcost_len::float AS reverse_cost FROM ’ || quote_ident(tbl) || ‘’’::text’ || ‘,’
source::integer || ‘,’
target::integer || ‘, true, true, ‘‘SELECT to_cost, teid AS target_id, feid||COALESCE(’’’,‘’‘||via,’‘’‘) AS via_path FROM itn_turn_restrictions’'::text) AS route JOIN ’
quote_ident(tbl) || ’ ON itn_network.gid = route.id2 ORDER BY seq’" returned 2 columns
SQL state: 42601
Context: PL/pgSQL function pgr_trsp_rcost(character varying,integer,integer) line 7 at assignment
Carrying on with the workshop and updating this wrapper example (http://workshop.pgrouting.org/chapters/wrapper.html#route-between-lat-lon-points-and-return-ordered-geometry-with-heading) to look like this (https://gist.github.com/mixedbredie/4f7dac382d07afaf3599) I get a similar error when using the function:
SELECT * FROM pgr_fromatob_trsp(‘itn_network’,325000,725000,350000,750000);
ERROR: query "SELECT 'SELECT gid, geometry, cost, source, target, ST_Reverse(geometry) AS flip_geom FROM
pgr_trsp(''SELECT gid as id, source::int, target::int,
cost_len::float AS cost, rcost_len::float AS reverse_cost FROM ’ || quote_ident(tbl) || ‘’,‘’ || source || ‘’,‘’ || target || ‘’,‘’ ||
true,
true,
‘SELECT to_cost, teid AS target_id, feid||COALESCE(’‘,’‘||via,’‘’‘) AS via_path FROM routing.itn_turn_restrictions’‘),’ ||
quote_ident(tbl) ||
‘WHERE gid = id2 ORDER BY seq’" returned 6 columns
SQL state: 42601
Context: PL/pgSQL function pgr_fromatob_trsp(character varying,double precision,double precision,double precision,double precision) line 23 at assignment
Error 42601 is a syntax error
I am expecting 2 columns to be returned from the first query and the function will return 3 columns.
The second function returns 6 columns and my query returns 6 columns
Can anyone see where I am going wrong?
Thanks in advance!
Ross
Ross McDonald | GIS Data Coordinator | Resources Department, IT Division | Angus Council, Angus House, Orchardbank Business Park, Forfar, DD8 1AT
T: 01307 476419 | F: 01307 476401 | E: mcdonaldr@angus.gov.uk