SpatiaLite & GeoPackage Automation with Python

Managing Spatial Reference Systems in SQLite

To register a coordinate reference system (CRS) in SQLite you must insert an EPSG definition into spatialrefsys (SpatiaLite) or gpkgspatialrefsys…

To register a coordinate reference system (CRS) in SQLite you must insert an EPSG definition into spatial_ref_sys (SpatiaLite) or gpkg_spatial_ref_sys (GeoPackage), then bind the SRID to each geometry column — SQLite never infers projections automatically.

Why This Matters

Unlike enterprise spatial databases that store projection metadata inside every geometry BLOB, SQLite delegates CRS ownership entirely to its SpatiaLite metadata tables. The spatial_ref_sys and gpkg_spatial_ref_sys tables act as a lookup registry: an integer SRID points to an authoritative WKT definition, and every geometry column in the database must reference a registered SRID.

This design has a sharp edge in offline-first and embedded deployments. Desktop installations of QGIS or SpatiaLite GUI pre-populate the registry with several thousand EPSG entries. Mobile builds, Docker images, and Python virtual environments usually ship with a stripped or empty registry. The moment a client tries to render a layer whose SRID has no matching row, spatial index creation fails silently, distance calculations return wrong units, and desktop GIS applications display geometries in the wrong place. Automating registry validation and injection before any schema migration or bulk import is the lowest-cost way to prevent these failures.

Prerequisites

  • Python 3.9+ with the built-in sqlite3 module
  • pyproj 3.4+ (pip install pyproj)
  • SpatiaLite shared library (mod_spatialite.so on Linux, mod_spatialite.dylib on macOS, mod_spatialite.dll on Windows) — only required for .sqlite/.db files, not GeoPackage
  • Read/write access to the target database file
  • Basic familiarity with EPSG codes and WKT projection strings

How Spatial SQLite Stores CRS Definitions

The two formats share the same concept but differ in schema. Both enforce strict referential integrity: inserting geometries with an unregistered SRID causes index creation to fail and triggers client-side projection errors.

SpatiaLite stores definitions in spatial_ref_sys with five mandatory columns:

ColumnTypeDescription
sridINTEGER PRIMARY KEYThe integer Spatial Reference System ID
auth_nameTEXTAuthority name, e.g. EPSG
auth_sridINTEGERAuthority-assigned code, e.g. 4326
srtextTEXTWell-Known Text (WKT) projection string
proj4textTEXTLegacy PROJ.4 string (may be empty in newer setups)

GeoPackage uses gpkg_spatial_ref_sys per the OGC GeoPackage specification, replacing srid with srs_id and adding srs_name, organization, organization_coordsys_id, and definition. The definition column accepts WKT2:2019 strings. Two rows with srs_id values of 0 (undefined Cartesian) and -1 (undefined geographic) are mandatory and must never be removed.

The relationship between these registries and the geometry column tables is covered in full in SpatiaLite Metadata Tables Explained. The broader format contract between SpatiaLite and GeoPackage is described in the GeoPackage Specification Deep Dive.

Primary Method: Validate, Inject, and Bind in One Transaction

The function below is the recommended production approach. It resolves an EPSG code through pyproj, checks whether the SRID is already registered, inserts the definition if missing, and binds it to the target geometry column — all inside a single transaction so a partial failure cannot leave the schema inconsistent.

python
# -- SpatiaLite (.sqlite/.db) and GeoPackage (.gpkg) -- Python 3.9+
import sqlite3
import os
from pyproj import CRS
from pyproj.exceptions import CRSError


