SpatiaLite & GeoPackage Automation with Python

How to Check mod_spatialite Version Compatibility

After loading the extension, call SELECT spatialiteversion(), SELECT geosversion(), and SELECT projversion() on the connection, compare the returned…

After loading the extension, call SELECT spatialite_version(), SELECT geos_version(), and SELECT proj_version() on the connection, compare the returned strings against a pinned minimum with packaging.version, and probe individual SQL functions with a guarded query before your pipeline depends on them.

This page belongs to the Extension Compatibility in Spatial SQLite guide, which walks through loading mod_spatialite across Linux, macOS, and Windows. Here the focus narrows to a single question you should answer at startup on every device: which capabilities did the binary that actually loaded bring with it?

Why This Matters

A mod_spatialite binary that loads successfully is not the same as one that can run your queries. The extension bundles two heavy dependencies — GEOS for geometry predicates and PROJ for coordinate reference system transforms — and their versions drift independently of the SpatiaLite release number. A field tablet built from an old distribution package might load SpatiaLite 4.3 with PROJ 4.9, while your CI runner has SpatiaLite 5.1 with PROJ 9. Functions such as ST_ClipByBox2D, ST_Node, or newer PROJ pipeline syntax simply do not exist on the older build.

Because SQLite reports a missing SQL function as a generic OperationalError: no such function, an unversioned pipeline surfaces this failure deep inside a geometry loop — often on the field device where you cannot attach a debugger. Detecting the version boundary at boot converts a silent, late runtime error into a clear, actionable startup message. It also lets you pin a known-good version in your dependency manifest and refuse to run against anything older, which is the difference between a reproducible offline deployment and one that behaves differently on every handset.

Prerequisites

  • Python 3.9+ with a sqlite3 module that permits enable_load_extension (see the parent guide for how to confirm this)
  • A loadable mod_spatialite binary matching your host architecture
  • pip install packaging for robust version comparison (avoid comparing version strings lexically)
  • A loaded spatial connection — the detection code below assumes load_extension already succeeded; if it does not, see Resolving mod_spatialite Load Errors Across Platforms
  • Familiarity with the SpatiaLite SQL function names your pipeline calls

Primary Method

The routine below loads the extension, reads all three version strings, checks each against a pinned floor, and returns a structured report. It raises a single, descriptive RuntimeError if any component is too old — call it once during application boot.

python
# SpatiaLite -- runtime version and capability detection
import sqlite3
from dataclasses import dataclass
from packaging.version import Version, InvalidVersion

# Pin the floor your code was tested against.
MIN_SPATIALITE = Version("5.0.0")
MIN_GEOS       = Version("3.8.0")
MIN_PROJ       = Version("6.0.0")


@dataclass
class SpatialVersions:
    spatialite: str
    geos: str
    proj: str


def _clean(raw: str) -> str:
    """PROJ/GEOS return strings like '3.11.1-CAPI-1.17.1'. Keep the leading X.Y.Z."""
    return raw.strip().split("-")[0].split(" ")[0]


def read_spatial_versions(conn: sqlite3.Connection) -> SpatialVersions:
    """Read version strings from an already extension-loaded connection."""
    spatialite = conn.execute("SELECT spatialite_version()").fetchone()[0]
    geos       = conn.execute("SELECT geos_version()").fetchone()[0]
    # proj_version() is the modern name; older builds only expose proj4_version().
    try:
        proj = conn.execute("SELECT proj_version()").fetchone()[0]
    except sqlite3.OperationalError:
        proj = conn.execute("SELECT proj4_version()").fetchone()[0]
    return SpatialVersions(spatialite=spatialite, geos=geos, proj=proj)


def assert_min_versions(conn: sqlite3.Connection) -> SpatialVersions:
    """Raise RuntimeError if any component is older than the pinned floor."""
    v = read_spatial_versions(conn)
    problems = []
    for name, got_raw, floor in (
        ("SpatiaLite", v.spatialite, MIN_SPATIALITE),
        ("GEOS",       v.geos,       MIN_GEOS),
        ("PROJ",       v.proj,       MIN_PROJ),
    ):
        try:
            if Version(_clean(got_raw)) < floor:
                problems.append(f"{name} {got_raw} < required {floor}")
        except InvalidVersion:
            problems.append(f"{name} version unparseable: {got_raw!r}")
    if problems:
        raise RuntimeError(
            "Incompatible spatial runtime:\n  " + "\n  ".join(problems)
        )
    return v

The key defensive detail is _clean: geos_version() returns strings such as 3.11.1-CAPI-1.17.1, and comparing that raw string against 3.8.0 with a naive < on strings gives wrong answers. Parsing only the leading X.Y.Z with packaging.version makes the comparison numeric and correct.

Step-by-Step Walkthrough

1. Read the three version strings

Each component exposes its own scalar SQL function. Run them on the connection immediately after the extension loads:

python
# SpatiaLite -- raw version probe
for fn in ("spatialite_version", "geos_version", "proj_version"):
    value = conn.execute(f"SELECT {fn}()").fetchone()[0]
    print(f"{fn}(): {value}")

