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

[Stephen Woodbridge]

On 4/20/2017 6:49 AM, McDonaldR wrote:
> 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?

Yes, this sounds reasonable.

> 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?

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.

-Steve W

create or replace function pointToEdgePos(edge_table text, lon float8, 
lat float8, tol float8, OUT edge_id integer, OUT pos float8)
     returns record as
$body$
-- ASSUMPTIONS
-- * edge_table as columns "gid" and "geom"
-- * edge_able.geom uses srid=4326
declare
     rr record;
     pct float8;
     pnt geometry;
     geom geometry;

begin
     -- create a point from lon, lat
     pnt := st_setsrid(st_makepoint(lon,lat), 4326);

     -- set the error condition
     edge_id := null;
     pos := -1.0;

     -- find the closest edge within tol distance
     execute 'select * from ' || _pgr_quote_ident(edge_table) ||
             ' where st_dwithin(''' || pnt::text ||
             '''::geometry, geom, ' || tol || ') order by 
st_distance(''' || pnt::text || '''::geometry, geom) asc limit 1' into rr;

     if rr.geom is not null then
         -- deal with MULTILINESTRINGS
         geom := rr.geom;
         if geometrytype(geom)='MULTILINESTRING' THEN
             geom := ST_GeometryN(geom, 1);
         end if;

         -- project the point onto the linestring
         pos := st_line_locate_point(geom, pnt);
         edge_id := rr.gid;
     end if;

end;
$body$
     language plpgsql stable;


> 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;
> 
>    EXIT WHEN NOT FOUND;
> 
>    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
> 
>                  FROM hw_roadlink'::text,
> 
>                  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;
> 
> Thanks in advance
> 
> 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.
> 
> 
> 
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/pgrouting-users
> 


---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus