Spatial Epi Public Health GIS

Cloud-Native Geospatial Storage for Surveillance

This guide is part of the Surveillance Pipeline Automation & Spatial Data Quality section. It extends the single-file format reasoning in Spatial Data Types & Formats to the case that actually occurs in a running system: an archive that grows nightly, is queried by date and area, and must still answer a question about 2019.

Concept & Epidemiological Alignment

Cloud-native geospatial storage means formats whose internal layout allows a reader to fetch only the bytes it needs, over a network, without a server process in between. A columnar table with row-group statistics lets a query for one county in one month read a few megabytes of a multi-terabyte archive. A tiled, overviewed raster lets a zonal statistic over one city read one tile rather than the whole state.

For surveillance the relevant consequence is operational rather than architectural. The archive is queried in two shapes: recent and everywhere, for the nightly analysis, and historical and local, for an investigation into one area. A layout that serves the first and not the second produces a system where routine work is fast and every question anyone actually asks requires a full scan.

The second consequence is about governance. A columnar archive partitioned by date and area makes retention enforceable — a policy that deletes protected intermediates after 90 days is a partition drop, not a scan-and-delete across a billion rows. It also makes access control expressible at partition granularity, so an analyst authorised for one jurisdiction reads only its partitions.

The third is about the past. Boundaries and denominators change, and an archive that stores only current geography loses the ability to answer historical questions on the geography that was current then. Vintage becomes a first-class dimension of the layout rather than a property of whichever file somebody happened to keep.

Format and Layout Choices

Need Format Layout Why
Case and area tables that grow nightly GeoParquet Partition by event date, then jurisdiction Predicate pushdown on both query shapes
Boundary and reference geography GeoParquet or GeoPackage One file per vintage, never overwritten Vintage is the identity of the file
Exposure and modelled rasters Cloud-optimized GeoTIFF Internal tiling plus overviews Windowed reads and cheap previews
Very large multi-band time series Zarr or a tiled raster set Chunked on time and space Time-series extraction without full reads
Ad hoc analyst queries DuckDB over the Parquet archive No copy, no server Analysts query the archive in place
Web map delivery Vector tiles Pre-generated, from suppressed data only Never serve raw geometry to a browser
Two Query Shapes, One Layout A grid representing an archive partitioned by event date across the horizontal axis and by jurisdiction down the vertical axis. The nightly analysis query selects the most recent date column across every jurisdiction, reading one narrow slice. An investigation query selects one jurisdiction row across several historical years, reading a different narrow slice. Both are served by the same partitioning because the predicate pushes down on either dimension. A third pattern, an unpartitioned archive, is shown as requiring a full scan for the second query. Partition on both axes the system is actually queried by county A county B county C county D county E 2022 2024 this week nightly query latest date, all jurisdictions investigation one county, several years Both slices are narrow; an unpartitioned archive makes the second a full scan and retention becomes a partition drop rather than a delete across a billion rows

Spatial Data Prerequisites

  • A declared partition scheme, chosen from the two query shapes the system actually serves, and applied consistently across tables.
  • A single working coordinate reference system for the archive, with the authority string written into every file’s metadata.
  • Geometry encoded per the GeoParquet specification, with the coordinate system recorded in the file metadata rather than assumed.
  • Vintage as a column and a partition key for reference geography, never as a filename convention.
  • Raster tiling and overviews present in every raster written, so windowed reads are possible.
  • A retention policy expressed in partitions, so enforcement is a drop rather than a scan.

Production Implementation

The routines below write a partitioned GeoParquet archive with sensible row-group sizing, write a properly tiled and overviewed raster, and query the archive without loading it.

# Writing and querying a partitioned cloud-native surveillance archive.
# Pinned: geopandas==0.14.4, pyarrow==16.1.0, rasterio==1.3.10, duckdb==1.0.0
import logging
import os

import geopandas as gpd
import pyarrow as pa
import pyarrow.parquet as pq
import rasterio
from rasterio.enums import Resampling

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("storage.archive")

TARGET_ROWGROUP_ROWS = 100_000      # large enough for pushdown, small enough to skip


