1

Im trying to create a matrix of driving distances from an osm2pgrouting parsed dataset.

Following many blogs I have the below incorrect SQL:

SELECT * from ways join (
SELECT
*
FROM
pgr_drivingDistance(
    'SELECT gid as id
        , source
        , target
        , length_m as cost FROM "ways"
        '
    ,274738,500,FALSE)
) as route on ways.gid = route.id

I get the error " ROUTE.ID" does not exist. This seems sensible since the pgRouting docs for 2.62 show no field returned in the examples called ID.

If I replace route.ID with say route.seq (a column PG driving distance does return) i get a very dodgy result with points random scattered over the map.

What is the equivalent of ID or what do i need to change to get a proper matric returned of points with 500m?

  • do the sub queries (SELECT gid as id , source , target , length_m as cost FROM "ways") and SELECT * FROM pgr_drivingDistance( 'SELECT gid as id , source , target , length_m as cost FROM "ways" ' ,274738,500,FALSE) ) as route return sensible results? – Ian Turton Jul 8 at 15:44
  • pgr_drivinDistance primarily returns all nodes that can be reached within the cost threshold; you want to join the route results of your example with the vertice table using route.node. to get isochrones, try pgr_pointsAsPolygon. this might help. – ThingumaBob Jul 8 at 15:48
  • @IanTurton yes both return a sensible number of results but I dont know how to relate any of the results from the pgr_drivingDistance (seq,node,edge,cost,aggcost) back to the original ways table. nothing seems to make sense. – AK9 Jul 8 at 16:02
  • Nothing to do with your question, but you could be interested by the project OpenTripPlanner (open source) that can create some isochrone based on OSM data and GTFS data (if you also want to compute public transport isochrone). – obchardon Jul 9 at 15:13
1

so after some manual checking here is a workin answer for people using OSM with the latest versions of the tools (OSM2pgsql and routing 2.62)

bascially way.target = route.node

    SELECT * from ways join (
SELECT * from pgr_drivingDistance(
        'SELECT gid as id
        , source
        , target
        , length_m as cost FROM "ways"      '
        ,274738,500,FALSE)
) as route on ways.target = route.node

enter image description here

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.