[pgrouting-users] pgr_trsp wrapper errors

[McDonaldR]

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 at angus.gov.uk<mailto:mcdonaldr at angus.gov.uk>



This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20150302/c2c07c40/attachment-0001.html>