Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I've posted on here before about my battles with drivetime polygons and got some sensible suggestions back, so having Googled myself stupid over this I'm turning to you once again in the hope of a nugget of wisdom.

I'm on a Windows 7 PC. I've followed the excellent guide at underdark.wordpress.com and got postgreSQL installed, with PostGIS and pgRouting. I've downloaded OSM data for the UK from Cloudmade (and Geofabrik for that matter) and I've used the wonderful osm2po to get that data into the postgreSQL database. So far, so good. No errors, no problems, looks like a database to me. I've got a table called network with obvious column names like id, source, target, kmh, cost etc. Just what I would have expected if I'd defined them myself.

So I've fired up the SQL query panel, banged in the SELECT * FROM Driving_Distance statement and fed it the id, source, target and cost columns. I was trying to find 30 minutes around Northampton and the output did indeed some roads within Northampton(shire) - but the rest were in North Wales, which can't possibly be right. At 3:30am this morning it occured to me that the table also had columns named osm_source_id, osm_target_id and osm_id so perhaps it might be a bit more sensible if I used those. Made sense to me as most things do at that time of the morning, so I ran it again and now I just get "starting vertex not found" every single time.

So to summarise. This works, but doesn't find the right roads:

SELECT network.id,network.x2,network.y2,network.geom_way
 FROM network
 JOIN
 (SELECT * FROM driving_distance('
  SELECT id,
source,
target,
     cost/60::float8 AS "cost"
  FROM network',
  1516905,
  0.025,
  false,
  false)) AS route
 ON
 network.id = route.vertex_id;

This doesn't work at all:

SELECT network.id,network.x2,network.y2,network.geom_way
 FROM network
 JOIN
 (SELECT * FROM driving_distance('
  SELECT osm_id::int4 AS "id",
osm_source_id::int4 AS "source",
osm_target_id::int4 AS "target",
    cost/60::float8 AS "cost"
  FROM network',
  1516905,
  0.025,
  false,
  false)) AS route
 ON
 network.id = route.vertex_id;

The same message happens whether or not I'm using Cloudmade or Geofabrik data, so I'm at a bit of a loss. Any suggestions at this point would be extremely welcome as my deadline is long passed and I don't think I can come up with any new reasons to the boss why it's not done!

Thanks Michael

share|improve this question

2 Answers 2

I've looked into using osm2po for pgRouting. You can find the full workflow of converting OSM data to SQL, importing it into pgRouting and routing using "pgRouting Layer" plugin for QGIS in this post.

The query used by the plugin looks like this (select values from source/target for [fromNode] and [toNode]):

SELECT osm2po.*, route.cost AS route_cost 
FROM osm2po
JOIN (
   SELECT * FROM shortest_path('
      SELECT id,
             source,
             target, 
             cost,
             reverseCost
      FROM osm2po',
      [fromNode],
      [toNode],
      true,
      true)
   ) AS route 
ON osm.po.id = route.edge_id

enter image description here

share|improve this answer

Just a hint:

use source and target for routing (both int4) osm_IDs like osm_source_id etc. are of int8 (long integer 64 Bits)

Greets from Hamburg

Carsten M. (osm2po)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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