def write_partitioned(gdf: gpd.GeoDataFrame, root: str,
                      partition_cols=("event_date", "jurisdiction")) -> None:
    """Write GeoParquet partitioned on the columns the archive is queried by."""
    if gdf.crs is None:
        raise ValueError("refusing to write geometry with no CRS")
    missing = [c for c in partition_cols if c not in gdf.columns]
    if missing:
        raise KeyError(f"partition columns absent: {missing}")
    # Sort within partitions so row-group statistics are tight and skippable.
    gdf = gdf.sort_values(list(partition_cols) + ["geoid"])
    gdf.to_parquet(root, index=False, partition_cols=list(partition_cols),
                   row_group_size=TARGET_ROWGROUP_ROWS,
                   compression="zstd", geometry_encoding="WKB",
                   write_covering_bbox=True)     # bbox columns enable spatial pushdown
    log.info("wrote %d rows to %s partitioned by %s (crs=%s)",
             len(gdf), root, list(partition_cols), gdf.crs.to_string())


def write_cog(src_path: str, dst_path: str, blocksize: int = 512,
              overview_levels=(2, 4, 8, 16)) -> None:
    """Rewrite a raster as a tiled, overviewed cloud-optimized GeoTIFF."""
    with rasterio.open(src_path) as src:
        profile = src.profile.copy()
        profile.update(driver="GTiff", tiled=True, blockxsize=blocksize,
                       blockysize=blocksize, compress="deflate", predictor=2,
                       BIGTIFF="IF_SAFER")
        with rasterio.open(dst_path, "w", **profile) as dst:
            for i in range(1, src.count + 1):
                dst.write(src.read(i), i)
                dst.set_band_description(i, src.descriptions[i - 1] or f"band{i}")
            # Overviews must be built INSIDE the file, or a reader fetches full
            # resolution to draw a thumbnail.
            dst.build_overviews(list(overview_levels), Resampling.average)
            dst.update_tags(ns="rio_overview", resampling="average")
    log.info("wrote COG %s tiled=%d overviews=%s", dst_path, blocksize,
             list(overview_levels))


def query_archive(root: str, sql: str) -> "pa.Table":
    """Run SQL over the Parquet archive in place — no load, no server."""
    import duckdb
    con = duckdb.connect()
    con.execute("INSTALL spatial; LOAD spatial;")
    view = f"read_parquet('{os.path.join(root, '**', '*.parquet')}', " \
           f"hive_partitioning=true)"
    full = sql.replace("{archive}", view)
    log.info("query: %s", " ".join(full.split())[:160])
    return con.execute(full).arrow()

Three details in that code are the difference between an archive and a pile of files. Sorting within partitions before writing makes the row-group minimum and maximum statistics tight, which is what lets a reader skip groups; an unsorted write produces groups whose ranges all overlap and no skipping happens. The covering bounding-box columns enable spatial predicate pushdown, so a query for one county reads only the groups that intersect it. And overviews built inside the raster are what make a preview cheap — a file without them forces a full-resolution read for a thumbnail, which is the most common reason a map viewer over an archive feels slow.

The hive_partitioning flag matters because it exposes the partition columns as queryable fields derived from the directory names, so a date predicate eliminates whole directories before any file is opened.

Parameter Selection & Tuning

Partition grain. Daily partitions per jurisdiction produce a very large number of small files in a state with many counties, and small files are the dominant cost in object storage. Monthly partitions with a date column inside are usually the better trade; the row-group statistics still allow date pushdown within the month.

Row-group size. Too small and metadata dominates; too large and skipping is coarse. Around 100,000 rows or 128 MB is a reasonable starting point, tuned by measuring how much of a typical query’s bytes were actually needed.

Compression. Zstandard at a moderate level beats the older defaults on both size and decompression speed for this data. For rasters, deflate with a horizontal predictor suits continuous surfaces; lossless is not negotiable for anything analytic.

Raster block size. 512 pixels is a good default. Smaller blocks suit point sampling; larger blocks suit whole-area reads. The choice should follow the dominant access pattern, which for exposure assignment is point sampling.

Overview levels. Enough to reach a thumbnail in two or three steps. Missing overviews are the single most common defect in a raster archive.

Edge Cases & Failure Modes

Small-file proliferation. A pipeline writing one file per run per partition accumulates thousands of tiny files, and query planning time exceeds read time. Compact partitions on a schedule, keeping the pre-compaction files only as long as the provenance requires.

Coordinate system lost in translation. Some writers drop the coordinate reference system into a non-standard key that other readers ignore. Assert on read that the system is present and is the expected one, rather than trusting the write.

