[Tile-serving] [osm2pgsql-dev/osm2pgsql] Linking between nodes and ways (Discussion #2455)

[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.



-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2455#discussioncomment-16053774
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/repo-discussions/2455/comments/16053774 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20260309/852cefd4/attachment.htm>