SpatiaLite & GeoPackage Automation with Python

How to Automate R-tree Index Rebuilds After Bulk Load

After a fast direct bulk insert that bypasses the OGR or SpatiaLite maintenance triggers, detect the stale spatial index by comparing feature and index…

After a fast direct bulk insert that bypasses the OGR or SpatiaLite maintenance triggers, detect the stale spatial index by comparing feature and index row counts, then rebuild it — DisableSpatialIndex/CreateSpatialIndex for SpatiaLite, or a DELETE plus envelope INSERT for GeoPackage — and confirm the fix with EXPLAIN QUERY PLAN.

This page belongs to the End-to-End Spatial Automation Recipes guide, where the index stage is one link in the ingest-to-sync chain. Here we treat that stage on its own, for both spatial engines.

Why This Matters

The fastest way to load a million geometries into SQLite is a direct executemany insert wrapped in one transaction. It is also the fastest way to silently break spatial queries. The R-tree index that accelerates bounding-box filters is not part of the feature table; it is a separate virtual table kept in sync by triggers that fire on ordinary inserts. A bulk load that inserts rows through a path those triggers do not see — a raw sqlite3 executemany, an ATTACH-and-INSERT SELECT, a restored dump — leaves the R-tree empty or partial.

The failure is insidious because nothing errors. SELECT COUNT(*) shows every feature. Only spatially filtered queries return too few rows, and only because the planner consulted an index that does not know the new geometries exist. Automating a detect-and-rebuild step after every bulk load turns this silent corruption into a deterministic, self-healing stage.

Stale index detection and rebuild flowA bulk load writes into the feature table but not the R-tree, leaving it stale. A staleness check compares the feature row count with the index row count. If they differ, a rebuild repopulates the R-tree from geometry envelopes, after which the counts match.Bulk Loadbypasses triggersStaleness Checkrows vs indexedRebuildfrom envelopesVerifyquery plan
The rebuild stage is only triggered when the staleness check finds a row-count mismatch, keeping the common healthy case cheap.

Prerequisites

  • Python 3.9+ with the standard-library sqlite3 module
  • For SpatiaLite targets: mod_spatialite loadable at runtime
  • For GeoPackage targets: geometry envelope functions (ST_MinX and friends), available via a loaded spatial extension or computed in Python with Shapely 2.0+
  • A feature table already bulk-loaded through a trigger-bypassing path
  • Familiarity with native sqlite3 spatial extensions for extension loading

Primary Method

The function below auto-detects the engine, checks for staleness, rebuilds only when needed, and returns what it did. It is the whole recipe; the walkthrough dissects each part.

python
# -- SpatiaLite / GeoPackage context: detect and repair a stale R-tree
import sqlite3

def rebuild_spatial_index_if_stale(
    conn: sqlite3.Connection, table: str, geom_col: str
) -> dict:
    """Rebuild the R-tree for a table only when its index is out of sync."""
    engine = _detect_engine(conn, table)
    features = conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0]
    indexed = _index_row_count(conn, engine, table, geom_col)

    if features == indexed:
        return {"engine": engine, "rebuilt": False, "features": features}

    if engine == "spatialite":
        conn.execute(f"SELECT DisableSpatialIndex('{table}', '{geom_col}')")
        conn.execute(f"SELECT CreateSpatialIndex('{table}', '{geom_col}')")
    else:  # geopackage
        rtree = f"rtree_{table}_{geom_col}"
        conn.execute(f"DELETE FROM {rtree}")
        conn.execute(
            f"INSERT INTO {rtree} "
            f"SELECT fid, ST_MinX({geom_col}), ST_MaxX({geom_col}), "
            f"ST_MinY({geom_col}), ST_MaxY({geom_col}) "
            f"FROM {table} WHERE {geom_col} IS NOT NULL"
        )
    conn.commit()
    return {"engine": engine, "rebuilt": True, "features": features}

Step-by-Step Walkthrough

1. Detect which engine the file uses

A GeoPackage advertises itself with the gpkg_contents table; a SpatiaLite database uses geometry_columns. Probing for those tables tells the rebuild which dialect to speak.

python
import sqlite3

def _detect_engine(conn: sqlite3.Connection, table: str) -> str:
    """Return 'geopackage' or 'spatialite' based on the metadata tables present."""
    names = {
        row[0]
        for row in conn.execute(
            "SELECT name FROM sqlite_master WHERE type='table'"
        )
    }
    if "gpkg_contents" in names:
        return "geopackage"
    if "geometry_columns" in names:
        return "spatialite"
    raise ValueError("no recognized spatial metadata tables found")

For background on how these registries differ, see SpatiaLite metadata tables explained.

2. Measure staleness by row count

The cheapest staleness signal is a count mismatch between the feature table and the index’s backing store. For GeoPackage the R-tree rows live in rtree_<table>_<geom>; for SpatiaLite they live in the idx_<table>_<geom> virtual table’s node store, most reliably counted through the R-tree’s own rowid table.

python
import sqlite3

def _index_row_count(
    conn: sqlite3.Connection, engine: str, table: str, geom_col: str
) -> int:
    """Count rows the spatial index currently holds for the table."""
    if engine == "geopackage":
        rtree = f"rtree_{table}_{geom_col}"
    else:
        rtree = f"idx_{table}_{geom_col}"
    try:
        return conn.execute(f"SELECT COUNT(*) FROM {rtree}").fetchone()[0]
    except sqlite3.OperationalError:
        return 0  # index table absent entirely -> maximally stale

