pgRouting – How to Retrieve Other Edge Attributes Using Python

osm2pgroutingpgroutingpostgispython

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:

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);"
Related Question