Get Multiple Shortest Path using PgRouting in One Query

Question

I am trying to get multiple shortest path using A* Algorithm available in PgRouting.

I referred Solution given by UnderDark ( Link of the Stakoverflow Post ) . But it is not giving me proper Output.

MY Query (Simple Query - Without Multiple Path Detection)

SELECT * FROM shortest_path_astar('
            SELECT gid as id,
                     source::integer,
                     target::integer,
                     length(the_geom)::double precision as cost,
                     x1, y1, x2, y2
                    FROM connectivity',
            110, 111, false, false);

Output of this Query:

vetex_id  |  edge_id |  cost
110       | 1        | 0.01235
111       | -1       | 0

Now Query as suggested by Underdark on Given link:

 SELECT 
source, 
target,
(SELECT SUM(cost) FROM 
    (  --- Complete Above Query Here 
             )
    ) AS foo 
) AS cost
FROM connectivity;

Output of this Query:

source  |  target |  cost
9       | 8       | 0.01235
...     |...      | ...
(More 211 records ) 

Note: In my test table (connectivity) I have 212 Records

So this query is giving me all the records as output without any path relativity.

I do not know where I am doing wrong. any help / suggestion will be helpful for me.

Answer 1

There is likely a problem with your SQL in the subquery...

If you are putting this query as the subquery EXACTLY...

SELECT * FROM shortest_path_astar('
        SELECT gid as id,
                 source::integer,
                 target::integer,
                 length(the_geom)::double precision as cost,
                 x1, y1, x2, y2
                FROM connectivity',
        110, 111, false, false);

This will evaluate to a constant, so it would be like doing...

SELECT 
    source, 
    target,
    0.01235 AS cost
FROM connectivity;

You will just get all 212 records with 0.01235 as the cost.

If you've replaced "110, 111" with "source, target" then you will just get the single segment already defined in connectivity as (presumably) the shortest path, astar will not need to search anymore.

If you want to find all shortest paths to one specific destination, then replace "110, 111" with "source, 111" as this will give you the distance from all locations to location 111.