[Tile-serving] [openstreetmap/osm2pgsql] New "flex" backend (#1036)

[mboeringa]

Hi @joto 

A couple of thoughts about a more "flexible" backend that are not directly related to having multiple tables, but other options that might benefit usage:

- It would be nice if there was an option to specify a custom prefix for column names. E.g.:

**<CUSTOM_PREFIX><osm_key_name>**, e.g. "<MY_PREFIX>highway" or "aaa_highway"

Why would I want this? 
There are OpenStreetMap keys that are worth maintaining both as direct input by users, and as a tag converted column in the output table. By allowing to set prefixes for both original data columns and tag converted columns, you could maintain two sets of columns based on a single key, with one containing the original data and the other the tag converted value.

A classic example of a key that is worth maintaining both as an original value and as tag converted one, is the OSM **direction** key. It may contain both angles specified in degrees (0-359) and cardinal directions (NWSE).

You might then have two columns in the output table, one with the original values:
**aaa_direction**
'11'
'N'
'340'
'NE'

And the other a tag converted one:
**bbb_direction**
11
0
340
45

The tag converted column "bbb_direction" could then be used directly in applications to show e.g. the direction of viewpoints, while the original column "aaa_direction" still shows the original user input.

- So, as per above, a separate prefix for tag converted columns would be desirable as well.

- Added benefit of prefixes is that it allows easy detection of OSM key names in the SQL of a style.

- It would also be nice to have a custom replacement string for the OSM colon **":"** namespace separator character in OMS key names. Some applications and data formats do not allow exotic characters like colons in field names. Allowing to set a custom replacement string. would help with application compatibility and conversions to other data formats.

E.g. "addr<CUSTOM_COLON_REPLACEMENT>housenumber".

Of course, you could default to a single underscore, but allowing a custom string potentially allows setting a special marker and converting back to the original key. E.g., you might insert double underscore to signify the position of the colon versus the single underscore also used in openstreetmap keys.

E.g. **"addr__housenumber"** with double underscore could then be easily recognized as being "addr:housenumber" as key. This allows all kind of nice wizardry and automation of conversion between openstreetmap key names and database column names.

- For maximum compatibility, prefixes and a replacement string should be limited to alphanumeric characters and underscore only: a-z / 0-9 / _. And preferably not starting with a numeric character.

-- 
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/1036#issuecomment-577088161
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20200122/22a960bf/attachment.htm>