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

[Lyubomir Klyambarski]

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
http://lists.osgeo.org/mailman/listinfo/pgrouting-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/pgrouting-users/attachments/20140407/cdf75837/attachment-0001.html>