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)
Best Answer
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:
But you shouldn't use
osm_id AS id
. You should use the serial ID of yourways
table as ID, like: