SpatiaLite & GeoPackage Automation with Python

Asserting OGC Compliance in Continuous Integration

Express the conformance rules as SQL queries that return the offending rows, run them against the container your build produced, and fail the build on…

Express the conformance rules as SQL queries that return the offending rows, run them against the container your build produced, and fail the build on anything in the blocking bands. Written this way the gate needs no GDAL, runs in well under a second, and its failure output is a list of exactly what is wrong rather than a boolean.

This page belongs to the Testing & CI for Spatial Pipelines guide, and it turns the checks described in How to Validate GeoPackage OGC Compliance into something a build runs.

Why This Matters

Conformance is the property that makes a container readable by tools you have never tested against — which is the entire reason for choosing a standard format over a private one. It is also the property most easily lost by a pipeline that writes with raw SQL for speed, because the geometry is correct and the registry rows are not.

Checking it in CI rather than by hand matters because the failure is invisible locally. The code that wrote the container can read it back perfectly; only a consumer that trusts the registry notices, and by then the container has shipped.

Prerequisites

  • Python 3.9+ with sqlite3 — no GDAL required for the checks themselves
  • A build step that produces a container to check
  • A decision about which findings block and which only warn
  • Familiarity with the registry model from GeoPackage Specification Deep Dive

Primary Method

python
# conformance.py — SQL-expressed conformance checks, graded by severity
import sqlite3
from dataclasses import dataclass

BLOCKING, ADVISORY = "blocking", "advisory"


@dataclass(frozen=True)
class Check:
    name: str
    severity: str
    sql: str          # must return zero rows when the container conforms


CHECKS = [
    Check("required tables present", BLOCKING, """
        SELECT required FROM (
            SELECT 'gpkg_contents' AS required
            UNION ALL SELECT 'gpkg_geometry_columns'
            UNION ALL SELECT 'gpkg_spatial_ref_sys'
        ) WHERE required NOT IN (SELECT name FROM sqlite_master WHERE type='table')
    """),

    Check("registry names a table that exists", BLOCKING, """
        SELECT c.table_name FROM gpkg_contents c
        WHERE c.table_name NOT IN (SELECT name FROM sqlite_master WHERE type='table')
    """),

    Check("srs_id agrees across registries", BLOCKING, """
        SELECT c.table_name FROM gpkg_contents c
        JOIN gpkg_geometry_columns g ON g.table_name = c.table_name
        WHERE c.srs_id <> g.srs_id
    """),

    Check("every srs_id is defined", BLOCKING, """
        SELECT DISTINCT c.srs_id FROM gpkg_contents c
        WHERE c.srs_id NOT IN (SELECT srs_id FROM gpkg_spatial_ref_sys)
    """),

    Check("mandatory srs rows present", BLOCKING, """
        SELECT required FROM (
            SELECT 4326 AS required UNION ALL SELECT 0 UNION ALL SELECT -1
        ) WHERE required NOT IN (SELECT srs_id FROM gpkg_spatial_ref_sys)
    """),

    Check("application_id is GPKG", ADVISORY, """
        SELECT 1 WHERE (SELECT * FROM pragma_application_id()) <> 1196444487
    """),
]


def run_checks(path: str) -> list[tuple[Check, list]]:
    conn = sqlite3.connect(f"file:{path}?mode=ro", uri=True)
    try:
        return [(c, conn.execute(c.sql).fetchall()) for c in CHECKS]
    finally:
        conn.close()

Writing each check as “SQL that returns the offenders” rather than as a boolean is what makes the gate’s output useful. A failing build says registry names a table that exists: ['roads_2024'], which names the problem; a boolean says the container is non-conforming and leaves someone to find out why.

Expressing each check as offending rows rather than a verdictA check written to return a boolean tells the build only that something is wrong, so a person has to reproduce the failure to find out what. A check written to return the offending rows names them in the build output, so the failure is actionable without reproducing anything. Both cost the same query; only the second is diagnostic.returns a boolean"container is non-conforming"someone must reproduce itto learn anythingsame query, worse outputreturns the offenders"registry names roads_2024""which does not exist"actionable from the logno reproduction neededthe difference decides whether the gate survives its first false-looking failure
A gate whose failures cannot be understood from the build log gets bypassed, whatever it is checking.

