SpatiaLite & GeoPackage Automation with Python

Async Database Queries in Python GIS: Architecture & Implementation

Wrap SpatiaLite and GeoPackage queries in aiosqlite coroutines to keep an asyncio event loop responsive while SQLite does its blocking disk work in a…

Wrap SpatiaLite and GeoPackage queries in aiosqlite coroutines to keep an asyncio event loop responsive while SQLite does its blocking disk work in a background thread pool.

Why This Matters

Field data pipelines rarely run in isolation. A mobile sync daemon might simultaneously process incoming telemetry, serve a local HTTP tile endpoint, and write geometry edits to a .gpkg file. If each spatial query blocks the event loop — even for 50 ms — the tile server stutters, the sync log falls behind, and SQLITE_BUSY errors accumulate. Non-blocking spatial queries, implemented correctly under the Connection Pooling & Lifecycle Management discipline, eliminate those stalls without relaxing SQLite’s ACID guarantees.

This pattern matters most on constrained hardware: Raspberry Pi field stations, Android vehicles running Python via Termux, and Debian tablets that handle both GIS editing and background replication. On these devices, a single blocked event loop is the difference between a responsive application and one that appears frozen.

Prerequisites

  • Python 3.9+ (asyncio, concurrent.futures included in stdlib)
  • aiosqlite 0.19+ (pip install aiosqlite)
  • libspatialite installed and on the system library path (typically mod_spatialite.so on Linux, mod_spatialite.dylib on macOS)
  • A valid GeoPackage (.gpkg) or SpatiaLite (.sqlite) file with at least one geometry column
  • Familiarity with Python Integration & Database Workflows fundamentals and basic asyncio coroutine syntax

Primary Method

The following pattern is the single most reliable approach for async GIS queries. It uses aiosqlite as the async wrapper, enforces WAL mode for concurrent reads, loads mod_spatialite per connection, and handles the most common failure modes explicitly.

python
# GeoPackage / SpatiaLite — Python 3.9+, aiosqlite 0.19+
import asyncio
import aiosqlite
from pathlib import Path
from typing import Any

async def run_spatial_query(
    gpkg_path: str,
    query: str,
    params: tuple[Any, ...] = (),
) -> list[tuple[Any, ...]]:
    """
    Execute a spatial query asynchronously against a GeoPackage or SpatiaLite file.

    Connection lifecycle:
      1. WAL mode — set once, persisted in the file header.
      2. SpatiaLite extension — loaded per connection (not shared across connections).
      3. Parameterised query — ? placeholders only; no string formatting.
    """
    db_path = Path(gpkg_path)
    if not db_path.exists():
        raise FileNotFoundError(f"Database file not found: {db_path}")

    async with aiosqlite.connect(str(db_path)) as db:
        # WAL enables concurrent readers without blocking writes.
        # The setting is written to the file header — subsequent connections
        # inherit it automatically.
        await db.execute("PRAGMA journal_mode=WAL;")

        # SpatiaLite is a per-connection extension. It is disabled by default;
        # calling load_extension() without enabling it first raises:
        #   OperationalError: not authorized
        await db.enable_load_extension(True)
        await db.load_extension("mod_spatialite")

        cursor = await db.execute(query, params)
        return await cursor.fetchall()

Because aiosqlite routes every SQLite API call through a concurrent.futures.ThreadPoolExecutor, the coroutine yields control back to the event loop while the thread executes the query, then resumes when rows are ready.

Step-by-step Walkthrough

1. Understand how aiosqlite delegates work

SQLite’s C API is synchronous. aiosqlite does not add native async I/O; it wraps each call in loop.run_in_executor() so the blocking work happens in a background thread. The event loop is free for other coroutines while the thread runs the R-tree traversal, coordinate transformation, or bulk geometry read.

aiosqlite thread-delegation sequenceThe coroutine submits the query via run_in_executor to the thread pool and yields. A worker thread runs the blocking SQLite call against the .gpkg file, returns rows to the thread pool, which resumes the coroutine — the event loop stays responsive throughout.Event loopcoroutineThread poolexecutorSQLite.gpkg filerun_in_executor(execute)free for other tasksconn.execute() — blockingresult rowsresume coroutine with result
Async does not make SQLite concurrent — writes still serialize — but it keeps other coroutines running while disk I/O blocks.

