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.

[joto]

I did some experiments over the last month with different kinds of queries joining "nodes" with "ways" in various way in the database. Either through ids using a "nodes" array, special join tables connecting node_ids with way_ids or using the spatial indexes. This in various combinations of using the middle tables or creating special tables for this use case and so on. All the options were quite slow. Interestingly enough, the spatial queries worked somewhat better than going through ids.

So I believe we currently have no good way of handling this use case, at least for databases that are updated often. Running joins like this on a database that needs half a day to import and then lives for a long time isn't a huge problem, because it doesn't add that much extra processing time, but if we want minutely updates, the current situation is clearly not great.

So I thought about what kind of functionality we could add to osm2pgsql to help with this use case. Here is the idea:

• Add an optional in-memory "id cache" to node tables. This keeps track of all nodes added to a table in the process_node() function.
• In the process_way() function you can check this cache quickly and do extra processing based on this information.
• In append mode the cache is initialized after nodes are imported with the content of the database table.

Here is an example to make this clearer:

• You have all node barriers like gates etc. stored in a "barriers" table with the id cache.
• When processing ways that have a "highway" tag, you check all member nodes of the highway whether they are in the cache, if yes, it means you have a barrier on the road and you add them to a special "barriers_in_highways" table which includes at least the node_id and the way_id, but can include more information such as the geometry of the node and/or way, highway type, etc. You can not add the "barrier" type at this point because you don't have that information.
• After osm2pgsql runs you can use osm2pgsql-gen (or just run an sql script) to add extra information to the "barriers_in_highways" table. For instance to add the barrier type you just need a field that's NULL and that you fill with the corresponding information from the "barriers" table. Once it is set, it isn't NULL any more, so you don't have to do this over and over.

In this example it can happen that a barrier node is in more than one highway way. At render time you can choose, for instance, only to render the barrier on the highest road class or something like that. That kind of processing is cheap, because there is no join involved any more and you only have very few entries in your bounding box in the barriers_on_highways table.

I have some proof-of-concept code that makes all of this happen and it looks reasonable to me so far. This solution has the advantage that it doesn't add much memory overhead (as long as the table cached isn't too large) and is reasonably easy to understand (much easier than two-stage processing) and works okay with updates. But it will not work with really large lists of nodes, I wouldn't want to do this for all addresses or so. We'll have to see in practice how far we can stretch this.

Does this sound like a reasonable approach? Can you see any problems with this? Does it address the use cases you have?

[joto]

The code is now here: #2460