A count of zero against a populated feature table is the classic signature of a bulk load that never touched the triggers.

3. Rebuild for SpatiaLite

SpatiaLite exposes two SQL functions that do the work. DisableSpatialIndex detaches and drops the R-tree virtual table and its triggers; CreateSpatialIndex recreates them and populates the index from scratch over the current geometries. Running the pair is the canonical full rebuild.

python
# -- SpatiaLite context: full R-tree rebuild
def rebuild_spatialite(conn, table: str, geom_col: str) -> None:
    """Drop and recreate the SpatiaLite R-tree over current geometries."""
    conn.execute(f"SELECT DisableSpatialIndex('{table}', '{geom_col}')")
    conn.execute(f"SELECT CreateSpatialIndex('{table}', '{geom_col}')")
    conn.commit()

CreateSpatialIndex reads every geometry once and builds the tree in bulk, which is far faster than the incremental per-row maintenance the triggers would have done during the load — the reason skipping the triggers during bulk insert is a deliberate optimization, not just an oversight.

4. Rebuild for GeoPackage

GeoPackage has no CreateSpatialIndex function; you repopulate the rtree_* shadow table directly from geometry envelopes. Clear it first so the rebuild is idempotent, then insert one row per feature keyed by fid.

python
# -- GeoPackage context: repopulate the R-tree shadow table
def rebuild_geopackage(conn, table: str, geom_col: str) -> None:
    """Refill the GeoPackage R-tree from feature bounding boxes."""
    rtree = f"rtree_{table}_{geom_col}"
    conn.execute(f"DELETE FROM {rtree}")
    conn.execute(
        f"INSERT INTO {rtree} "
        f"SELECT fid, ST_MinX({geom_col}), ST_MaxX({geom_col}), "
        f"ST_MinY({geom_col}), ST_MaxY({geom_col}) "
        f"FROM {table} WHERE {geom_col} IS NOT NULL"
    )
    conn.commit()

If your connection has no spatial extension loaded and ST_MinX is undefined, compute envelopes in Python instead and insert the four bounds with executemany, reading each geometry blob as described in the serialization guides.

5. Wrap it as an automated stage

Fold the detect-and-rebuild call into the pipeline after every bulk load so no operator has to remember it. Because the check is a pair of counts, running it unconditionally costs almost nothing when the index is already healthy.

python
import sqlite3

def index_stage(db_path: str, table: str, geom_col: str) -> dict:
    """Pipeline stage: ensure the spatial index is consistent post-load."""
    conn = sqlite3.connect(db_path)
    try:
        return rebuild_spatial_index_if_stale(conn, table, geom_col)
    finally:
        conn.close()

Verification

A row-count match proves the index is complete; EXPLAIN QUERY PLAN proves the planner actually uses it. Confirm both after a rebuild.

python
import sqlite3

conn = sqlite3.connect("survey.gpkg")
plan = conn.execute(
    "EXPLAIN QUERY PLAN "
    "SELECT fid FROM parcels WHERE fid IN "
    "(SELECT id FROM rtree_parcels_geom "
    " WHERE minx <= 5 AND maxx >= 4 AND miny <= 50 AND maxy >= 49)"
).fetchall()
for row in plan:
    print(row[-1])
conn.close()

Look for a line naming the R-tree virtual table, for example SCAN rtree_parcels_geom or SEARCH rtree_parcels_geom. If the plan instead reads SCAN parcels, the filter is not reaching the index and the query is doing a full table scan.

sql
-- SpatiaLite context: confirm the R-tree is consulted for a spatial filter
EXPLAIN QUERY PLAN
SELECT fid FROM parcels
WHERE fid IN (
    SELECT rowid FROM SpatialIndex
    WHERE f_table_name = 'parcels' AND search_frame = BuildMbr(4, 49, 5, 50)
);

The presence of idx_parcels_geometry in the SpatiaLite plan output confirms the rebuilt index is live.

Alternative Approaches or Edge Cases

Compute envelopes in Python. When no spatial extension is loaded, read each geometry blob, parse it with shapely.from_wkb, and use geom.bounds to get (minx, miny, maxx, maxy). Insert those into the R-tree with executemany. This keeps the rebuild dependency-light at the cost of pulling geometries into Python.

Partial rebuild after incremental loads. If you know exactly which fid values were newly inserted, delete and reinsert only those R-tree rows instead of clearing the whole table. This is worthwhile only for very large indexes with small deltas; for a one-off bulk load the full rebuild is simpler and safe.

Troubleshooting

sqlite3.OperationalError: no such function: CreateSpatialIndex

Cause: The connection targets a SpatiaLite database but mod_spatialite was never loaded, so the spatial SQL functions are undefined.

Fix: Call conn.enable_load_extension(True) and conn.load_extension("mod_spatialite") before the rebuild, per native sqlite3 spatial extensions.

sqlite3.OperationalError: no such table: rtree_parcels_geom

Cause: The GeoPackage was created without a spatial index on that layer, so the shadow table does not exist to repopulate.

Fix: Create it first, then fill it. The how to create a spatial index in SQLite with Python recipe shows the CREATE VIRTUAL TABLE ... USING rtree statement and the matching triggers.

Rebuild succeeds but queries are still slow

Cause: The query is written so the planner cannot push the filter into the R-tree — for example, wrapping the geometry column in a function on the left side of the comparison.

Fix: Express the filter as an explicit bounding-box subquery against the R-tree table, as in the verification examples, so the planner can use the index directly.