[pgrouting-users] Many to many routing and traffic assignment

[Stephen Woodbridge]

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