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: