SpatiaLite & GeoPackage Automation with Python

Alerting on Stale Spatial Indexes

Compare the index's entry count against the layer's non-null geometry count, then compare a sample of stored extents against the geometry they claim to…

Compare the index’s entry count against the layer’s non-null geometry count, then compare a sample of stored extents against the geometry they claim to describe. The first check is one query and catches a missing rebuild; the second is the only thing that catches an index whose counts match and whose extents are wrong, which is what an in-place geometry update leaves behind.

This page belongs to the Data Quality Gates & Monitoring guide.

Why This Matters

A stale spatial index is the only defect on this site that makes a correct query return a wrong answer with no error, no warning and no visible symptom in the container. Every structural check passes. Conformance passes. Feature counts are right. The schema is unchanged. And a bounding-box query silently omits rows, because the index it consulted does not describe where the features actually are.

Two things produce it. A bulk load with the triggers dropped writes rows and no index entries, which the count check catches. An in-place geometry update — a repair pass, a reprojection, a snap — changes bounding boxes while the entry count stays identical, which only an extent comparison catches.

Prerequisites

  • A container with a spatial index, per How to Create a Spatial Index in SQLite with Python
  • mod_spatialite for the extent comparison; the count check needs nothing
  • Knowledge of which index naming the container uses — the two formats differ
  • A place to run the check: a gate, a nightly job, or both

Primary Method

python
# stale_index.py — the two checks, in increasing order of cost
import sqlite3


def index_health(path: str) -> list[dict]:
    """One report per indexed layer: entry count, geometry count, extent drift."""
    conn = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    conn.enable_load_extension(True)
    conn.load_extension("mod_spatialite")

    layers = conn.execute("""
        SELECT c.table_name, g.column_name
        FROM gpkg_contents c
        JOIN gpkg_geometry_columns g ON g.table_name = c.table_name
        WHERE c.data_type = 'features'
    """).fetchall()

    out = []
    for table, geom in layers:
        rtree = f"rtree_{table}_{geom}"
        exists = conn.execute(
            "SELECT count(*) FROM sqlite_master WHERE name = ?", (rtree,)
        ).fetchone()[0]
        if not exists:
            out.append({"layer": table, "state": "no index"})
            continue

        entries = conn.execute(f'SELECT count(*) FROM "{rtree}"').fetchone()[0]
        geoms = conn.execute(
            f'SELECT count(*) FROM "{table}" WHERE "{geom}" IS NOT NULL'
        ).fetchone()[0]

        # The cheap check: do the counts agree?
        if entries != geoms:
            out.append({"layer": table, "state": "count mismatch",
                        "entries": entries, "geometries": geoms})
            continue

        # The check that matters: do the stored extents match the geometry?
        drifted = conn.execute(f"""
            SELECT count(*) FROM "{table}" t
            JOIN "{rtree}" r ON r.id = t.rowid
            WHERE t."{geom}" IS NOT NULL
              AND (abs(r.minx - ST_MinX(t."{geom}")) > 1e-9
                OR abs(r.maxx - ST_MaxX(t."{geom}")) > 1e-9
                OR abs(r.miny - ST_MinY(t."{geom}")) > 1e-9
                OR abs(r.maxy - ST_MaxY(t."{geom}")) > 1e-9)
        """).fetchone()[0]

        out.append({
            "layer": table,
            "state": "stale extents" if drifted else "healthy",
            "entries": entries, "drifted": drifted,
        })

    conn.close()
    return out

The extent comparison uses ST_MinX and its siblings, which read the geometry header rather than decoding coordinates — so despite being a join across the whole layer it is far cheaper than a validity scan.

Two failure modes, two checksA bulk load with the index triggers dropped leaves fewer index entries than geometries, which a count comparison catches immediately and cheaply. An in-place geometry update — a repair, a reprojection, a snap — leaves the entry count identical while the stored bounding boxes describe where the features used to be, which only a comparison of the stored extents against the geometry can detect.missing entriesbulk load, triggers droppedentry count < geometry countone query catches iteffectively freestale extentsgeometry updated in placecounts match exactlyneeds an extent comparisona join, still cheapa monitor that only counts is blind to the right-hand column
The right-hand case is the one that survives every other check in a pipeline, which is why the count comparison alone is not enough.

Step-by-Step Walkthrough

1. Handle both index naming schemes

GeoPackage names the R-tree rtree_<table>_<geom>; SpatiaLite names it idx_<table>_<geom>. A container can carry either, and code that assumes one silently reports “no index” on the other.

python
def rtree_name(conn, table: str, geom: str) -> str | None:
    for candidate in (f"rtree_{table}_{geom}", f"idx_{table}_{geom}"):
        found = conn.execute(
            "SELECT count(*) FROM sqlite_master WHERE type='table' AND name = ?",
            (candidate,),
        ).fetchone()[0]
        if found:
            return candidate
    return None

The column names differ too: the GeoPackage R-tree uses minx/maxx/miny/maxy, while SpatiaLite’s uses xmin/xmax/ymin/ymax. Detecting the flavour once and selecting the column names from it keeps the comparison query correct on both.

2. Report per layer, with a state rather than a boolean

python
for report in index_health("field.gpkg"):
    layer, state = report["layer"], report["state"]
    if state == "healthy":
        continue
    if state == "no index":
        print(f"WARN  {layer}: no spatial index")
    elif state == "count mismatch":
        print(f"ALERT {layer}: {report['entries']} entries, "
              f"{report['geometries']} geometries")
    elif state == "stale extents":
        print(f"ALERT {layer}: {report['drifted']} entries describe the wrong extent")

Four states, not two. “No index” is a design choice a container may legitimately have made; the other two are defects, and they have different causes worth naming separately in the alert.

3. Rebuild unconditionally when either fires

Both failure modes have the same fix, and the fix is cheap relative to diagnosing which one occurred.

sql
-- SpatiaLite: rebuild from the current geometry, without dropping the virtual table
SELECT RecoverSpatialIndex('parcels', 'geom');
SELECT UpdateLayerStatistics('parcels', 'geom');
sql
-- GeoPackage: repopulate the R-tree from the feature table
DELETE FROM rtree_parcels_geom;
INSERT INTO rtree_parcels_geom (id, minx, maxx, miny, maxy)
SELECT fid, ST_MinX(geom), ST_MaxX(geom), ST_MinY(geom), ST_MaxY(geom)
FROM parcels WHERE geom IS NOT NULL;

Recovery rather than drop-and-recreate keeps the triggers in place, which matters: dropping the virtual table also drops the triggers that keep it current, and recreating one without the other leaves a container that looks healthy today and diverges tomorrow.

Recovery rebuild against drop and recreateA recovery rebuild repopulates the existing virtual table from the current geometry and leaves the triggers that keep it current untouched. Dropping and recreating the virtual table also removes those triggers, so unless they are recreated too the index is correct at that moment and diverges on the next write. The two look identical immediately afterwards and behave differently a day later.recovery rebuildrepopulates in placetriggers untouchedstays correct afterwardsthe safe defaultdrop and recreateremoves the triggers toocorrect at that momentdiverges on the next writeunless the triggers are restoredthe two are indistinguishable until something writes
This is why a container can be "fixed" one day and stale again the next with nobody having done anything unusual.

4. Run the check where it will actually catch things

Three placements, and each catches something the others do not. In the publication gate, it stops a stale index shipping. After any pass that rewrites geometry — repair, reprojection, snapping — it catches the case immediately rather than a day later. Against a copy of a live container on a schedule, it catches an index that went stale through a route nobody anticipated, such as a manual edit in a desktop tool.

bash
# In the gate, after the geometry-modifying stages
python3 stale_index.py "$TMP" || fail "spatial index is stale"

5. Watch the ratio over time

A count mismatch is binary, but the proportion of drifted extents is a useful trend. A layer where a small and stable fraction drifts each cycle points at a specific stage that is not rebuilding; one where the fraction grows points at a trigger that has been dropped and never restored.

python
manifest["index_health"] = {
    r["layer"]: {
        "state": r["state"],
        "drifted_fraction": (r.get("drifted", 0) / max(r.get("entries", 1), 1)),
    }
    for r in index_health(container_path)
}

Verification

The check must be shown to detect both failure modes.

python
# Mode 1: delete index entries, leaving the geometry alone
import shutil, sqlite3

shutil.copy("good.gpkg", "missing.gpkg")
conn = sqlite3.connect("missing.gpkg")
conn.execute("DELETE FROM rtree_parcels_geom WHERE id % 3 = 0")
conn.commit(); conn.close()

states = {r["layer"]: r["state"] for r in index_health("missing.gpkg")}
assert states["parcels"] == "count mismatch", states
python
# Mode 2: move the geometry without touching the index — counts stay equal
shutil.copy("good.gpkg", "drifted.gpkg")
conn = sqlite3.connect("drifted.gpkg")
conn.enable_load_extension(True); conn.load_extension("mod_spatialite")
conn.execute("DROP TRIGGER IF EXISTS rtree_parcels_geom_update1")
conn.execute(
    "UPDATE parcels SET geom = ST_Translate(geom, 1000, 1000) WHERE fid <= 5"
)
conn.commit(); conn.close()

states = {r["layer"]: r["state"] for r in index_health("drifted.gpkg")}
assert states["parcels"] == "stale extents", states
print("both failure modes detected")

The second test is the important one, because a check that only compares counts passes it — and passing it is exactly the failure this page exists to prevent.

Where the check belongsRun immediately after any stage that rewrites geometry, so a missing rebuild is caught at its source. Run in the publication gate, so a stale index never ships. Run on a schedule against a copy of a live container, so an index that went stale through an unanticipated route — a manual edit, a third-party tool — is noticed. Each placement catches something the others cannot.after a geometry-rewriting stagerepair, reprojection, snapping · catches a missing rebuild at its sourcein the publication gatestops a stale index reaching a deviceon a schedule, against a live copycatches staleness introduced by a route nobody anticipated
The third row is the one that finds the manual edit in a desktop tool, which no build-time check ever will.

Alternative Approaches or Edge Cases

Sampling instead of a full join. On a very large layer the extent comparison reads every row’s geometry header. Sampling a few thousand rows catches a systematic problem — which is what these failures are — at a fraction of the cost, and a full check on release builds covers the rest. Say which mode ran in the output, or a sampled check reported as a full one is worse than none.

Layers with no index by design. A small lookup layer may legitimately have none, and reporting it as a defect on every run trains people to ignore the output. Record the intended state per layer in the manifest and compare against that rather than assuming every feature layer should be indexed.

Verifying the planner as well. An index that is present, current and never used is a different problem with the same symptom of slow queries. Asserting the query plan, as covered in How to Benchmark a Spatial Query Regression, is the complementary check.

Troubleshooting

Counts match but queries still miss rows

Cause: Stale extents — the entries exist and describe where the features used to be. Fix: Run the extent comparison; this is precisely the case the count check cannot see.

The check reports “no index” on a container that has one

Cause: The other naming scheme. A SpatiaLite container’s index is idx_<table>_<geom>, not rtree_<table>_<geom>. Fix: Detect both, as in step 1, and remember the extent column names differ too.

The index goes stale again after a rebuild

Cause: The triggers were dropped, most likely by a drop-and-recreate rather than a recovery rebuild. Fix: Use the recovery function, and verify the triggers exist afterwards with a query against sqlite_master for type='trigger' and a name matching the index.

Frequently Asked Questions

How expensive is the extent comparison?

Far cheaper than it looks. ST_MinX and its siblings read the bounding box from the geometry header rather than decoding coordinates, so the join costs roughly a table scan of headers rather than a full geometry decode. On a layer where a validity scan takes minutes, this takes seconds.

Should a stale index block publication?

Yes, because it produces wrong answers rather than slow ones. A container with no index is slow and correct; one with a stale index is fast and wrong, and a field user has no way to tell. It is also trivially fixable at the point of detection, which makes blocking cheap.

Does this apply to tile tables?

No. Tile tables are addressed by an exact primary key on zoom, column and row, and no R-tree is involved — there is nothing to go stale. The whole class of problem is specific to feature tables with a spatial index.