TransWikia.com

Fixing pgRouting error "starting vertex not found"

Geographic Information Systems Asked on December 21, 2020

I’ve posted on here before about my battles with drivetime polygons and got some sensible suggestions back.

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.

2 Answers

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)

Answered by Carsten M. on December 21, 2020

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

Answered by underdark on December 21, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP