Versioning GeoPackage and PostGIS Round-Trips

Keep a GeoPackage export from PostGIS byte-stable across repeated dumps so a versioned snapshot diff reflects real edits, not serialization noise — part of GeoParquet vs GeoPackage vs Shapefile for Versioned Workflows.

Concept & Context

Many teams treat a GeoPackage as the canonical exchange artifact between an authoritative PostGIS database and a versioned repository. The appeal is real — a single-file SQLite container opens directly in QGIS and travels well. The problem is that a GeoPackage is a database, not a flat file, and a database has many degrees of freedom that have nothing to do with your data: the order rows land in, the value the driver assigns to the fid column, and metadata timestamps baked into the container. Dump the same unchanged PostGIS table twice and, without care, you get two files that differ in thousands of bytes. A version-control tool then reports a change on every export, defeating the entire purpose of snapshotting.

The fix is to remove every non-data degree of freedom so that identical database state always serializes to identical bytes. That means a deterministic ORDER BY, a fid pinned to a stable key, a single fixed SRID, and a write path with volatile metadata stripped. The parent format comparison explains why byte-stability is the precondition for a meaningful diff; a sibling guide applies the same discipline to the Shapefile-to-GeoParquet conversion path. Here the target is the PostGIS round-trip, and once the dump is stable a content-addressable store can encode each real change cleanly, exactly as delta tracking algorithms for vector data require.

Core Round-Trip Steps

  1. Query with a deterministic order. Every export query must end in an explicit ORDER BY on a stable primary key. PostGIS returns rows in physical-scan order otherwise, which changes after any VACUUM, update, or autovacuum cycle.
  2. Pin the fid to the source key. Let the GeoPackage driver bind fid to the source primary key instead of auto-incrementing, so a row keeps the same identity in every dump.
  3. Fix the SRID and snap precision. Reproject to one target SRID up front and snap coordinates to a defined grid so a reprojection round-trip cannot jitter vertices at the least-significant digit.
  4. Write with volatile metadata stripped. Disable driver timestamp metadata and keep the PROJ and GDAL versions pinned, so the container header is a function of the data alone.
  5. Verify stability, then re-import. Hash two successive dumps to prove byte-equality, then load the GeoPackage back into a staging PostGIS schema to confirm the round-trip is lossless.

Working Implementation

The script performs a complete, deterministic PostGIS-to-GeoPackage export and shows the exact ogr2ogr command for the same job. Both paths pin ordering, fid, and SRID.

"""Byte-stable PostGIS -> GeoPackage export for versioned snapshots."""
import os
import subprocess
import hashlib
import pathlib

import geopandas as gpd
import shapely
from sqlalchemy import create_engine

# --- Pinned configuration: every value below is part of the output identity ---
TABLE = "urban_zones"
FID_COLUMN = "zone_id"          # stable source primary key -> GeoPackage fid
OUT_GPKG = "snapshots/urban_zones.gpkg"
LAYER = "urban_zones"
TARGET_SRID = 4326
PRECISION_GRID = 0.000001       # snap so reprojection cannot jitter vertices


def export_geopandas() -> str:
    """Deterministic export via GeoPandas; returns the output content hash."""
    db_url = os.environ["POSTGIS_CONNECTION_STRING"]   # never hardcode credentials
    engine = create_engine(db_url)

    # Deterministic ORDER BY on the stable key is the single most important line here.
    query = f"SELECT * FROM {TABLE} ORDER BY {FID_COLUMN}"
    gdf = gpd.read_postgis(query, con=engine, geom_col="geom")

    # Fix SRID: reproject to one target, then snap precision.
    if gdf.crs is None:
        raise ValueError("Source geometry has no SRID; set one in PostGIS first.")
    gdf = gdf.to_crs(epsg=TARGET_SRID)
    gdf["geom"] = shapely.set_precision(gdf["geom"].values, PRECISION_GRID)

    # Pin the fid: use the source key as the index so the driver preserves it.
    gdf = gdf.set_index(FID_COLUMN, drop=False)
    gdf.index.name = "fid"

    pathlib.Path(OUT_GPKG).parent.mkdir(parents=True, exist_ok=True)
    if os.path.exists(OUT_GPKG):
        os.remove(OUT_GPKG)   # write fresh so no prior page layout leaks in

    gdf.to_file(
        OUT_GPKG,
        layer=LAYER,
        driver="GPKG",
        index=True,           # persist the pinned fid
    )
    return hashlib.sha256(pathlib.Path(OUT_GPKG).read_bytes()).hexdigest()


