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

[dch0ph]

This does look very promising. 

 **But it will not work with really large lists of nodes, I wouldn't want to do this for all addresses or so.**

Yes, that's an entirely reasonable restriction. 

It would be helpful to work through a concrete example and sketch out a concrete implementation. The key bits of the current spatial join between node and way for turning circles are:

>         (SELECT DISTINCT ON (way)
>            way, type, int_tc_type
>            FROM
>              (SELECT
>                p.way AS way,
>                p.highway AS type,
>                l.highway AS int_tc_type,
>                l.z_order AS z_order
>              FROM planet_osm_point p
>                JOIN planet_osm_line l
>                  ON ST_DWithin(p.way, l.way, 0.1) -- Assumes Mercator
>              WHERE p.highway IN ('turning_circle', 'turning_loop', 'mini_roundabout')
>                AND l.highway IN ('trunk', 'primary', 'secondary', 'tertiary', 'unclassified', 'residential', 'living_street', 'service', 'track')
>                AND l.way && !bbox!
>                AND p.way && !bbox! -- Both conditions are necessary for good index usage, even with the DWithin above
>              ) _
>          ORDER BY
>            way,
>            z_order DESC,
>        ) AS turning_circle_sql

The other cases where we would like to do this all follow this pattern where tag/column values from the node and way are needed to determine the rendering. The sorting by `z_order` ensures that the turning circle colour is determined by the highest priority road, i.e. it handles the case where the node intersects with more than one way. Note that avoiding the spatial join would avoid the hardcoding to Web Mercator.

So, we would:
Create a "turning circle nodes" table[1], with an "id cache". 

As the nodes are read in, turning circles are added to the dedicated TC nodes table NOT `planet_osm_point`.

When processing highway ways, check if nodes are present in TC nodes, and (if yes) add the way_id to a "highways with TCs table" which would have one row per connection. The way would still be added to way tables as normal since this effort is purely about determining the appropriate node rendering. It would be better to work with the way_id rather than fix at import time which tags are needed from the way. 

Can we use the way_id as an index into `planet_osm_line` in the SQL query, or would we need (as implied) to use a post-import SQL script to copy across the relevant tag info into "highways with TCs table"?

The SQL query above would work with the "highways with TCs table" to pull out the information needed above [a bit vague on this point]

I'm also not clear on how the "highway with TCs table" is kept current during updates. 





[1] Or potentially a more general "nodes_to_connect_to_ways" table.

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

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