I am trying to find the closest landfill by distance from various locations, however, the computation time is 8 hr 36 min to find the shortest path from 3602 locations to 5 landfill locations.
Before using pgRouting I used QNEAT3 on another larger data set and it didn't take nearly as long.
As a test, I scaled down the data to 9 locations and 5 landfill locations and it completed the shortest path analysis in ~10 min.
Is there a problem with the code and its efficiency?
-- build road topology ALTER TABLE landfill.road ADD COLUMN "source" integer; -- add source column for pg routing ALTER TABLE landfill.road ADD COLUMN "target" integer; -- add target column for pg routing SELECT pgr_createTopology('landfill.road', 0.001, 'geom', 'id'); -- prepare roads layer by building network topology based on geometry information. -- add pgr networking information ALTER TABLE landfill.road ADD COLUMN length float8; -- add length field for pgr cost ALTER TABLE landfill.road ADD COLUMN cost float8; -- add cost column ALTER TABLE landfill.road ADD COLUMN reverse_cost float8; -- add reverse_cost column UPDATE landfill.road SET length = ST_Length(geom::geography); -- replace null values with 0 UPDATE landfill.road SET oneway=0 WHERE oneway IS null; -- create cost and reverse costs for road network based on ONEWAY field UPDATE landfill.road SET cost = ST_Length(geom::geography) WHERE oneway IN ('0','FT'); -- both direction, road direction and the digitizing direction are the same UPDATE landfill.road SET cost = 999999999 WHERE oneway = 'TF'; -- The road direction and the digitizing direction are opposite. UPDATE landfill.road SET reverse_cost = ST_Length(geom::geography) WHERE oneway IN ('0', 'TF'); UPDATE landfill.road SET reverse_cost = 999999999 WHERE oneway = 'FT'; -- Begin Dijkstra algorithm shortest path query CREATE TABLE landfill.pgr_routes AS 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.road_vertices_pgr ORDER BY the_geom <-> fgeom LIMIT 1) as fv, (SELECT id -- proximity search for closest to vertex FROM landfill.road_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.road', fv, tv, directed := true ) from vertices ) SELECT fid, tid, (pgr_dijkstra).* FROM pgr_result -- to unpack all fields in the composite column with each as a seperate column. WHERE (pgr_dijkstra).edge = -1; -- min distance value of agg_cost results CREATE TABLE landfill.pgr_routes_min AS SELECT DISTINCT ON (fid) fid, tid, agg_cost FROM landfill.pgr_routes WHERE agg_cost > 0 -- landfill must be greater than 0 m from location ORDER BY fid, agg_cost; -- Left join min distance results to locations and original landfill locations table CREATE TABLE landfill.nearest_landfill AS SELECT public.from_pts.*,landfill.pgr_routes_min.*,landfill.to_pts.name FROM landfill.pgr_routes_min LEFT JOIN public.from_pts ON landfill.pgr_routes_min.fid = public.from_pts.id LEFT JOIN landfill.to_pts ON landfill.pgr_routes_min.tid = landfill.to_pts.id;