12

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
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;
4

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

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.