2

I have imported some OSM data in a PostGIS database (using osm2pgrouting) to use with pgRouting. I use Python to send the SQL queries. It works fine, however I noticed that the result includes many edges with the same ID. For example this is the results I get for a simple routing query:

   seq  path_seq   node      edge      cost  agg_cost
0    1         1   7215  30893710  0.001981  0.000000
1    2         2   7216  30893710  0.001536  0.001981
2    3         3   7221  30893710  0.001988  0.003517
3    4         4   7222  30893710  0.003713  0.005505
4    5         5   1928  19845569  0.002535  0.009218
5    6         6  12145        -1  0.000000  0.011753

It seems many edges between the two vertices have the same edge ID=30893710. However, the gid or osm_id that are also available in the table are unique. How can I return these attributes along with the "default" attributes returned by e.g. pgr_dijkstra? Ideally, even the geometry field the_geom?

Here is the code I use:

import sqlalchemy as db
import pandas as pd

engine = db.create_engine('postgresql://postgres:postgres@localhost:5432/mytable')

query = "SELECT * FROM pgr_dijkstra('SELECT osm_id AS id, source, target, cost FROM ways', 7215, 12145);"
res = pd.read_sql_query(sql=query, con=engine)
print(res)
1
  • 2
    double check the oms_id uniqueness in your table... because in OSM, it is not meant to be unique.
    – JGH
    Aug 26 at 15:16
2

OSM data doesn't necessarily have a valid network topology to be usable with pgRouting. Therefor osm2pgouting fixes the network topology if necessary.

It keeps the osm_id for reference, but you shouldn't use this as ID in your shortest path query.

According to your code sample this is your query:

SELECT * FROM pgr_dijkstra('SELECT osm_id AS id, source, target, cost FROM ways', 7215, 12145);"

But you shouldn't use osm_id AS id. You should use the serial ID of your ways table as ID, like:

SELECT * FROM pgr_dijkstra('SELECT gid AS id, source, target, cost FROM ways', 7215, 12145);"
1
  • I see now, it works, thanks!
    – Marc
    Aug 27 at 6:50

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.