PostGIS – How to Access pgr_Dijkstra Return Values Including Aggregated Cost

pgroutingpostgispostgresql

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

Best Answer

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.

Related Question