# How to get multiple shortest paths with PgRouting in one query?

I want to run shortest path algorithm on multiple source&target pairs at once and get a result as a table and process it then.

How do I do this? This query does not work:

``````SELECT a.source, a.target, paths.*
FROM all_to_all a, shortest_path_astar('...', a.source, a.target, false, false) paths;

ERROR:  function expression in FROM cannot refer to other relations of same query level
``````

(btw, all_to_all does not literally mean all to all, :) it's just a number of random pairs)

This does not work either:

``````SELECT *
FROM all_to_all a, (
SELECT * FROM shortest_path_astar('...', a.source, a.target, false, false) yyy
) AS t2;
``````
-
---- could you please expand on this? i have the same problem but cant get these pairs right? (from an attempted edit of the post] – Mapperz Jan 27 '12 at 16:13

Something like

``````SELECT
source,
target,
(SELECT SUM(cost) FROM  -- or whatever you want to do with the routing result
(SELECT * FROM shortest_path_astar('...',
source,
target,
false,
false)
) AS foo
) AS cost
FROM all_to_all;
``````
-
I knew it would be you to answer :) Thanks a lot! – culebrón Jun 2 '11 at 10:12

Here's a query that returns all segments for all source-target combinations:

``````SELECT
source,
target,
shortest_path_astar('SELECT gid AS id, length AS cost, * FROM ways', source, target, false, false) AS segment
FROM
all_to_all
``````

Incredible, inconsistent with SQL syntax, but works!

``````source | target | segment
-------+--------+----------------
1 |      4 | (1, 2, 0.1357)
1 |      4 | (2, 3, 0.2468)
1 |      4 | (3, 4, 0.9)
1 |      4 | (4, -1, 0)
other sources & targets here
``````
-