Partitioning a GeoParquet OSM Lake by H3 Cell

Lay out a multi-gigabyte GeoParquet dataset so a query over one city reads a few megabytes instead of the whole thing — without shattering it into a million files nobody can list.

Prerequisites

Conceptual minimum

Partitioning is directory-level filtering: the key becomes part of the path, and a reader that can evaluate a predicate against the key skips whole files without opening them. It is coarse, free at query time, and completely separate from the row-group statistics that filter within a file.

The only real decision is granularity.

Partition count and file size at five H3 resolutions A bar chart of partition counts for 14.2 million European buildings. Resolution 2 gives 12 partitions of about 1.2 gigabytes each and barely prunes. Resolution 3 gives 84 partitions of 175 megabytes. Resolution 4 gives 512 partitions of 29 megabytes. Resolution 5 gives 3100 partitions of 4.8 megabytes, where directory listing starts to hurt. Resolution 6 gives 19400 partitions of 0.8 megabytes and is unusable. Partition count is the dial, and both ends of it are bad 14.2 M European buildings, partitioned at five H3 resolutions r2 — 12 partitions 12 files · 1.2 GB each · barely prunes r3 — 84 partitions 84 files · 175 MB each · good r4 — 512 partitions 512 files · 29 MB each · good r5 — 3 100 partitions 3 100 files · 4.8 MB each · listing hurts r6 — 19 400 partitions 19 400 files · 0.8 MB each · unusable Aim for partitions in the tens to low hundreds of megabytes. The cost at the small end is not storage, it is the metadata read before a single row is returned.
The useful band is narrow and it is set by file size, not by how finely you would like to slice the world.

Both ends of that dial are bad in different ways. Too few partitions and a query reads gigabytes to return kilobytes. Too many and the query spends longer listing and opening files than reading them — a Parquet file carries a metadata footer that must be read before any row can be returned, so a thousand tiny files means a thousand footer reads.

Three partition-key strategies compared Three panels. Partitioning by country is natural and human-readable and matches how questions are asked, but is wildly uneven between large and small countries, so the largest partition sets the memory floor and no pruning happens within a country. Partitioning by H3 cell gives even cells but uneven data, with empty ocean cells and enormous city cells; it prunes on any bounding-box query but directory names are opaque and the resolution is one global choice. Adaptive cells split only where density demands, keeping a parent cell until it exceeds a size threshold and then splitting to children, giving even file sizes and uneven resolution at the cost of a manifest. Three partition keys, three different failure shapes By country Natural, human-readable Matches how people ask questions Wildly uneven: DE vs LI Largest partition sets the memory floor Cannot prune within a country By H3 cell Even cells, uneven data Ocean cells empty, cities enormous Prunes on any bbox query Directory names are opaque Resolution is one global choice By adaptive cell Split only where density demands Parent cell until it exceeds a size Then split to children Even file sizes, uneven resolution Needs a manifest to resolve Uniform cells give uneven files because the data is uneven. Adaptive splitting inverts that at the cost of a manifest the reader has to consult.
Most OSM lakes are best served by a fixed cell resolution chosen from the densest region you care about, with the sparse partitions simply being small.

For OSM specifically, a fixed H3 resolution is usually the right answer despite giving uneven file sizes, because the alternative — adaptive splitting — requires every reader to consult a manifest to know which resolution applies where, and that manifest becomes a piece of infrastructure you have to keep correct.

Runnable solution

python 
#!/usr/bin/env python3
"""Write an OSM feature stream as an H3-partitioned GeoParquet dataset."""
from __future__ import annotations

import json
import logging
from collections import defaultdict
from pathlib import Path
from typing import Iterable

import h3
import pyarrow as pa
import pyarrow.parquet as pq

logging.basicConfig(level=logging.INFO, format="%(levelname)s %(message)s")
logger = logging.getLogger(__name__)

PARTITION_RES = 4        # directory granularity — one global choice
SORT_RES = 7             # in-file sort key, finer than the partition
ROW_GROUP_ROWS = 200_000
TARGET_PARTITION_BYTES = 128 * 1024 * 1024


def partition_key(lat: float, lon: float) -> str:
    return h3.latlng_to_cell(lat, lon, PARTITION_RES)


def sort_key(lat: float, lon: float) -> str:
    return h3.latlng_to_cell(lat, lon, SORT_RES)