def export_ogr2ogr() -> None:
    """Equivalent deterministic export using ogr2ogr, for CI shells without Python GIS."""
    conn = os.environ["POSTGIS_CONNECTION_STRING"]
    if os.path.exists(OUT_GPKG):
        os.remove(OUT_GPKG)
    subprocess.run(
        [
            "ogr2ogr",
            "-f", "GPKG", OUT_GPKG,
            f"PG:{conn}",
            "-sql", f"SELECT * FROM {TABLE} ORDER BY {FID_COLUMN}",
            "-nln", LAYER,
            "-preserve_fid",                 # keep row identity stable
            "-t_srs", f"EPSG:{TARGET_SRID}",  # fix the SRID
            "-lco", "FID=" + FID_COLUMN,     # bind fid to the source key
        ],
        check=True,
    )


def reimport_to_staging() -> int:
    """Round-trip the GeoPackage back into a PostGIS staging schema; return row count."""
    gdf = gpd.read_file(OUT_GPKG, layer=LAYER)
    engine = create_engine(os.environ["POSTGIS_CONNECTION_STRING"])
    gdf.to_postgis("urban_zones_staging", con=engine,
                   schema="staging", if_exists="replace", index=False)
    return len(gdf)


if __name__ == "__main__":
    digest = export_geopandas()
    print(f"Exported {OUT_GPKG}\nsha256: {digest}")

Validation & Output Verification

Prove byte-stability by exporting twice and comparing hashes. Because the write path is fully pinned, the two digests must match:

python export.py | tee dump_a.log
python export.py | tee dump_b.log
diff <(grep sha256 dump_a.log) <(grep sha256 dump_b.log) && echo "BYTE-STABLE"

Confirm the round-trip is lossless by comparing feature counts and the fid set against the source table:

import geopandas as gpd
from sqlalchemy import create_engine
import os

engine = create_engine(os.environ["POSTGIS_CONNECTION_STRING"])
src = gpd.read_postgis("SELECT * FROM urban_zones ORDER BY zone_id",
                       con=engine, geom_col="geom")
out = gpd.read_file("snapshots/urban_zones.gpkg", layer="urban_zones")

assert len(out) == len(src), f"row count drift: {len(src)} -> {len(out)}"
assert set(out["fid"]) == set(src["zone_id"]), "fid set changed across round-trip"
assert out.crs.to_epsg() == 4326, f"unexpected SRID: {out.crs}"
print("OK:", len(out), "features, fid preserved, EPSG:4326")

Inspect the container metadata to confirm no timestamp leaked into the header:

ogrinfo -al -so snapshots/urban_zones.gpkg
# Confirm Layer SRS = EPSG:4326 and Feature Count matches the source table.

Once both the hash and the round-trip checks pass, commit the pointer:

dvc add snapshots/urban_zones.gpkg
git add snapshots/urban_zones.gpkg.dvc .gitignore
git commit -m "Snapshot urban_zones as byte-stable GeoPackage"

Failure Modes

  • Symptom: an unchanged table dumps to a different GeoPackage every run. Root cause: the export query lacked ORDER BY, so PostGIS returned rows in physical-scan order that shifts after any write. Fix: always append ORDER BY on a stable primary key.
  • Symptom: the diff shows every feature changed after a single-row edit. Root cause: the driver auto-incremented fid, renumbering every row. Fix: bind fid to the source key with -preserve_fid or the FID= layer-creation option.
  • Symptom: coordinates wobble in the last decimal place between dumps. Root cause: an unpinned PROJ version produced slightly different reprojection results. Fix: fix one target SRID, snap precision with set_precision, and pin the PROJ and GDAL versions in the environment.
  • Symptom: the re-import into PostGIS drops or alters attribute types. Root cause: GeoPackage stores a narrower type set than PostGIS, so a numeric or timestamp column may downcast. Fix: declare explicit column types on re-import and assert the schema after loading into the staging schema.

Back to GeoParquet vs GeoPackage vs Shapefile for Versioned Workflows