def manage_srs_in_sqlite(
    db_path: str,
    target_srid: int,
    table_name: str,
    geom_col: str,
    geom_type: str = "GEOMETRY",
) -> None:
    """Validate EPSG, inject CRS if missing, and bind to a geometry column.

    Args:
        db_path:      Absolute path to the .sqlite, .db, or .gpkg file.
        target_srid:  EPSG integer code (e.g. 4326, 32632).
        table_name:   Target feature table name (created if absent).
        geom_col:     Name of the geometry column to register.
        geom_type:    OGC geometry type string, e.g. "POINT", "MULTIPOLYGON".
    """
    is_geopackage = db_path.lower().endswith(".gpkg")
    if not os.path.exists(db_path):
        raise FileNotFoundError(f"Database not found: {db_path}")

    # Step 1: resolve the EPSG code through pyproj before touching the database.
    try:
        crs = CRS.from_epsg(target_srid)
        # WKT2:2019 is preferred by GeoPackage 1.3+.
        wkt = crs.to_wkt(version="WKT2_2019")
        proj4 = crs.to_proj4()  # lossy for some CRS; kept for SpatiaLite legacy compat
        authority = crs.to_authority()
        if authority is None:
            raise ValueError(f"No EPSG authority match for SRID {target_srid}")
        auth_name, auth_srid_str = authority
        auth_srid = int(auth_srid_str)
    except CRSError as exc:
        raise ValueError(f"Invalid EPSG code {target_srid}: {exc}") from exc

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

    try:
        if not is_geopackage:
            # mod_spatialite must be loaded before any spatial function call.
            conn.load_extension("mod_spatialite")
            conn.execute("SELECT spatialite_version();")

        with conn:  # single transaction — either all steps commit or all roll back
            registry = "gpkg_spatial_ref_sys" if is_geopackage else "spatial_ref_sys"
            id_col = "srs_id" if is_geopackage else "srid"

            # Step 2: check registry — insert only when the SRID is absent.
            exists = conn.execute(
                f"SELECT COUNT(*) FROM {registry} WHERE {id_col} = ?",
                (target_srid,),
            ).fetchone()[0]

            if not exists:
                if is_geopackage:
                    conn.execute(
                        """
                        INSERT INTO gpkg_spatial_ref_sys
                            (srs_name, srs_id, organization, organization_coordsys_id,
                             definition, description)
                        VALUES (?, ?, ?, ?, ?, ?)
                        """,
                        (crs.name, target_srid, auth_name, auth_srid, wkt, crs.name),
                    )
                else:
                    conn.execute(
                        """
                        INSERT INTO spatial_ref_sys
                            (srid, auth_name, auth_srid, srtext, proj4text)
                        VALUES (?, ?, ?, ?, ?)
                        """,
                        (target_srid, auth_name, auth_srid, wkt, proj4),
                    )

            # Step 3: bind the SRID to the geometry column.
            if is_geopackage:
                conn.execute(
                    f"CREATE TABLE IF NOT EXISTS {table_name} "
                    f"(id INTEGER PRIMARY KEY AUTOINCREMENT, {geom_col} {geom_type})"
                )
                conn.execute(
                    """
                    INSERT OR IGNORE INTO gpkg_geometry_columns
                        (table_name, column_name, geometry_type_name, srs_id, z, m)
                    VALUES (?, ?, ?, ?, 0, 0)
                    """,
                    (table_name, geom_col, geom_type.upper(), target_srid),
                )
            else:
                # AddGeometryColumn registers the column in geometry_columns
                # and creates the associated triggers automatically.
                conn.execute(
                    f"CREATE TABLE IF NOT EXISTS {table_name} "
                    f"(id INTEGER PRIMARY KEY AUTOINCREMENT)"
                )
                conn.execute(
                    "SELECT AddGeometryColumn(?, ?, ?, ?, 2, 0)",
                    (table_name, geom_col, target_srid, geom_type),
                )

        print(f"Bound SRID {target_srid} ({crs.name}) to {table_name}.{geom_col}")
    finally:
        conn.close()

Step-by-Step Walkthrough

1. Resolve the EPSG code before touching the database

