Getting node information of a way in OSM2PGSQL data

Question

I have a feeling this is not possible, but I am looking to get information on the nodes which are part of a line in the planet_osm_line table.

The issue which I am trying to handle is I am trying to map out train routes, which I can identify through the "route=train" tag in the planet_osm_line table.

However, I want to identify the train stops. Nodes which indicate a train stop are marked "railway=station" tag in the planet_osm_point table.

I'm looking for a way to associate a train stop, or nodes in general, in the planet_osm_point table to the train route, or any way in general, in the planet_osm_line table.

As a final note, I am working through SQLAlchemy, but I have a feeling that is not important to this particular problem.

Answer 1

You have not written what you want to do with the stops or why you want to know which stop belongs to which route. This might influence what the best solution to your problem is.

The data you need is in the DB. If we look at Relation 1658527 as example you get the routes out of planet_osm_line by looking for osm_id -1658527. The information about the stops (e.g. Node 302347196) does not end up in the standard rendering tables. However, you can access this data by looking at the planet_osm_rels table (if you have imported in slim mode, see also osm2pgsql schema). There id is your relation id and within members you find a list of all relation members. Our node above would be represented as ...,n302347196,stop.... The downside here is that you would have to parse the array within members yourself.

Please note that this data is created by osm2pgsql for applying diffs and is not meant to be accessed that way.

A better solution would be to use the multi backend with Lua transforms to get the information you need. There is an example to get you started or have a look at ClearTables (no route relation processing there though).