adding geometries to a source, target table for multiple rows in pgrouting

Question

I am having an issue trying to perform the pgr_dijkstra algorithm and getting back a line in my table for multiple rows. I have a table that has a columns of source, target node id's but I cannot figure out how to feed them into the pgr_dijkstra function properly. My table basically looks like this, but I need to fill in the routeline column with the shortest path line connecting the source and target. ID | source | target | routeline 1 | 30 | 217 | geometry(linestring) 2 | 217 | 2143 | 2 | 15 | 43 | I would eventually like to make a linestring out of several of the rows from the roueline column, as I am basically trying to follow survey respondents around a network to all the stop locations they visited, but I can't seem to find a pgr function for multiple ordered nodes. For now, i just figured I would connect the two nodes and then connect the lines together later. Please let me know if you have a better approach! I have been following the tutorial here : http://workshop.pgrouting.org/chapters/geoserver.html My problem is when I run my code:

select a.uniqueid, a.source, a.target , st_makeline(route.geom) as routeline 
from 
     analysis.routing as a, 
    (select geom from pgr_dijkstra('ways',a.source,b.target)
order by seq) as route;

I get the following error, and can't figure out how to pass the source and target from each row into the function.

ERROR:  invalid reference to FROM-clause entry for table "a"
LINE 1: ...uting as a, (select geom from pgr_dijkstra('ways',a.source,b...
                                                             ^
HINT:  There is an entry for table "a", but it cannot be referenced from this part of the query.

Does anyone know what is going on, I can't figure out how to reference my source from the reference table. Eventually, I need to insert this into an update statement such as

update analysis.routing set
routeline = (select ***the querry above with results as linestring)

Answer 1

I have solved the problem with the simple querry

update analysis.routing
set
routeline = (select st_makeline(pgr.geom) ,
from (select * from pgr_dijkstra('ways',source,target)) as pgr)

Answer 2

PG_Routing is not as difficult as it seems to be in the first contact. Every routing function will give you set of edges you have to drive in specified order from begining to reach the end, so (pseudocode):

pgr_dijkstra('all edges as sql',start_edge as int, stop_edge as int, ....options)

All edges topology must have a specified collumns. All you have to do is join output from function with edges table (since it not returns any geometry) and collect it, so this query should do the work (written ad-hoc so may not work 'as is') :

SELECT 
    sum(pgr.km) AS km, 
    sum(pgr.cost) AS cost, 
    ST_Union(pgr.geom_way) AS geom 
FROM 
    all_edges, 
    (SELECT * FROM pgr_astar(
        'select id, source,target, cost, reverse_cost, x1, x2, y1, y2 from all_edges', --this will prepare data for routing 
        1234, --ex. point of start 
        4321, --ex. point of destination
        true, true --options (see the spec.)
        )) AS rte  
        WHERE pgr.id = rte.id2';

This query will give you sum of cost and distance and geometry of way you have to drive. If you need to steer clear of highways (for example) you have to manipulate in query in function... and that's all story