Using dijkstra in Postgres with a table containing source and target node IDs

Question

I'm very new to this - trying to use dijkstra to find distances and routes between a list of around 10,000 people's addresses and their chosen facility. I have a small subquery (below) deriving the node ids (source and target) for every person. I would have thought I could simply join that into dijkstra to provide the source and target for the algorithm?

Sample SQL that is failing and my brain is fading:

SELECT *
FROM pgr_dijkstra(
    'SELECT gid as id, source, target, length as cost FROM roads',
    mappingt.source,
    mappingt.target,
    directed := false
) AS route
JOIN (
    SELECT 
        p.nearestnode AS source,
        f.nearestnode AS target
    FROM people p
    INNER JOIN facilities f 
    ON p.chosenfacilityid = f.FacilityId
) AS mappingt 
ON route.target = mappingt.target
and route.source = mappingt.source
JOIN roads AS roads
ON route.edge = roads.gid;

Any tips appreciated!

score 0 · Answer 1 · answered Apr 20 '23 at 22:36

Ok for anyone arriving with the same issue - fixed like this (I think)!

select 
        p.nearestnode,
        f.nearestnode,
        dijkstra.*
from people p   
inner join facilities f 
on p.chosenfacilityid = f.FacilityId
CROSS JOIN LATERAL
  pgr_dijkstra(
    'SELECT id, source, target, cost FROM roads',
    p.nearestnode,
    f.nearestnode,
    directed := false
  ) AS dijkstra
JOIN
  roads road
ON
  dijkstra.edge = road.gid;

Using dijkstra in Postgres with a table containing source and target node IDs

1 Answers1