2. Set WAL mode once, not per query

PRAGMA journal_mode=WAL modifies the database file header. All subsequent connections inherit the setting automatically. Issue it once at first open; repeating it per query wastes a round-trip to the executor.

WAL mode is essential for async workflows because it allows multiple readers to proceed concurrently with a single writer, using snapshot isolation. Without WAL, even read queries can serialize behind an open write transaction, negating the benefit of async routing entirely.

3. Load mod_spatialite per connection

SQLite extensions are connection-scoped. A connection obtained from aiosqlite.connect() starts with no spatial functions registered. The sequence is strict:

python
# SpatiaLite — must enable before loading; order is non-negotiable
await db.enable_load_extension(True)   # lifts the security restriction
await db.load_extension("mod_spatialite")  # registers ST_* functions

If you call db.load_extension() before enable_load_extension(True), SQLite raises OperationalError: not authorized. If mod_spatialite is not on the library path, the call raises OperationalError: The specified module could not be found (Windows) or a similar error on Linux/macOS.

4. Run a bounded-box spatial query

python
# GeoPackage — bounding-box filter using R-tree-backed BuildMbr
async def fetch_features_in_bbox(
    gpkg_path: str,
    table: str,
    min_lon: float, min_lat: float,
    max_lon: float, max_lat: float,
    srid: int = 4326,
) -> list[tuple[Any, ...]]:
    query = f"""
        SELECT id, feature_type, ST_AsText(geometry)
        FROM {table}
        WHERE ST_Intersects(
            geometry,
            BuildMbr(?, ?, ?, ?, ?)
        )
    """
    params = (min_lon, min_lat, max_lon, max_lat, srid)
    return await run_spatial_query(gpkg_path, query, params)

Always use ? placeholders — never string-format table or column names for user-supplied values. For table and column names that must be dynamic, validate against an allowlist derived from gpkg_contents.

5. Run multiple queries concurrently

Because each awaited call yields control back to the event loop, you can fan out independent reads with asyncio.gather():

python
# GeoPackage — parallel spatial reads from two separate tables
async def fetch_parallel(gpkg_path: str) -> tuple[list, list]:
    survey_coro = fetch_features_in_bbox(
        gpkg_path, "survey_points",
        -122.5, 37.7, -122.3, 37.8,
    )
    boundary_coro = fetch_features_in_bbox(
        gpkg_path, "admin_boundaries",
        -122.5, 37.7, -122.3, 37.8,
    )
    return await asyncio.gather(survey_coro, boundary_coro)

Each coroutine opens its own connection. SQLite WAL mode allows both reads to proceed simultaneously on the same file without blocking each other.

6. Apply pool-aware reuse for high-throughput pipelines

For pipelines that issue hundreds of spatial queries per second, opening a new aiosqlite connection per query is too expensive. Pair async queries with the bounded pool described in Connection Pooling & Lifecycle Management. A pool pre-warms connections with WAL mode and mod_spatialite already loaded, then hands them out via an async context manager, eliminating the per-query extension-load overhead.

Verification

After running async spatial queries, confirm the operation succeeded with these checks:

python
# SpatiaLite — verify WAL mode is active and spatial functions are available
import asyncio
import aiosqlite

async def verify_async_setup(gpkg_path: str) -> None:
    async with aiosqlite.connect(gpkg_path) as db:
        await db.enable_load_extension(True)
        await db.load_extension("mod_spatialite")

        # WAL mode should be active after the first connection set it
        cur = await db.execute("PRAGMA journal_mode;")
        mode = (await cur.fetchone())[0]
        assert mode == "wal", f"Expected WAL, got: {mode}"

        # Spatial functions must resolve without error
        cur = await db.execute("SELECT spatialite_version();")
        version = (await cur.fetchone())[0]
        print(f"SpatiaLite version: {version}")

        # R-tree index should exist for the geometry column
        cur = await db.execute(
            "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'rtree_%';"
        )
        rtree_tables = [row[0] for row in await cur.fetchall()]
        assert rtree_tables, "No R-tree indexes found — spatial queries will be slow"
        print(f"R-tree indexes: {rtree_tables}")

