[Solved] How to get a road path which can be travelled in 10 minutes from a location

Question

pgr_drivingDistance uses the cost value you provide, and in the units you implicitly specify, meaning that when you add a column <traveling_time> (note that I use seconds in my example) as the time needed to traverse an edge (given the length and speed limit) and select that as cost, the functions result will represent the equal driving time limits.

As for the parts where the algorithm couldn´t fully traverse the next edge ‘in time’, you will need to add those yourself. The general idea here is to identify all possible edges connected to the end vertices in the result set of pgr_drivingDistance, but not equal to any of the involved edges, and interpolate a new end point along those lines.

– Updated –

The following query ~~is an out-of-my-head attempt and not tested at all, but in theory should~~ is tested and returns ~~a polygon~~ all full and partial edges representing a 600 seconds trip along your network:

WITH
  dd AS (
    SELECT pg.id1 AS node,
           pg.id2 AS edge,
           pg.cost
    FROM pgr_drivingDistance('SELECT id,
                                     source,
                                     target,
                                     <travel_time_in_sec> AS cost
                              FROM <edge_table>',
                             <start_id>,
                             600,
                             false,
                             false
         ) AS pg

  ),
  dd_edgs AS (
    SELECT edg.id,
           edg.geom
    FROM <edge_table> AS edg
    JOIN dd AS d1
      ON edg.source = d1.node
    JOIN dd AS d2
      ON edg.target = d2.node
  ),
  dd_ext AS (
    SELECT edg.id,
             CASE
               WHEN dd.node = edg.source
               THEN ST_LineSubstring(edg.geom, 0, (600 - dd.cost) / edg.<travel_time>)
               ELSE ST_LineSubstring(edg.geom, 1 - ((600 - dd.cost) / edg.<travel_time>), 1)
             END AS geom
    FROM dd
    JOIN <edge_table> AS edg
      ON dd.node IN (edg.source, edg.target) AND edg.id NOT IN (SELECT id FROM dd_edgs)
  )

SELECT id,
       geom
FROM dd_ext
UNION ALL
SELECT id,
       geom
FROM dd_edgs;

The CASE statement decides if, for any follow-up edge, the fraction of line length will be calculated from the start or end point.

As a sidenote: the current version of pgRouting provides a set of functions where inter-edge-points are to be considered; if updating your (rather outdated) PostGIS/pgRouting versions is an option, consider those functions instead.

Accepted Answer