How to Diff Two GeoPackage Files

ATTACH the second container and compare in SQL, keyed on a business identifier rather than on row order. Never compare the files byte for byte: two containers built from identical data differ in page allocation, timestamps and free-list state, so a hash comparison reports a difference every time and tells you nothing.

This page belongs to the Data Quality Gates & Monitoring guide, which covers the manifest comparison this falls back to when a summary is not enough.

Why This Matters #

Two questions come up constantly and have different answers. “Did this run produce the same thing as last night?” is answered by the manifest — layer sets, counts, schema — in milliseconds. “What exactly changed in the parcels layer?” is not, and that is where a real diff is needed: which rows appeared, which vanished, which attributes moved, and whether any geometry actually changed.

Doing it by hashing the files answers neither. SQLite makes no promise of byte-level reproducibility, and it does not need to: the two containers can hold identical data and differ in every page.

Prerequisites #

• Two GeoPackage containers with a comparable layer
• A stable business key per layer — fid is a row identifier, not an identity
• sqlite3, and mod_spatialite for the geometry comparison
• A tolerance decided in advance for anything numeric

Primary Method #

# gpkg_diff.py — attach and compare in SQL, keyed on a business identifier
import sqlite3


def diff_layer(a_path: str, b_path: str, layer: str, key: str) -> dict:
    conn = sqlite3.connect(f"file:{a_path}?mode=ro", uri=True)
    conn.execute("ATTACH DATABASE ? AS b", (f"file:{b_path}?mode=ro",))
    try:
        added = [r[0] for r in conn.execute(f"""
            SELECT b."{key}" FROM b."{layer}" b
            WHERE b."{key}" NOT IN (SELECT "{key}" FROM main."{layer}")
        """)]
        removed = [r[0] for r in conn.execute(f"""
            SELECT a."{key}" FROM main."{layer}" a
            WHERE a."{key}" NOT IN (SELECT "{key}" FROM b."{layer}")
        """)]
        return {"added": added, "removed": removed}
    finally:
        conn.close()

ATTACH on a read-only URI is what makes this cheap: both containers are open in one connection, the comparison runs inside SQLite, and only the differences cross into Python. Reading both into memory and comparing there works and is orders of magnitude slower on anything real.

Step-by-Step Walkthrough #

1. Compare the layer sets first #

-- Attached as b; layers present in one container and not the other
SELECT 'only in A' AS side, table_name FROM main.gpkg_contents
WHERE table_name NOT IN (SELECT table_name FROM b.gpkg_contents)
UNION ALL
SELECT 'only in B', table_name FROM b.gpkg_contents
WHERE table_name NOT IN (SELECT table_name FROM main.gpkg_contents);

If that returns rows, the deeper comparison is usually not worth running until it is understood — two containers with different layer sets are answering different questions.

2. Key on identity, not on row order #

fid is assigned by the writer and is not stable across rebuilds: the same feature can be fid 41 in one container and 903 in another. Comparing on it produces a diff where every row appears changed.

# Wrong: fid is a row identifier, not an identity
diff_layer(a, b, "parcels", key="fid")

# Right: the business key the data actually carries
diff_layer(a, b, "parcels", key="parcel_ref")

Where no business key exists, the honest options are to add one upstream or to compare geometrically — matching features by spatial coincidence — which is slower and gives approximate answers.

3. Compare attributes for rows present in both #

-- Rows whose attributes differ, with the differing values side by side
SELECT a.parcel_ref,
       a.area_m2   AS a_area,   b.area_m2   AS b_area,
       a.surveyed  AS a_surv,   b.surveyed  AS b_surv
FROM main.parcels a
JOIN b.parcels b ON b.parcel_ref = a.parcel_ref
WHERE a.area_m2 IS NOT b.area_m2
   OR a.surveyed IS NOT b.surveyed;

IS NOT rather than <> is deliberate: <> is unknown when either side is NULL, so a column that gained or lost a NULL would not appear in the result. IS NOT handles NULL as a value and reports the change.

4. Compare geometry with a predicate, not with bytes #

-- Geometry that genuinely differs, using a topological comparison
SELECT a.parcel_ref,
       round(ST_Area(a.geom), 4) AS a_area,
       round(ST_Area(b.geom), 4) AS b_area
