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