Removing certains routes from pgrouting output table

Question

I have used pgRouting to generated multiple many to many traffic routes different points of interest. I have used st_Dwithin to put a catchment area on the routing alogirthm, however the agg_cost(total_distance) in the table shows that some routes are still too long (for walking for example).

Is there a way to remove whole routes from the output table (i.e. path_seq 1...n) for routes where the agg_cost is too long for my analysis?

I need to show the whole for for my analysis, not to condense the output into a new table and remove rows where agg_cost >5km.

** It would need all edges to be deleted up to the max agg_cost

Answer 1

I'm not sure there is a way to do it directly with pg_routing, but you can filter the result after.

For example you can delete the path too long in your table by getting there max agg_cost for each path:

WITH elegible_paths AS (
   SELECT 
     path_id 
   FROM your_table 
   GROUP BY path_id 
   HAVING max(agg_cost) > 5000
)
DELETE FROM your_table WHERE path_id IN (SELECT path_id FROM Id elegible_paths)

Answer 2

You might have better luck with the pgr_drivingdistance function for this problem. You input one or many nodes as the starting point of the catchment area and it will return all the nodes in the distance parameter you enter.

For my example below I use cost_s and reverse_cost_s which are osm2pgrouting columns of cost in seconds. So this example takes the nearest node of the latlng I enter and returns all the nodes in a 10 minute drive time (600 seconds). I put those nodes through the pgr_alphashape function and only return the polygon results.

If you are using simply using KM as the cost, just replace cost_s and reverse_cost_s with your KM cost columns and enter in 5 where I entered in 600.

 select st_collectionextract(pgr_alphashape(st_collect(the_geom),2),3)
 geom
    from pgr_drivingDistance(
       'SELECT gid as id,
        source,
        target,
        cost_s as cost,
        reverse_cost_S as reverse_cost
        FROM ways',
            getnearestnode(41.4648944,-81.563833),
            600,
        true
      )d join ways_vertices_pgr b on d.node=b.id

The ouput for my above query