def geo_metadata(bbox: list[float]) -> bytes:
    return json.dumps({
        "version": "1.1.0",
        "primary_column": "geometry",
        "columns": {"geometry": {"encoding": "WKB", "geometry_types": [],
                                 "crs": None, "bbox": bbox}},
    }).encode()


def write_partitioned(batches: Iterable[pa.Table], root: Path) -> dict[str, int]:
    """Route each row to its partition file, sorting within the partition on close.

    Rows arrive in whatever order the parser produced. Buffering per partition and
    flushing when a buffer is large enough keeps memory bounded while still letting
    each written chunk be sorted, which is what makes row-group statistics useful.
    """
    root.mkdir(parents=True, exist_ok=True)
    buffers: dict[str, list[pa.Table]] = defaultdict(list)
    buffered_rows: dict[str, int] = defaultdict(int)
    written: dict[str, int] = defaultdict(int)

    def flush(cell: str) -> None:
        if not buffers[cell]:
            return
        table = pa.concat_tables(buffers[cell])
        table = table.sort_by([("h3_sort", "ascending"), ("osm_id", "ascending")])
        meta = dict(table.schema.metadata or {})
        meta[b"geo"] = geo_metadata(list(h3.cell_to_boundary(cell)[0]) * 2)
        table = table.replace_schema_metadata(meta)
        out_dir = root / f"h3_r{PARTITION_RES}={cell}"
        out_dir.mkdir(exist_ok=True)
        part = out_dir / f"part-{written[cell]:05d}.parquet"
        pq.write_table(table, part, compression="zstd", compression_level=3,
                       row_group_size=ROW_GROUP_ROWS, write_statistics=True)
        written[cell] += 1
        buffers[cell].clear()
        buffered_rows[cell] = 0

    for batch in batches:
        for cell in set(batch.column("h3_part").to_pylist()):
            mask = pa.compute.equal(batch.column("h3_part"), cell)
            slice_ = batch.filter(mask)
            buffers[cell].append(slice_)
            buffered_rows[cell] += slice_.num_rows
            if buffered_rows[cell] * 400 > TARGET_PARTITION_BYTES:   # ~400 B/row estimate
                flush(cell)

    for cell in list(buffers):
        flush(cell)

    total_files = sum(written.values())
    logger.info("wrote %d file(s) across %d partition(s)", total_files, len(written))
    return dict(written)

Reading it back exercises the pruning:

python 
import pyarrow.dataset as ds
import pyarrow.compute as pc

dataset = ds.dataset("lake/", format="parquet", partitioning="hive")

# Resolve the query bbox to the partition cells it touches, then filter on the key.
cells = h3.geo_to_cells({"type": "Polygon", "coordinates": [BBOX_RING]}, PARTITION_RES)
table = dataset.to_table(filter=pc.field(f"h3_r{PARTITION_RES}").isin(list(cells)))

Step-by-step walkthrough

write_partitioned buffers per partition rather than writing a file per batch. Writing immediately would produce one small file per partition per batch — the many-tiny-files failure, arrived at by accident. Buffering until a partition has roughly a target file’s worth of rows, then flushing, gives files in the intended size band regardless of the order rows arrive in.

The two H3 resolutions do different jobs and should not be the same number. PARTITION_RES sets the directory granularity and therefore how many files exist. SORT_RES is finer and only orders rows inside a file, which is what makes the per-row-group min/max useful — the mechanism covered in the parent topic, Exporting OSM to GeoParquet & PostGIS. Using one resolution for both means every row in a file shares the sort key and the statistics distinguish nothing.

Hive-style directory names — h3_r4=841f8d7ffffffff — are what let pyarrow.dataset expose the key as a queryable column. A directory named just 841f8d7ffffffff still partitions the data physically but the reader cannot filter on it without being told the schema.

How four layouts serve three different query shapes A grid of four layouts against three queries. A single file reads everything for all three. Country partitions read one country for a city bounding box and for a country aggregate, but everything for a global count. H3 resolution 4 partitions read two to six partitions for a city, about forty for a country aggregate, and everything for a global count. H3 resolution 4 with rows sorted inside each partition reads the same files but prunes within them, and answers a global count from metadata alone. What each layout does to the same three queries bbox over one city country-wide aggregate global count by tag single file reads everything reads everything reads everything country partitions reads one country reads one country reads everything H3 r4 partitions reads 2–6 partitions reads ~40 partitions reads everything H3 r4 + sorted rows reads 2–6, prunes inside reads ~40, prunes inside metadata only for counts The bottom row is the point: partitioning selects files and row-group statistics select within them, and you want both.
Partitioning and in-file sorting are not alternatives. One picks files, the other picks row groups, and a layout with only the first still reads far more than it needs.