spatialite_version() returns the SpatiaLite release (e.g. 5.1.0). geos_version() and proj_version() return the linked C-library versions, which govern which predicates and transforms are available — often the more important numbers for capability decisions.

2. Handle the PROJ function rename

SpatiaLite 5 renamed proj4_version() to proj_version() when PROJ dropped the “4” from its name. A build compiled against PROJ 4.x or an older SpatiaLite may only expose the legacy name. The try/except in the primary method covers both, so the same code runs on a legacy field device and a modern server.

3. Compare against a pinned floor

Store your minimum versions as constants and compare with packaging.version.Version, never with string ordering — "5.10.0" < "5.9.0" is True as strings but False as versions:

python
# SpatiaLite -- correct numeric version comparison
from packaging.version import Version

assert Version("5.10.0") > Version("5.9.0")   # numeric: passes
assert ("5.10.0" > "5.9.0") is False          # lexical: wrong

4. Probe for a specific function before using it

Version numbers are a proxy; the ground truth is whether a function is callable. Probe it directly against a trivial input and catch the no such function error:

python
# SpatiaLite -- capability probe for a single SQL function
import sqlite3

def has_function(conn: sqlite3.Connection, probe_sql: str) -> bool:
    """Return True if probe_sql runs without a 'no such function' error."""
    try:
        conn.execute(probe_sql).fetchone()
        return True
    except sqlite3.OperationalError as exc:
        if "no such function" in str(exc).lower():
            return False
        raise  # a different error (bad args, etc.) — surface it

can_clip = has_function(
    conn, "SELECT ST_ClipByBox2D(GeomFromText('POINT(0 0)'), 0, 0, 1, 1)"
)
print(f"ST_ClipByBox2D available: {can_clip}")

This lets your pipeline branch to a fallback (for example, a slower ST_Intersection path) instead of crashing when a newer function is absent.

5. Pin the version in your dependency manifest

Detection at runtime pairs with pinning at build time. When you install SpatiaLite through Conda, pin it so every environment resolves the same binary:

yaml
# environment.yml -- reproducible spatial stack
name: spatial-field
channels:
  - conda-forge
dependencies:
  - python=3.11
  - libspatialite=5.1.0
  - geos=3.12
  - proj=9.3

Pinning guarantees the versions your assert_min_versions check expects, so the runtime assertion becomes a safety net that catches drift on hand-provisioned devices rather than the primary control.

Verification

Run this at application startup and confirm it prints a clean report. On an underspecified device it should raise before any spatial query executes:

python
# SpatiaLite -- startup verification
import sqlite3

conn = sqlite3.connect(":memory:")
conn.enable_load_extension(True)
conn.load_extension("mod_spatialite")   # bare name if on the loader path

versions = assert_min_versions(conn)
print(f"SpatiaLite {versions.spatialite} | GEOS {versions.geos} | PROJ {versions.proj}")

# Spot-check a function your pipeline relies on
assert has_function(conn, "SELECT ST_Node(GeomFromText('LINESTRING(0 0, 1 1)'))")
print("Capability probe: PASS")
conn.close()

You can also read the versions from the command line without Python, which is handy inside a container build step:

bash
# SpatiaLite -- CLI version check
sqlite3 :memory: "SELECT load_extension('mod_spatialite'); \
  SELECT spatialite_version(), geos_version(), proj_version();"

Alternative Approaches or Edge Cases

Target-version feature flags. Instead of a single global floor, build a capability dictionary once at startup ({"clip_by_box": has_function(...), ...}) and pass it to the pipeline. This decouples your code from exact version numbers and survives distributions that backport individual functions.

GeoPackage-only deployments. If you distribute GeoPackage files but never call SpatiaLite SQL functions, you may not load mod_spatialite at all — GDAL’s own GEOS/PROJ linkage governs capability instead. In that case check osgeo.gdal.__version__ and osgeo.osr PROJ linkage rather than the SQL scalar functions above.

Static SQLite builds without proj4_version. Some minimal builds omit even the legacy PROJ function. Wrap the PROJ read so its absence downgrades to a warning rather than aborting a pipeline that never reprojects.

Troubleshooting

sqlite3.OperationalError: no such function: spatialite_version

Cause: The extension was not actually loaded on this connection, or a fresh connection was opened after loading. Version functions only exist once load_extension has run on that connection object.

Fix: Call enable_load_extension(True) and load_extension(...) on the same connection before reading versions. If the load itself fails, follow Resolving mod_spatialite Load Errors Across Platforms.

sqlite3.OperationalError: no such function: proj_version

Cause: An older SpatiaLite (4.x) or a build linked against PROJ 4 exposes only proj4_version().

Fix: Fall back to proj4_version() as the primary method does, or raise your pinned floor to SpatiaLite 5.0+ so the modern name is guaranteed.

packaging.version.InvalidVersion: Invalid version: '5.1.0-rc1'

Cause: A release-candidate or vendor-suffixed string reached Version() without being cleaned.

Fix: Strip suffixes before parsing (the _clean helper splits on - and space). For pre-release builds, compare only the X.Y.Z prefix or use Version(...).release to ignore pre-release markers.