Adding Spatial Indices & Metadata

The add commands enhance GeoParquet files with spatial indices, geometry metrics, and administrative metadata.

Coordinate Reference Systems

The grid-index commands (h3, s2, a5, quadkey) and admin-divisions are CRS-aware. If your input declares a projected CRS (e.g. EPSG:5070 or EPSG:28992), the geometry centroid is reprojected to OGC:CRS84 (lon/lat degrees) before the cell is computed, and the input is reprojected to the admin boundaries' CRS before the spatial join — so you no longer need to run gpio convert reproject first. Inputs without a declared CRS are treated as OGC:CRS84 per the GeoParquet spec.

Bounding Boxes

Add precomputed bounding boxes for faster spatial queries:

CLIPython

gpio add bbox input.parquet output.parquet

# Works with remote files
gpio add bbox s3://bucket/input.parquet s3://bucket/output.parquet --aws-profile prod

import geoparquet_io as gpio

gpio.read('input.parquet').add_bbox().write('output.parquet')

# Custom column name
gpio.read('input.parquet').add_bbox(column_name='bounds').write('output.parquet')

Creates a struct column with {xmin, ymin, xmax, ymax} for each feature. Bbox covering metadata is automatically added to comply with GeoParquet 1.1 spec.

Existing Bbox Detection

The command automatically checks for existing bbox columns:

• If bbox exists with metadata: Informs you and exits successfully (no action needed)
• If bbox exists without metadata: Suggests using gpio add bbox-metadata instead
• Use --force: Replace existing bbox column with a freshly computed one

# Check and skip if bbox already exists
gpio add bbox input.parquet output.parquet

# Force replace existing bbox
gpio add bbox input.parquet output.parquet --force

Options:

# Custom column name
gpio add bbox input.parquet output.parquet --bbox-name bounds

# Force replace existing bbox
gpio add bbox input.parquet output.parquet --force

# With compression settings
gpio add bbox input.parquet output.parquet --compression ZSTD --compression-level 15

# Dry run (preview SQL)
gpio add bbox input.parquet output.parquet --dry-run

Add Bbox Metadata Only

If your file already has a bbox column but lacks covering metadata (e.g., from external tools):

CLIPython

gpio add bbox-metadata myfile.parquet

This modifies the file in-place to add only the metadata, without creating a new file.

import geoparquet_io as gpio

# Add bbox column, then add covering metadata
table = gpio.read('input.parquet')
table_with_bbox = table.add_bbox().add_bbox_metadata()
table_with_bbox.write('output.parquet')

# Or just add metadata if bbox column already exists
table = gpio.read('file_with_bbox.parquet')
table.add_bbox_metadata().write('output.parquet')

H3 Hexagonal Cells

Input CRS

The grid commands (h3, s2, a5, quadkey) key on lon/lat. Input in a non-CRS84 CRS is reprojected to lon/lat automatically before cell assignment, so projected data (e.g. national grids in metres) keys correctly with no manual reprojection. Input already in OGC:CRS84 / EPSG:4326 is untouched.

Add H3 hexagonal cell IDs based on geometry centroids:

CLIPython

gpio add h3 input.parquet output.parquet --resolution 9

# From HTTPS to S3
gpio add h3 https://example.com/data.parquet s3://bucket/indexed.parquet --resolution 9

import geoparquet_io as gpio

gpio.read('input.parquet').add_h3(resolution=9).write('output.parquet')

# Custom column name
gpio.read('input.parquet').add_h3(column_name='h3_index', resolution=13).write('output.parquet')

Resolution guide:

• Resolution 7: ~5 km² cells
• Resolution 9: ~105 m² cells (default)
• Resolution 11: ~2 m² cells
• Resolution 13: ~0.04 m² cells

Options:

# Custom column name
gpio add h3 input.parquet output.parquet --h3-name h3_index

# Different resolution
gpio add h3 input.parquet output.parquet --resolution 13

# With row group sizing
gpio add h3 input.parquet output.parquet --row-group-size-mb 256MB

S2 Spherical Cells

Add S2 spherical cell IDs based on geometry centroids:

CLIPython

gpio add s2 input.parquet output.parquet --level 13

# From HTTPS to S3
gpio add s2 https://example.com/data.parquet s3://bucket/indexed.parquet --level 13

import geoparquet_io as gpio

gpio.read('input.parquet').add_s2(level=13).write('output.parquet')

# Custom column name
gpio.read('input.parquet').add_s2(column_name='s2_index', level=18).write('output.parquet')

S2 uses Google's Spherical Geometry library which divides the Earth's surface into a hierarchy of cells using quadtree subdivision. Unlike H3's hexagonal grid, S2 cells are variable quads that provide hierarchical spatial indexing.

Level guide:

Level	Approx. Area	Use Case
0	85M km²	Global
1	21M km²	Continent
2	5.3M km²	Large country
3	1.3M km²	Medium country
4	324,000 km²	Small country
5	81,000 km²	State/province
6	20,000 km²	Large city
7	5,100 km²	Metropolitan area
8	1,250 km²	City
9	313 km²	District
10	78 km²	Neighborhood cluster
11	20 km²	Large neighborhood
12	4.9 km²	Neighborhood
13	1.2 km²	Small neighborhood (default)
14	0.31 km²	Street cluster
15	77,000 m²	City block
16	19,000 m²	Large building complex
17	4,800 m²	Building complex
18	1,200 m²	Large building
19	300 m²	Building
20	75 m²	Building section
21	19 m²	Room cluster
22	4.7 m²	Room
23	1.2 m²	Furniture
24	0.29 m²	Small object
25	0.07 m²	Tiny object
26	0.02 m²	Precision object
27	0.005 m²	High precision
28	0.001 m²	Very high precision
29	0.0003 m²	Ultra precision
30	0.00007 m²	Maximum precision (~0.7 cm²)

Options:

# Custom column name
gpio add s2 input.parquet output.parquet --s2-name s2_index

# Different level
gpio add s2 input.parquet output.parquet --level 18

# With row group sizing
gpio add s2 input.parquet output.parquet --row-group-size-mb 256MB

Technical Details

S2 cell IDs are computed using DuckDB's geography extension:

s2_cell_token(
    s2_cell_parent(
        s2_cellfromlonlat(
            ST_X(ST_Centroid(geometry)),
            ST_Y(ST_Centroid(geometry))
        ),
        level
    )
)

• s2_cellfromlonlat: Converts lon/lat to S2 cell at maximum precision (level 30)
• s2_cell_parent: Gets parent cell at desired level
• s2_cell_token: Converts to hex token string for portability

Cell IDs are stored as hex strings (e.g., "89c25901") rather than integers for maximum portability across systems.

DuckDB version and the geography extension

The geography extension is a DuckDB community extension that is built per DuckDB release. If you install a DuckDB version for which it has not been published yet, gpio add s2 (and gpio partition s2) fail with a clear message telling you the extension is unavailable for that version. All other gpio commands continue to work; either wait for the extension to be rebuilt or install a DuckDB version that provides it.

A5 Cells

Add A5 spatial cell IDs based on geometry centroids.

CLIPython

gpio add a5 input.parquet output.parquet --resolution 15

# From HTTPS to S3
gpio add a5 https://example.com/data.parquet s3://bucket/indexed.parquet --resolution 15

import geoparquet_io as gpio

gpio.read('input.parquet').add_a5(resolution=15).write('output.parquet')

# Custom column name
gpio.read('input.parquet').add_a5(column_name='a5_index', resolution=12).write('output.parquet')

Options:

# Custom column name
gpio add a5 input.parquet output.parquet --a5-name a5_index

# Different resolution
gpio add a5 input.parquet output.parquet --resolution 12

# With row group sizing
gpio add a5 input.parquet output.parquet --row-group-size-mb 256MB

KD-Tree Partitions

Add balanced spatial partition IDs using KD-tree:

CLIPython

# Auto-select partitions (default: ~120k rows each)
gpio add kdtree input.parquet output.parquet

# Explicit partition count (must be power of 2)
gpio add kdtree input.parquet output.parquet --partitions 32

# Exact mode (deterministic but slower)
gpio add kdtree input.parquet output.parquet --partitions 16 --exact

import geoparquet_io as gpio

# Add kdtree column with default settings (9 iterations = 512 partitions)
gpio.read('input.parquet').add_kdtree().write('output.parquet')

# Custom column name and iterations
gpio.read('input.parquet').add_kdtree(
    column_name='partition_id',
    iterations=5  # 2^5 = 32 partitions
).write('output.parquet')

Auto mode (default): - Targets ~120k rows per partition - Uses approximate computation (O(n)) - Fast on large datasets

Explicit mode: - Specify partition count (2, 4, 8, 16, 32, ...) - Control granularity

Exact vs Approximate: - Approximate: O(n), samples 100k points - Exact: O(n × log₂(partitions)), deterministic

Options:

# Custom target rows per partition
gpio add kdtree input.parquet output.parquet --auto 200000

# Custom sample size for approximate mode
gpio add kdtree input.parquet output.parquet --approx 200000

# Track progress
gpio add kdtree input.parquet output.parquet --verbose

Geometry Metrics

Add geodesic area and perimeter measurements to each feature:

CLIPython

gpio add geometry-metrics input.parquet output.parquet

# Preview SQL without executing
gpio add geometry-metrics input.parquet output.parquet --dry-run

# Without Vecorel metadata
gpio add geometry-metrics input.parquet output.parquet --no-vecorel

