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`

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.
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".

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.

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.

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

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')

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, --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.	DOUBLE	Numeric rollups from `--metric`
`count_<value>`	BIGINT	Per-category counts from `--breakdown`
`count_other`	BIGINT	Count for categories beyond `--breakdown-limit`
`geometry`	WKB	Bucket polygon or centroid (absent when `--out-geometry none`)

Common Options¶

Both commands support the standard output options:

--compression SNAPPY      # Output compression (default: ZSTD)
--geoparquet-version 1.1  # GeoParquet spec version
--show-sql                # Print the generated DuckDB SQL
--verbose                 # Detailed progress output

Aggregating Data¶

Choosing What to Aggregate¶

Which metric function to use¶

Which breakdown to use¶

A5 Grid Aggregation¶

Resolution¶

Metric Rollups¶

Category Breakdowns¶

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¶

Common Options¶

See Also¶