#7 pgrouting query fails

[Bruce Drees]

In testing, pgrouting queries 'astar_sp'and 'shortest_path_astar' fail on a SQL error. The error msg is "unable to find column 'gid' in table navigation_motor."

[Sakari A. Maaranen]

Maybe you tried to use the example query from pgRouting AStart page directly? The example query at the end of the page http://pgrouting.postlbs.org/wiki/AStar expects a column named 'gid' trying to use that as 'id'. In the case of OSM2PostGIS, there is no 'gid' column, but instead there is 'id' column that can be used directly without renaming.

Try this one instead:

SELECT * FROM shortest_path_astar('SELECT id, source, target, cost, x1, y1, x2, y2, reverse_cost FROM navigation_motor',source_id, target_id, true, true);

Replace source_id and target_id with the node id's from and to. You can omit the reverse_cost column and set the last two boolean flags to false, if you want to experiment with undirected graph.

Oh yes, I need to write a proper tutorial before closing this "bug". This one is caused by my lacking documentation. Thanks again for the bug report!

[Bruce Drees]

I tried running the above query using my own client as well as pgAdmin, but it crashes the PostGreSQL server. However, the shooting star query seems to succeed, at least so far. Probably not related to anything in osm2postgis, but I thought I'd post this update just in case.

[Bruce Drees]

server crash during shortest_path_astar query

[Sakari A. Maaranen]

I haven't actually tested the routing functions with any bigger data sets yet. For example the database table indexes may be 'less than optimal'. It might be a good idea to test with smaller routing networks first and see how the PostgreSQL query planner handles those. I'm guessing the database indexing and the queries may need some optimizing to actually work with bigger data sets.

[Sakari A. Maaranen]

I tested various Dijkstra and A* queries and got similar crashes with PostgreSQL server. Didn't find the cause yet. Hard to say if it's a bug with OSM2PostGIS or with pgRouting (or something else).

Here is a shooting star algorithm query that has been tested to work with OSM data from Malta:

SELECT * FROM shortest_path_shooting_star('SELECT id,source,target,cost,reverse_cost,x1,x2,y1,y2,to_cost,rule FROM navigation_motor', 37367, 37296, true, true);

Note that the source and target for the shooting star algorithm are navigation network graph edges (route segment id's) and not vertices (node id's). So the above query starts at edge 37367 and ends at edge 37296.

The example edge id's were found via OSM node.id=246158282 (source edge=37367) and node.id=246156215 (target edge=37296). In other words, OSM2PostGIS has generated the topology graph edges 37367 and 37296 in the navigation_motor table. Your edge id's may vary (because they are generated), but the OSM node id's are probably still the same. You can find out your local edge id's using the followin queries:

SELECT id FROM navigation_motor WHERE source=246158282;
SELECT id FROM navigation_motor WHERE target=246156215;

The above queries return the route segment id's of all routes ending at node id=246156215 and starting from node id=246158282. (These node id's were arbitrarily chosen from the OSM data for Malta.)

[Nobody/Anonymous]

I ran this as suggested and it worked. Two questions:
1) So far I have been unable to relate the values returned in the vertex_id column to anything in navigation_motor. However, I can trace edge_id through to all of it's related attributes in osm_ways, osm_nodes, etc.
2) in navigation_motor, does use of column identifier 'geom' vice 'the_geom' limit the useable functions from PostGis and pgrouting?

[Sakari A. Maaranen]

1) Me neither... That question should actually go to pgRouting. The pgRouting documentation says "Column vertex_id contains start vertex of an edge from column edge_id." However, it does not say where do these vertices come from.
http://pgrouting.postlbs.org/wiki/ShootingStar

Maybe you could ask pgRouting forums where do the vertex_id's come from.

2) I don't think the column name should limit the use of any functions. If you know any tools that require a specific column name (like "the_geom" or something else), please add a Feature Request for configurable geometry column name. Also explain which tool has that requirement.

[Bruce Drees]

I found a thread in the pgrouting forum on this. Bottom line appears to be to just ignore it when using shortest_path_shooting_star:
http://pgrouting.postlbs.org/discussion/topic/225

[Bruce Drees]

With regard to certain pgrouting queries crashing postgreSQL, I came across a posting earlier today which indicated this is possible if cost or reverse_cost are negative values. I havent seen that case in my testing, but it may be something to watch for.