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)