I'm working in PGROUTING - POSTGIS I have my road network and two sets of points -firestation and the entire cityblocks from town (in diferent tables, 1 for origin, and 1 for destination).
The plan is to calculate the optimal routes from the fire station to the blocks. Later it would filter for maximum time. I have already achieved this first point just explained using algorithm pgr_dijkstra (many-to-many).
DO $$DECLARE mzn_o integer = (SELECT ARRAY (select origen from origin_table)); mzn_d integer = (SELECT ARRAY (select destino from destination_table)); BEGIN create table my_routes_table as SELECT seq::integer, path_seq::integer, start_vid::integer, end_vid::integer, concat(start_vid::integer, end_vid::integer) as id, node::integer,edge::integer, a.cost::double precision, agg_cost::double precision, b.class_id::integer, b.the_geomx::geometry FROM pgr_dijkstra( 'SELECT id, source, target, traveltime as cost, traveltime_rev as reverse_cost FROM my_network_table', mzn_o, mzn_d1,directed := false) a left join my_network_table b on a.edge=b.id; END$$;
I would like to improve this operation, because in the above code you get all possible routes from all sources to all destinations. I would like that when a block finds its nearest fire station, it stops searching. In other words, each station is assigned only the closest station and not all of them. In this way, achieve a higher calculation performance and with less cost, since the combinations would be significantly limited. That's the idea, something similar or identical to what is achieved with the Network Analyst Closest Facility tool in ArcMap, where it seems that you can enter the maximum number of facilities to calculate the route.https://desktop.arcgis.com/en/arcmap/latest/extensions/network-analyst/closest-facility.htm
Maybe a picture for understanding:
My purpose is not to insert a filter after main operation, but to integrate that idea so that it results in an improvement in the performance of the operation that causes a considerable decrease in the amount of operation and can work with large networks and improve the capacity of the computer.
I think I have already tried everything within my reach and my technical level, and my eyes are bleeding from looking at possibilities on the internet. In Postgis or PG Routing I have not been able to find anything related to Closest Facilities (if to calculate with service area and pgr_drivingtime algorithm), something that limited the calculation to find only the closest route. I thought that with the pgr_astar algorithm it could be achieved since its concept is to find the best route and reduce possibilities but it also returns the same as with Dijkstra in my tests. I have also tried to make a cost matrix and try filtering with the minimum cost min (cost), I thought that this way I would filter out the final nodes not needed, as explained in this example https://www.qgistutorials.com/en/docs/3/origin_destination_matrix.html. But it's not working for me.
SELECT * FROM pgr_dijkstraCost( 'select id, source, target, cost from my_network_table', (SELECT ARRAY (select source from origin_table)), (SELECT ARRAY (select target from destination_table))); select source, min(cost) as shortest_path from costs_table group by source;