2

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.

3

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.
  • Thank you for the quick reply. I think I am almost there. I ran the following which allowed me to export a list of ids with total costs, but no line geometries. I think I am doing something wrong with the dijkstra section of the query. – rkemp78 Feb 10 at 7:21
  • SELECT a."inputid", SUM(a.cost) AS "Aggregated Cost", ST_MakeLine(b.geom ORDER BY a.seq) AS "Geometry" FROM ( SELECT "inputid", (pgr_dijkstra( 'select gid as id, source, target, length as cost from tl_road', "origin", "destinatio", false )).* FROM origindestination ) AS a JOIN tl_road AS b ON a.edge = b.gid GROUP BY a."inputid" ORDER BY a."inputid" ; – rkemp78 Feb 10 at 7:23
  • @rkemp78 when there are rows, the join works...what's in the "Geometry" column when you run the query? does tl_roads store geometries in a column geom? – ThingumaBob Feb 10 at 9:08
  • @rkemp78 you can try with ST_Union instead of ST_MakeLine. what PostGIS version are you using? – ThingumaBob Feb 10 at 9:17
  • St_union works, thank you. I'll do some digging over the next few days to see if I can work out why the other one doesn't, this will help me get to grips with it. Thanks you again, you have been incredibly helpful. – rkemp78 Feb 10 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.