# [pgrouting-users] best/fastest way to calculate thousands of trsp routes

McDonaldR McDonaldR at angus.gov.uk
Thu Apr 20 03:49:46 PDT 2017

```Hi List,

I'm running pgRouting 2.1 with PostGIS 2.2.2 on PostgreSQL 9.2.20.

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.

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?

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?

CREATE OR REPLACE FUNCTION public.pupil_journeys()
RETURNS character varying AS
\$BODY\$
DECLARE

cur_pupil refcursor;
v_pid integer;
v_pnn integer;
v_snn integer;
v_cost double precision;
v_sql varchar(1000);

BEGIN
RAISE NOTICE 'Processing pupil journeys...';
OPEN cur_pupil FOR EXECUTE format('SELECT pupilid, pupil_nn, school_nn FROM edn_pupilschoolroute WHERE pupil_NN IS NOT NULL');
LOOP
FETCH cur_pupil INTO v_pid, v_pnn, v_snn;
SELECT SUM(cost) AS v_cost FROM pgr_trsp('
SELECT ogc_fid AS id,
source::integer,
target::integer,
cost_len::double precision AS cost,
rcost_len::double precision AS reverse_cost
v_pnn, --pupil_nn gets inserted here
v_snn, --school_nn gets inserted here
true,
true,
'select to_cost, teid as target_id, feid||coalesce('',''||via,'''') as via_path from hw_nt_restrictions'::text) INTO v_cost;
-- insert route cost into pupil data table
v_sql:='UPDATE edn_pupilschoolroute SET p_dist = ' || v_cost || ' WHERE pupil_nn = ' || v_pnn || ' AND school_nn = ' || v_snn || ' AND pupilid = ' || v_pid;
EXECUTE v_sql;
END LOOP;
RETURN 'Well, that worked!';
CLOSE cur_pupil;
END;
\$BODY\$
LANGUAGE plpgsql VOLATILE
COST 100;

Ross

Ross McDonald | GIS Data Coordinator | Angus Council | Angus House, Orchardbank Business Park, Sylvie Way, Forfar DD8 1AT | t: 01307 476419

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/20170420/cde4d24f/attachment.html>
```