Extracting geometry column from GeoParquet file streams the entire remote file

[glaroc]

Hi, I'm having a weird issue with a particular GeoParquet that I've created of protected areas of the world. Any time I try to get the geometry column through a query, it appears to stream the entire file, which is over 1GB.

LOAD SPATIAL;
CREATE OR REPLACE VIEW wdpa AS SELECT * FROM read_parquet("https://object-arbutus.cloud.computecanada.ca/bq-io/vectors-cloud/wdpa/wdpa.parquet");
CREATE OR REPLACE TABLE region AS SELECT * FROM 'https://data.fieldmaps.io/edge-matched/humanitarian/intl/adm1_polygons.parquet' WHERE adm0_src='CAN' and adm1_name='Quebec';`
CREATE OR REPLACE TABLE wdpa_region AS (WITH reg AS (
          SELECT
              geometry_bbox.xmin AS xmin_r,
              geometry_bbox.ymin AS ymin_r,
              geometry_bbox.xmax AS xmax_r,
              geometry_bbox.ymax AS ymax_r
          FROM region
      )
      SELECT w.*
      FROM wdpa w, reg r
      WHERE 
          bbox.xmax >= r.xmin_r AND
          bbox.xmin <= r.xmax_r AND
          bbox.ymin <= r.ymax_r AND
          bbox.ymax >= r.ymin_r);

This query should select 7487 out of 296046 polygons. If I SELECT SITE_ID, the query is very fast. If I SELECT w.* or SELECT geometry, then it is very slow and it appears that it is streaming well over 1GB of data, which is about the size of the WDPA parquet file. Any idea why that would be ?

[glaroc]

After quite a bit of investigating, my understanding is that the issue is with the row group size. This is a polygon file with many very small and some very large polygons. Setting a row group size of 100 000 or more means that DuckDB will compress many rows of the geometry column together into large chunks when creating the GeoParquet. So, when you run a query that fetches polygons from multiple row groups, you essentially need to download the entire parquet and uncompress the row group geometries to get the result. I re-created the parquet with a row group size of 2500 instead of the default, and queries that return the geometry column now run much faster. Maybe this issue should be considered when creating GeoParquet from line or polygon layers that contain large geometry blobs.