Multiple shortest paths with pgRouting using a list as a secondary table

Question

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.

Answer 1

You want to simply run that function on every row in the <list> table; generally speaking, this is no different than any other function applied to a column/row. In practice, this here is a little tricky if unfamiliar with set returning functions.

One comprehensible way to write that query could be:

SELECT  a."RefNo",
        SUM(a.cost) AS "Aggregated Cost",
        ST_Union(b.geom) AS "Geometry"        -- ST_Collect(b.geom)
FROM    (
    SELECT  "RefNo",
            (pgr_dijkstra(
                '<edges_sql>',
                "Origin",
                "Destination",
                false
            )).*
    FROM    <list>
) AS a
JOIN    tl_road AS b
  ON    a.edge = b.<id>
GROUP BY
        a."RefNo"
ORDER BY
        a."RefNo"
;

Some notes:

• I used "Special Column Names" everywhere in the output; do yourself a favor and use lowercase (possibly underscored) identifiers instead...
• (pgr_dijkstra()).* will expand each row of the set returned by the function into individual columns; there are other, arguably more performant ways, including the elegant LATERAL, but I haven't encountered performance penalties this way yet.