Verification

Check the shape of the layout before checking the queries:

bash 
find lake -name '*.parquet' | wc -l
find lake -name '*.parquet' -printf '%s\n' | sort -n | awk '
  {a[NR]=$1; s+=$1} END {printf "min %.1f MB  median %.1f MB  max %.1f MB  total %.1f GB\n",
  a[1]/1e6, a[int(NR/2)]/1e6, a[NR]/1e6, s/1e9}'

A healthy layout has a median in the tens of megabytes and a maximum under a few hundred. A minimum in the kilobytes is fine — those are sparse cells — but a median in the kilobytes means the partition resolution is too fine.

Then confirm the pruning is real rather than assumed, by comparing bytes read:

python 
import pyarrow.dataset as ds
scanner = dataset.scanner(filter=pc.field("h3_r4").isin(["841f8d7ffffffff"]))
print(scanner.count_rows())          # rows returned
print(dataset.count_rows())          # rows in the whole lake

If the two numbers are close for a small-area filter, the filter is not being pushed down — usually because the partitioning scheme was not declared when the dataset was opened.

Common errors and fixes

Symptom Root cause Fix
Every query reads the whole lake partitioning="hive" not passed when opening Declare it, or use ds.partitioning() explicitly
Thousands of sub-megabyte files Flushed per batch instead of per size Buffer per partition to a target size
One partition is 4 GB A dense city at a coarse resolution Split that cell to children, or raise the global resolution
Reader cannot find the geometry Metadata attached before the sort, then lost Attach geo metadata to the table you actually write
h3.latlng_to_cell raises on some rows Null or invalid geometry reached the keying step Filter invalid geometry upstream
Partition column missing from results Directory named without key=value Use Hive-style names

Frequently Asked Questions

Which H3 resolution should I partition at?

Start from file size, not from geography. Estimate bytes per feature, multiply by the features in your densest region, and pick the coarsest resolution that keeps that region’s partition under a few hundred megabytes. For continental OSM feature layers that usually lands at resolution 3 or 4; for a single country, 4 or 5.

Should I partition by country instead?

Only if consumers overwhelmingly ask country-shaped questions and you can tolerate Germany and Liechtenstein being one partition each. Country partitioning cannot prune within a country, so a query for one city still reads the whole of Germany. A cell scheme costs readability and gains uniform behaviour everywhere.

Can I repartition without rewriting everything?

Not really — the partition key is the directory path, so changing it means moving every row. What you can do cheaply is split an over-large partition: read that one cell, re-key its rows to child cells, write those, and delete the parent. Readers that filter on the parent key need to understand both, which is the manifest problem adaptive layouts have.

Do empty ocean cells cost anything?

No, because they never get created — a cell with no features produces no directory. What does cost is a cell with three features, which produces a file whose metadata footer is larger than its data. Those are harmless individually and worth watching in aggregate: if most partitions are tiny, the resolution is wrong.

Living with the layout

A partition scheme is a long-lived decision, because changing it means rewriting every row. Two habits make that decision survivable.

Record the scheme alongside the data. A small manifest at the dataset root naming the partition key, its resolution, the sort key and the writer version costs nothing and answers the question every later reader has: what does this directory name mean, and can I rely on rows inside being sorted. Without it, the layout is discoverable only by inspection and the sort order is discoverable not at all.

Monitor the partition size distribution on every write. The layout that was right when the dataset was built drifts as the underlying data grows unevenly — a city that doubles its building coverage turns a well-sized partition into an outsized one, and nothing announces it. A single log line per run reporting the median and maximum partition size makes the drift visible while it is still cheap to fix by splitting one cell rather than by repartitioning everything.

Specification reference

Hive-style partitioning encodes each key as a name=value directory component. pyarrow.dataset discovers these when opened with partitioning="hive" and exposes them as columns, allowing a filter on the key to eliminate files before any Parquet footer is read.

Up one level: Exporting OSM to GeoParquet & PostGIS.