Question about Parquet statistics for STRUCT subfields (geom_bbox) in DuckDB Spatial

[aborruso]

Hi all,

I’m quite new to DuckDB and DuckDB Spatial, so I might be misunderstanding something. I’ve been experimenting with creating a partitioned GeoParquet dataset and noticed something about statistics and predicate pushdown.

I started from a large national dataset (~86M rows) with a geom column and a geom_bbox column of type:

STRUCT(xmin FLOAT, ymin FLOAT, xmax FLOAT, ymax FLOAT)

The files were written with:

COPY (
  SELECT * EXCLUDE (hkey)
  FROM ple_ordered
)
TO '/path/to/output'
(
  FORMAT PARQUET,
  COMPRESSION 'zstd',
  ROW_GROUP_SIZE 100000,
  PARTITION_BY (COD_REG, COD_UTS),
  PER_THREAD_OUTPUT FALSE
);

When I inspected the Parquet metadata using:

SELECT DISTINCT path_in_schema
FROM parquet_metadata('.../**/*.parquet')
WHERE path_in_schema LIKE 'geom_bbox.%';

I saw no min/max statistics for the geom_bbox subfields. As a result, spatial filters like:

WHERE geom_bbox.xmax >= 12 AND geom_bbox.xmin <= 13
  AND geom_bbox.ymax >= 41 AND geom_bbox.ymin <= 42

would still read all files/row groups.

---

What I tried

I then flattened the bounding box into 4 top-level FLOAT columns: minx, miny, maxx, maxy.
With this schema, the Parquet metadata does contain min/max stats for each column, and predicate pushdown works: only relevant row groups are read, and queries are much faster.

Example metadata check:

SELECT path_in_schema, rg_con_stats
FROM parquet_metadata('.../**/*.parquet')
WHERE path_in_schema IN ('minx','miny','maxx','maxy');

rg_con_stats equals the total number of row groups, confirming stats are present.

---

My question

Is it expected that DuckDB does not write statistics for STRUCT subfields in Parquet?
Am I missing an option or a different way to preserve stats for geom_bbox subfields when writing?

For GeoParquet datasets, having the bbox as a STRUCT is semantically nice, but for efficient spatial filtering it seems I need to flatten it.

I'm using ddb v1.3.0 71c5c07cdd and 7ab1710 spatial extension.

Thanks for any clarification, and sorry if I’m overlooking something obvious.

[cholmes]

Is it expected that DuckDB does not write statistics for STRUCT subfields in Parquet?

It should write the stats. @Maxxen sounded in when we were first considering adding the bbox column, but then he implemented, you can see the results in this comment

Am I missing an option or a different way to preserve stats for geom_bbox subfields when writing?

I think it's supposed to 'just work'? Can you share how you determined that it wasn't reading all the rows?

[Maxxen]

Hello!
Yes, this is supposed to "just work", but there was a regression between v1.2.0 and v1.3.0 where DuckDB would not write stats for floating point columns in general, but that was fixed in duckdb/duckdb#17883. Do you still observe the same behavior if you upgrade to DuckDB v1.3.1?

[Maxxen]

This seems to work for me:

pragma version;
┌─────────────────┬────────────┬─────────────────────┐
│ library_version │ source_id  │      codename       │
│     varchar     │  varchar   │       varchar       │
├─────────────────┼────────────┼─────────────────────┤
│ v1.4.0-dev2413  │ ff0f95954b │ Development Version │
└─────────────────┴────────────┴─────────────────────┘


copy (select geom, st_extent(geom) from geonames) to 'geonames.parquet';

SELECT DISTINCT path_in_schema FROM parquet_metadata('geonames.parquet') WHERE path_in_schema LIKE 'geom%';

┌──────────────────┐
│  path_in_schema  │
│     varchar      │
├──────────────────┤
│ geom             │
│ geom_bbox, min_x │
│ geom_bbox, min_y │
│ geom_bbox, max_y │
│ geom_bbox, max_x │
└──────────────────┘

[aborruso]

Thank you very much @Maxxen

I was really unlucky in my first real test with partitioned geoparquet files:

• I discovered a small error in a post by @cholmes
• I used a version of duckdb with a major regression.

In the meantime, I ran various types of tests because I didn't understand if I was doing something wrong, since I'm not very good at this, so I built and destroyed things.

But now, thanks to you, everything should be working. I hope I'll be able to write a good README so that I'll have a guide to use when I need it again.

Thank you, you're fantastic.

[aborruso]

Hi @Maxxen please help me understand.

I updated to version 1.3.2 of duckdb. And I created a sample output file like this

