Partitioning GeoParquet by Region and Date

This page covers one concrete layout decision: partitioning a GeoParquet dataset on S3 by region and date so that a query for “one region, one month” scans a handful of files instead of the whole lake.

A partitioned lake is one of the load-bearing pieces of the broader location intelligence architecture and data foundations: it is the storage tier every downstream join and dashboard reads from. Partitioning is the difference between a lake that a dashboard can query interactively and one that forces a full scan on every read. A GeoParquet dataset written as Hive-style directories — region=midwest/date=2026-07-01/part-0.parquet — lets any reader that understands partition filters prune entire directories before opening a single file. Combined with the bounding-box statistics Parquet already stores per row group, a well-partitioned lake turns most analytical reads into a few megabytes of I/O rather than gigabytes. The trade-off is granularity: partition too coarsely and you scan too much, partition too finely and you drown in tiny files. This page is about finding the middle.

Prerequisites #

• Python packages: geopandas >= 0.14, pyarrow >= 14, and pandas. GeoParquet writing goes through geopandas.GeoDataFrame.to_parquet, which serializes CRS and geometry-column metadata per the GeoParquet spec. Install with pip install "geopandas>=0.14" "pyarrow>=14" pandas.
• An S3 bucket configured as a data lake. The bucket, prefix conventions, and access controls come from configuring AWS S3 for geospatial data lakes. Writing partitioned datasets also needs s3fs so pyarrow can address s3:// paths directly.
• A defined region key. Partition on a low-cardinality, business-meaningful region column (sales region, state group, or market) — not on something high-cardinality like ZIP, which explodes the partition count.
• A CRS decision made up front. Store geometry in a single interchange CRS (EPSG:4326) and record it in the file metadata. Any customer-location layer that carries PII must be governed before it lands here — see best practices for securing PII in customer location datasets.

Partition and Row-Group Parameters #

The layout is governed by a few numbers. Get these right and pruning follows; get them wrong and you either scan too much or hit the small-file problem.

The two levers in tension are date granularity and file size. If a region produces only a few thousand rows per day, daily partitioning yields thousands of sub-megabyte files — the small-file problem — where per-file open overhead and S3 request cost dominate the actual read. In that regime, partition monthly and let row groups do the finer pruning. If a region produces hundreds of MB per day, daily partitioning is correct and keeps each file near the target size.

A concrete way to choose: estimate a region’s per-day row count, multiply by the average serialized row size (geometry WKB plus attributes — often 1–4 KB for polygon trade areas), and see where daily volume lands relative to the 128 MB floor. Say a mid-size market emits 20,000 trade-area rows a day at 2 KB each — roughly 40 MB per day. That is below the target file size, so daily partitioning would produce chronically undersized files; monthly partitioning aggregates ~1.2 GB into a handful of right-sized files instead. A high-volume market emitting 400,000 rows a day clears 128 MB comfortably and should stay daily. The rule of thumb: pick the coarsest date granularity that still keeps hot queries selective, then rely on row-group bbox pruning for anything finer.

Row-group sizing is the second-order control. A Parquet file is internally divided into row groups, each carrying its own column statistics — including, with write_covering_bbox=True, a bounding box. A reader skips any row group whose statistics cannot satisfy the query. Smaller row groups (50k rows) prune more precisely but add per-group metadata and slightly slow full scans; larger groups (150k rows) are leaner but coarser. For spatially and temporally selective dashboard queries, 100k is a safe default that keeps both the metadata footprint and the pruning granularity reasonable.

Annotated Write and Read-Back #

The script below writes a partitioned GeoParquet dataset with the CRS pinned in metadata, then reads it back with a partition filter to prove that pushdown only touches the relevant directories. The comments flag every place CRS or partitioning can silently go wrong.

import geopandas as gpd
import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq
import pyarrow.dataset as ds
from pyproj import CRS

STORAGE_CRS = CRS.from_epsg(4326)          # single interchange CRS for the lake
LAKE_ROOT = "s3://retail-geolake/trade_areas"


def write_partitioned(gdf: gpd.GeoDataFrame) -> None:
    """Write a GeoDataFrame as region/date-partitioned GeoParquet.

    CRS is asserted before write and persisted in the GeoParquet metadata so
    every reader recovers it without guessing.
    """
    # 1. Assert the CRS explicitly. Never write geometry with an unknown CRS.
    assert gdf.crs is not None, "GeoDataFrame has no CRS; refusing to write"
    if CRS.from_user_input(gdf.crs) != STORAGE_CRS:
        gdf = gdf.to_crs(STORAGE_CRS)      # normalize to the lake's CRS

    # 2. Derive partition columns. 'region' is low-cardinality business key;
    #    'date' is truncated to the day. Both must be plain columns, not index.
    gdf = gdf.copy()
    gdf["date"] = pd.to_datetime(gdf["captured_at"]).dt.strftime("%Y-%m-%d")

    # 3. Write. geopandas 0.14+ serializes the geometry + CRS into the
    #    GeoParquet 'geo' metadata block automatically. write_covering_bbox
    #    adds a per-row bbox column that enables row-group spatial pruning.
    gdf.to_parquet(
        LAKE_ROOT,
        partition_cols=["region", "date"],   # -> region=.../date=.../*.parquet
        row_group_size=100_000,              # finer bbox pruning within a file
        compression="zstd",
        write_covering_bbox=True,            # spatial predicate pushdown
        index=False,
    )


