Link Tabular Parquet to GeoParquet

[diegoripley]

I looked through the issues and discussions and did not find anything regarding this idea.

I have 7000+ datasets from Canada's Statistical agency (Statistics Canada) called "tables" that I would like to present on a web map. I am currently working on creating the metadata for them as STAC GeoParquet as I have already created optimized Parquet files for each table (see my blog post if interested).

Each table is tied to a geospatial boundary via a unique identifier called DGUID. The challenge is that some of these tables can be quite large (ex. the largest table has 427,009,412 records), and it does not make sense to add the geometries, as these tables are primarily temporal in nature, so you would be repeating the geometry several times for no good reason.

My idea is to provide just enough information in the metadata so that applications like stac-map can use it to link the tabular data to their geographic areas, which I have as GeoParquet, FlatGeoBuf, PMTiles, etc.

I am very motivated into getting people quick and easy access to statistical data (as they are used by the various levels of government to make decisions), so let me know if you need a guinea pig or sample data (I have not made the data publicly available).

Brainstorming Implementation

Since you are using DuckDB-Wasm, could you potentially leverage DuckDB's ST_AsMVT and ST_AsMVTGeom to join the tabular Parquet tables to the GeoParquet boundaries?

[diegoripley]

Here is a sample file that pertains to this Statistics Canada table that discusses "Monthly average retail prices for gasoline and fuel oil, by geography" (at the country level, municipality level, etc).

The sample file is outdated as I processed it last year, but hopefully it helps you understand better.

Other

If you are interested in the script I used to optimize the Statistics Canada tables, you can see it here. It needs to be cleaned up, but it's not bad for 403 lines.

[gadomski]

My idea is to provide just enough information in the metadata so that applications like stac-map can use it to link the tabular data to their geographic areas, which I have as GeoParquet, FlatGeoBuf, PMTiles, etc.

In this case, I think the "STAC/stac-map way" would be to include the vector files as assets, either on a collection or item. Then, once #341 is implemented, stac-map would render the asset on the map.

In short, good idea, and I think #341 should get you most/all of the way there.