Reading Spatial Metadata with Python

Load mod_spatialite into a standard sqlite3 connection, detect whether the file uses a legacy SpatiaLite or OGC GeoPackage schema, and query the appropriate system tables — returning coordinate reference systems, geometry types, spatial extents, and column mappings in milliseconds, with no GDAL wheel required.

Why This Matters #

Field devices and CI runners often cannot install binary GIS stacks. When an automation pipeline ingests a mix of .db and .gpkg files collected from survey teams, every step that silently assumes one schema or the other will fail in production. Understanding the SpatiaLite metadata tables at the Python layer lets you build a single, format-aware reader that normalises geometry column names, CRS identifiers, and Z/M dimension flags before any business logic runs — entirely offline, with no network round-trips.

Prerequisites #

• Python 3.9 or later
• mod_spatialite shared library installed and on your system PATH (.so on Linux, .dylib on macOS, .dll on Windows)
• Standard library only: sqlite3, os, platform; optional pandas ≥ 1.3 for DataFrame output
• A valid SpatiaLite (.db / .sqlite) or GeoPackage (.gpkg) file

Reading metadata portably means writing the query twice, because the same facts live under different table and column names in each format — and a container can, legitimately, contain both:

Primary Method #

The function below performs format detection via sqlite_master, executes a normalised query against whichever metadata table is present, and returns a list of plain dictionaries. It runs in under 5 ms on databases with hundreds of spatial tables.

# -- SpatiaLite or GeoPackage: read_spatial_metadata.py (Python 3.9+)
import sqlite3
import os
from typing import Any

def read_spatial_metadata(db_path: str) -> list[dict[str, Any]]:
    """
    Extract normalised spatial metadata from a SpatiaLite or GeoPackage file.

    Returns a list of dicts with keys:
      table_name, column_name, geometry_type, srid, z, m
    """
    if not os.path.exists(db_path):
        raise FileNotFoundError(f"Database not found: {db_path}")

    conn = sqlite3.connect(db_path)
    conn.enable_load_extension(True)

    try:
        conn.load_extension("mod_spatialite")
    except sqlite3.OperationalError as exc:
        conn.close()
        raise RuntimeError(
            "mod_spatialite could not be loaded. "
            "Verify the shared library (.so/.dylib/.dll) is installed "
            "and accessible in your system PATH."
        ) from exc

    cur = conn.cursor()

    # Detect format: prefer GeoPackage table name; fall back to SpatiaLite view
    cur.execute(
        "SELECT name FROM sqlite_master "
        "WHERE type IN ('table','view') "
        "AND name IN ('gpkg_geometry_columns','geometry_columns') "
        "LIMIT 1"
    )
    row = cur.fetchone()
    if row is None:
        conn.close()
        raise ValueError(
            "No spatial metadata table found. "
            "The file may not be a valid SpatiaLite or GeoPackage database."
        )

    is_gpkg = row[0] == "gpkg_geometry_columns"

    if is_gpkg:
        # GeoPackage: geometry_type_name is an OGC string (e.g. 'POLYGONZM')
        query = """
            SELECT
                table_name,
                column_name,
                geometry_type_name  AS geometry_type,
                srs_id              AS srid,
                z,
                m
            FROM gpkg_geometry_columns
        """
    else:
        # SpatiaLite: geometry_type is an integer code (1=POINT, 3=POLYGON …)
        # z and m are not stored; default to 0 for schema compatibility
        query = """
            SELECT
                f_table_name        AS table_name,
                f_geometry_column   AS column_name,
                geometry_type,
                srid,
                0                   AS z,
                0                   AS m
            FROM geometry_columns
        """

    cur.execute(query)
    cols = [d[0] for d in cur.description]
    results = [dict(zip(cols, r)) for r in cur.fetchall()]

    conn.close()
    return results

Step-by-step Walkthrough #

1. Enable extension loading #

sqlite3.Connection.enable_load_extension(True) must be called before load_extension. On Python builds where extension loading is compiled out (some OS packages strip it), this raises AttributeError — use a try/except and fall back to GDAL/OGR if required.

2. Load mod_spatialite #

# -- SpatiaLite extension load (Python 3.9+)
conn.load_extension("mod_spatialite")

mod_spatialite registers the geometry_columns view and spatial functions. For GeoPackage files you technically do not need the extension (the tables are ordinary SQLite tables), but loading it allows you to call ST_* functions on geometries in follow-up queries. If the library name differs on your platform, try "mod_spatialite.so" or "mod_spatialite.dylib" explicitly.

3. Detect the schema #

# -- Detect SpatiaLite vs GeoPackage via sqlite_master (Python 3.9+)
cur.execute(
    "SELECT name FROM sqlite_master "
    "WHERE type IN ('table','view') "
    "AND name IN ('gpkg_geometry_columns','geometry_columns') "
    "LIMIT 1"
)

Querying sqlite_master before touching a metadata table prevents OperationalError: no such table on files that contain neither schema. The LIMIT 1 keeps the lookup sub-millisecond regardless of table count.

4. Execute the normalised query #

Both branches alias their columns to the same six names (table_name, column_name, geometry_type, srid, z, m). Downstream code never needs to branch on the source format again.

5. Convert geometry_type for SpatiaLite files #

SpatiaLite 4.0+ encodes geometry type as an integer. The mapping for 2D types is:

# -- Decode SpatiaLite integer geometry codes (Python 3.9+)
_GEOM_BASE = {
    1: "POINT", 2: "LINESTRING", 3: "POLYGON",
    4: "MULTIPOINT", 5: "MULTILINESTRING", 6: "MULTIPOLYGON",
    7: "GEOMETRYCOLLECTION",
}

def decode_geometry_type(code: int | str) -> str:
    """Normalise a SpatiaLite integer code or a GeoPackage string to a base name."""
    if isinstance(code, str):
        return code.rstrip("ZM").rstrip("ZM")  # strip trailing Z/M/ZM
    suffix = ""
    if code >= 3000:
        code -= 3000; suffix = "ZM"
    elif code >= 2000:
        code -= 2000; suffix = "M"
    elif code >= 1000:
        code -= 1000; suffix = "Z"
    return _GEOM_BASE.get(code, f"UNKNOWN({code})") + suffix

Verification #

After calling read_spatial_metadata, run this assertion block to confirm the output is well-formed before passing it further down the pipeline:

# -- Verify metadata extraction result (Python 3.9+)
metadata = read_spatial_metadata("survey.gpkg")

assert isinstance(metadata, list), "Expected a list"
assert len(metadata) > 0, "No spatial layers found"

required_keys = {"table_name", "column_name", "geometry_type", "srid", "z", "m"}
for row in metadata:
    missing = required_keys - row.keys()
    assert not missing, f"Row missing keys: {missing}"
    assert isinstance(row["srid"], int), f"srid must be int, got {type(row['srid'])}"

print(f"OK — {len(metadata)} spatial layer(s) detected")

You can also verify from the command line with the SQLite CLI:

# -- GeoPackage: inspect gpkg_geometry_columns from the shell
sqlite3 survey.gpkg "SELECT table_name, geometry_type_name, srs_id FROM gpkg_geometry_columns;"

Metadata Schema Diagram #

The diagram below shows how the two metadata schemas map to the same normalised output, and where sqlite_master fits in the detection path.

Alternative Approaches and Edge Cases #

Load into a pandas DataFrame #

When the metadata feeds a reporting step or a validation summary, converting to a DataFrame is one line:

# -- Convert metadata to DataFrame (Python 3.9+, pandas ≥ 1.3)
import pandas as pd

df = pd.DataFrame(read_spatial_metadata("survey.gpkg"))
df["has_z"] = df["z"].astype(bool)
df["has_m"] = df["m"].astype(bool)
print(df[["table_name", "geometry_type", "srid", "has_z"]])

Read-only connection for shared files #

On field devices where another process may hold the file, open the connection with a URI and the ro mode flag to avoid accidentally acquiring a write lock:

# -- Read-only URI connection (Python 3.9+)
import urllib.parse

def _ro_connect(path: str) -> sqlite3.Connection:
    uri = "file:" + urllib.parse.quote(os.path.abspath(path)) + "?mode=ro"
    return sqlite3.connect(uri, uri=True)

Pass the resulting connection to a variant of read_spatial_metadata that accepts a pre-opened conn argument instead of a path. For broader concurrency patterns, see Connection Pooling & Lifecycle Management.

Filter stale SpatiaLite records #

Older SpatiaLite databases accumulate orphaned rows in geometry_columns when tables are dropped without calling DiscardGeometryColumn(). Cross-reference against sqlite_master to prune them:

# -- Filter orphaned geometry_columns entries (SpatiaLite, Python 3.9+)
def live_tables(conn: sqlite3.Connection) -> set[str]:
    cur = conn.cursor()
    cur.execute("SELECT name FROM sqlite_master WHERE type='table'")
    return {r[0] for r in cur.fetchall()}

metadata = [r for r in results if r["table_name"] in live_tables(conn)]

Troubleshooting #

sqlite3.OperationalError: The specified module could not be found. (Windows)

The mod_spatialite.dll is not on the Windows PATH. Copy the DLL (and its dependencies — typically libsqlite3-0.dll, libgeos_c.dll, libproj*.dll) into the same directory as your script, or add their location to PATH before the Python process starts. Loading by full path also works: conn.load_extension(r"C:\spatialite\mod_spatialite").

ValueError: No spatial metadata table found

The file is a plain SQLite database with no spatial extension initialised, or it was created by a tool that does not write geometry_columns / gpkg_geometry_columns. Confirm with sqlite3 file.db ".tables". If the file is a GeoPackage, the tables must be present per the OGC specification — a missing gpkg_geometry_columns indicates a non-compliant or corrupt file. See How to Validate GeoPackage OGC Compliance for diagnosis steps.

AttributeError: 'Connection' object has no attribute 'enable_load_extension'

Your Python installation was built without loadable extension support (common on some Linux distribution packages). Install Python from python.org, use a virtual environment with the pysqlite3-binary wheel, or use GDAL/OGR as an alternative reader — see Native sqlite3 Spatial Extensions for platform-specific workarounds.

Frequently Asked Questions #

Related #

• SpatiaLite Metadata Tables Explained — parent page: full reference for geometry_columns, spatial_ref_sys, and gpkg_* table schemas
• Managing Spatial Reference Systems in SQLite — how to insert, update, and validate SRID records in both schemas
• GeoPackage Specification Deep Dive — authoritative breakdown of the OGC GeoPackage table contracts this page queries
• Native sqlite3 Spatial Extensions — loading mod_spatialite across platforms and pinning extension versions
• Connection Pooling & Lifecycle Management — managing multiple concurrent readers of the same SpatiaLite or GeoPackage file