Step-by-Step Walkthrough

1. Grade the findings before writing the runner

The severity is a decision about consequences, not about how serious the rule sounds. Three bands work well: a container that no standards-compliant reader can open, one that opens but violates the contract, and one that is conforming but unusual.

python
SEVERITY_ACTION = {
    BLOCKING: "fail the build",
    ADVISORY: "record and continue",
}

A gate that fails on everything gets disabled the first time it blocks a release for a page-size warning. Deciding the bands up front is what stops that.

2. Wire it into pytest

python
# tests/test_conformance.py
import pytest
from conformance import CHECKS, BLOCKING, run_checks


@pytest.mark.parametrize("check", CHECKS, ids=lambda c: c.name)
def test_conformance(built_container, check):
    conn = sqlite3.connect(f"file:{built_container}?mode=ro", uri=True)
    offenders = conn.execute(check.sql).fetchall()
    conn.close()

    if check.severity == BLOCKING:
        assert not offenders, f"{check.name}: {offenders[:10]}"
    elif offenders:
        pytest.skip(f"advisory — {check.name}: {offenders[:10]}")

Parameterising over the checks gives one test per rule, so the build report names which rule failed rather than pointing at a single opaque conformance test. Advisories surface as skips, which appear in the summary without failing anything.

3. Add the checks a query cannot make

Two important properties are not expressible in SQL against the container: whether every geometry decodes, and whether the reference-system definitions parse. Both need a decoder.

python
def test_every_geometry_decodes(built_container):
    """The expensive check — full scan, so it runs once rather than per rule."""
    from shapely import from_wkb

    conn = sqlite3.connect(f"file:{built_container}?mode=ro", uri=True)
    layers = conn.execute(
        "SELECT table_name, column_name FROM gpkg_geometry_columns"
    ).fetchall()

    failures = []
    for table, col in layers:
        for fid, blob in conn.execute(
            f'SELECT rowid, "{col}" FROM "{table}" WHERE "{col}" IS NOT NULL'
        ):
            try:
                from_wkb(gpb_to_wkb(blob))
            except Exception as exc:
                failures.append((table, fid, str(exc)[:60]))

    assert not failures, f"{len(failures)} undecodable geometries: {failures[:5]}"
Cost of each conformance checkThe registry checks are index reads and complete in single-digit milliseconds regardless of container size. The reference-system definition parse costs a few milliseconds per definition and there are usually only a handful. The geometry decode scan reads every geometry in the container and scales with data volume, which is why it is a single test rather than one per rule. The index consistency check is two counts and is effectively free.Where the gate's time goesregistry checksmilliseconds, size-independentindex consistencytwo counts, effectively freedefinitions parsea few ms per definition, a handful of themgeometry decodefull scan — scales with the data
Only the bottom bar grows with the container, which is the argument for keeping it as one test rather than one per rule.

4. Check index consistency

An index that does not match the data is non-conforming in effect if not in letter, and it is the failure most often introduced by a pipeline that bulk-loads with triggers dropped.

sql
-- One row per indexed layer; a mismatch means a rebuild was skipped
SELECT 'parcels' AS layer,
       (SELECT count(*) FROM rtree_parcels_geom)                    AS index_rows,
       (SELECT count(*) FROM parcels WHERE geom IS NOT NULL)        AS geom_rows
WHERE (SELECT count(*) FROM rtree_parcels_geom)
   <> (SELECT count(*) FROM parcels WHERE geom IS NOT NULL);

Generate this per layer rather than hard-coding table names, so a layer added next month is checked without anyone remembering to add it.

5. Run it against the built artefact, not a fixture

The point of the gate is to check what the build produced. A conformance suite that runs against a hand-built pytest fixture verifies the fixture, which is not the thing that ships.

python
@pytest.fixture(scope="session")
def built_container(tmp_path_factory):
    """Run the real pipeline, then check its output."""
    out = tmp_path_factory.mktemp("build") / "artifact.gpkg"
    run_pipeline(source=TEST_SOURCE, destination=out)
    assert out.exists(), "pipeline produced no container"
    return out

Verification

The gate must be shown to reject a non-conforming container, or it is decoration.