import geoparquet_io as gpio
from geoparquet_io.core.add.geometry_metrics import add_geometry_metrics

# Add area and perimeter columns
add_geometry_metrics('input.parquet', 'output.parquet')

# Without Vecorel schema metadata
add_geometry_metrics('input.parquet', 'output.parquet', vecorel=False)

Calculates two columns using WGS84 spheroid-based calculations:

• metrics:area — geodesic area in square meters (m²)
• metrics:perimeter — geodesic perimeter in meters (m)

By default, the output follows the Vecorel geometry-metrics extension specification. This adds collection metadata to the Parquet file referencing the schema URL, and ensures required columns (id, geometry) are present and non-nullable. Use --no-vecorel to skip the metadata and just add the raw metric columns.

Options:

# With compression settings
gpio add geometry-metrics input.parquet output.parquet --compression ZSTD --compression-level 15

# With row group sizing
gpio add geometry-metrics input.parquet output.parquet --row-group-size-mb 256MB

# Show generated SQL
gpio add geometry-metrics input.parquet output.parquet --show-sql

Input CRS

The spheroid calculations assume WGS84 (EPSG:4326) input. If your data uses a different CRS, reproject first with gpio convert reproject.

Administrative Divisions

Add administrative division columns via spatial join with remote boundaries datasets:

How It Works

Performs spatial intersection between your data and remote admin boundaries to add admin division columns. Uses efficient spatial extent filtering to query only relevant boundaries from remote datasets.

Input in a non-CRS84 CRS is reprojected to the boundaries' CRS (OGC:CRS84) before the intersection, so projected data joins correctly instead of erroring on a CRS mismatch. (For a non-CRS84 input the bbox pre-filter is skipped, since the stored bbox is in the source CRS — the extent filter still bounds the query.)

For native-geometry inputs (GeoParquet 2.0 and GeoParquet 1.1 with geoarrow encoding), the spatial join relies on native Parquet column statistics to skip irrelevant data, but this is not quite working yet, so expect those to be a bit slower until #462 is implemented. For non-native inputs (GeoParquet 1.x that carry a bbox column), a cheap bbox-overlap test is applied before ST_Intersects to prune candidates, and using it will be 5-10x faster.

The join is a streaming LEFT JOIN so it scales to very large inputs (hundreds of millions of features) with bounded memory. A feature is attributed to the admin polygon(s) it intersects; because the per-level caches are non-overlapping, this is normally exactly one polygon per level. A feature straddling overlapping polygons at a border is emitted once per match. De-duplicating such border overlaps is intentionally left to a future dedicated operation rather than folded into this join, as including it greatly degraded performance.

Quick Start

CLIPython

# Add all GAUL levels (continent, country, department)
gpio add admin-divisions input.parquet output.parquet --dataset gaul

# Preview SQL before execution
gpio add admin-divisions input.parquet output.parquet --dataset gaul --dry-run

import geoparquet_io as gpio

# Add country codes using Overture dataset
table = gpio.read('input.parquet')
enriched = table.add_admin_divisions(
    dataset='overture',
    levels=['country']
)
enriched.write('output.parquet')

Multi-Level Admin Divisions

Add multiple hierarchical administrative levels:

CLIPython

# Add all GAUL levels (adds admin:continent, admin:country, admin:department)
gpio add admin-divisions buildings.parquet output.parquet --dataset gaul

# Add specific levels only
gpio add admin-divisions buildings.parquet output.parquet --dataset gaul \
  --levels continent,country

# Use Overture Maps dataset
gpio add admin-divisions buildings.parquet output.parquet --dataset overture \
  --levels country,region

import geoparquet_io as gpio

# Add multiple levels
table = gpio.read('buildings.parquet')
enriched = table.add_admin_divisions(
    dataset='gaul',
    levels=['continent', 'country', 'department']
)
enriched.write('output.parquet')

# With country filter for faster processing
enriched = table.add_admin_divisions(
    dataset='overture',
    levels=['country', 'region'],
    country_filter='US'
)

Vecorel-Compliant Output

Use --vecorel for output that follows the Vecorel administrative division extension:

CLIPython

# Vecorel-compliant admin columns (uses Overture dataset automatically)
gpio add admin-divisions input.parquet output.parquet --vecorel

# Equivalent to:
gpio add admin-divisions input.parquet output.parquet \
  --dataset overture --levels country,region --prefix admin

import geoparquet_io as gpio

table = gpio.read('input.parquet')
enriched = table.add_admin_divisions(
    dataset='overture',
    levels=['country', 'region'],
    vecorel=True
)
enriched.write('output.parquet')

The --vecorel flag:

• Forces the Overture dataset (overrides --dataset)
• Sets levels to country,region (overrides --levels)
• Adds admin:country_code (ISO 3166-1 alpha-2) and admin:subdivision_code (ISO 3166-2)
• Writes collection metadata with the Vecorel schema URL
• Ensures id and geometry columns are present and non-nullable

Datasets

Two remote admin boundary datasets are supported:

Dataset	Standard	Columns Added	Description
gaul (default)	GAUL naming + ISO 3166-1 alpha-3	admin:continent, admin:country, admin:department	FAO Global Administrative Unit Layers (GAUL) L2 - worldwide coverage with standardized naming
overture	Vecorel compliant (ISO 3166-1/2)	admin:country_code, admin:subdivision_code	Overture Maps Divisions with ISO 3166 codes (219 countries, 3,544 regions) - docs

Vecorel Compliance (Overture Dataset Only)

The overture dataset follows the Vecorel administrative division extension specification with standardized ISO codes:

• admin:country_code (REQUIRED): ISO 3166-1 alpha-2 country code (e.g., "US", "AR", "DE")
• admin:subdivision_code: ISO 3166-2 subdivision code WITHOUT country prefix (e.g., "CA" not "US-CA")

The tool automatically transforms Overture's native region codes (e.g., "US-CA") to strip the country prefix for Vecorel compliance.

Note: The GAUL dataset uses FAO's standardized naming system but is NOT Vecorel compliant: - Has ISO 3166-1 alpha-3 codes (e.g., "TZA"), but Vecorel requires alpha-2 (e.g., "TZ") - Uses GAUL's standardized naming for subnational units, not ISO 3166-2 codes - Columns: admin:continent (continent name), admin:country (GAUL country name), admin:department (GAUL L2 name)

Notes

• Overture dataset: Vecorel compliant with ISO 3166-1 alpha-2 and ISO 3166-2 codes
• GAUL dataset: FAO standardized naming system - source.coop GAUL L2
• Performs spatial intersection to assign admin divisions based on geometry
• Requires internet connection to access remote datasets
• Uses spatial extent filtering and bbox columns for optimization

Overture: per-level cache and simplified boundaries

The Overture dataset uses a per-level cache: country and region boundaries are downloaded and cached as separate files rather than one combined file.

Overture boundaries are simplified with ST_SimplifyPreserveTopology at a tolerance of 0.0001 degrees (roughly 11 m near the equator, ~7 m at 50°N). Country and region layers are simplified independently, so shared borders are not perfectly coincident. As a result, near-border attribution is approximate: a feature sitting in a border zone may be attributed to the neighboring region. This is a deliberate accuracy-for-memory tradeoff. If you need exact boundaries near borders, use the gaul dataset instead.

Caching

Admin datasets (GAUL, Overture) are automatically cached locally on first use:

• First run: Downloads and caches the full dataset (~5-50MB depending on dataset)
• Subsequent runs: Uses cached version (instant startup)
• Cache location: ~/.geoparquet-io/cache/admin/
• Warning: Shown if cache is older than 6 months

Cache management options:

CLIPython

# Skip cache and use remote directly
gpio add admin-divisions input.parquet output.parquet --dataset gaul --no-cache

# Clear all cached datasets (prompts for confirmation)
gpio add admin-divisions input.parquet output.parquet --dataset gaul --clear-cache

import geoparquet_io as gpio
from geoparquet_io.core.admin_datasets import get_cache_dir, clear_cache

# Default: uses cached datasets automatically
gpio.read('input.parquet').add_admin_divisions(
    dataset='overture',
    levels=['country', 'admin1']
).write('output.parquet')

# Check cache location (~/.geoparquet-io/cache/admin/)
cache_dir = get_cache_dir()
print(f"Cache location: {cache_dir}")

# Clear all cached admin datasets
result = clear_cache(confirm=True)
print(f"Cleared {result['files_deleted']} files, {result['bytes_freed'] / 1024 / 1024:.2f} MB freed")

When to clear cache

Clear your cache when you need fresh admin boundary data, such as after a new Overture Maps release. The cache files are named with version numbers (e.g., gaul-2024-12-19.parquet, overture-2025-10-22.0.parquet).

Common Options

All add commands support:

# Compression settings
--compression [ZSTD|GZIP|BROTLI|LZ4|SNAPPY|UNCOMPRESSED]
--compression-level [1-22]

# Row group sizing
--row-group-size [exact row count]
--row-group-size-mb [target size like '256MB' or '1GB']

# Workflow options
--dry-run          # Preview SQL without executing
--verbose          # Detailed output
--preview          # Preview results (partition commands)
--hive             # Use Hive-style partitioning
--overwrite        # Overwrite existing files
--aws-profile NAME # AWS profile for S3 operations

--add-bbox         # Auto-add bbox if missing (some commands)

See Also

• CLI Reference - Full command reference
• partition command
• sort command