Distance Thresholds - pgRouting

Question

I have been working with a set of data which mimics a shortest distance 'many to many' network analysis between COA population weighted centroids and education points in Bristol. My population centroids have been snapped to the road network, and I have tried various distance threshold within this section of code

-- ###
UPDATE bristol_coa11_centroids SET id = br.id
FROM bristol_roads_vertices_pgr br
WHERE ST_DWithin(bristol_coa11_centroids.geom, br.geom, 0.001);
-- ###
SELECT *
INTO test_route_v5
FROM pgr_dijkstra(
    'SELECT gid as id, source, target, length:: double precision AS cost
        FROM bristol_roads',
    ARRAY(SELECT id from bristol_coa11_centroids),
    ARRAY(SELECT id from bristol_education_v2), false) as be
    LEFT JOIN bristol_roads AS br ON be.edge = br.gid
    ORDER BY be.seq;

SELECT * FROM test_route_v5;

However, I have noticed that routes aren't being generated from all of the centroids (see image). Is there a way to get round this in Postgres or Arcmap first? I have measured distances from centroids and roads and determined different threshold against that but have seen no change. I need to expand the study size area so cannot afford to have a magnitude of points being missing. Thankyou!

Answer 1

You can check which ID is assigned to each point. Ideally, you would take a much larger distance, then keep the ID of the nearest neighbor.

That being said, it is likely not the issue here.

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)