Schema drift across partitions. A column added in 2024 does not exist in the 2019 partitions, and a naive scan fails or silently nulls. Maintain an explicit schema with defaults, and evolve it deliberately.

Raster written without tiling. A striped GeoTIFF forces a full-width read for any window, so a point sample reads an entire row of the state. Assert tiling on write.

Vintage confusion. A boundary file overwritten in place destroys the ability to reproduce anything computed with the previous version. Reference geography is append-only; this is covered in Versioning Boundary Files Across Vintages.

Protected data in a convenient location. A Parquet archive is easy to copy, and an analyst copying a partition to a laptop for a quick query is the most likely exfiltration route in the whole system. Partition-level access control and query-in-place tooling exist partly to remove the motive.

What Layout Buys on a Real Query Bytes read for a query selecting one county and one month, under three layouts. An unpartitioned, unsorted archive reads essentially the whole dataset because no statistics allow skipping. A partitioned but unsorted archive reads one partition, which is far less but still includes every row group in it. A partitioned and sorted archive with covering bounding boxes reads only the row groups whose statistics intersect the predicate, which is a small fraction again. The chart notes that sorting costs write time once and saves read time on every query. Sorting costs once at write and pays back on every read unpartitioned full scan of every partition partitioned only one whole partition partitioned and sorted matching row groups only bytes read for: one county, one month, from a multi-year archive The third layout also makes the query affordable directly from object storage

Schema drift across years of partitions is the failure that surfaces latest and most confusingly, so its shape is worth recognising:

Schema Drift Across Years of Partitions A grid of four yearly partitions against five columns. The identifier, event date and case count columns are present in every year. A geocode match type column exists only from 2022 onward, so earlier partitions return null for it under a union-by-name read. A column named tract in 2019 and 2020 was renamed tract geoid in 2021, so a naive read returns two half-empty columns. An explicit schema with declared defaults and a rename map is what keeps a query over all four years correct. A decade-long archive needs an explicit, evolving schema 2019 2020 2022 2024 record_id event_date cases tract / tract_geoid geocode_match tract tract tract_geoid tract_geoid rename map for tract, declared default for geocode_match dashed cells are absent in that partition; outlined cells use the old column name

Compliance & Audit Controls

Record the partition scheme and the reason for its grain, the coordinate reference system written into file metadata, the schema with its evolution history, the row-group and compression settings, the compaction schedule, and the retention policy expressed as partition lifetimes.

Access control belongs at partition granularity, and the layout should make it natural: a jurisdiction partition key lets a data-sharing agreement be enforced by the storage layer rather than by a query convention. Query-in-place tooling matters for the same reason — an analyst who can answer a question without copying a partition has no reason to create an uncontrolled copy, and uncontrolled copies are the most common way protected surveillance data leaves an agency.

Every file in the archive should carry, in its own metadata, the run identifier that produced it and the configuration digest of that run. That single convention means a file found in isolation — attached to an email, on a shared drive — can still be traced to its provenance row, which is what makes an audit of an unexpected copy possible rather than merely alarming.

Production Implementation Checklist

Frequently Asked Questions

GeoParquet or PostGIS? They answer different questions. A database is the right home for transactional work, row-level updates and concurrent editing. A partitioned columnar archive is the right home for an append-only analytical record that many readers scan. Most surveillance systems want both: a database for the operational case register, an archive for the analytic history.

How do I query this without setting up a cluster? An in-process engine reading Parquet directly handles archives into the terabytes on a single machine, because the layout means a selective query reads a small fraction. Reach for distributed compute when a query genuinely needs most of the archive, which for surveillance work is rare.

Does partitioning by jurisdiction break cross-boundary analysis? Not if the partition is a physical layout rather than an analytical boundary. A query that spans jurisdictions reads several partitions, which is exactly what the engine is for. The problem to avoid is partitioning so finely that a cross-boundary query opens tens of thousands of files.

What about the geometry column itself? Store it as well-known binary in the projected working system, with the coordinate reference system in the file metadata per the GeoParquet specification. Storing geographic coordinates and projecting on read is slower and invites the mismatch errors that this layout otherwise prevents.

How long should protected partitions be retained? As long as the governing policy requires and no longer, with the lifetime enforced by the storage layer. The value of the partition layout here is that the policy becomes a mechanical drop rather than a project, which is what makes it actually happen.