FROM main.parcels a
JOIN b.parcels b ON b.parcel_ref = a.parcel_ref
WHERE NOT ST_Equals(a.geom, b.geom);

Comparing the BLOBs directly reports a difference for a geometry that was normalised, whose rings were rotated to a canonical start vertex, or whose components were reordered — all of which describe the same shape. ST_Equals compares what the geometry means rather than how it was serialised.

5. Produce a summary a person can read #

def summarise(a_path: str, b_path: str, layers: dict[str, str]) -> str:
    lines = []
    for layer, key in layers.items():
        d = diff_layer(a_path, b_path, layer, key)
        changed = attribute_diff(a_path, b_path, layer, key)
        moved = geometry_diff(a_path, b_path, layer, key)
        lines.append(
            f"{layer}: +{len(d['added'])} −{len(d['removed'])} "
            f"~{len(changed)} attrs, ~{len(moved)} geometry"
        )
        for ref in d["added"][:5]:
            lines.append(f"    added:   {ref}")
        for ref in d["removed"][:5]:
            lines.append(f"    removed: {ref}")
    return "\n".join(lines)

Capping the examples at five keeps the output readable while still naming enough to start an investigation. The counts are the summary; the identifiers are the entry point.

Verification #

# A container diffed against itself must report nothing
d = diff_layer("field.gpkg", "field.gpkg", "parcels", "parcel_ref")
assert not d["added"] and not d["removed"], d

# And a known change must be reported exactly
import shutil, sqlite3
shutil.copy("field.gpkg", "modified.gpkg")
conn = sqlite3.connect("modified.gpkg")
conn.execute("DELETE FROM parcels WHERE parcel_ref = 'P-002'")
conn.execute("UPDATE parcels SET area_m2 = area_m2 + 1 WHERE parcel_ref = 'P-001'")
conn.commit(); conn.close()

d = diff_layer("field.gpkg", "modified.gpkg", "parcels", "parcel_ref")
assert d["removed"] == ["P-002"], d
assert not d["added"], d

The self-diff is the more important of the two. A diff that reports differences between a container and itself is comparing representation rather than content, and every result it produces afterwards is noise.

# A rebuild from identical source must also diff clean
subprocess.run(["./build.sh", "source/", "rebuild.gpkg"], check=True)
d = diff_layer("field.gpkg", "rebuild.gpkg", "parcels", "parcel_ref")
assert not d["added"] and not d["removed"], (
    "a rebuild from the same source produced different rows"
)

Alternative Approaches or Edge Cases #

ogr_layer_algebra and ogrinfo. GDAL’s tools compare at the layer level and are convenient for a quick look, at the cost of a GDAL dependency and less control over the key. For a one-off investigation they are quicker than writing anything; for a repeatable check the SQL version is more precise.

Diffing across formats. ATTACH requires both sides to be SQLite, so comparing a GeoPackage against a Shapefile means converting first. Convert the Shapefile into a temporary GeoPackage rather than the other way round — the conversion is lossless in that direction and lossy in the other.

Very large layers. The keyed comparison is two anti-joins and benefits enormously from an index on the business key. Where none exists, creating one on a temporary copy costs less than the scans it avoids.

Troubleshooting #

Everything appears added and removed #

Cause: Keyed on fid, or on a column that is not stable across builds. Fix: Use a business key. The self-diff test catches this in one line.

Every geometry appears changed #

Cause: Comparing blobs rather than geometry, and something in the pipeline normalised. Fix: Use ST_Equals. A normalisation pass rewrites every byte and moves no vertex.

ATTACH fails with “database is locked” #

Cause: One of the containers is open read-write elsewhere. Fix: Attach with the mode=ro URI form, as in the primary method, and confirm nothing else holds a write lock — a diff has no reason to be a writer.

Frequently Asked Questions #

Related #

• Data Quality Gates & Monitoring — parent guide: the manifest comparison this complements
• Detecting Schema Drift Between Sync Cycles — comparing structure rather than content
• Tracking Row Changes for Incremental GeoPackage Sync — recording changes as they happen, rather than reconstructing them
• Geometry Validity & Topology Repair — a common source of the normalisation that makes a blob diff useless