asyncio.run(verify_async_setup("field_survey.gpkg"))

Alternative Approaches and Edge Cases

Using asyncio.to_thread for one-off queries

When you control a codebase that already uses raw sqlite3 connections (not aiosqlite) — for example, when integrating with a synchronous spatial library — you can route individual calls off the event loop without switching the entire connection to aiosqlite:

python
# Python 3.9+ stdlib only — no aiosqlite required
import asyncio
import sqlite3

def _sync_spatial_query(db_path: str, query: str, params: tuple) -> list:
    conn = sqlite3.connect(db_path)
    conn.enable_load_extension(True)
    conn.load_extension("mod_spatialite")
    conn.execute("PRAGMA journal_mode=WAL;")
    rows = conn.execute(query, params).fetchall()
    conn.close()
    return rows

async def query_via_thread(db_path: str, query: str, params: tuple) -> list:
    # asyncio.to_thread wraps the function in loop.run_in_executor internally
    return await asyncio.to_thread(_sync_spatial_query, db_path, query, params)

This pattern is simpler but creates a new connection per call, making it unsuitable for sustained high-throughput use. Reserve it for occasional background checks or validation scripts.

Bulk async inserts with explicit transaction batching

SQLite serializes writes regardless of WAL mode. Issuing individual INSERT statements from async coroutines produces one write transaction per row — catastrophically slow for bulk geometry ingestion. Batch inserts into a single transaction instead:

python
# GeoPackage — async bulk insert, single transaction
async def bulk_insert_geometries(
    gpkg_path: str,
    table: str,
    rows: list[tuple],
) -> int:
    async with aiosqlite.connect(gpkg_path) as db:
        await db.execute("PRAGMA journal_mode=WAL;")
        await db.enable_load_extension(True)
        await db.load_extension("mod_spatialite")

        await db.execute("BEGIN;")
        await db.executemany(
            f"INSERT INTO {table} (feature_type, geometry) VALUES (?, GeomFromText(?, 4326));",
            rows,
        )
        await db.execute("COMMIT;")
        return len(rows)

For very large datasets, split rows into chunks of 5,000–10,000 and commit after each chunk. This keeps write transactions short and reduces WAL file growth on constrained field storage.

Troubleshooting

OperationalError: not authorized

Exact message: sqlite3.OperationalError: not authorized

Cause: load_extension() was called before enable_load_extension(True). SQLite disables extension loading by default as a security measure.

Fix: Always call await db.enable_load_extension(True) before await db.load_extension("mod_spatialite"). The order is non-negotiable.

OperationalError: database is locked

Exact message: sqlite3.OperationalError: database is locked

Cause: A long-running write transaction in another coroutine or thread is blocking the WAL checkpoint, or WAL mode is not enabled and a reader is competing with a writer.

Fix: Confirm WAL mode is active (PRAGMA journal_mode; must return wal). Add a busy timeout so SQLite retries for a configurable period before raising:

python
# SpatiaLite / GeoPackage — retry locked writes for up to 5 seconds
await db.execute("PRAGMA busy_timeout=5000;")

If lock errors persist, check that every write path opens exactly one connection, issues a single transaction, and closes the connection cleanly rather than leaving it open across multiple event loop cycles.

OSError: cannot load library 'mod_spatialite'

Exact message (Linux): OSError: /usr/lib/x86_64-linux-gnu/mod_spatialite.so: cannot open shared object file: No such file or directory

Cause: libspatialite is not installed, or the .so file is not on the library search path.

Fix: Install the system package (sudo apt install libspatialite-dev on Debian/Ubuntu) and verify the path:

bash
# Find mod_spatialite on Linux
find /usr -name "mod_spatialite*" 2>/dev/null

If the file exists but is not found at runtime, pass the full path to load_extension():

python
await db.load_extension("/usr/lib/x86_64-linux-gnu/mod_spatialite")

On macOS with Homebrew, the typical path is /opt/homebrew/lib/mod_spatialite.dylib.