[Tile-serving] [osm2pgsql-dev/osm2pgsql] Get information of nearby roads / How setup processing for the cycling quality index (Discussion #2146)

[Tobias]

We are thinking about how to migrate the proof of concept of the cycling quality index to an osm2pgsql processing.
- https://www.osm-verkehrswende.org/cqi/
- https://www.osm-verkehrswende.org/cqi/posts/2024-03-23-next/

Here is what the QGIS-Python "proof of concept"-script does…
- derive an infrastructure category for a segment
- derive other categories
- calculate an index based on the infra-category as base with modifies based on the other categories

The result is an index value for the road segment.

This far this could all be done with regular osm2pgsql. BUT, an important information for the infra-category selection is, whether the (cycle)way is parallel to a (car)street. This [can be mapped explicitly](https://wiki.openstreetmap.org/wiki/Proposal:Key:is_sidepath) but seldom is. What @SupaplexOSM does is to probe for highways ever 100 meters and if 2/3 are nearby a (car)highway, attributes of that "parallel" highway are used to change the categorization.

My issue with that is, that in osm2pgsql we cannot switch between "LUA > SQL > back to LUA" (right?).

So ATM I see two options…

a. Osm2pgsql get a magic helper that provides information on parallel highway infrastructure that I can use right in LUA.

b. We need to split the processing and do quite a bit of it in SQL…
   - First so some general categorization in LUA > write to DB
   - Then do the geometric processing in SQL
   - Then do another pass of categorization, but this time in SQL
   - Now do the calculation which also needs to happen in SQL

I really don't like that we have to move so much logic into SQL in scenario **b**. 

Is there a better way to do this?
Is option **a** something that might happen (this year…)?


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

Message ID: <osm2pgsql-dev/osm2pgsql/repo-discussions/2146 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20240324/85130dc9/attachment.htm>