PgRouting shortest path aggregate cost is wrong

Question

I have the following code to determine the shortest route for a set of FROM and TO points along a road network; however, the agg_cost (distance) results are wrong.

When I sum the lengths of each line segment travelled for an fid-tid pair the length is correct, but the agg_cost is less.

An example of the pgr_routes table is below:

    -- build topology of road network for nearest from and to locations by distance
    ALTER TABLE landfill.gdm_connected_4326 ADD COLUMN "source" integer; -- add source column for pg routing
    ALTER TABLE landfill.gdm_connected_4326 ADD COLUMN "target" integer; -- add target column for pg routing
    SELECT pgr_createTopology('landfill.gdm_connected_4326', 0.001, 'geom', 'id'); -- prepare roads layer using pg routing builder to build a network topology based on the geometry information.
    
    -- add pgr network cost information
    ALTER TABLE landfill.gdm_connected_4326 ADD COLUMN length float8; -- add length field for pgr cost
    ALTER TABLE landfill.gdm_connected_4326 ADD COLUMN cost float8; -- add cost column
    ALTER TABLE landfill.gdm_connected_4326 ADD COLUMN reverse_cost float8; -- add reverse_cost column
    UPDATE landfill.gdm_connected_4326 SET length = ST_Length(geom::geography);
    
    -- replace null values in oneway field with 0
    UPDATE landfill.gdm_connected_4326 SET oneway=0 WHERE oneway IS null; 
    
    -- create cost and reverse costs for road network based on oneway field
    UPDATE landfill.gdm_connected_4326
    SET cost = length(geom::geography) 
    WHERE oneway IN ('0','FT'); -- both direction, road direction and digitizing direction are the same
    
    UPDATE landfill.gdm_connected_4326
    SET cost = 999999999
    WHERE oneway = 'TF'; -- The road direction and the digitizing direction are opposite.
    
    UPDATE landfill.gdm_connected_4326
    SET reverse_cost = length(geom::geography)
    WHERE oneway IN ('0', 'TF');
    
    UPDATE landfill.gdm_connected_4326
    SET reverse_cost = 999999999999
    WHERE oneway = 'FT';
    
    -- Begin Dijkstra algorthm shortest path query
    CREATE TABLE landfill.pgr_routes AS -- Create table of edge = -1 results
    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.id AS fid, f.geom as fgeom,
             t.id as tid, t.geom as tgeom
        FROM public.from_pts AS f,
             landfill.to_pts AS t
    ), vertices AS (
      SELECT fid, tid,
           (SELECT id -- proximity search for closest from vertex
              FROM landfill.gdm_connected_4326_vertices_pgr
             ORDER BY the_geom <-> fgeom
             LIMIT 1) as fv,
           (SELECT id -- proximity search for closest to vertex
              FROM landfill.gdm_connected_4326_vertices_pgr
             ORDER BY the_geom <-> tgeom
             LIMIT 1) as tv
      FROM all_pairs
    ), pgr_result AS (
      SELECT fid, tid, pgr_Dijkstra(
        'SELECT id, source, target, length AS cost, reverse_cost FROM landfill.gdm_connected_4326',
        fv, tv, 
        directed := true
      ) from vertices
    )
    SELECT fid, tid, (pgr_dijkstra).* FROM pgr_result
    WHERE (pgr_dijkstra).edge = -1;

The distance for fid, tid = 5 should be ~ 117 km

fid	tid	seq	path_seq	node	edge	cost	agg_cost
5	5	91	91	545897	-1	0	6189.429
6	1	430	430	545894	-1	0	35899.55
6	2	344	344	545895	-1	0	47399.31
6	3	428	428	545896	-1	0	61720.07
6	4	430	430	545894	-1	0	35899.55
6	5	289	289	545897	-1	0	15723.43

Answer 1

I fixed the problem by changing SET cost = length(geom::geography) to ST_Length(geom::geography).