def read_region_month(region: str, month_prefix: str) -> gpd.GeoDataFrame:
    """Read back ONLY the files for one region and one month via partition
    filters. Pyarrow prunes directories before opening any file."""
    dataset = ds.dataset(
        LAKE_ROOT,
        format="parquet",
        partitioning="hive",                 # parse region=/date= from paths
    )
    # Partition filter: pushed down to directory pruning, not a post-read filter.
    flt = (ds.field("region") == region) & \
          (ds.field("date").cast(pa.string()).starts_with(month_prefix))
    table = dataset.to_table(filter=flt)

    gdf = gpd.GeoDataFrame.from_arrow(table)
    # Recover and re-assert the CRS that was persisted in metadata.
    assert gdf.crs is not None, "CRS not recovered from GeoParquet metadata"
    assert CRS.from_user_input(gdf.crs) == STORAGE_CRS
    return gdf


if __name__ == "__main__":
    df = read_region_month("midwest", "2026-07")
    print(f"rows: {len(df)}  crs: {df.crs.to_epsg()}")

The write_covering_bbox=True flag is what connects partitioning to spatial pushdown. Partition pruning handles region and date; the per-row bounding-box column lets a reader further skip row groups whose bbox does not intersect the query window. Together they mean a spatial-plus-temporal query reads only the geometry it needs. This same partitioned layout is how isochrone contours are stored at scale — isochrone generation and network analysis writes drive-time polygons as partitioned GeoParquet keyed by region and travel-time band for exactly this pruning benefit.

Failure Modes and Debugging #

Two failures dominate in practice. The small-file problem is insidious because it does not error — the lake works, just slowly and expensively, as S3 request counts balloon. Monitor the file-count-to-data-ratio and compact when a partition holds many sub-64-MB files. The second is CRS not persisted: if geometry is ever written through a code path that bypasses GeoParquet’s metadata (for example, dumping WKB into a plain Parquet column), the CRS is lost and every downstream reader is guessing. The assertion in read_region_month is there precisely to fail loudly when that has happened upstream.

Compacting Small Partitions #

When a low-volume region has already accumulated many undersized files — because it was partitioned daily before the volume was understood — the remedy is a compaction pass that reads a partition’s fragments and rewrites them as one right-sized file. Run it as a periodic maintenance job, not on the write path, so it never blocks ingestion.

def compact_partition(region: str, month_prefix: str) -> None:
    """Rewrite one region-month's many small files into one compacted file.
    CRS is re-asserted so compaction never silently drops the projection."""
    gdf = read_region_month(region, month_prefix)     # partition-filtered read
    assert CRS.from_user_input(gdf.crs) == STORAGE_CRS
    gdf.to_parquet(
        f"{LAKE_ROOT}/region={region}/date={month_prefix}",
        row_group_size=100_000,
        compression="zstd",
        write_covering_bbox=True,
        index=False,
    )

Compaction trades a periodic rewrite cost for a permanent read-latency win. Schedule it against partitions whose file count crosses a threshold, and delete the superseded fragments in the same transaction so readers never see a mix of old and new.

Verification #

Confirm the layout does what it claims before trusting it:

Partition count is sane. List the prefixes and count region=*/date=* directories. It should match regions × active dates, not balloon into the tens of thousands. Pushdown reads only relevant files: inspect which fragments a filtered read touches — it must be a small subset, not the whole set.

import pyarrow.dataset as ds

dataset = ds.dataset(LAKE_ROOT, format="parquet", partitioning="hive")
fragments = list(dataset.get_fragments())
print(f"partition files: {len(fragments)}")

flt = ds.field("region") == "midwest"
touched = list(dataset.get_fragments(filter=flt))
print(f"files scanned for one region: {len(touched)} of {len(fragments)}")

The remaining checks are quick to assert:

• CRS round-trips. After reading back, gdf.crs.to_epsg() returns 4326; the assertion in the reader guards this automatically.
• File sizes are in band. Spot-check that individual part-*.parquet files land in the 128–512 MB target, not clustered near a few hundred KB.
• Row totals reconcile. The sum of rows across all partitions equals the source row count — a partition write that silently drops the small-cardinality date column would fail this.

A read that touches only a handful of fragments for a single-region query, with the CRS recovered from metadata, is the accepted end state — pruning is working and the geometry is self-describing.

Frequently Asked Questions #

Related #

• Configuring AWS S3 for Geospatial Data Lakes — the bucket, prefix, and access-control baseline this layout sits on.
• Best Practices for Securing PII in Customer Location Datasets — governing sensitive location data before it lands in a partitioned lake.
• Isochrone Generation & Network Analysis — stores drive-time contours as region-partitioned GeoParquet for the same pushdown benefit.

← Back to Configuring AWS S3 for Geospatial Data Lakes