3

I am using a road network and the following postgres/postgis operations to perform routing using pg_routing and the pgr_dijkstra method. I am successfully generating routes but they are not the shortest. Here are my methods..

Uploaded road data to postgis.
Added columns source and target.

alter table roads_4326 add column source integer;
alter table roads_4326 add column target integer;

Created topology.

select pgr_createTopology('roads_4326', .0001, 'geom', 'id');

Which successfully produces the '..vertices_pgr' layer.

Then I run the following to route from source id=1 to target id=99.

SELECT seq, edge, geom
FROM pgr_dijkstra(
  'SELECT id,
      source, 
      target,
      st_length(geom) AS cost
    FROM roads_4326',
  1, 99,
  directed => false
) AS a
JOIN roads_4326 AS b ON (a.edge = b.id) ORDER BY seq;

A route is generated but I am unsure why much shorter routes are not returned. Image 1 is returned.

Image 1

Why are the much shorter connections in Image 2 ignored?

Image 2

4
  • 2
    Have you checked your network, that it is correctly nodes? These errors usually happen at the data side, where the map looks ok but the data is not properly connected at some noded.
    – dkastl
    Jun 2, 2021 at 0:11
  • Apart from lines not connected as mentioned by @ dkastl : Did you check if some restrictions like direction (one-way) could cause this?
    – Babel
    Jun 2, 2021 at 5:47
  • I think @dkastl has it right. I was able to produce better results after planarizing in ArcGIS. I thought this was what pgr_createTopology() was supposed to do? Does it only create nodes? Jun 2, 2021 at 20:03
  • The pgRouting topology function can snap nearby nodesig if they are within a certain tolerance, that you can specify. However, it doesn't create nodes.
    – dkastl
    Jun 3, 2021 at 21:49

1 Answer 1

3

Even though you have instructed to use an undirected graph, you must still use set a cost and a reverse_cost (which can be the same).

SELECT gid as id, source, target, 
        length:: double precision AS cost, 
        length:: double precision AS reverse_cost
 FROM bristol_roads

See the examples from the doc:

SELECT * FROM pgr_dijkstra(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table',
    2, 3,
    FALSE
);
 seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
   1 |        1 |    2 |    2 |    1 |        0
   2 |        2 |    3 |   -1 |    0 |        1
(2 rows)

SELECT * FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM edge_table',
    2, 3,
    FALSE
);
 seq | path_seq | node | edge | cost | agg_cost
-----+----------+------+------+------+----------
   1 |        1 |    2 |    4 |    1 |        0
   2 |        2 |    5 |    8 |    1 |        1
   3 |        3 |    6 |    5 |    1 |        2
   4 |        4 |    3 |   -1 |    0 |        3
(4 rows)
1
  • This worked for me. Big belated thanks @JGH. Sep 7, 2021 at 19:40

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.