0

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
1
  • Error messages and code should be posted as text, not as a screenshot. It is very difficult to read for some users and impossible for screen readers, and google. Please take the tour to see how this site works.
    – Ian Turton
    May 23, 2022 at 7:45

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.