python
def test_gate_rejects_a_dropped_registry_table(built_container, tmp_path):
    """Break a container deliberately; the blocking checks must catch it."""
    import shutil

    broken = tmp_path / "broken.gpkg"
    shutil.copy(built_container, broken)

    conn = sqlite3.connect(broken)
    conn.execute("DROP TABLE gpkg_geometry_columns")
    conn.commit()
    conn.close()

    results = run_checks(str(broken))
    blocking_failures = [
        c.name for c, rows in results if c.severity == BLOCKING and rows
    ]
    assert blocking_failures, "the gate did not notice a dropped registry table"
python
def test_gate_rejects_an_orphaned_registry_row(built_container, tmp_path):
    broken = tmp_path / "orphan.gpkg"
    shutil.copy(built_container, broken)

    conn = sqlite3.connect(broken)
    conn.execute("""
        INSERT INTO gpkg_contents (table_name, data_type, identifier, srs_id)
        VALUES ('does_not_exist', 'features', 'ghost', 4326)
    """)
    conn.commit()
    conn.close()

    results = dict((c.name, rows) for c, rows in run_checks(str(broken)))
    assert results["registry names a table that exists"], (
        "the gate did not notice an orphaned registry row"
    )

Two negative tests are enough. They cost milliseconds, they run on every build, and they are the only evidence the gate does anything.

Negative tests for the gateTwo deliberate corruptions prove the gate works. Dropping a registry table must trip the required-tables check. Inserting a gpkg_contents row naming a table that does not exist must trip the orphan check. Each takes a copy of a good container, breaks one thing, and asserts the gate notices — which is the only evidence the checks do anything.copy a good containerthe build outputuntouched originaldrop a registry tablemust fail the gateinsert an orphan rowmust fail the gate
Two negative tests, milliseconds each, and the only thing separating a working gate from a decorative one.

Alternative Approaches or Edge Cases

Using an external validator. Reference validators exist and check more of the specification than a home-grown set will. They are slower, add a dependency, and their output is designed for people rather than for a build. Running one on release builds and the SQL set on every commit gets most of the value of both.

Checking SpatiaLite containers. The registry names differ — geometry_columns and spatial_ref_sys rather than the gpkg_ tables — but the rule shapes are identical. Parameterise the check set over the container flavour, detected as described in Reading Spatial Metadata with Python.

Extension declarations. A container using the R-tree must declare it in gpkg_extensions. Adding that check is a few lines and catches the most common non-conformance in hand-built containers — worth including once the basics pass consistently.

Troubleshooting

no such table: gpkg_contents when running the checks

Cause: The container is not a GeoPackage, or the build produced nothing and the path points at an empty file SQLite happily created. Fix: Assert the file exists and is non-empty in the fixture, and check application_id first so the failure names the real problem.

A check passes locally and fails in CI

Cause: The two are checking different containers — usually the local run checks a fixture and CI checks the build output. Fix: Point both at the pipeline’s output. If the pipeline cannot run locally, that is the more important problem.

The geometry decode test is slow enough to hurt

Cause: It reads every geometry in the container by design. Fix: Sample on every commit and scan fully on release builds, and say in the output which mode ran — a sampled scan reported as a full one is worse than no scan.

Frequently Asked Questions

Should conformance be a test or a build step?

A test, because that gives per-rule reporting, familiar tooling and a natural place for the negative tests that keep it honest. A separate build step is appropriate when the same checks must also run against containers the build did not produce — a partner’s delivery, an archived artefact — in which case make the checks a library both call.

What about containers the pipeline only reads?

Check them on ingest, with the advisory band widened. You did not produce them, so failing your build over someone else’s non-conformance helps nobody; what you want is to know before the data enters the pipeline, and to reject only what would actually break downstream. Registry integrity and geometry decode are the two worth blocking on.

Does this replace running ogrinfo?

No, and it is not trying to. ogrinfo exercises the real driver stack, which catches problems no SQL query will — a geometry the driver refuses, a layer definition it cannot build. The SQL checks are fast, dependency-free and precise about registry structure. Running ogrinfo -al -so as one additional test gets both, at the cost of a GDAL dependency in the test environment.