Is there a way to get the geoparquet 'geo' metadata from within DuckDB?

[cholmes]

I'm trying to build a little python tool to wrap some DuckDB spatial functionality, and want to get the primary_column or the crs from GeoParquet geo metadata.

But I can't seem to figure out how to extract that information. If I do:
select * from parquet_metadata('my_file.parquet');

I get key_value_metadata column, but all entries are {}. I'm sure these are geoparquet, and I got the metadata working fine with pyarrow. I couldn't find any other suggested route.

thanks!

[cholmes]

Hrm, guess I should have read the other discussions more. Just found #453 with info on the parquet_kv_metadata function. That should likely be sufficient for me.

[Maxxen]

This isn't currently possible. You can override the kv-metadata written by the spatial extension by passing a struct to the KV_METADATA parameter, but if you give that struct a geo key its going to overwrite the geoparquet kv-metadata already written. E.g.

COPY (
    SELECT
        42 AS number,
        true AS is_even
) TO 'kv_metadata.parquet' (
    FORMAT PARQUET,
    KV_METADATA {
        number: 'Answer to life, universe, and everything',
        is_even: 'not ''odd''' -- single quotes in values must be escaped
    }
);

Adding support for specifying CRS/bbox covering columns is planned but most likely won't make it in before next release.

[cholmes]

But could I just read the full geo key and then modify the whole thing, and write it all back in?

[cholmes]

Like get the full 'geo' key's json, then do json manipulation in python to insert crs and bbox covering info, then write that full json back to the geo key?

[Maxxen]

Maybe? But I don't think you can update parquet kv-metadata in-place after the file has been written right?

[cholmes]

Ah, probably not. But my current approach with pyarrow rewrites the file anyways, so maybe I can read it in again with duckdb, add the metadata and write it out. Or is there a way to get the geo key that the geoparquet writer would write out?