[pgrouting-users] best/fastest way to calculate thousands of trsp routes
Vicky Vergara
vicky at georepublic.de
Wed Apr 26 11:37:46 PDT 2017
Please, save all your tables, views, functions, etc before upgrading, there
is a bug on the upgrading script that hasnt being fix, (recently found it)
On Wed, Apr 26, 2017 at 3:55 AM, McDonaldR <McDonaldR at angus.gov.uk> wrote:
> Wow! Thanks for that, Regina.
>
> I’ve downloaded the file and will apply it as soon as I can.
>
> Most of my pgRouting work has been done on 2.1 so it will be good see how
> much has changed in 2.4.
>
>
>
> Ross
>
>
>
> *From:* Pgrouting-users [mailto:pgrouting-users-bounces at lists.osgeo.org] *On
> Behalf Of *Regina Obe
> *Sent:* 24 April 2017 15:36
>
> *To:* 'pgRouting users mailing list'
> *Subject:* Re: [pgrouting-users] best/fastest way to calculate thousands
> of trsp routes
>
>
>
> Ross,
>
>
>
> I'm the one that builds the windows packages.
>
>
>
> I just built binaries for PostgreSQL 9.2 pgRouting 2.4.1.
>
>
>
> Can you give those a try?
>
>
>
> http://winnie.postgis.net/download/windows/pg92/buildbot/
>
>
>
> Should work fine with your PostGIS 2.2.2
>
>
>
> 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.
>
>
>
> Let me know if you run into any issues.
>
>
>
> Thanks,
>
> Regina
>
> http://www.postgis.us
>
> PostGIS PSC Member
>
> http://postgis.net
>
>
>
>
>
>
>
>
>
>
>
> *From:* Pgrouting-users [mailto:pgrouting-users-bounces at lists.osgeo.org
> <pgrouting-users-bounces at lists.osgeo.org>] *On Behalf Of *McDonaldR
> *Sent:* Monday, April 24, 2017 5:00 AM
> *To:* pgRouting users mailing list <pgrouting-users at lists.osgeo.org>
> *Subject:* Re: [pgrouting-users] best/fastest way to calculate thousands
> of trsp routes
>
>
>
> Hi Vicky
>
>
>
> 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.
>
>
>
> 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.
>
>
>
> Thanks again
>
>
>
> Ross
>
>
>
> *From:* Pgrouting-users [mailto:pgrouting-users-bounces at lists.osgeo.org
> <pgrouting-users-bounces at lists.osgeo.org>] *On Behalf Of *Vicky Vergara
> *Sent:* 20 April 2017 17:39
> *To:* pgRouting users mailing list
> *Subject:* Re: [pgrouting-users] best/fastest way to calculate thousands
> of trsp routes
>
>
>
> 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
>
>
>
>
>
> 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.
>
>
> 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
>
--
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/20170426/76b99e2a/attachment-0001.html>
More information about the Pgrouting-users
mailing list