<div dir="ltr"><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">He is using ordinace surveay, he said so in this twit<br></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Apr 25, 2017 at 12:36 PM, Stephen Woodbridge <span dir="ltr"><<a href="mailto:woodbri@swoodbridge.com" target="_blank">woodbri@swoodbridge.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi Ross,<br>
<br>
Out of curiosity, what data are you using? Ordinance Survey? or OSM or something else.<br>
<br>
We hope to have a GSoC student do some work on the TRSP code. Ideally, we would like to migrate that code to use boost graph and to bring it in alignment with the other Dijkstra functions.<br>
<br>
Most real world routing application need support for turn restriction, OSM has been adding them but it will be a while before they are well represented in most areas and that leaves data commercial data sets as the only source for routable data that contains turn restrictions. I pretty sure that Ordinance Survey has them as does Nokia HERE.<br>
<br>
Thanks for sharing you stored procedure, I'm sure others can learn a lot from it.<br>
<br>
Best regards,<br>
-Steve<div class="HOEnZb"><div class="h5"><br>
<br>
On 4/25/2017 12:15 PM, McDonaldR wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hi Steve<br>
<br>
Thanks for the pointers - I've got it working now. It's much slower than node to node (about 0.8s per route compared to about 0.3s per route) but a more accurate result.<br>
<br>
The function FWIW:<br>
<br>
CREATE OR REPLACE FUNCTION corporate.pupil_journeys_route<wbr>s2()<br>
RETURNS character varying AS<br>
$BODY$<br>
DECLARE<br>
<br>
cur_pupil refcursor;<br>
v_pid integer;<br>
v_seid integer;<br>
v_spos float8;<br>
v_teid integer;<br>
v_tpos float8;<br>
v_geom geometry;<br>
v_sql varchar(1000);<br>
<br>
BEGIN<br>
OPEN cur_pupil FOR EXECUTE format('SELECT pupilid, source_eid, source_pos, target_eid, target_pos FROM edn_pupilschoolroute WHERE pupil_nn IS NOT NULL LIMIT 1000');<br>
LOOP<br>
FETCH cur_pupil INTO v_pid, v_seid, v_spos, v_teid, v_tpos;<br>
EXIT WHEN NOT FOUND;<br>
RAISE NOTICE 'Processing pupil % journey...', v_pid;<br>
WITH results AS (<br>
SELECT d.seq, d.id1 AS _node, d.id2 AS _edge, cost, r.centrelinegeometry AS geometry FROM pgr_trsp('<br>
SELECT ogc_fid AS id,<br>
source::integer,<br>
target::integer,<br>
cost_len::double precision AS cost,<br>
rcost_len::double precision AS reverse_cost<br>
FROM hw_roadlink'::text, -- the trsp route<br>
v_seid, --pupil source edge id<br>
v_spos, --source edge position<br>
v_teid, --school target edge id<br>
v_tpos, --target edge position<br>
true, --directed<br>
true, --reverse costs<br>
'select to_cost, teid as target_id, feid||coalesce('',''||via,''''<wbr>) as via_path from hw_nt_restrictions'::text) AS d<br>
INNER JOIN hw_roadlink r ON d.id2 = r.ogc_fid<br>
ORDER BY d.seq),<br>
max_seq AS (<br>
SELECT max(seq) AS max_seq FROM results<br>
),<br>
network AS<br>
(<br>
SELECT ogc_fid AS id, source, target FROM hw_roadlink a, results WHERE a.ogc_fid = results._edge<br>
),<br>
path_geom AS<br>
(<br>
(SELECT ST_LineSubstring(geometry,v_sp<wbr>os,1.0) AS geometry -- source edge and first row<br>
FROM results, network<br>
WHERE seq = (SELECT min(seq) FROM results)<br>
AND network.target = (SELECT _node FROM results WHERE seq = 1)<br>
AND <a href="http://network.id" rel="noreferrer" target="_blank">network.id</a> = (SELECT _edge FROM results WHERE seq = 0)<br>
UNION<br>
SELECT ST_LineSubstring(ST_Reverse(ge<wbr>ometry),v_spos,1.0) AS geometry -- source edge and first row<br>
FROM results, network<br>
WHERE seq = (SELECT min(seq) FROM results)<br>
AND network.source = (SELECT _node FROM results WHERE seq = 1)<br>
AND <a href="http://network.id" rel="noreferrer" target="_blank">network.id</a> = (SELECT _edge FROM results WHERE seq = 0)<br>
)<br>
UNION<br>
(SELECT ST_LineSubstring(geometry,0.0,<wbr>1.0 - v_tpos) AS geometry -- target edge and last row<br>
FROM results, network<br>
WHERE seq = (SELECT max(seq) FROM results)<br>
AND network.source = (SELECT _node FROM results, max_seq WHERE seq = max_seq)<br>
AND <a href="http://network.id" rel="noreferrer" target="_blank">network.id</a> = (SELECT _edge FROM results, max_seq WHERE seq = max_seq)<br>
UNION<br>
SELECT ST_LineSubstring(ST_Reverse(ge<wbr>ometry),0.0,1.0 - v_tpos) AS geometry -- target edge and last row<br>
FROM results, network<br>
WHERE seq = (SELECT max(seq) FROM results)<br>
AND network.target = (SELECT _node FROM results, max_seq WHERE seq = max_seq)<br>
AND <a href="http://network.id" rel="noreferrer" target="_blank">network.id</a> = (SELECT _edge FROM results, max_seq WHERE seq = max_seq)<br>
)<br>
UNION<br>
SELECT geometry -- intermediate edges and rows<br>
FROM results WHERE seq NOT IN ((SELECT max(seq) FROM results),(SELECT min(seq) FROM results))<br>
)<br>
SELECT ST_Multi(ST_Union(geometry)) AS geometry FROM path_geom INTO v_geom;<br>
<br>
-- insert route cost into pupil data table<br>
EXECUTE format('INSERT INTO %s(pupilid,geometry) VALUES ($1,$2)','corporate.edn_pupilr<wbr>outes2')<br>
USING v_pid, v_geom;<br>
<br>
END LOOP;<br>
RETURN 'Oooh, get in!';<br>
CLOSE cur_pupil;<br>
END;<br>
$BODY$<br>
LANGUAGE plpgsql VOLATILE<br>
COST 100;<br>
<br>
<br>
<br>
<br>
<br>
<br>
-----Original Message-----<br>
From: Pgrouting-users [mailto:<a href="mailto:pgrouting-users-bounces@lists.osgeo.org" target="_blank">pgrouting-users-bounce<wbr>s@lists.osgeo.org</a>] On Behalf Of Stephen Woodbridge<br>
Sent: 24 April 2017 15:23<br>
To: <a href="mailto:pgrouting-users@lists.osgeo.org" target="_blank">pgrouting-users@lists.osgeo.or<wbr>g</a><br>
Subject: Re: [pgrouting-users] best/fastest way to calculate thousands of trsp routes<br>
<br>
On 4/24/2017 4:55 AM, McDonaldR wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Thanks Steve<br>
<br>
The function to get the source and target points on to the nearest<br>
edges gives a much better result than using nearest node. Now I just<br>
need to work out how to get the geometry of the route from these<br>
projected points.<br>
</blockquote>
<br>
For the first and last edgeid in the result, use the linear referencing functions to split that edge and keep the part you need instead of the whole thing. the "pos" can be used as the split location on the edge.<br>
<br>
-Steve<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Thanks very much for the help<br>
<br>
Ross<br>
<br>
-----Original Message----- From: Pgrouting-users<br>
[mailto:<a href="mailto:pgrouting-users-bounces@lists.osgeo.org" target="_blank">pgrouting-users-bounce<wbr>s@lists.osgeo.org</a>] On Behalf Of Stephen<br>
Woodbridge Sent: 20 April 2017 16:50 To:<br>
<a href="mailto:pgrouting-users@lists.osgeo.org" target="_blank">pgrouting-users@lists.osgeo.or<wbr>g</a> Subject: Re: [pgrouting-users]<br>
best/fastest way to calculate thousands of trsp routes<br>
<br>
On 4/20/2017 6:49 AM, McDonaldR wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
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<br>
restrictions. I have a function (below) that loops through all the<br>
pupils and calculates a TRSP route and updates the pupil table with<br>
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<br>
make things faster if I created, say, 4 functions that each processed<br>
a different set of pupils? I have a multicore server and memory to<br>
spare. I have to run this for time and distance and also for all the<br>
pupils assigned an “optimally located” school. What’s the<br>
best/fastest way to do this?<br>
</blockquote>
<br>
Yes, this sounds reasonable.<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
2.I pre-processed the pupil data to assign each pupil the nearest<br>
node on the network. Some pupils in rural areas are a mile from the<br>
nearest node and are assigned the node at the end furthest from the<br>
school. How would I use the pgr_trsp with edges and edge position to<br>
create a position for each pupil at a point on the nearest edge?<br>
</blockquote>
<br>
Here is a function that might be useful, or you might modify it for<br>
your specific needs. tol is maximum search distance in degrees, you<br>
can estimate this degrees = meters/111120.0. It returns the edge_id,<br>
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,<br>
lat float8, tol float8, OUT edge_id integer, OUT pos float8) returns<br>
record as $body$ -- ASSUMPTIONS -- * edge_table as columns "gid" and<br>
"geom" -- * edge_able.geom uses srid=4326 declare rr record; pct<br>
float8; pnt geometry; geom geometry;<br>
<br>
begin -- create a point from lon, lat pnt :=<br>
st_setsrid(st_makepoint(lon,la<wbr>t), 4326);<br>
<br>
-- set the error condition edge_id := null; pos := -1.0;<br>
<br>
-- find the closest edge within tol distance execute 'select * from '<br>
|| _pgr_quote_ident(edge_table) || ' where st_dwithin(''' ||<br>
pnt::text || '''::geometry, geom, ' || tol || ') order by<br>
st_distance(''' || pnt::text || '''::geometry, geom) asc limit 1'<br>
into rr;<br>
<br>
if rr.geom is not null then -- deal with MULTILINESTRINGS geom :=<br>
rr.geom; if geometrytype(geom)='MULTILINES<wbr>TRING' THEN geom :=<br>
ST_GeometryN(geom, 1); end if;<br>
<br>
-- project the point onto the linestring pos :=<br>
st_line_locate_point(geom, pnt); edge_id := rr.gid; end if;<br>
<br>
end; $body$ language plpgsql stable;<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
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,<br>
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,''''<wbr>)<br>
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 || '<br>
WHERE pupil_nn = ' || v_pnn || ' AND school_nn = ' || v_snn || '<br>
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<br>
<br>
*Ross McDonald | *GIS Data Coordinator | Angus Council | Angus House,<br>
Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT*| t:<br>
01307 476419*<br>
<br>
This message is strictly confidential. If you have received this in<br>
error, please inform the sender and remove it from your system.<br>
If received in error you may not copy, print, forward or use it or<br>
any attachment in any way. This message is not capable of creating a<br>
legal contract or a binding representation and does not represent the<br>
views of Angus Council. Emails may be monitored for security and<br>
network management reasons.Messages containing inappropriate content<br>
may be intercepted. Angus Council does not accept any liability for<br>
any harm that may be caused to the recipient system or data on it by<br>
this message or any attachment.<br>
<br>
<br>
<br>
______________________________<wbr>_________________ Pgrouting-users<br>
mailing list <a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.or<wbr>g</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/pgrouting-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailma<wbr>n/listinfo/pgrouting-users</a><br>
<br>
</blockquote>
<br>
<br>
--- This email has been checked for viruses by Avast antivirus<br>
software. <a href="https://www.avast.com/antivirus" rel="noreferrer" target="_blank">https://www.avast.com/antiviru<wbr>s</a><br>
<br>
______________________________<wbr>_________________ Pgrouting-users<br>
mailing list <a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.or<wbr>g</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/pgrouting-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailma<wbr>n/listinfo/pgrouting-users</a><br>
<br>
This message is strictly confidential. If you have received this in<br>
error, please inform the sender and remove it from your system. If<br>
received in error you may not copy, print, forward or use it or any<br>
attachment in any way. This message is not capable of creating a legal<br>
contract or a binding representation and does not represent the views<br>
of Angus Council. Emails may be monitored for security and network<br>
management reasons. Messages containing inappropriate content may be<br>
intercepted. Angus Council does not accept any liability for any harm<br>
that may be caused to the recipient system or data on it by this<br>
message or any attachment.<br>
______________________________<wbr>_________________ Pgrouting-users<br>
mailing list <a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.or<wbr>g</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/pgrouting-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailma<wbr>n/listinfo/pgrouting-users</a><br>
<br>
</blockquote>
<br>
<br>
---<br>
This email has been checked for viruses by Avast antivirus software.<br>
<a href="https://www.avast.com/antivirus" rel="noreferrer" target="_blank">https://www.avast.com/antiviru<wbr>s</a><br>
<br>
______________________________<wbr>_________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.or<wbr>g</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/pgrouting-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailma<wbr>n/listinfo/pgrouting-users</a><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>
______________________________<wbr>_________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.or<wbr>g</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/pgrouting-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailma<wbr>n/listinfo/pgrouting-users</a><br>
<br>
</blockquote>
<br>
<br>
---<br>
This email has been checked for viruses by Avast antivirus software.<br>
<a href="https://www.avast.com/antivirus" rel="noreferrer" target="_blank">https://www.avast.com/antiviru<wbr>s</a><br>
<br>
______________________________<wbr>_________________<br>
Pgrouting-users mailing list<br>
<a href="mailto:Pgrouting-users@lists.osgeo.org" target="_blank">Pgrouting-users@lists.osgeo.or<wbr>g</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/pgrouting-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailma<wbr>n/listinfo/pgrouting-users</a></div></div></blockquote></div><br><br clear="all"><br>-- <br><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><pre>Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44,
81739 München, Germany
Vicky Vergara
Operations Research
eMail: vicky@<a href="http://georepublic.de" target="_blank">georepublic.de</a>
Web: <a href="https://georepublic.info" target="_blank">https://georepublic.info</a>
Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9
Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl
<span></span></pre></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div>
</div>