Linking between nodes and ways

[dch0ph]

As discussed in the development Roadmap, there is currently limited "linkage" between different geometry types.

This isn't a problem for 95% of rendering, but there are cases where it is necessary to cross-reference between a node and the way(s) it is part of. Currently, for example, the turning circle / loop / mini-roundabout rendering in OSM Carto requires a relatively expensive and inelegant spatial join (ST_DWithin) to (re)determine the types of highways that a turning circle is connected to and then render with the correct colour etc. There are other places where we would like to do similar things, e.g. resolve problems with level crossings where the starting zoom level should be adjusted based on the type of railway it attached to, adjust station rendering depending on railway type etc.

As I understand, "linkage" between ways and multipolygons is achieved with callback functions so that relevant tags from the MP can be attached to constituent ways. You can imagine a similar mechanism for nodes / ways, where specific node types register a callback for further tag processing. That said, I don't think this callback mechanism is the most flexible / elegant solution, not least because you would need to make decisions about combining tags from multiple ways at import time.

I think the most flexible approach would be an explicit "node/way correlation table" with a row for every "connection of interest" e.g. turning circles, level crossings, railway stations etc. You would probably need to add primary key (serial-like type) to the node and way tables, rather than use the OSM ID as key/index. But having done this, the spatial joins would be simply replaced by normal SQL joins.

In the longer term, I'm sure there would be other applications of linking ways and nodes e.g. site-like relations where you would then know which nodes were contained within which bounding area.

[There was some related, but unresolved discussion in #2311.]

[joto]

explicit "node/way correlation table" with a row for every "connection of interest"

And that's something you can already do with osm2pgsql, just not very efficiently. You can create one table with, say, all nodes tagged with level_crossing and another table with all ways tagged railway and a third with all ways tagged highway. You have the list of nodes referenced from a way available in Lua and you can use that to write a table that connects all way node members with all ways. Problem is: This table contains all nodes referenced from highway/railway ways, not just the ones connecting a "level crossing node" to a "highway/railway way", so this table is huge.

The problem for osm2pgsql is: It can only see one object at a time. For a node it imports it can know whether it contains a level_crossing tag, not whether it is part of a highway/railway. For a way it can know whether it has a highway or railway tag, but not whether one of the nodes it uses has a level_crossing tag. This information is simply not available due to the streaming processing. The two-stage processing you mentioned is a way around that, but it currently only works for relations and their ways members. And it is unintuitive and hard to use.

A way to solve this with current osm2pgsql is to just import that huge table as described above and then run an SQL query after the import removing all the node/way combinations that are no interesting. Not great either, but it would work. An alternative would be to add a trigger to that table connection nodes with ways that will check whether a node added has a level_crossing tag and only then insert it, ignoring all other inserts. That way you can avoid creating the huge table, but need to write and maintain the trigger.

And another possibility: When you have an updatable database, ie. in slim mode, you aready have the list of member nodes for all ways available in the database. So you can run an SQL query after import that finds all ways having a level_crossing node in them and add some data from the ways to the nodes row.

The way to think about this is: Osm2pgsql can only work on one object at a time, but after the import (or update) you do have a database with all sorts of clever functions in it. So you can prepare the data in osm2pgsql in a useful way but then in a second step
use the power of the database to connect all sorts of data. Where this becomes tricky is with updates, it is not always easy to make this work correctly with updates.

So this is already something that can arguably be solved with current osm2pgsql and some basic SQL and doesn't necessarily need spatial joins or other complex spatial queries. A way to make this easier could be to add some functionality in osm2pgsql that would allow you to query member nodes from Lua when importing ways. But that adds a huge overhead because of the many database queries needed, not a great solution either, but certainly something we are considering.

[dch0ph]

A way to solve this with current osm2pgsql is to just import that huge table as described above and then run an SQL query after the import removing all the node/way combinations that are no interesting. Not great either, but it would work.

At that point, it would probably be easier run the existing SQL query on the final database. But this would still need to be done smartly, as a global spatial join would be a performance disaster (the existing SQL query carefully first narrows things down to nodes and ways inside a given (meta)tile).

The problem for osm2pgsql is: It can only see one object at a time. For a node it imports it can know whether it contains a level_crossing tag, not whether it is part of a highway/railway. For a way it can know whether it has a highway or railway tag, but not whether one of the nodes it uses has a level_crossing tag. This information is simply not available due to the streaming processing.

I must have an incorrect model of how the processing works. In my mind:

The nodes are first read in. Lua functions are run per node to determine which tags are being imported and to insert into appropriate table(s). The tags would be checked and a "mark" function called for nodes of interest, e.g. level_crossing.

The ways are then read in. These contain a list of node references. These could be internally checked against the limited list of "nodes of interest". A reference / ID number for both the node and the way could then be added to the connections table when the way is added. This could optionally involve a callback to check the tags on the way (e.g. only make connections for railway) or the table could contain all ways containing "nodes of interest".

Or have I misunderstood?

Osm2pgsql can only work on one object at a time, but after the import (or update) you do have a database with all sorts of clever functions in it. So you can prepare the data in osm2pgsql in a useful way but then in a second step
use the power of the database to connect all sorts of data. Where this becomes tricky is with updates, it is not always easy to make this work correctly with updates.

Without making internal connections between nodes and ways, I doubt that trying to do this in SQL post-processing will be viable. You will need a spatial join to make connection, which risks adding significant overhead. In the end, having a per-tile spatial join at say 10 different zoom levels during on demand rendering is probably a safer (if less efficient) option than adding unpredictable overhead in the import/update.

You could imagine cleaning up a connections table to remove uninteresting way/nodes in post-processing. The key thing is to have limited it to a small subset of nodes.

Where this becomes tricky is with updates, it is not always easy to make this work correctly with updates.

Indeed. This is why it would be best to have an internal mechanism to ensure everything is self-consistent. I think updates would work with a node/way connection table - you would delete rows when node or ways were deleted. But there might be sequencing issues I don't understand.