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), false, true ) AS a Join routing b ON a.id2 = b.id ORDER BY seq;