Aggregating Data¶

The process aggregate commands reduce large GeoParquet datasets into compact summary files — one row per spatial bucket — suitable for low-zoom visualization, dashboards, and reporting.

When to use it: You have millions of features (fields, buildings, parcels) and want to display them at country or grid-cell scale without transmitting all the raw geometry. Aggregation gives you a small file where every cell or region carries a count, optional numeric rollups (sum, avg, min, max), and optional per-category breakdowns.

Three bucketing schemes are available:

Command	Buckets	Bucket id column
`gpio process aggregate a5`	Equal-area A5 grid cells	`a5_cell` (UBIGINT)
`gpio process aggregate h3`	H3 hexagonal grid cells	`h3_cell` (string)
`gpio process aggregate admin`	Administrative regions (Overture Maps)	`admin_code` + `admin_name`

Every output row carries the bucket id regardless of the --out-geometry setting, so a --out-geometry none table can be re-joined to geometry later.

Choosing What to Aggregate¶

Each output cell or region can carry three kinds of statistics. Pick the ones that answer the question you want the map to show — you can combine all three in one run.

Column	Question it answers	Reach for it when
`count` (always present)	How much data is here?	Show density — where features are concentrated vs. sparse.
`--metric func:column`	What's the total or typical value here?	Shade cells by a numeric attribute (area, population, price).
`--breakdown column`	What's the mix of categories here?	Toggle/filter classes (crop types) or animate over time (year).

Which metric function to use¶

--metric takes comma-separated func:column pairs. A bare column name is shorthand for sum.

Function	Per-cell result	Use when the quantity is…	Example
`sum`	Total across features in the cell	additive — area, population, revenue	`sum:area_ha` → total hectares of fields in the cell
`avg`	Mean across features	a typical value/intensity, independent of how many features	`avg:yield` → average yield per field
`min` / `max`	Smallest / largest value	an extreme or range — oldest, newest, cheapest, peak	`max:price`, `min:built_year`
`pct_cell`	Percent of the cell's own area covered	a share of the landscape — cropland cover, built-up fraction	`pct_cell:metrics:area` → `pct_metrics:area`, percent of the cell under fields

Rules of thumb:

sum scales with count — dense cells naturally get large sums. Use it for "total stuff here," and keep count alongside to interpret it.
avg does not scale with count — it isolates size/intensity, so a cell with 5 large fields is comparable to one with 5 000 small fields. Best for "typical value" maps.
min/max surface outliers; pair min:year with max:year to show the span of values in a cell.
Don't request count via --metric — every output row already includes a count column automatically (COUNT(*) per bucket). For per-category counts see Which breakdown to use.
The column must already exist and be numeric. To aggregate a geometry-derived value like area, compute it first with gpio add geometry-metrics (writes metrics:area in m²), then --metric "sum:metrics:area".
NoData sentinels skew every metric. Real-world numeric columns frequently encode "no value" as a sentinel like -999 or -9999 rather than SQL NULL (e.g. GlobalBuildingAtlas heights use -999). Fed to --metric directly, the sentinel drags avg/sum toward garbage and min reports the sentinel itself. Pass --metric-nodata "-999" (comma-separate multiple sentinels) to map them to NULL before aggregation — sum/avg/min/max then ignore those values while count still counts every feature. nan is accepted for NaN-encoded nodata, and sentinels are compared at the metric column's own precision, so the classic float32 nodata -3.4028235e+38 matches REAL (float32) columns correctly. Sentinels apply only to numeric metric columns.

CLIPython

gpio process aggregate a5 buildings.parquet cells.parquet --auto \
    --metric "avg:height,max:height" --metric-nodata "-999"

import geoparquet_io as gpio

result = gpio.read('buildings.parquet').aggregate_a5(
    resolution=10,
    metric="avg:height,max:height",
    metric_nodata="-999",
)
result.write('cells.parquet')

Percent of the cell covered (`pct_cell`)¶

"How much of each cell is covered?" is the map most polygon layers are aggregated for, and it is the one metric you cannot build from sum alone, because it needs the cell's area. pct_cell:<column> supplies that denominator: <column> holds each feature's area in square metres, and the output column pct_<column> is 100 × Σ column ÷ cell area.

Compute the per-feature area first with gpio add geometry-metrics (it writes metrics:area in m²), then:

CLIPython

gpio process aggregate a5 fields.parquet coverage.parquet \
    --resolution 7 --metric "pct_cell:metrics:area"

import geoparquet_io as gpio

result = gpio.read('fields.parquet').aggregate_a5(
    resolution=7,
    metric="pct_cell:metrics:area",
)
result.write('coverage.parquet')

Where the denominator comes from:

A5 is an equal-area grid, so every cell at a resolution covers the same number of square metres and the denominator is one constant per resolution — 33,207 km² at r5, 2,075.5 km² at r7, 518.9 km² at r8. gpio takes it from the grid itself (a5_cell_area), so there is nothing to configure and nothing to measure.
H3 is not equal-area — a cell near a pole is about a quarter smaller than one at the equator — so each cell is divided by its own measured area (h3_cell_area). Two cells with the same total area therefore report different percentages, which is the correct answer. Note that h3_cell_area measures on H3's sphere while gpio add geometry-metrics measures features on the WGS84 ellipsoid, so h3 percentages carry a latitude-dependent error of up to about one percentage point (#1192); a5 is unaffected, because its cell area and the feature areas share an earth model.
Admin regions have no cell area, so pct_cell is refused for gpio process aggregate admin. Use sum:<column> and divide by the region's own area downstream.

Two things to know about the numbers:

sum_<column> comes with it. Requesting pct_cell:x also writes sum_x, whether or not you asked for it. That total is what gpio process overview recomputes a coarser cell's percentage from; without it a percentage cannot be rolled up at all, because averaging fine cells' percentages is not the coarse cell's percentage.
A value can exceed 100, and by a lot if the resolution is wrong. Every feature is assigned wholly to the cell its keying point falls in, so a field straddling a cell boundary contributes its full area to one cell and none to the other, and two overlapping features each contribute their full area. Pick a resolution whose cells are comfortably larger than the features and the overshoot stays small — read a value just over 100 as "fully covered". Pick one whose cells are smaller than the features and the number stops meaning anything: a 1,100 km² polygon keyed into a 3.8 km² h3 r7 cell reports about 57,000. A result far above 100 is the signal that the resolution is too fine for the data, not that the cell is very full.

Which breakdown to use¶

--breakdown pivots one categorical (or time-bucket) column into a count_<value> column per value — the building block for filterable or animated maps:

A class column (crop_type) → count_wheat, count_corn, … that a frontend can toggle on/off.
A year column (harvest_year) → count_2021, count_2022, … driving a time slider.

Values beyond --breakdown-limit (default 20, ordered by frequency) collapse into count_other, so a high-cardinality column won't explode the schema.

Each pivot holds a count by default. --breakdown-metric makes it hold a magnitude instead: --breakdown month --breakdown-metric "sum:peak" writes sum_peak_202605, sum_peak_202606, … each the total peak for that month, in place of the count_<value> columns. sum, min and max are available; avg is not, because a per-category mean cannot be rolled up to a coarser overview level without the category's own count, which the output does not carry (--metric avg:<column> still gives a per-cell mean). A category with no rows in a cell, or only NULL values, reads NULL, as --metric sum: does for the same cell.

A5 Grid Aggregation¶

Aggregate into A5 equal-area hexagonal grid cells. A5 cells at lower resolutions cover large areas (useful for global overviews) while higher resolutions approach parcel level.

Resolution¶

Specify a resolution explicitly or let gpio choose one automatically:

CLIPython

# Explicit resolution
gpio process aggregate a5 fields.parquet cells.parquet --resolution 8

# Auto-select resolution (~10 000 features per cell, up to 500 000 cells)
gpio process aggregate a5 fields.parquet cells.parquet --auto

# Auto with custom target
gpio process aggregate a5 fields.parquet cells.parquet \
    --auto --target-per-cell 5000 --max-cells 200000

import geoparquet_io as gpio

# Explicit resolution
result = gpio.read('fields.parquet').aggregate_a5(resolution=8)

# Write the result
result.write('cells.parquet')

Metric Rollups¶

Aggregate numeric columns with --metric (func:column, comma-separated; bare column = sum). See Which metric function to use for when to pick sum vs. avg vs. min/max.

CLIPython

# Sum one column
gpio process aggregate a5 fields.parquet cells.parquet \
    --resolution 8 --metric "sum:area_ha"

# Multiple rollups (bare column = sum)
gpio process aggregate a5 fields.parquet cells.parquet \
    --resolution 8 --metric "area_ha,avg:yield,max:price"

import geoparquet_io as gpio

result = gpio.read('fields.parquet').aggregate_a5(
    resolution=8,
    metric="area_ha,avg:yield,max:price",
)
result.write('cells.parquet')

Category Breakdowns¶

Use --breakdown to pivot a categorical column into per-category count columns (count_<value>). Values beyond the limit are rolled into count_other.

--breakdown-metric changes what each pivot holds: "sum:peak" gives sum_peak_<value> columns (remainder sum_peak_other) carrying a weighted total instead of a row count. The names start with sum_/min_/max_, so gpio process overview rolls them up exactly rather than dropping them.

CLIPython

# Breakdown by crop type (up to 20 categories, default)
gpio process aggregate a5 fields.parquet cells.parquet \
    --resolution 8 --breakdown crop_type

# Limit to top 10 categories
gpio process aggregate a5 fields.parquet cells.parquet \
    --resolution 8 --breakdown crop_type --breakdown-limit 10

# Weighted pivot: total area per crop type, not a count of fields
gpio process aggregate a5 fields.parquet cells.parquet \
    --resolution 8 --breakdown crop_type --breakdown-metric "sum:area_ha"

import geoparquet_io as gpio

result = gpio.read('fields.parquet').aggregate_a5(
    resolution=8,
    metric="sum:area_ha",
    breakdown="crop_type",
    breakdown_limit=10,
)
result.write('cells.parquet')

# Weighted pivot: total area per crop type, not a count of fields
weighted = gpio.read('fields.parquet').aggregate_a5(
    resolution=8,
    breakdown="crop_type",
    breakdown_metric="sum:area_ha",
)
weighted.write('cells_weighted.parquet')

Row Filtering¶

Use --where to aggregate only a subset of rows — a year, a category, a confidence threshold — without a separate pre-filter/rewrite pass. The clause is standard DuckDB SQL and applies to the source scan, so counts, metrics, breakdowns, and --auto resolution sizing all reflect only the filtered rows. All three subcommands (a5, h3, admin) support it, with the same semantics as gpio extract --where.

CLIPython

# Only 2025 rows (column names with special characters need
# double quotes in SQL; shell escaping varies)
gpio process aggregate a5 fields.parquet cells.parquet --resolution 6 \
    --where "year(\"determination:datetime\") = 2025"

# Category / attribute filters
gpio process aggregate h3 fields.parquet cells.parquet --resolution 8 \
    --where "\"crop:name\" = 'wheat'"
gpio process aggregate admin fields.parquet by_country.parquet --level country \
    --where "confidence >= 50"

# Hive partition columns are filterable too
gpio process aggregate h3 "fields/**/*.parquet" cells.parquet --resolution 8 \
    --where "year = 2025"

import geoparquet_io as gpio

result = gpio.read('fields.parquet').aggregate_a5(
    resolution=6,
    where="year(\"determination:datetime\") = 2025",
)
result.write('cells.parquet')

On a hive-partitioned input (year=2025/...), the partition columns are part of the scan, so --where "year = 2025" filters on them and DuckDB prunes the partitions it does not need. Partition columns never appear in the aggregated output.

The clause must be a single filtering expression: a ; statement separator outside a quoted string is rejected, as are keywords that modify data (DROP, DELETE, INSERT, ...).

Bucketing Point¶

Grid keying reduces every feature to a single point (by default the geometry centroid) just to pick a cell — and with the default cell-polygon output, the input geometry is read only to compute that point. For large polygon datasets where geometry dominates file size, --bucket-point bbox derives the point from a bbox covering column instead and skips reading the geometry column entirely, cutting the scan to a small fraction:

Value	Keying point	Reads geometry column?
`geometry` (default)	`ST_Centroid(geometry)`	yes
`bbox`	Center of the bbox covering column	no
`<column>`	An existing point column	no

The bbox column is auto-detected from the primary geometry column's own covering.bbox metadata when present, falling back to a struct column named exactly bbox with xmin/ymin/xmax/ymax fields. Nothing else is guessed from a name: a boundary_bbox bounds the secondary boundary column, not the geometry being keyed. Pass --bbox-column NAME for an undeclared column under any other name. Bbox center differs from the true centroid only for L-shaped/very elongated features — negligible at aggregation resolutions, where keying by centroid is already an approximation.

CLIPython

# 225 GB of building polygons, but only the bbox + height columns are read
gpio process aggregate a5 buildings.parquet cells.parquet --auto \
    --bucket-point bbox --metric "avg:height"

# Nonstandard bbox column name (not referenced by covering metadata and
# not matching the naming conventions, so it can't be auto-detected)
gpio process aggregate a5 buildings.parquet cells.parquet --resolution 8 \
    --bucket-point bbox --bbox-column my_box

# Key from an existing point column
gpio process aggregate h3 parcels.parquet cells.parquet --resolution 8 \
    --bucket-point anchor_point

import geoparquet_io as gpio

result = gpio.read('buildings.parquet').aggregate_a5(
    resolution=10,
    metric="avg:height",
    bucket_point="bbox",
)
result.write('cells.parquet')

--auto still probes the geometry column

Auto-resolution sizing samples geometry centroids over a bounded sample (≤500k rows), so --auto reads a small slice of the geometry column even with --bucket-point bbox. Pass --resolution explicitly to avoid touching geometry altogether.

Output Geometry¶

Control what geometry each output row carries with --out-geometry:

Value	Output
`polygon`	A5 cell polygon (default, valid GeoParquet)
`centroid`	Cell centroid point (valid GeoParquet)
`both`	Both polygon and centroid (centroid as WKB column)
`none`	No geometry — plain Parquet table

CLIPython

# Default: A5 polygon (valid GeoParquet)
gpio process aggregate a5 fields.parquet cells.parquet --resolution 8

# Centroid only
gpio process aggregate a5 fields.parquet cells.parquet \
    --resolution 8 --out-geometry centroid

# No geometry — plain Parquet, re-join to geometry later
gpio process aggregate a5 fields.parquet cells_stats.parquet \
    --resolution 8 --out-geometry none

import geoparquet_io as gpio

# Centroid geometry
result = gpio.read('fields.parquet').aggregate_a5(
    resolution=8,
    out_geometry="centroid",
)
result.write('cells.parquet')

# No geometry (plain Parquet)
result = gpio.read('fields.parquet').aggregate_a5(
    resolution=8,
    out_geometry="none",
)
result.write('cells_stats.parquet')

Re-joining Geometry Later¶

When --out-geometry none is used, the output is a plain (non-geo) Parquet file. The a5_cell bucket id lets you re-join to any A5 geometry source:

CLIPython

# Step 1: aggregate without geometry
gpio process aggregate a5 fields.parquet cells_stats.parquet \
    --resolution 8 --metric "sum:area_ha" --out-geometry none

# Step 2: inspect the result (non-geo Parquet)
gpio inspect summary cells_stats.parquet
# a5_cell, count, sum_area_ha columns are present

import geoparquet_io as gpio

stats = gpio.read('fields.parquet').aggregate_a5(
    resolution=8, metric="sum:area_ha", out_geometry="none"
)
# stats is a plain (non-geo) Table; use .to_arrow() for DuckDB joins
arrow = stats.to_arrow()