Call CRS.from_epsg(target_srid) first. If pyproj cannot resolve the code it raises CRSError immediately, before you open a connection. This keeps error handling clean and prevents half-written registry rows.

2. Choose WKT version based on format

Use crs.to_wkt(version="WKT2_2019") for GeoPackage 1.3+ targets. For SpatiaLite databases consumed by older QGIS releases (pre-3.16) or ArcGIS Desktop, switch to crs.to_wkt(version="WKT1_GDAL"). WKT1 is lossier but universally supported.

3. Check before inserting

The SELECT COUNT(*) guard avoids duplicate-key errors on srid (SpatiaLite) and srs_id (GeoPackage), which are both primary keys. Never use INSERT OR REPLACE on gpkg_spatial_ref_sys — it cascades deletes to gpkg_geometry_columns through the foreign-key constraint.

4. Wrap everything in one transaction

The with conn: context manager commits on exit and rolls back on any exception. If AddGeometryColumn fails after the spatial_ref_sys insert has already executed, the rollback removes the orphaned registry row — keeping the database consistent.

5. Register the spatial index after binding

Registering an SRID and binding the geometry column do not automatically create an R-tree index. For SpatiaLite, run SELECT CreateSpatialIndex('table_name', 'geom_column') in a follow-up call. GeoPackage manages spatial index registration through gpkg_extensions entries; use GDAL or a conformant OGR driver to create these rather than writing them by hand.

CRS Registry Flow

The diagram below shows the path from an EPSG code to a bound geometry column and spatial index in both formats.

CRS Registration Flow in SpatiaLite and GeoPackageA flow diagram showing how an EPSG integer code is resolved by pyproj, checked against the CRS registry, inserted if missing, bound to a geometry column, and used to create a spatial index.EPSG integere.g. 4326, 32632pyproj resolveCRS.from_epsg()alreadyregistered?yesnoINSERT into registryspatial_ref_sys / gpkg_srsBind geometry colAddGeomCol / gpkg_geom_colsSpatial indexCreateSpatIdx / gpkg_extSpatiaLitespatial_ref_sys · sridGeoPackagegpkg_srs · srs_id

Verification

After running manage_srs_in_sqlite, confirm the SRID is present and bound with these queries. Run them in a fresh connection without loading mod_spatialite to verify the registry rows are persisted rather than cached in memory.

python
# -- SpatiaLite verification
import sqlite3

def verify_srid_registered(db_path: str, srid: int, table_name: str) -> None:
    conn = sqlite3.connect(db_path)
    try:
        is_gpkg = db_path.lower().endswith(".gpkg")
        registry = "gpkg_spatial_ref_sys" if is_gpkg else "spatial_ref_sys"
        id_col   = "srs_id"               if is_gpkg else "srid"

        row = conn.execute(
            f"SELECT {id_col}, srs_name FROM {registry} WHERE {id_col} = ?"
            if is_gpkg else
            f"SELECT srid, srtext FROM {registry} WHERE srid = ?",
            (srid,),
        ).fetchone()
        assert row is not None, f"SRID {srid} not found in {registry}"
        print(f"Registry OK: {row}")

        geom_table = "gpkg_geometry_columns" if is_gpkg else "geometry_columns"
        id_ref     = "srs_id"               if is_gpkg else "srid"
        bound = conn.execute(
            f"SELECT table_name, column_name FROM {geom_table} "
            f"WHERE table_name = ? AND {id_ref} = ?",
            (table_name, srid),
        ).fetchone()
        assert bound is not None, f"SRID {srid} not bound to {table_name}"
        print(f"Geometry column bound: {bound}")
    finally:
        conn.close()

You can also run a quick check from the command line using ogrinfo:

bash
# Replace 'layers.gpkg' and 'features' with your file and layer name
ogrinfo -al -so layers.gpkg features | grep -E "SRS|Geometry"

