I have been using a postgis database with pgRouting to solve shortest path problems. I have been using a query like:
select seq, e.geom, pd.cost, pd.id1 as node from pgr_dijkstra( 'select gid as id, source, target, length as cost from tl_road', 15489, 12854, false, false) as pd left join tl_road as e on pd.id2 = e.gid order by pd.seq
tl_road is my network. 15489 and 12854 are example start and finish points. This works well.
I have some commuting data of start and finish points and I have merged and found the nearest network node for each point, then imported it into my postgis database. My second table looks like this:
id RefNo Origin Destination -- -- -- -- 1 65277 61780 33025 2 67784 22005 18464 3 65897 257 17407
How can I ammend my pgrouting query to find the shortest path for EVERY ref number on my list using the origin & destination columns?
I have done a lot of googling and have found various solutions but these all seem to be multiple start points to ONE destination or vice versa. I have tried various adaptations of my pgrouting query and usually get the error: "more than one row returned by subquery used as an expression".
Ideally I want to be able to export one result table with one shortest path polyline for each refno id with the refno identifier in the attributes.
At the moment my list is 20 records. I may want to do this for more records in the future but it depends on memory and processing time.