Check intersection with preprocessed (area) dataset

[tordans]

For radverkehrsatlas.de we have a few usecases where we would like to add attributes to our ways/POI whenever they are within a pre defined / preprocessed area.

Use case:

• preprocess areas that represent residential areas and set a default speed limit to 50 if nothing more precise is present
• preprocess areas where biking is prohibited like cemeteries, industrial areas, allotments … and set a default bicycle-access if nothing more precise is present
• preprocess areas that represent residential areas and set a different assumed oneway-tag for bike path without explicit oneway (in cities oneway=yes, in rural areas oneway=no)

I wonder, if this could be something that comes to osm2pgsql in some generic form.
I could see us running to osm2pgsql runs in sequence:

1. a osm2pgsql run to extract and sanitize the areas
2. a sql run to buffer and merge the areas as needed
3. another osm2pgsql run which then can look at those tables from 1+2

The check in run 3 could be different things

• "is point in area"
• "is way fully in area"
• "is way mostly in area, like 80%"
• "is way in area, regardless how much of the way"

Ideally, part of step 2 could also be done in osm2pgsql by re-using the tools that the generalization already has (AFAIK), like merging areas that are close into one shape, which the generalization does for woods and such, right? This could work nicely for cases where the areas are split into sections (which is why (2) would have to buffer and merge).

[tordans]

A extension for this feature could be, to also count and sum stuff in other tables inside an osm2pgsql-run.

The setup could be very similar to what is described above. The main difference is, that here the osm2pgsql-run in step 3 would ideally represent the area which then counts points or lines in a different table.

The UseCases are more in the area of statistics:

• I have a table of Mapillary picture points and want to add properties to my way. The most comprehensive lookup would be "number of Mapillary foto points that are junger than 2 years (created at) and panoramas (pano:true) and inside a buffer of the given way"

• Have a boundaries table and add properties to it based on the length of certain ways in a different table.

[joto]

I don't think having two osm2pgsql runs is the way to go for something like this. It would mean having two configurations that are tightly integrated but separate. And you can not do updates any more. As you mention the osm2pgsql generalization framework is probably better suited for this kind of work.

For the "default values" stuff you describe I'd just import any available maxspeed data etc. into a column which is left as NULL if there is no specific data. Then use the run_sql() function of the generalization framework to run some SQL that finds all rows with a NULL in that column and fill it from some other data in the database. That data can come from anything, including having run any of the generalizer functions. It gets a bit tricky of you want to use updates. If you are happy with changed or new ways to get the correct maxspeed (or whatever) it will just work. But if, say, an industrial area is added and you need to recompute the default value for the ways in that area, you need to reset the values of all those ways to NULL first. But this is something that could be done.

What osm2pgsql is lacking at the moment is some "make"-like dependency graph where you can say: If this thing changes you have to recompute that other thing. At some point it would be great to have something like this, but at the moment you have to carefully sequence the generalization steps, so that they do the right thing.

I am not sure about the buffer-and-merge thing you are describing, because you loose detail that way. That might or might not be what you want. But you could certainly use the output of a generalization step for that. Because that generalization happens in (possibly overlapping) tiles, you just have to make sure that doesn't mess up your processing somehow.

Creating those statistics should also be no problem. You have all the power of the database at your disposal. I'd use a similar approach as described above, always writing NULL into some column and then updating that. Or you can just create some tables that are not filled by osm2pgsql and fill them with statistic data. You can even use the per-tile SQL strategy of the generalizer to update stats inside a tile whenever something in there changes.