I am facing the following problem with two tables:

The first table contains all my streetnetwork with cost, source, target. Another table contains 60 nodes (from the above network). Between all these 60 nodes I want to calculate the length of the path (e.g.: from node 1 to 2,3,4...60, from node 2 to 1,3,4,5...60 etc.)

Do you have a solution which could automatically calculate the length of all paths? Until now I can just calculate manually one path, then save the table and calculate the length.

But as you can imagine I don't wanna calculate 60*60 relations manually.

See my SQL-query so far:

SELECT seq, id1 AS node, id2 AS edge, a.cost, geom_way
FROM pgr_dijkstra(
'SELECT id, source, target, km as cost, km as reverse_cost FROM 
routing where cost is not null',
(SELECT nodes FROM nodelist WHERE id =1),
(SELECT nodes FROM nodelist WHERE id =2), 
) AS a
Join routing b
ON a.id2 = b.id

You can use pgr_kDijkstra to compute the path or total cost from 1 node to many. There is no function so far for many-to-many calculations, but the one above should already be a lot better.

The pgRouting workshop also explains how to use pgr_kDijkstra:

SELECT seq, id1 AS source, id2 AS target, cost FROM pgr_kdijkstraCost('
                SELECT gid AS id,
                         length::double precision AS cost
                        FROM ways',
                10, array[60,70,80], false, false);

The argument to specify target nodes must be an array of node IDs's. You had to run this function then once for each of your 60 nodes.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.