[pgrouting-users] Отн: Many to many routing and traffic assignment

[Stephen Woodbridge]

Yes, writing functions is always easier for me to get my head around :)
If you are building a distance matrix, there are some tools that wil. 
help you with that. kdijkstra is good for one to many answers.

You might also want to look at OSRM-Tools if you need more performance:
https://github.com/woodbri/osrm-tools

-Steve

On 4/7/2014 11:06 AM, Lyubomir Klyambarski wrote:
> Hi! Thanks for the response.
> I changed the from/to table a little - I created an table with 3 columns
> (from_vertice, to_vertice, traffic). And with this function I solve the
> problem:
>
> CREATE OR REPLACE FUNCTION get_all_routes() RETURNS void  AS
> $BODY$
> DECLARE
> i record;
>
> BEGIN
>
>   FOR i IN
> select traffic.from_v, traffic.to_v, traffic.cars from traffic
>   LOOP
>
> create table aux as select  seq, id1 AS node, id2 AS edge, cost FROM
> pgr_dijkstra('
>                  SELECT id,
>                           source,
>                           target,
>                           cost
>                          FROM osm',
>                  i.from_v::integer, i.to_v::integer, false, false);
> update osm
> set trf=trf+i.cars
> from aux
> where id=aux.edge;
> drop table aux;
> RAISE NOTICE  ' Running at % source id' ,i.from_v ;
> end loop;
> return;
> END;
> $BODY$
> LANGUAGE plpgsql;
>
> But it is a litle slower than I expected (~10 min for 28 pairs). Maybe
> if I use pgr_kdijkstra function I could speed up the process.
>
> Best regards
> Lyubomir Klyambarski
> На понеделник, 7 април 2014 г. 15:46, Stephen Woodbridge
> <woodbri at swoodbridge.com> написа:
> On 4/7/2014 5:13 AM, Lyubomir Klyambarski wrote:
>  > Dear pgrouting users,
>  > I am begginer at using pgrouting and SQL as a whole, but this tool would
>  > be very useful for my projects. My task is to perform traffic assignment
>  > on the road network of Bulgaria. I use routable map prepared with
>  > Osm2po. My start/end points are defined according to the map nodes.
>  > Using the code from the example
>  >
>  > SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
>  >                  SELECT  id,
>  >                          source,
>  >                          target,
>  >                            cost
>  >                          FROM osm',
>  >                  30, 60, false, false);
>  >
>  > how can I read my start/end points from another table and attach traffic
>  > value to the edges in new column?
>  > Thanks in advance.
>
> Untested:
>
> See the results:
>
> select a.start, a.end, sum( (pgr_dijkstra(
>          'SELECT  id, source, target, cost  FROM osm',
>          a.start, a.end, false, false)).cost ) as cost
>    from routes a;
>
> Update the cost column in routes:
>
> update routes set cost=sum( (pgr_dijkstra(
>
>          'SELECT  id, source, target, cost  FROM osm',
>
>          a.start, a.end, false, false)).cost )
>    from (select start, stop from routes) a
>    where routes.start=a.start and routes.end=a.end;
>
>
> You might need to fiddle with the sql a little but this should give you
> the idea.
>
> -Steve
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org <mailto:Pgrouting-users at lists.osgeo.org>
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>
>
>
>
>
> _______________________________________________
> Pgrouting-users mailing list
> Pgrouting-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/pgrouting-users
>