Complete A5 Example¶

Combining all three kinds of statistics. The output cell carries count, sum_area_ha, avg_yield, and a count_<crop> column per crop type — so a map can size each cell by count, shade it by sum_area_ha (total farmed area) or avg_yield (typical productivity), and filter by crop.

CLIPython

gpio process aggregate a5 fields.parquet cells.parquet \
    --auto \
    --metric "sum:area_ha,avg:yield" \
    --breakdown crop_type \
    --breakdown-limit 15 \
    --out-geometry polygon

import geoparquet_io as gpio

gpio.read('fields.parquet') \
    .aggregate_a5(
        resolution=8,
        metric="sum:area_ha,avg:yield",
        breakdown="crop_type",
        breakdown_limit=15,
    ) \
    .write('cells.parquet')

H3 Grid Aggregation¶

H3 aggregation works exactly like A5 — the same --metric, --breakdown, --breakdown-limit, --breakdown-metric, --out-geometry, --resolution, and --auto options apply. The differences are that H3 uses Uber's hexagonal grid, its resolution range is 0–15 (A5 is 0–30), and the bucket id column is h3_cell (a string, e.g. 861fa80e7ffffff).

CLIPython

gpio process aggregate h3 fields.parquet cells.parquet \
    --resolution 8 \
    --metric "sum:area_ha" \
    --breakdown crop_type

import geoparquet_io as gpio

gpio.read('fields.parquet') \
    .aggregate_h3(
        resolution=8,
        metric="sum:area_ha",
        breakdown="crop_type",
    ) \
    .write('cells.parquet')

Admin Region Aggregation¶

Aggregate into administrative regions (countries or sub-national regions) using Overture Maps boundary data. A cache of the admin dataset is downloaded on first use.

Level¶

CLIPython

# Country level (default)
gpio process aggregate admin fields.parquet by_country.parquet

# Sub-national regions
gpio process aggregate admin fields.parquet by_region.parquet --level region

import geoparquet_io as gpio

# Country level
result = gpio.read('fields.parquet').aggregate_admin(level="country")
result.write('by_country.parquet')

# Region level
result = gpio.read('fields.parquet').aggregate_admin(level="region")
result.write('by_region.parquet')

The bucket id columns in admin output are admin_code (ISO country/region code) and admin_name. Features that fall outside all known admin regions are placed in an unassigned bucket.

Known limitation

admin_name currently equals the ISO code (same as admin_code). A separate human-readable name column is not yet available from the per-level Overture cache.

Metric Rollups and Breakdown¶

The same --metric and --breakdown options are available for admin aggregation:

CLIPython

gpio process aggregate admin fields.parquet by_country.parquet \
    --level country \
    --metric "sum:area_ha,avg:yield" \
    --breakdown crop_type

import geoparquet_io as gpio

gpio.read('fields.parquet') \
    .aggregate_admin(
        level="country",
        metric="sum:area_ha,avg:yield",
        breakdown="crop_type",
    ) \
    .write('by_country.parquet')

Output Geometry for Admin¶

The same --out-geometry polygon|centroid|both|none options apply. With none, the output is a plain Parquet table that can be joined to country/region geometry from any source using admin_code.

CLIPython

# Stats only — no geometry
gpio process aggregate admin fields.parquet country_stats.parquet \
    --level country --metric "sum:area_ha" --out-geometry none

import geoparquet_io as gpio

stats = gpio.read('fields.parquet').aggregate_admin(
    level="country", metric="sum:area_ha", out_geometry="none"
)
stats.write('country_stats.parquet')

Output Schema¶

Regardless of the chosen bucket scheme, every output file contains:

Column	Type	Description
`a5_cell` or `admin_code`	UBIGINT / VARCHAR	Bucket identifier
`admin_name`	VARCHAR	Human-readable name (admin only; currently equals `admin_code`)
`count`	BIGINT	Number of input features in the bucket
`sum_<col>`, `avg_<col>`, etc.	the column's type (a `sum` of integers is HUGEINT, written as DECIMAL(38,0))	Numeric rollups from `--metric`
`pct_<col>`	DOUBLE	Percent of the cell's area covered, from `--metric pct_cell:<col>`; `sum_<col>` is written with it. Can exceed 100 — see Percent of the cell covered
`count_<value>`	BIGINT	Per-category counts from `--breakdown`
`count_other`	BIGINT	Count for categories beyond `--breakdown-limit`
`sum_<col>_<value>`, `min_`/`max_` likewise	as for `--metric`	Per-category rollups when `--breakdown-metric` is set, replacing the `count_<value>` columns; NULL for a category with no rows or no values in the cell
`sum_<col>_other`	as above	Rollup for categories beyond `--breakdown-limit`
`geometry`	WKB	Bucket polygon or centroid (absent when `--out-geometry none`)

Cells at the antimeridian and the poles¶

A grid cell is a region of the sphere, and a few of them cannot be drawn as a single flat polygon. Those are written the way RFC 7946 3.1.9 prescribes:

a cell straddling the antimeridian is cut at ±180 into a MultiPolygon, with one part either side of the line, rather than a ring that stretches across the whole map;
a cell holding a pole is closed through an explicit seam that runs up to ±90, so it covers the polar cap instead of self-intersecting;
every coordinate written stays inside [-180, 180], which is what gpio check spec requires of a geographic CRS.

So geometry_types in the output's GeoParquet metadata reads ["Polygon", "MultiPolygon"] (or just ["MultiPolygon"]) for any dataset that reaches the antimeridian, and consumers must expect both. A dataset that sits astride the line — rather than spanning the globe — also gets the RFC 7946 5.2 wrap-around bbox, with xmin > xmax.

--out-geometry centroid and both take the centroid from the grid library's own cell-centre function, which reports lon/lat in range, so it always falls inside the polygon on the same row.

Common Options¶

Both commands support the standard output options:

--where "confidence >= 50"  # DuckDB WHERE clause filtering input rows
--metric-nodata "-999"    # NoData sentinel(s) mapped to NULL in --metric columns
--write-memory 8GB        # Memory limit for the aggregation
--compression SNAPPY      # Output compression (default: ZSTD)
--geoparquet-version 1.1  # GeoParquet spec version
--show-sql                # Print the generated DuckDB SQL
--verbose                 # Detailed progress output

Memory¶

An aggregation reads only what it aggregates: the keying point, the --metric columns and the --breakdown column. Nothing else in the input is scanned, so a wide file with one metric costs about what a narrow one does — and --bucket-point bbox leaves the geometry unread on top of that.

DuckDB runs inside a memory limit of half the process's memory ceiling: physical RAM, or the container or Slurm job cap when that is lower. The cap is what matters on a shared node — without it DuckDB sizes itself for the whole machine and the job's cgroup kills it first. --write-memory overrides the default:

CLIPython

gpio process aggregate a5 fields.parquet cells.parquet \
    --resolution 7 --metric "sum:area" --write-memory 8GB

import geoparquet_io as gpio

result = gpio.read('fields.parquet').aggregate_a5(
    resolution=7,
    metric="sum:area",
    memory_limit="8GB",
)
result.write('cells.parquet')

gpio process overview takes the same flag.

Aggregating Data¶

Choosing What to Aggregate¶

Which metric function to use¶

Percent of the cell covered (pct_cell)¶

Which breakdown to use¶

A5 Grid Aggregation¶

Resolution¶

Metric Rollups¶

Category Breakdowns¶

Row Filtering¶

Bucketing Point¶

Output Geometry¶

Re-joining Geometry Later¶

Complete A5 Example¶

H3 Grid Aggregation¶

Admin Region Aggregation¶

Level¶

Metric Rollups and Breakdown¶

Output Geometry for Admin¶

Output Schema¶

Cells at the antimeridian and the poles¶

Common Options¶

Memory¶

See Also¶

Percent of the cell covered (`pct_cell`)¶