bbox struct missing from GeoParquet output despite using COPY TO with spatial extension

[aborruso]

I expected the bounding box column (bbox) to be generated automatically in my GeoParquet output, as described in the Radiant Earth blog and related discussions.

I ran a COPY TO with the spatial extension loaded, using this (simplified for clarity):

LOAD spatial;

COPY (
  SELECT
    ST_Hilbert(geom, (SELECT ST_Extent(ST_Extent_Agg(geom))::BOX_2D FROM st_read('input.gpkg'))) AS hilbert_order,
    *,
    geom  -- renamed from msGeometry
  FROM st_read('input.gpkg')
  ORDER BY hilbert_order
)
TO 'output_dir/' (
  FORMAT PARQUET,
  COMPRESSION 'zstd',
  ROW_GROUP_SIZE 100000,
  PARTITION_BY (some_region_code),
  OVERWRITE_OR_IGNORE
);

I did not manually include ST_Envelope(geom) in the query, because I understood that the bbox column would be created automatically during the export.

After exporting, I ran:

DESCRIBE SELECT * FROM read_parquet('output_dir/**/*.parquet');

And the output shows no bbox or geometry_bbox field — only geom.

Is the bbox column supposed to be created automatically by COPY TO when the spatial extension is loaded? Or must I explicitly compute it with ST_Envelope() in the SELECT clause?

Thank you

[cholmes]

Is the bbox column supposed to be created automatically by COPY TO when the spatial extension is loaded? Or must I explicitly compute it with ST_Envelope() in the SELECT clause?

From a GeoParquet perspective it'd be ideal if it was created automatically (or at least had an option to), if writing GeoParquet 1.1, but it's not currently supported (apologies for the mis-information on that blog, I'll correct it now - I only realized a couple months later that it doesn't actually write the bbox, as I had just happened to be mostly working with files like Overture that include the bbox). The call to create the bbox is a bit non-obvious, as writing out with ST_Envelope() doesn't exactly match the GeoParquet spec. But you can do it with a call like:

(
    SELECT struct_pack(xmin := ddb_box.min_x, ymin := ddb_box.min_y, xmax := ddb_box.max_x, ymax := ddb_box.max_y)
    FROM (SELECT unnest(ST_Extent(<table_name>.<geometry_column>)::BOX_2D)) AS ddb_box
) as bbox 

Note that this doesn't actually add the GeoParquet covering / bbox metadata, so it's not technically spec compliant (but some libraries will take advantage of it).

A couple other notes:

• I believe you don't really need to do hilbert sorting on a geopackage, as they're most always created with a spatial index.
• If you don't need to include hilbert_order as a field, you can just order by it, but leave it out of the select statement.

[aborruso]

From a GeoParquet perspective it'd be ideal if it was created automatically (or at least had an option to), if writing GeoParquet 1.1, but it's not currently supported (apologies for the mis-information on that blog, I'll correct it now - I only realized a couple months later that it doesn't actually write the bbox

Thank you very much, I will include in my resume that one of my notes made you correct one of your texts :)
@ cholmes you are a geo guru, so your texts are indexed by search engines and LLMs, and that note was driving me crazy.

• I believe you don't really need to do hilbert sorting on a geopackage, as they're most always created with a spatial index.

Maybe I'm missing something. The geopackage file is input, and I apply Hilbert to get the geometries sorted in the parquet output. Is that wrong?

Thank you

[aborruso]

The call to create the bbox is a bit non-obvious, as writing out with ST_Envelope() doesn't exactly match the GeoParquet spec. But you can do it with a call like:

(
    SELECT struct_pack(xmin := ddb_box.min_x, ymin := ddb_box.min_y, xmax := ddb_box.max_x, ymax := ddb_box.max_y)
    FROM (SELECT unnest(ST_Extent(<table_name>.<geometry_column>)::BOX_2D)) AS ddb_box
) as bbox 

Thank you @cholmes help me understand. But if I use a source like the one I'm testing, do I have to use a subquery and unnest box_2d, or can I just use ST_XMin, etc.?

COPY (
  SELECT
    geom,
    struct_pack(
      xmin := ST_XMin(geom),
      ymin := ST_YMin(geom),
      xmax := ST_XMax(geom),
      ymax := ST_YMax(geom)
    ) AS 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_with_struct.parquet'
WITH (
  FORMAT PARQUET
);

Thank you

[cholmes]

Maybe I'm missing something. The geopackage file is input, and I apply Hilbert to get the geometries sorted in the parquet output. Is that wrong?

Well it won't hurt things, and it'll certainly guarantee that the data is sorted. It's just that in my experience GeoPackages are already sorted sufficiently, so you don't need to sort them again. The way GDAL/OGR sorts GeoParquet (with SORT_BY_BBOX=YES​ is actually to create a temporary geopackage). In general GeoParquet is quite flexible with how you sort things, it just needs some spatial sort (hilbert, r-tree like geopackage, etc) to then use for stats.

[cholmes]

Thank you @cholmes help me understand. But if I use a source like the one I'm testing, do I have to use a subquery and unnest box_2d, or can I just use ST_XMin, etc.?

Oh yeah, I think that should work. The subquery is more for when you already have a table in DuckDB. I believe you're right, that you can just use ST_XMin. Though now I'm wondering if that should just be the recommended way, as it seems simpler. I got this snipped from a colleague and haven't had the time to dig deeply into it.

Oh, but do note that if you're using pure DuckDB and not writing out the GeoParquet metadata that it'd be better to call it bbox (and not geom_bbox, as at least my code (and maybe others) will make use of the bbox (if you're running it through gdal/ogr at the end then it's fine). I think we could probably add that to compatible parquet spec.