Consider flattening table schemas

[jake-low]

Currently, all Layercake tables have the following schema:

column_name	column_type
type	VARCHAR
id	BIGINT
tags	STRUCT(...)
bbox	STRUCT(xmin FLOAT, ymin FLOAT, xmax FLOAT, ymax FLOAT)
geometry	GEOMETRY

The tags field is the only thing that varies between layers; it is a struct and contains all of the layer-specific columns as fields.

I am considering flattening the contents of the tags column. This would for example mean that the new settlements layer (added in #19) would have the following schema:

column_name	column_type
type	VARCHAR
id	BIGINT
place	VARCHAR
name	VARCHAR
names	MAP(VARCHAR, VARCHAR)
alt_name	VARCHAR
alt_names	MAP(VARCHAR, VARCHAR)
official_name	VARCHAR
official_names	MAP(VARCHAR, VARCHAR)
wikidata	VARCHAR
wikipedia	VARCHAR
population	UBIGINT
bbox	STRUCT(xmin FLOAT, ymin FLOAT, xmax FLOAT, ymax FLOAT)
geometry	GEOMETRY

Advantages:

• Flatter schemas are more familiar to most people (SQL tables, Excel spreadsheets, CSV files, etc are typically flat) and easier to visualize (a two-dimensional data table is easy to show on paper or on a screen).
• Flatter schemas are also more straightforward to convert to other formats (e.g. export from Layercake data to Shapefile or CSV), since column remapping isn't necessary.
• DuckDB's describe table and the gpq describe output are more useful. Currently, both commands output a table that looks much like the first table above (i.e. most of the interesting bits are hidden).
• Many queries become simpler (e.g. where name = 'New York' instead of where tags.name = 'New York').
• Quoting tag names that have special characters (most commonly :) is easier. Is it tags."building:levels" or "tags.building:levels"? No need to remember anymore, just quote the whole name: "building:levels".
• tags is a bad name for the struct column since it is no longer a strict 1:1 representation of a feature's OSM tags (see discussion in Support more fine-grained schema definition (non-string types) #7); columns or attributes would be less wrong, but bland; flattening the struct sidesteps having to name it.

Disadvantages:

• type, id, bbox, and geometry columns can potentially collide with tag keys.
• Breaks backwards compatibility for existing Layercake users.

I think the advantages outweigh the disadvantages, but I'd like to hear from current users. Please comment if you have an opinion or alternative suggestions.

Note: This should have no effect on dataset sizes; under the hood struct fields in Parquet files get flattened anyways (each individual field becomes its own column and is stored in a contiguous block within each row group). So this is mainly about ergonomics.

[ianthetechie]

type, id, bbox, and geometry columns can potentially collide with tag keys.

This is the only drawback I see. We actually do need this for relation type IIRC. I don't have an issue changing this though to relation_type or something.

I think you're correct overall and I'm in favor of a change. The schema is not formally documented anywhere and I think the disclaimers are clear that we may change things as it's still very experimental :)

[brawer]

Has the opposite been considered, ie. to preserve all original OSM tags in a map-valued column, without any filtering or renaming? This would be useful for jobs that match thematic OSM excerpts against other data, for example POIs in OSM (once layercake generates a POI extract) against scraped POIs in AllThePlaces. Preserving the original tags from OSM might also be useful for other purposes — it seems difficult to predict which specific tags are useful for potential clients without knowing all potential use cases, so couldn't we keep all tags?

[ianthetechie]

Has the opposite been considered, ie. to preserve all original OSM tags in a map-valued column, without any filtering or renaming?

Good question @brawer! This would definitely increase the dataset size, and wouldn't be nearly as efficient when querying (I think these don't get predicate pushdown like a struct). But if you exclude the column in your query, then maybe it's less of an issue for consumers that don't need it?

Some other considerations:

• Should it be all_tags or other_tags? I'd lean towards "other" but that's easy to mess up when programming writers ;) The counter-argument would be that you might really want a non-normalized value like a negative population number I guess.
• Are there specific fields you can think of that would enable this conflation? I think we should try to bring over most IDs like wiki(pedia|data), plus the original OSM ID of course. I'm not really sure what else you'd need that wouldn't be useful as a top level column already, but I'm probably missing something.

[jake-low]

Has the opposite been considered, ie. to preserve all original OSM tags in a map-valued column, without any filtering or renaming? -- @brawer

I hadn't explicitly considered this, so thanks for bringing it up. I've been thinking about it over the last couple of days.

I think @ianthetechie is right that Map columns in Parquet files can't be efficiently queried (unlike Struct columns which have a fixed set of fields, each of which is basically its own ordinary column under the hood). This doesn't mean they aren't useful, and we do use Map columns already for multilingual names (for example Map<str, str> { "en" => "London", "es" => "Londres", "zh" => "伦敦" }). But it does somewhat limit their usefulness, since filtering by one of the key-value pairs in the Map requires a full table scan.

[Some data consumers] might really want a non-normalized value like a negative population number I guess. -- @ianthetechie

My gut feeling is that this specifically is out of scope of Layercake's project goals. The only use case I can think of for preserving tags that are malformed (like population=-20 i.e. negative number) is for QA checks on OSM data, and I think that anyone who wants to run such checks is better served by using the raw OSM PBF planet file (or, if a spatial index and fast random access is needed, then OSMExpress).

This does give me an idea though: if we implement a rejects file (see #9), we can emit rows into that file for elements whose geometry is invalid, or whose tags can't be parsed to the expected column types.

Anyways, thanks for making this suggestion. I'll keep it in mind.