Get information of nearby roads / How to setup processing for the cycling quality index

[tordans]

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 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?

[pnorman]

Osm2pgsql DBs are not designed to be accessed while they are importing. Lua transforms generally only work if the output of the transform depends only on the object and it's parent/children objects.

It is technically possible to connect to another postgres DB from Lua, but this would not be connecting through osm2pgsql.

[tordans]

Just checking if I get this right, the setup would be roughly …

• have one db with all geometries and tags required for the lookup
• during the main osm2pgsql-run, I do the lookup against that db

Did anyone do something like this already?

Update: In such a setup, I would probably do the geometric processing before while creating that first db, right? And not during the main osm2pgsql-run. So the second step would only need to look up if the preprosessed db has data for that way id that I would want to add to my run.

[pnorman]

I do not recommend connecting to another DB during the import, but it can be done.

[tordans]

Maybe there is a different way to think about this…
Is there a way to modify the input file that osm2pgsql gets before importing?
So instead of doing all the sql-work after osm2pgsql ran I would run some parts before (like the probing for parallel roads), then enrigh the/a input file that osm2pgsql then uses (not knowing some tags where artificially created).

[lonvia]

It can be done but I'd do such pre-processing in-memory. It's almost certainly out-of-scope for osm2pgsql.

[lonvia]

Two-stage processing can in theory do what you want:

• during the first stage you import all the large roads that can be potentially parallel roads. All roads that need a check for such a parallel road, you mark to be processed in the second stage.
• for the second stage, open a connection to the database from lua (look for a lua-dbi module) and when you get the cycleway, query parallel roads before computing the complex quality index.

In practice, you might fail to get the way geometry necessary for the query in the second stage. I don't think there are good functions to access them right now. I also don't know how fast this approach would be.

I'd probably rather go for a post-processing script that runs after import/update to osm2pgsql is completely done. The good news is that you can do almost all the post-processing in, say Python:

• on import/update set a marker in the table where data has been added or changed. That can be some 'needs_indexing' column that always gets set to true in the Lua script.
• create a SELECT query that selects the entries that need indexing and for each computes the potential parallel road returning its properties with the result
• in your Python script go over the results of the SELECT query one by one, make the quality computations, and write the results back using an UPDATE that also resets your marker

You don't need to have any quality-index related code in Lua. Just make sure that the flex script imports all the tags needed for the calculation. (If you encapsulate the actual index calculation in the right way in a Python library, you can even use the same code for the QGIS plugin and the osm2pgsql postprocessing. But I'm getting ahead of myself.)

Both approaches kind-of work for updates. The catch is that you'd usually would want to update the quality measure of your cycle way when the properties of the parallel road change. To cover that case you would need a trigger that marks surrounding cycleways as 'needs update' when a large road changes.

[pnorman]

I suppose the important question is if updates are necessary or if can be done with a reimport. If it's a reimport it's pretty easy write some code that for each linestring finds the parallel roads, computes properties, and UPDATEs the linestring.

[joto]

Osm2pgsql has a framework for generating generalized data from OSM data. It works by first doing the import and then calling osm2pgsql-gen which does various generalization steps. Those can be either just SQL queries, or C++ code reading data from the database, processing it in some way and writing it back to the database. All of this was intended for generalizing data, but there is no reason why this framework could not also be used for other types of "post-processing" like in your case. There is currently no way to run something other than SQL or C++ code but it would certainly be an easily addable option to call external programs (like some Python script) to do part of the processing. Or we can think about adding some functionality to bring the data back into Lua.