<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=utf-8">
<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:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
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;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
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";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;
mso-fareast-language:EN-GB;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
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-size:10.0pt;}
@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:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Wow! Thanks for that, Regina.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I’ve downloaded the file and will apply it as soon as I can.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Most of my pgRouting work has been done on 2.1 so it will be good see how much has changed in 2.4.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Ross<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Pgrouting-users [mailto:pgrouting-users-bounces@lists.osgeo.org]
<b>On Behalf Of </b>Regina Obe<br>
<b>Sent:</b> 24 April 2017 15:36<br>
<b>To:</b> 'pgRouting users mailing list'<br>
<b>Subject:</b> Re: [pgrouting-users] best/fastest way to calculate thousands of trsp routes<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Ross,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I'm the one that builds the windows packages.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I just built binaries for PostgreSQL 9.2 pgRouting 2.4.1.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Can you give those a try?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><a href="http://winnie.postgis.net/download/windows/pg92/buildbot/">http://winnie.postgis.net/download/windows/pg92/buildbot/</a><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Should work fine with your PostGIS 2.2.2<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">If you have trouble upgrading between 2.1 and 2.4, you can drop the pgRouting 2.1 extension and reinstall the pgRouting 2.4 extension.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Let me know if you run into any issues.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Thanks,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Regina<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><a href="http://www.postgis.us">http://www.postgis.us</a><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">PostGIS PSC Member<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><a href="http://postgis.net">http://postgis.net</a><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:36.0pt"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri","sans-serif""> Pgrouting-users [<a href="mailto:pgrouting-users-bounces@lists.osgeo.org">mailto:pgrouting-users-bounces@lists.osgeo.org</a>]
<b>On Behalf Of </b>McDonaldR<br>
<b>Sent:</b> Monday, April 24, 2017 5:00 AM<br>
<b>To:</b> pgRouting users mailing list <<a href="mailto:pgrouting-users@lists.osgeo.org">pgrouting-users@lists.osgeo.org</a>><br>
<b>Subject:</b> Re: [pgrouting-users] best/fastest way to calculate thousands of trsp routes<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Hi Vicky<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I have plans to move to the latest pgRouting. We are busy planning our upgrade from PostgreSQL 9.2 to 9.6 which will then let us
upgrade all our extensions to the latest and greatest. We’re running everything in a Windows Server environment so I have to rely on prebuilt x64 binaries for them all.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">I follow all the pgRouting updates and really appreciate all the work you put into it. Certainly, all my customers appreciate the
results that are produced and come back for more.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Thanks again<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Ross<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> Pgrouting-users [</span><span lang="EN-US"><a href="mailto:pgrouting-users-bounces@lists.osgeo.org"><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">mailto:pgrouting-users-bounces@lists.osgeo.org</span></a></span><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">]
<b>On Behalf Of </b>Vicky Vergara<br>
<b>Sent:</b> 20 April 2017 17:39<br>
<b>To:</b> pgRouting users mailing list<br>
<b>Subject:</b> Re: [pgrouting-users] best/fastest way to calculate thousands of trsp routes<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:12.0pt;margin-left:36.0pt">
<span style="font-family:"Arial","sans-serif"">Hello Ross<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:12.0pt;margin-left:36.0pt">
<span style="font-family:"Arial","sans-serif"">I want to encourage you to use the latest version of pgRouting:<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif"">Here starts the compilation on 2.1<br>
</span><span lang="EN-US"><a href="https://travis-ci.org/cvvergara/pgrouting/jobs/224015091#L1905"><span lang="EN-GB" style="font-family:"Arial","sans-serif"">https://travis-ci.org/cvvergara/pgrouting/jobs/224015091#L1905</span></a></span><span style="font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:12.0pt;margin-left:36.0pt">
<span style="font-family:"Arial","sans-serif"">Here starts the compilation of the latest 2.4<br>
</span><span lang="EN-US"><a href="https://travis-ci.org/pgRouting/pgrouting/jobs/214667949#L766"><span lang="EN-GB" style="font-family:"Arial","sans-serif"">https://travis-ci.org/pgRouting/pgrouting/jobs/214667949#L766</span></a></span><span style="font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif"">I must say that in particular pgr_TRSP still has compilation problems (besides other issues) but thanks to some pull requests done by the community, in the 2.4 version
it no longer crashes the server.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif""><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif"">A lot of work has being done rewriting the functions: removing the warnings in linux, that are errors in other systems, and fixing other per function related issues.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:12.0pt;margin-left:36.0pt">
<span style="font-family:"Arial","sans-serif""><br>
In 2.4 version some of the pgr_TRSP function related issues has being hidden by using wrappers to other functions that don't have turn restrictions. (mainly because the turn restriction query is optional), but I see that you are going to use restrictions, so
you will still be using the original code.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif"">I made a comparison of using the original code (the one in version 2.1) and using the wrapped pgr_TRSP, I must say this time I didn't review my comments so maybe
they are outdated, but is automatically generated, so the results are the current results:<br>
</span><span lang="EN-US"><a href="https://github.com/pgRouting/pgrouting/tree/master/src/trsp/doc"><span lang="EN-GB" style="font-family:"Arial","sans-serif"">https://github.com/pgRouting/pgrouting/tree/master/src/trsp/doc</span></a></span><span style="font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif"">That file uses the sample data:<br>
</span><span lang="EN-US"><a href="http://docs.pgrouting.org/2.4/en/sampledata.html"><span lang="EN-GB" style="font-family:"Arial","sans-serif"">http://docs.pgrouting.org/2.4/en/sampledata.html</span></a></span><span style="font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif""><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif"">Instructions to get the latest version:<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif""></span><span lang="EN-US"><a href="https://github.com/pgRouting/pgrouting/wiki/Notes-on-Download%2C-Installation-and-building-pgRouting"><span lang="EN-GB" style="font-family:"Arial","sans-serif"">https://github.com/pgRouting/pgrouting/wiki/Notes-on-Download%2C-Installation-and-building-pgRouting</span></a></span><span style="font-family:"Arial","sans-serif""><o:p></o:p></span></p>
</div>
<p class="MsoNormal" style="margin-left:36.0pt"><o:p> </o:p></p>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif"">Regards<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif"">Vicky<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif""><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-family:"Arial","sans-serif""><o:p> </o:p></span></p>
</div>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><o:p> </o:p></p>
<div>
<p class="MsoNormal" style="margin-left:36.0pt">On Thu, Apr 20, 2017 at 10:49 AM, Stephen Woodbridge <<span lang="EN-US"><a href="mailto:woodbri@swoodbridge.com" target="_blank"><span lang="EN-GB">woodbri@swoodbridge.com</span></a></span>> wrote:<o:p></o:p></p>
<p class="MsoNormal" style="margin-left:36.0pt">On 4/20/2017 6:49 AM, McDonaldR wrote:<o:p></o:p></p>
<p class="MsoNormal" style="margin-left:36.0pt">Hi List,<br>
<br>
I’m running pgRouting 2.1 with PostGIS 2.2.2 on PostgreSQL 9.2.20.<br>
<br>
I have 16,000 pupils going to 57 schools. I have a network with turn restrictions. I have a function (below) that loops through all the pupils and calculates a TRSP route and updates the pupil table with the route cost. I have some questions too.<br>
<br>
1.It’s going to take ~ 2 hours to process all the pupils. Will it make things faster if I created, say, 4 functions that each processed a different set of pupils? I have a multicore server and memory to spare. I have to run this for time and distance and
also for all the pupils assigned an “optimally located” school. What’s the best/fastest way to do this?<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:12.0pt;margin-left:36.0pt">
<br>
Yes, this sounds reasonable.<o:p></o:p></p>
<p class="MsoNormal" style="margin-left:36.0pt">2.I pre-processed the pupil data to assign each pupil the nearest node on the network. Some pupils in rural areas are a mile from the nearest node and are assigned the node at the end furthest from the school.
How would I use the pgr_trsp with edges and edge position to create a position for each pupil at a point on the nearest edge?<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:12.0pt;margin-left:36.0pt">
<br>
Here is a function that might be useful, or you might modify it for your specific needs. tol is maximum search distance in degrees, you can estimate this degrees = meters/111120.0. It returns the edge_id, pos on the that edge and these can be used to args to
trsp.<br>
<br>
-Steve W<br>
<br>
create or replace function pointToEdgePos(edge_table text, lon float8, lat float8, tol float8, OUT edge_id integer, OUT pos float8)<br>
returns record as<br>
$body$<br>
-- ASSUMPTIONS<br>
-- * edge_table as columns "gid" and "geom"<br>
-- * edge_able.geom uses srid=4326<br>
declare<br>
rr record;<br>
pct float8;<br>
pnt geometry;<br>
geom geometry;<br>
<br>
begin<br>
-- create a point from lon, lat<br>
pnt := st_setsrid(st_makepoint(lon,lat), 4326);<br>
<br>
-- set the error condition<br>
edge_id := null;<br>
pos := -1.0;<br>
<br>
-- find the closest edge within tol distance<br>
execute 'select * from ' || _pgr_quote_ident(edge_table) ||<br>
' where st_dwithin(''' || pnt::text ||<br>
'''::geometry, geom, ' || tol || ') order by st_distance(''' || pnt::text || '''::geometry, geom) asc limit 1' into rr;<br>
<br>
if rr.geom is not null then<br>
-- deal with MULTILINESTRINGS<br>
geom := rr.geom;<br>
if geometrytype(geom)='MULTILINESTRING' THEN<br>
geom := ST_GeometryN(geom, 1);<br>
end if;<br>
<br>
-- project the point onto the linestring<br>
pos := st_line_locate_point(geom, pnt);<br>
edge_id := rr.gid;<br>
end if;<br>
<br>
end;<br>
$body$<br>
language plpgsql stable;<o:p></o:p></p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:12.0pt;margin-left:36.0pt">
CREATE OR REPLACE FUNCTION public.pupil_journeys()<br>
<br>
RETURNS character varying AS<br>
<br>
$BODY$<br>
<br>
DECLARE<br>
<br>
cur_pupil refcursor;<br>
<br>
v_pid integer;<br>
<br>
v_pnn integer;<br>
<br>
v_snn integer;<br>
<br>
v_cost double precision;<br>
<br>
v_sql varchar(1000);<br>
<br>
BEGIN<br>
<br>
RAISE NOTICE 'Processing pupil journeys...';<br>
<br>
OPEN cur_pupil FOR EXECUTE format('SELECT pupilid, pupil_nn, school_nn FROM edn_pupilschoolroute WHERE pupil_NN IS NOT NULL');<br>
<br>
LOOP<br>
<br>
FETCH cur_pupil INTO v_pid, v_pnn, v_snn;<br>
<br>
EXIT WHEN NOT FOUND;<br>
<br>
SELECT SUM(cost) AS v_cost FROM pgr_trsp('<br>
<br>
SELECT ogc_fid AS id,<br>
<br>
source::integer,<br>
<br>
target::integer,<br>
<br>
cost_len::double precision AS cost,<br>
<br>
rcost_len::double precision AS reverse_cost<br>
<br>
FROM hw_roadlink'::text,<br>
<br>
v_pnn, --pupil_nn gets inserted here<br>
<br>
v_snn, --school_nn gets inserted here<br>
<br>
true,<br>
<br>
true,<br>
<br>
'select to_cost, teid as target_id, feid||coalesce('',''||via,'''') as via_path from hw_nt_restrictions'::text) INTO v_cost;<br>
<br>
-- insert route cost into pupil data table<br>
<br>
v_sql:='UPDATE edn_pupilschoolroute SET p_dist = ' || v_cost || ' WHERE pupil_nn = ' || v_pnn || ' AND school_nn = ' || v_snn || ' AND pupilid = ' || v_pid;<br>
<br>
EXECUTE v_sql;<br>
<br>
END LOOP;<br>
<br>
RETURN ‘Well, that worked!';<br>
<br>
CLOSE cur_pupil;<br>
<br>
END;<br>
<br>
$BODY$<br>
<br>
LANGUAGE plpgsql VOLATILE<br>
<br>
COST 100;<br>
<br>
Thanks in advance<br>
<br>
Ross<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:12.0pt;margin-left:36.0pt">
*Ross McDonald | *GIS Data Coordinator | Angus Council | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT*| t: 01307 476419*<br>
<br>
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.<br>
<br>
<br>
<br>
_______________________________________________<br>
Pgrouting-users mailing list<br>
<span lang="EN-US"><a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank"><span lang="EN-GB">Pgrouting-users@lists.osgeo.org</span></a></span><br>
<span lang="EN-US"><a href="https://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank"><span lang="EN-GB">https://lists.osgeo.org/mailman/listinfo/pgrouting-users</span></a></span><o:p></o:p></p>
<p class="MsoNormal" style="margin-left:36.0pt"><br>
<br>
---<br>
This email has been checked for viruses by Avast antivirus software.<br>
<span lang="EN-US"><a href="https://www.avast.com/antivirus" target="_blank"><span lang="EN-GB">https://www.avast.com/antivirus</span></a></span><br>
<br>
_______________________________________________<br>
Pgrouting-users mailing list<br>
<span lang="EN-US"><a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank"><span lang="EN-GB">Pgrouting-users@lists.osgeo.org</span></a></span><br>
<span lang="EN-US"><a href="https://lists.osgeo.org/mailman/listinfo/pgrouting-users" target="_blank"><span lang="EN-GB">https://lists.osgeo.org/mailman/listinfo/pgrouting-users</span></a></span><o:p></o:p></p>
</div>
<p class="MsoNormal" style="margin-left:36.0pt"><br>
<br clear="all">
<br>
-- <o:p></o:p></p>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<div>
<pre style="margin-left:36.0pt">Georepublic UG (haftungsbeschränkt)<o:p></o:p></pre>
<pre style="margin-left:36.0pt">Salzmannstraße 44, <o:p></o:p></pre>
<pre style="margin-left:36.0pt">81739 München, Germany<o:p></o:p></pre>
<pre style="margin-left:36.0pt"><o:p> </o:p></pre>
<pre style="margin-left:36.0pt">Vicky Vergara<o:p></o:p></pre>
<pre style="margin-left:36.0pt">Operations Research<o:p></o:p></pre>
<pre style="margin-left:36.0pt"><o:p> </o:p></pre>
<pre style="margin-left:36.0pt">eMail: vicky@<span lang="EN-US"><a href="http://georepublic.de" target="_blank"><span lang="EN-GB">georepublic.de</span></a></span><o:p></o:p></pre>
<pre style="margin-left:36.0pt">Web: <span lang="EN-US"><a href="https://georepublic.info" target="_blank"><span lang="EN-GB">https://georepublic.info</span></a></span><o:p></o:p></pre>
<pre style="margin-left:36.0pt"><o:p> </o:p></pre>
<pre style="margin-left:36.0pt">Tel: +49 (089) 4161 7698-1<o:p></o:p></pre>
<pre style="margin-left:36.0pt">Fax: +49 (089) 4161 7698-9<o:p></o:p></pre>
<pre style="margin-left:36.0pt"><o:p> </o:p></pre>
<pre style="margin-left:36.0pt">Commercial register: Amtsgericht München, HRB 181428<o:p></o:p></pre>
<pre style="margin-left:36.0pt">CEO: Daniel Kastl<o:p></o:p></pre>
<pre style="margin-left:36.0pt"><o:p> </o:p></pre>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:36.0pt"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">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.
</span><o:p></o:p></p>
</div>
</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>