A valid output shows the layer’s geometry type and its authority-registered projection string. If ogrinfo reports SRS: unknown, the SRID row is missing or the definition column contains a string the GDAL WKT parser rejects.

Alternative Approaches

Using GDAL/OGR instead of raw SQL

For one-off CRS registration during file creation, the GDAL Python bindings handle everything automatically:

python
# -- GeoPackage via GDAL/OGR 3.4+ -- Python 3.9+
from osgeo import ogr, osr

driver = ogr.GetDriverByName("GPKG")
ds = driver.CreateDataSource("output.gpkg")

srs = osr.SpatialReference()
srs.ImportFromEPSG(4326)
srs.SetAxisMappingStrategy(osr.OAMS_TRADITIONAL_GIS_ORDER)

layer = ds.CreateLayer("features", srs=srs, geom_type=ogr.wkbPoint)
ds = None  # flush and close

GDAL inserts the required gpkg_spatial_ref_sys rows and the gpkg_geometry_columns entry automatically. Use this path when creating new files; use the sqlite3 path above when patching existing databases or deploying to environments without GDAL.

Bulk-populating the registry from a CSV seed file

For embedded deployments that must ship with a pre-populated registry, generate a CSV of EPSG definitions at build time and load it at first-run:

python
# -- SpatiaLite bulk seed -- Python 3.9+
import csv
import sqlite3

def seed_registry(db_path: str, csv_path: str) -> int:
    """Insert EPSG definitions from a CSV seed into spatial_ref_sys.
    CSV columns: srid,auth_name,auth_srid,srtext,proj4text
    Returns the number of rows inserted.
    """
    conn = sqlite3.connect(db_path)
    conn.enable_load_extension(True)
    conn.load_extension("mod_spatialite")
    inserted = 0
    with conn, open(csv_path, newline="") as fh:
        for row in csv.DictReader(fh):
            exists = conn.execute(
                "SELECT COUNT(*) FROM spatial_ref_sys WHERE srid = ?",
                (int(row["srid"]),),
            ).fetchone()[0]
            if not exists:
                conn.execute(
                    "INSERT INTO spatial_ref_sys "
                    "(srid, auth_name, auth_srid, srtext, proj4text) VALUES (?,?,?,?,?)",
                    (int(row["srid"]), row["auth_name"], int(row["auth_srid"]),
                     row["srtext"], row["proj4text"]),
                )
                inserted += 1
    conn.close()
    return inserted

This pattern is useful in offline sync scenarios where every field device needs an identical registry snapshot. Version-control the CSV alongside your application schema and validate it in CI before deployment.

Troubleshooting

OperationalError: no such table: spatial_ref_sys

Cause: The mod_spatialite extension was not loaded, or the database was not initialised with InitSpatialMetaData().

Fix: Add these two lines immediately after connecting:

python
conn.enable_load_extension(True)
conn.load_extension("mod_spatialite")
# Only needed for brand-new, empty databases:
conn.execute("SELECT InitSpatialMetaData(1);")

The 1 argument to InitSpatialMetaData suppresses the error if metadata tables already exist.

IntegrityError: UNIQUE constraint failed: gpkg_spatial_ref_sys.srs_id

Cause: A row with the same srs_id already exists in the GeoPackage registry. This usually happens when a script is run twice or when a GeoPackage created by GDAL already contains the common EPSG entries.

Fix: Use the SELECT COUNT(*) guard before inserting (as shown in the primary method). Never use INSERT OR REPLACE — it triggers a cascading delete on gpkg_geometry_columns.

CRSError: Invalid projection: ...

Cause: The EPSG code is not in the local pyproj data directory (PROJ_DATA env var). This is common in minimal Docker images.

Fix:

bash
pip install pyproj --upgrade
python -c "import pyproj; print(pyproj.datadir.get_data_dir())"
# Verify the directory is non-empty and contains .db files

Alternatively, set PROJ_DATA to point to a pre-downloaded PROJ data directory bundled with your application.