1

I have the following pgr_Dijkstra SQL code for determining the shortest path between two points on a directed water stream network. The results of the query don't give the total_cost or aggregated cost (agg_cost). I know this is one of the outputs of the pgr_Dijkstra command, but just can't seem to make it return the -1 edge without an error.

ALTER TABLE streams ADD COLUMN length float8; -- add length field for pgr cost
ALTER TABLE streams ADD COLUMN cost float8; -- add cost column
ALTER TABLE streams ADD COLUMN reverse_cost float8; -- add reverse_cost column
UPDATE streams SET length = ST_Length(geom::geography);

-- create cost and reverse costs for stream network based on fldir field
UPDATE streams
SET cost = length(geom::geography) 
WHERE fldir IN (20,32,34); -- not identified(both direction), flow direction and the digitizing direction are the same, and not applicable values

UPDATE streams
SET cost = 1000000
WHERE fldir = 33; -- The flow direction and the digitizing direction are opposite.

UPDATE streams
SET reverse_cost = length(geom::geography)
WHERE fldir IN (20, 34, 33);

UPDATE streams
SET reverse_cost= 1000000
WHERE fldir =32;

WITH all_pairs AS (
  -- all pairs of start and end geometries with IDs
  -- that get carried through so the routing results
  -- match with the pt IDs you know.
  SELECT f.gid AS fid, f.geom as fgeom,
         t.gid as tid, t.geom as tgeom
    FROM from_pts AS f,
         to_pts AS t
), vertices AS (
SELECT fid, tid,
       (SELECT id -- proximity search for closest from vertex
          FROM streams_vertices_pgr
         ORDER BY the_geom <-> fgeom
         LIMIT 1) as fv,
       (SELECT id -- proximity search for closest to vertex
          FROM streams_vertices_pgr
         ORDER BY the_geom <-> tgeom
         LIMIT 1) as tv
  FROM all_pairs
)
SELECT fid, tid, agg_cost, pgr_Dijkstra(   -- added agg_cost, but error is produced.
  'SELECT gid AS id, source, target, length AS cost, reverse_cost FROM streams',
  fv, tv, 
  directed := true
) from vertices;

The results of the query are as follows:

pgr_Dijkstra Results

1 Answer 1

1

The field that you are trying to access, agg_cost, is one of the columns returned from the pgr_Dijkstra algorithm. As shown in the table in your question, all the values returned from Dijkstra (wrapped in parentheses) are returned in a composite type. You need to unpack those.

To do this, let's add another WITH clause, containing the final query you show above and then an unpacking query. The algorithm results are in a column called pgr_dijkstra. You can pull just the agg_cost out of that as (pgr_dijkstra).agg_cost and it would be returned as a separate column. To get all fields in the composite type, you can use (pgr_dijkstra).* and each field in the composite type will be returned as it's own column. Putting this together:

WITH all_pairs AS (
  -- all pairs of start and end geometries with IDs
  -- that get carried through so the routing results
  -- match with the pt IDs you know.
  SELECT f.gid AS fid, f.geom as fgeom,
         t.gid as tid, t.geom as tgeom
    FROM from_pts AS f,
         to_pts AS t
), vertices AS (
  SELECT fid, tid,
       (SELECT id -- proximity search for closest from vertex
          FROM streams_vertices_pgr
         ORDER BY the_geom <-> fgeom
         LIMIT 1) as fv,
       (SELECT id -- proximity search for closest to vertex
          FROM streams_vertices_pgr
         ORDER BY the_geom <-> tgeom
         LIMIT 1) as tv
  FROM all_pairs
), pgr_result AS (
  SELECT fid, tid, pgr_Dijkstra(
    'SELECT gid AS id, source, target, length AS cost, reverse_cost FROM streams',
    fv, tv, 
    directed := true
  ) from vertices
)
SELECT fid, tid, (pgr_dijkstra).* FROM pgr_result;

With the composite unpacked, you will be able to select just the final agg_cost by adding WHERE (pgr_dijkstra).edge = -1 if you only need that.

4
  • I am sorry that I am not catching on, but I am unsure how to send the query into a new table. The code confuses me because I don't understand the last lines. I don't know where to put the INTO pgr_result FROM command.
    – MJM
    Apr 8 at 22:41
  • @MJM Okay, rather than suggesting how to do it, I expanded my answer to include the extra WITH clause and unpack the composite type in an extra SELECT.
    – hgb
    Apr 8 at 23:02
  • It works amazing. I added some sql statements and it produces everything I need. Thank you.
    – MJM
    Apr 11 at 15:59
  • you really helped me out. Please go to gis.stackexchange.com/questions/426935/… and I will award the bounty to you. This didn't answer my QNEAT3 question, but it is a far better solution.
    – MJM
    Apr 11 at 16:07

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.