2

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 – mlinth Aug 4 '15 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... – makovz Aug 4 '15 at 17:06
  • "Error computing path: Unknown exception caught!" using pgr_apspJohnson – makovz Aug 4 '15 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/… – mlinth Aug 4 '15 at 19:08
2

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:

enter image description here

Any further suggestion is welcome,of course!!

0

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
  )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.