<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:"Century Gothic";
panose-1:2 11 5 2 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Balloon Text Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";
mso-fareast-language:EN-US;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Century Gothic","sans-serif";
color:windowtext;
font-weight:normal;
font-style:normal;}
span.BalloonTextChar
{mso-style-name:"Balloon Text Char";
mso-style-priority:99;
mso-style-link:"Balloon Text";
font-family:"Tahoma","sans-serif";}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Hi List<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">This query returns the correct result from the network:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">SELECT gid,geometry
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">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,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">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
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">JOIN itn_network ON itn_network.gid = route.id2
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">ORDER BY seq;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Adding this query to my function replacing the pgr_dijkstra function from
<a href="http://workshop.pgrouting.org/chapters/wrapper.html#simplified-input-parameters-and-geometry-output">
http://workshop.pgrouting.org/chapters/wrapper.html#simplified-input-parameters-and-geometry-output</a> makes my function look like this (if the formatting goes then you can see it here
<a href="https://gist.github.com/mixedbredie/ee3abdb247be6840b400">https://gist.github.com/mixedbredie/ee3abdb247be6840b400</a>):
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">-- Function: routing.pgr_trsp_rcost(character varying, integer, integer)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">-- DROP FUNCTION routing.pgr_trsp_rcost(character varying, integer, integer);<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">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)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> RETURNS SETOF record AS<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">$BODY$<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">DECLARE<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> sql text;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> rec record;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">BEGIN<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> seq := 0;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> 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' || ',' <o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> || source::integer || ','<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> || 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 '<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> || quote_ident(tbl) || ' ON itn_network.gid = route.id2 ORDER BY seq';<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> FOR rec IN EXECUTE sql<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> LOOP<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> seq := seq + 1;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> gid := rec.gid;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> geom := rec.geometry;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> RETURN NEXT;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> END LOOP;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> RETURN;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">END;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">$BODY$<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> LANGUAGE plpgsql VOLATILE STRICT<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> COST 100<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> ROWS 1000;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">ALTER FUNCTION routing.pgr_trsp_rcost(character varying, integer, integer)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> OWNER TO postgisadmin;<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">COMMENT ON FUNCTION routing.pgr_trsp_rcost(character varying, integer, integer) IS 'OL3 turn restricted shortest path with reverse cost';<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Running the function returns the following error:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">SELECT * FROM pgr_trsp_rcost('itn_network',31365,31360);<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">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' || ',' <o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> || source::integer || ','<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> || 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 '<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> || quote_ident(tbl) || ' ON itn_network.gid = route.id2 ORDER BY seq'" returned 2 columns<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">SQL state: 42601<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Context: PL/pgSQL function pgr_trsp_rcost(character varying,integer,integer) line 7 at assignment<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Carrying on with the workshop and updating this wrapper example (<a href="http://workshop.pgrouting.org/chapters/wrapper.html#route-between-lat-lon-points-and-return-ordered-geometry-with-heading">http://workshop.pgrouting.org/chapters/wrapper.html#route-between-lat-lon-points-and-return-ordered-geometry-with-heading</a>)
to look like this (<a href="https://gist.github.com/mixedbredie/4f7dac382d07afaf3599">https://gist.github.com/mixedbredie/4f7dac382d07afaf3599</a>) I get a similar error when using the function:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">SELECT * FROM pgr_fromatob_trsp('itn_network',325000,725000,350000,750000);<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">ERROR: query "SELECT 'SELECT gid, geometry, cost, source, target, ST_Reverse(geometry) AS flip_geom FROM
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> pgr_trsp(''SELECT gid as id, source::int, target::int,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> cost_len::float AS cost, rcost_len::float AS reverse_cost FROM ' || quote_ident(tbl) || '','' || source || '','' || target || '','' ||
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> true,
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> true,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> 'SELECT to_cost, teid AS target_id, feid||COALESCE('',''||via,'''') AS via_path FROM routing.itn_turn_restrictions''),' ||<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> quote_ident(tbl) ||<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""> 'WHERE gid = id2 ORDER BY seq'" returned 6 columns<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">SQL state: 42601<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Context: PL/pgSQL function pgr_fromatob_trsp(character varying,double precision,double precision,double precision,double precision) line 23 at assignment<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Error 42601 is a syntax error<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">I am expecting 2 columns to be returned from the first query and the function will return 3 columns.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">The second function returns 6 columns and my query returns 6 columns<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Can anyone see where I am going wrong?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Thanks in advance!<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif"">Ross<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif""><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228;mso-fareast-language:EN-GB">Ross McDonald
</span></b><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228;mso-fareast-language:EN-GB">|<b>
</b>GIS Data Coordinator | Resources Department, IT Division | Angus Council, Angus House, Orchardbank Business Park, Forfar, DD8 1AT<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228;mso-fareast-language:EN-GB">T:
<b>01307 476419</b> | F: 01307 476401 | E: <a href="mailto:mcdonaldr@angus.gov.uk">
<span style="color:#4F6228">mcdonaldr@angus.gov.uk</span></a> <b><o:p></o:p></b></span></p>
<p class="MsoNormal" align="center" style="text-align:center"><span style="font-size:13.0pt;font-family:"Century Gothic","sans-serif";color:#4F6228;mso-fareast-language:EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div> </div>
<div>
<p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt"><span style="FONT-SIZE: 7.5pt; FONT-FAMILY: 'Arial','sans-serif'; mso-bidi-font-size: 8.0pt; mso-bidi-font-family: 'Times New Roman'"><font size="2">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.<span style="mso-spacerun: yes">
</span>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.
</font></span></p>
</div>
</body>
</html>