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

pgroutingpostgispostgresqlroutingsql

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)

Best Answer

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)
Related Question