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 |