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

pgRouting pgrouting-users
1

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.

Best regards
Lyubomir Klyambarski

2

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

3

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@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

4

Yes, writing functions is always easier for me to get my head around :slight_smile:
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@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@lists.osgeo.org <mailto:Pgrouting-users@lists.osgeo.org>
http://lists.osgeo.org/mailman/listinfo/pgrouting-users

_______________________________________________
Pgrouting-users mailing list
Pgrouting-users@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/pgrouting-users