# driving time to the nearest facility using pgrouting

I'm using pgrouting 2.0 and postgis in postgresql. My problem is to calculate the driving time to the closest facility for each node of my OSM derived network. These are the steps to be implemented (if I'm not mistaken..): 1. Find the closest node of the network for each facility (let't call them "nodes_fac"); 2. Calculate iteratively the driving distance to all nodes (or within a given distance) from each of the previous nodes_fac writing the results in a table; 3. Filter the results using the minimum operator to select the driving time to the closest facility (the result should be a table including the nodes geometries with the minimum values);

Step 1: I think that this code can work (posts is the table of facilities):

``````SELECT posts.id AS post_id,
(SELECT nodes.id AS node_id
FROM  ways_vertices_pgr as nodes
ORDER BY posts.geom <#> nodes.the_geom LIMIT 1)
FROM post_offices as posts
ORDER BY post_id;
``````

Step 2: The following code could be used, but I don't know how to loop it to produce a table with the driving distance for each node_fac:

``````SELECT ways_vertices_pgr.id,
ways_vertices_pgr.the_geom,
basin.cost
FROM ways_vertices_pgr
JOIN ( SELECT pgr_drivingdistance.id1,
pgr_drivingdistance.cost
FROM pgr_drivingdistance('
SELECT gid AS id,
source::integer,
target::integer,
avg_cost_min::double precision AS cost
FROM ways'::text, 22938, 10::double precision, false, false)) basin ON ways_vertices_pgr.id = basin.id1;
``````

avg_cost_min is a calculated column with the driving time for each network segment, 22938 is one of the node_fac, 10 is the maximum driving time.

• I think you can use a different pgr function that will get you along the way without having to loop. Either pgr_apspJohnson or pgr_apspWarshall will give you an all-pairs set of costs. You could then join this to your facilities table and select the minimum cost... I've never tried this but you can check the documentation docs.pgrouting.org/2.0/en/doc/index.html Aug 4, 2015 at 13:17
• thanks.. I've tried both pgr_apspJohnson and pgr_apspWarshall, but unfortunately I've got an "error computing path" at the beginning of calculations.. probably calculating all-pairs set of costs for 58000 nodes can be awkward... Aug 4, 2015 at 17:06
• "Error computing path: Unknown exception caught!" using pgr_apspJohnson Aug 4, 2015 at 17:14
• Perhaps you have hit a limit. Here's an answer that shows how to iterate across results (I'd go with the FOR LOOP approach). Hope that gets your further... stackoverflow.com/questions/15266345/… Aug 4, 2015 at 19:08

Here is my solution (it can be improved for sure!). I wrote this function:

``````RETURNS TABLE(node integer, cost double precision) AS
\$BODY\$
DECLARE
node integer;
BEGIN
FOR node IN SELECT DISTINCT (temp.node_id) FROM temp
-- WHERE node_id = 6626 (temporary filter to speed up testing)
LOOP
RETURN QUERY EXECUTE
'SELECT pgr_drivingdistance.id1, pgr_drivingdistance.cost AS cost
FROM pgr_drivingdistance(''
SELECT gid AS id,
source::integer,
target::integer,
avg_cost_min::double precision AS cost
FROM ways'',
' || node || ' ,
60,
false,
false)';
END LOOP;
RETURN;
END;
\$BODY\$
``````

and this script to be run for minimum driving distance calculation:

``````-- create table (temp) with closest network node to points included in facilities table (facs)
DROP TABLE IF EXISTS temp;
CREATE temp TABLE temp AS (SELECT facs.id AS fac_id,
(SELECT nodes.id AS node_id
FROM  ways_vertices_pgr as nodes
ORDER BY facs.geom <#> nodes.the_geom LIMIT 1)
FROM facs
-- WHERE fac_type = --use this line as filter on facility types
ORDER BY fac_id)
;
-- use isochrones_fac function to calculate driving time from nodes in temp table
-- aggregate by node returning minimum value and join geometry from the node table

CREATE TABLE posts_mincost -- modify output table name
AS (SELECT output.id,
output.min_cost,
vertices.the_geom
FROM (SELECT isochrones_fac.node AS id,
min(isochrones_fac.cost) AS min_cost
FROM isochrones_fac() isochrones_fac(node, cost)
GROUP BY isochrones_fac.node
ORDER BY isochrones_fac.node) output,
nodes_gbe AS vertices
WHERE output.id = vertices.id);
``````

It seems working good (about 15 seconds for 100 facilities, 58000 nodes, max 60 min of driving distance). I'm quite happy considering my sql programming level!! ;)

This is a snapshot of an output of a spline interpolation:

Any further suggestion is welcome,of course!!

Based on makovz' the self provided answer I had a go at this. In the current version of pgr_drivingDistance at least, it is possible to provide an array of input nodes, thereby eliminating the need for loops. Using 300k nodes and 33 facilities the query time for distance to closest facility is <10s on my laptop:

``````WITH
--first map the facilities to the nearest node on the network
node_facility AS (
SELECT
facilities.id AS facilities_id,
(SELECT
nodes.id AS node_id
FROM ways_vertices_pgr AS nodes
ORDER BY facilities.geom <#> nodes.the_geom
LIMIT 1)
FROM facilities
),

--aggregate all nodes in one array
nodes AS (
SELECT array_agg(node_id) AS nodes FROM node_facility
),

--then calculate the distance from every point on the network to each of the
--nodes. Stop searching after max distance (10km in this example) is reached.
distance AS (
SELECT *
FROM nodes, pgr_drivingDistance('
SELECT gid AS id,
source::int4,
target::int4,
length_m::float8 AS cost
FROM ways',
nodes.nodes,
10000::FLOAT,
false,
false))

--use the result to select the single nearest facility
SELECT DISTINCT ON (nodes.id)
nodes.id,
nodes.the_geom,
node_facility.facilities_id,
distance.agg_cost
FROM ways_vertices_pgr AS nodes
JOIN distance ON distance.node = nodes.id
JOIN node_facility ON node_facility.node_id = distance.from_v
ORDER BY nodes.id, agg_cost ASC
)
``````