Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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;
share|improve this question
    
---- 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
add comment

2 Answers

up vote 7 down vote accepted

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;
share|improve this answer
    
I knew it would be you to answer :) Thanks a lot! –  culebrón Jun 2 '11 at 10:12
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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