INSTALL httpfs;
LOAD httpfs;

INSTALL spatial;
LOAD spatial;

-- Configura l'endpoint per Source Cooperative
SET s3_endpoint = 'data.source.coop';
SET s3_use_ssl = true;
SET s3_url_style = 'path';

-- Esporta le geometrie e le loro singole estensioni in un file Parquet
COPY (
  SELECT
    geom,
    ST_Envelope(geom) AS extent
  FROM read_parquet(
    's3://ondata/cadastral-italy-geospatial-data/italy_optimized_bbox/**/*.parquet'
  )
  WHERE
    COD_REG = '19'
    AND COD_UTS = '282'
    AND PRO_COM_T = '082075'
) TO 'tmp/ustica_individual_extents.parquet'
WITH (
  FORMAT PARQUET
);

If I run this on it

SELECT DISTINCT path_in_schema FROM parquet_metadata('tmp/ustica_individual_extents.parquet') WHERE path_in_schema LIKE 'geom%';

I have

┌──────────────────┐
│  path_in_schema  │
│     varchar      │
├──────────────────┤
│ geom             │
└──────────────────┘

So with version 1.4, the struct geom_bbox field will be generated automatically, but with 1.3.2 it won't?

However, with 1.3.2, struct fields with floats are correctly indexed, so can I use a struct field and not have the 4 fields mix miny maxx and maxY hard coded?

Thank you

[Maxxen]

So in your example you:

1. Used the wrong function. ST_Envelope returns a GEOMETRY (blob), but ST_Extent returns a BOX_2D (struct)
2. Gave the box the wrong alias, you called it "extent", which is why it's not showing up when you search for it in the stats.

Here's the fixed sample:

INSTALL httpfs;
LOAD httpfs;

INSTALL spatial;
LOAD spatial;

-- Configura l'endpoint per Source Cooperative
SET s3_endpoint = 'data.source.coop';
SET s3_use_ssl = true;
SET s3_url_style = 'path';

-- Esporta le geometrie e le loro singole estensioni in un file Parquet
COPY (
  SELECT
    geom,
    ST_Extent(geom) AS geom_bbox -- Use ST_Extent, and name it "geom_bbox"
  FROM read_parquet(
    's3://ondata/cadastral-italy-geospatial-data/italy_optimized_bbox/**/*.parquet'
  )
  WHERE
    COD_REG = '19'
    AND COD_UTS = '282'
    AND PRO_COM_T = '082075'
) TO 'tmp/ustica_individual_extents.parquet'
WITH (
  FORMAT PARQUET
);

-- Now query the stats
SELECT DISTINCT path_in_schema FROM parquet_metadata('ustica_individual_extents.parquet') WHERE path_in_schema LIKE 'geom%';
┌──────────────────┐
│  path_in_schema  │
│     varchar      │
├──────────────────┤
│ geom_bbox, min_x │
│ geom_bbox, min_y │
│ geom_bbox, max_x │
│ geom_bbox, max_y │
│ geom             │
└──────────────────┘

Note that this still won't write the geom_box column into the extra geoparquet-specific metadata, because thats not implemented in DuckDB yet. But it will write it in the general "parquet statistics" which does allow you to filter efficiently.

[aborruso]

Note that this still won't write the geom_box column into the extra geoparquet-specific metadata, because thats not implemented in DuckDB yet. But it will write it in the general "parquet statistics" which does allow you to filter efficiently.

Thank you very much e I apologize for my errors.

I will try e to rewrite each output partitioned parquet file using ogr2ogr, as @cholmes told me. It should add the geoparquet-specific metadata.

Thank you again

[Maxxen]

Ah, I think it's your LIKE predicate thats different. If you change 'geom_bbox.% to 'geom_bbox%' you should get the correct output from inspecting the stats. DuckDB prints the nested stats with a comma and space (', '), not a dot ('.')

SELECT DISTINCT path_in_schema
  FROM parquet_metadata('geonames.parquet')
  WHERE path_in_schema LIKE 'geom_bbox.%';
┌────────────────┐
│ path_in_schema │
│    varchar     │
├────────────────┤
│     0 rows     │
└────────────────┘

SELECT DISTINCT path_in_schema
  FROM parquet_metadata('geonames.parquet')
  WHERE path_in_schema LIKE 'geom_bbox%';
┌──────────────────┐
│  path_in_schema  │
│     varchar      │
├──────────────────┤
│ geom_bbox, max_x │
│ geom_bbox, max_y │
│ geom_bbox, min_x │
│ geom_bbox, min_y │
└──────────────────┘