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

Thu Apr 20 09:38:53 PDT 2017

Hello Ross

I want to encourage you to use the latest version of pgRouting:

Here starts the compilation on 2.1
https://travis-ci.org/cvvergara/pgrouting/jobs/224015091#L1905
Here starts the compilation of the latest 2.4
https://travis-ci.org/pgRouting/pgrouting/jobs/214667949#L766

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.

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.

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.

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:
https://github.com/pgRouting/pgrouting/tree/master/src/trsp/doc
That file uses the sample data:
http://docs.pgrouting.org/2.4/en/sampledata.html

Instructions to get the latest version:
https://github.com/pgRouting/pgrouting/wiki/Notes-on-Download%2C-Installation-and-building-pgRouting

Regards
Vicky

On Thu, Apr 20, 2017 at 10:49 AM, Stephen Woodbridge <
woodbri at swoodbridge.com> wrote:

> 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
>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/pgrouting-users

-- 

Georepublic UG (haftungsbeschränkt)
Salzmannstraße 44,
81739 München, Germany

Vicky Vergara
Operations Research

eMail: vicky at georepublic.de
Web: https://georepublic.info

Tel: +49 (089) 4161 7698-1
Fax: +49 (089) 4161 7698-9

Commercial register: Amtsgericht München, HRB 181428
CEO: Daniel Kastl
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20170420/0e4e6b61/attachment-0001.html>