SpatiaLite & GeoPackage Automation with Python

How to Validate GeoPackage OGC Compliance

Run Python sqlite3 schema checks against the mandatory gpkg metadata tables, then confirm geometry encoding and spatial reference integrity with GDAL's…

Run Python sqlite3 schema checks against the mandatory gpkg_* metadata tables, then confirm geometry encoding and spatial reference integrity with GDAL’s ogrinfo — the combined output gives you a structured pass/fail report suitable for CI/CD pipeline gates before files reach field devices or sync endpoints.

Why This Matters

Field GIS technicians and mobile developers treat GeoPackage as a portable replacement for shapefiles, but its SQLite foundation enforces structural rules that most tools do not surface until a query silently fails. When a file violates the schema contracts described in the GeoPackage Specification Deep Dive, mobile SQLite drivers often fail silently — producing corrupted spatial indexes, missing geometry columns, or broken sync sessions in QField, ArcGIS Field Maps, and custom React Native or Flutter clients.

Validating at ingestion prevents cascading data loss in offline-first workflows. A ten-millisecond check at the pipeline gate is far cheaper than debugging a 500-device field deployment where half the units received a non-compliant container.

Prerequisites

  • Python 3.9+ with the standard sqlite3 module (no extra install required)
  • GDAL/OGR 3.4+ installed system-wide (ogrinfo must be on PATH); skip on headless environments and substitute the gdal_geometry check with a pure-SQL fallback
  • Familiarity with the four mandatory GeoPackage metadata tables (gpkg_contents, gpkg_spatial_ref_sys, gpkg_geometry_columns, gpkg_extensions)
  • A .gpkg file produced by GDAL, GeoPandas, or any OGC-compliant tool

Validation Flow at a Glance

The diagram below maps the four checks performed by the script: header identification, mandatory table presence, gpkg_contents data-type enumeration, spatial reference binding, and GDAL WKB geometry inspection.

GeoPackage OGC Validation FlowSequential validation steps: SQLite header check, mandatory table check, gpkg_contents check, SRS integrity check, and GDAL geometry check, each branching to PASS or FAIL.SQLite HeaderIdentificationMandatoryTable Presencegpkg_contentsData TypesSRS BindingIntegrityGDAL WKBGeometryPASS ↓PASS ↓PASS ↓PASS ↓PASS ↓FAIL ↑ quarantineFAIL ↑ re-exportFAIL ↑ fix typeFAIL ↑ inject SRSFAIL ↑ rebuild WKBStructured JSON Report

Primary Method: Python + GDAL Validation Script

The script below combines SQLite schema inspection with GDAL geometry validation. It checks the four mandatory OGC tables, validates gpkg_contents data types, verifies spatial reference bindings, and delegates WKB integrity to ogrinfo. The output is a structured JSON report with a top-level compliant boolean — parse it in CI/CD to block non-compliant files.

python
# GeoPackage context — OGC 12-128 compliance validation
import sqlite3
import subprocess
import sys
import json
from pathlib import Path


def validate_geopackage(gpkg_path: str) -> dict:
    """Validate OGC GeoPackage compliance using SQLite schema checks + GDAL.

    Returns a dict with keys:
        file       — absolute path
        compliant  — bool, True only if ALL checks pass
        checks     — list of individual check results
    """
    path = Path(gpkg_path).resolve()
    if not path.exists():
        return {"file": str(path), "compliant": False, "checks": [
            {"check": "file_exists", "status": "FAIL", "error": "File not found"}
        ]}

    report: dict = {"file": str(path), "compliant": True, "checks": []}

    try:
        with sqlite3.connect(str(path)) as conn:
            conn.row_factory = sqlite3.Row
            cur = conn.cursor()

            # ── 1. Mandatory OGC Table Presence ─────────────────────────────
            # OGC 12-128r20 §2.1: these four tables must exist in every GPKG.
            mandatory = {
                "gpkg_spatial_ref_sys",
                "gpkg_contents",
                "gpkg_geometry_columns",
                "gpkg_extensions",
            }
            cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
            existing = {row["name"] for row in cur.fetchall()}
            missing = sorted(mandatory - existing)
            if missing:
                report["compliant"] = False
                report["checks"].append({
                    "check": "mandatory_tables",
                    "status": "FAIL",
                    "missing": missing,
                })
            else:
                report["checks"].append({"check": "mandatory_tables", "status": "PASS"})

            # ── 2. gpkg_contents Data-Type Enumeration ───────────────────────
            # Allowed values are strictly: features | attributes | tiles
            cur.execute(
                "SELECT table_name, data_type, srs_id FROM gpkg_contents;"
            )
            contents = cur.fetchall()
            if not contents:
                report["compliant"] = False
                report["checks"].append({
                    "check": "gpkg_contents",
                    "status": "FAIL",
                    "message": "gpkg_contents is empty — no registered layers",
                })
            else:
                valid_types = {"features", "attributes", "tiles"}
                invalid = [r["data_type"] for r in contents
                           if r["data_type"] not in valid_types]
                if invalid:
                    report["compliant"] = False
                    report["checks"].append({
                        "check": "gpkg_contents",
                        "status": "FAIL",
                        "invalid_types": invalid,
                    })
                else:
                    report["checks"].append({
                        "check": "gpkg_contents",
                        "status": "PASS",
                        "layer_count": len(contents),
                    })

            # ── 3. Spatial Reference Integrity ───────────────────────────────
            # Every srs_id referenced in gpkg_contents must have a row in
            # gpkg_spatial_ref_sys. srs_id=0 (undefined) is always permitted.
            cur.execute("""
                SELECT c.srs_id
                FROM gpkg_contents c
                LEFT JOIN gpkg_spatial_ref_sys s ON c.srs_id = s.srs_id
                WHERE s.srs_id IS NULL AND c.srs_id != 0;
            """)
            orphan_srs = [r[0] for r in cur.fetchall()]
            if orphan_srs:
                report["compliant"] = False
                report["checks"].append({
                    "check": "spatial_ref_sys",
                    "status": "FAIL",
                    "orphan_srs_ids": orphan_srs,
                    "message": "srs_id values referenced in gpkg_contents but absent from gpkg_spatial_ref_sys",
                })
            else:
                cur.execute(
                    "SELECT COUNT(*) FROM gpkg_spatial_ref_sys WHERE srs_id != 0;"
                )
                count = cur.fetchone()[0]
                report["checks"].append({
                    "check": "spatial_ref_sys",
                    "status": "PASS",
                    "defined_srs_count": count,
                })

    except sqlite3.DatabaseError as exc:
        # Not a valid SQLite file at all
        report["compliant"] = False
        report["checks"].append({
            "check": "sqlite_connection",
            "status": "FAIL",
            "error": str(exc),
        })
        return report

    # ── 4. GDAL WKB Geometry Validation (ogrinfo) ────────────────────────────
    # ogrinfo -so -al summarises every layer's geometry type and SRS.
    # Any ERROR in stderr signals a malformed WKB blob or driver rejection.
    try:
        result = subprocess.run(
            ["ogrinfo", "-so", "-al", str(path)],
            capture_output=True,
            text=True,
            check=True,
            timeout=30,
        )
        if "ERROR" in result.stderr.upper():
            report["compliant"] = False
            report["checks"].append({
                "check": "gdal_geometry",
                "status": "FAIL",
                "details": result.stderr.strip(),
            })
        else:
            report["checks"].append({"check": "gdal_geometry", "status": "PASS"})
    except FileNotFoundError:
        report["checks"].append({
            "check": "gdal_geometry",
            "status": "SKIP",
            "message": "ogrinfo not found — install gdal-bin to enable geometry validation",
        })
    except subprocess.TimeoutExpired:
        report["compliant"] = False
        report["checks"].append({
            "check": "gdal_geometry",
            "status": "FAIL",
            "message": "ogrinfo timed out — file may be corrupt or excessively large",
        })
    except subprocess.CalledProcessError as exc:
        report["compliant"] = False
        report["checks"].append({
            "check": "gdal_geometry",
            "status": "FAIL",
            "details": exc.stderr.strip(),
        })

    return report


if __name__ == "__main__":
    if len(sys.argv) < 2:
        print("Usage: python validate_gpkg.py <path_to_gpkg>")
        sys.exit(1)
    output = validate_geopackage(sys.argv[1])
    print(json.dumps(output, indent=2))
    sys.exit(0 if output["compliant"] else 1)

Step-by-step Walkthrough

1. Install GDAL (if not already present)

On Debian/Ubuntu:

bash
sudo apt install gdal-bin python3-gdal

On macOS with Homebrew:

bash
brew install gdal

Confirm GDAL is on your PATH:

bash
ogrinfo --version
# GDAL 3.8.x, released 2024/...

Python’s sqlite3 ships with CPython 3.9+ and needs no separate install.

2. Run a single-file validation

bash
python validate_gpkg.py data/offline_survey.gpkg

Successful output looks like:

json
{
  "file": "/abs/path/data/offline_survey.gpkg",
  "compliant": true,
  "checks": [
    {"check": "mandatory_tables", "status": "PASS"},
    {"check": "gpkg_contents", "status": "PASS", "layer_count": 3},
    {"check": "spatial_ref_sys", "status": "PASS", "defined_srs_count": 2},
    {"check": "gdal_geometry", "status": "PASS"}
  ]
}

The script exits with code 0 when compliant and 1 when not — suitable for shell && chains.

3. Integrate into a CI/CD pipeline gate

In a GitHub Actions workflow:

yaml
- name: Validate GeoPackage compliance
  run: |
    python validate_gpkg.py ${{ env.GPKG_PATH }}

Because the script exits non-zero on failure, the step fails and blocks the workflow automatically. In GitLab CI or Airflow DAGs, parse the compliant key from the JSON output if you need finer-grained reporting:

python
import json, subprocess, sys

result = subprocess.run(
    ["python", "validate_gpkg.py", "data/field_export.gpkg"],
    capture_output=True, text=True
)
report = json.loads(result.stdout)
if not report["compliant"]:
    failed = [c for c in report["checks"] if c["status"] == "FAIL"]
    raise RuntimeError(f"GeoPackage compliance failed: {failed}")

4. Batch-validate a directory

Wrap the validation function in a thread pool for directory-level runs. GeoPackage files are single-file SQLite databases, making them safe for concurrent read-only checks:

python
# GeoPackage context — batch directory validation
import concurrent.futures
from pathlib import Path

def validate_directory(directory: str) -> list[dict]:
    gpkg_files = list(Path(directory).glob("**/*.gpkg"))
    with concurrent.futures.ThreadPoolExecutor(max_workers=4) as pool:
        results = list(pool.map(validate_geopackage, [str(f) for f in gpkg_files]))
    return results

if __name__ == "__main__":
    reports = validate_directory("data/")
    failures = [r for r in reports if not r["compliant"]]
    print(f"{len(failures)} of {len(reports)} files failed compliance checks.")

Verification

After running the script, confirm the report independently with a direct ogrinfo call and a SQL probe:

bash
# Verify layers and SRS with ogrinfo
ogrinfo -so -al data/offline_survey.gpkg
sql
-- GeoPackage context — confirm mandatory table contents
SELECT table_name, data_type, srs_id FROM gpkg_contents;
SELECT srs_id, organization, organization_coordsys_id FROM gpkg_spatial_ref_sys
WHERE srs_id != 0;

Also run an SQLite PRAGMA sanity check — a valid GeoPackage must be a well-formed SQLite 3 file:

bash
sqlite3 data/offline_survey.gpkg "PRAGMA integrity_check;"
# Expected: ok

If integrity_check returns anything other than ok, the file is corrupt at the page level and cannot be repaired by schema fixes alone — rebuild from source data.

Alternative Approaches

CLI-only path: ogr2ogr round-trip

If you do not need a structured JSON report, the fastest single-command check is an ogr2ogr copy to /vsimem/ (GDAL’s in-memory filesystem). GDAL will refuse to read non-compliant files:

bash
ogr2ogr -f GeoJSON /vsimem/test.geojson data/offline_survey.gpkg && echo "OK" || echo "FAIL"

This approach does not report which specific check failed, so it is better suited to quick manual triage than to automated pipelines.

Pure-SQL fallback (no GDAL required)

When deploying to headless environments without GDAL, replace the gdal_geometry check with a direct query against gpkg_geometry_columns. This does not validate WKB blob integrity, but it catches the most common schema-level violations:

sql
-- GeoPackage context — pure-SQL geometry column check
SELECT gc.table_name, gc.column_name, gc.geometry_type_name, gc.srs_id,
       CASE WHEN s.srs_id IS NULL THEN 'MISSING_SRS' ELSE 'OK' END AS srs_status
FROM gpkg_geometry_columns gc
LEFT JOIN gpkg_spatial_ref_sys s ON gc.srs_id = s.srs_id;

Rows with srs_status = 'MISSING_SRS' indicate the same orphan-SRS condition the Python script catches in check 3.

Troubleshooting

sqlite3.DatabaseError: file is not a database

Cause: The file at the given path is not a valid SQLite 3 container — it may be a truncated download, a corrupted filesystem write, or an entirely different format (e.g., a GeoJSON file renamed to .gpkg).

Fix: Verify the first 16 bytes of the file contain the SQLite magic string SQLite format 3\000:

bash
hexdump -C data/suspect.gpkg | head -1
# Should start with: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00

If they do not, the file must be regenerated from its source data using ogr2ogr -f GPKG.

missing: ['gpkg_extensions']

Cause: The file was created via a raw SQLite dump, a non-compliant tool, or an older GDAL version (pre-2.0) that did not write the gpkg_extensions table.

Fix: Re-export using a modern GDAL:

bash
ogr2ogr -f GPKG output_compliant.gpkg input_legacy.gpkg

This creates all four mandatory tables automatically. Confirm with:

bash
sqlite3 output_compliant.gpkg ".tables" | grep gpkg_
# gpkg_contents  gpkg_extensions  gpkg_geometry_columns  gpkg_spatial_ref_sys

gdal_geometry: FAIL with ERROR 1: ... Corrupt data

Cause: One or more geometry blobs contain malformed WKB — typically caused by a partial write, an interrupted bulk insert, or incorrect byte-order encoding. Managing transaction scoping and rollback strategies during bulk inserts prevents this class of corruption.

Fix: Identify the affected layer:

bash
ogrinfo -sql "SELECT fid FROM survey_points WHERE ST_IsValid(geom) = 0" \
    data/offline_survey.gpkg

Then rebuild the geometries in place using ST_MakeValid() (available in GDAL’s SQLite dialect) or re-export the layer with a spatial filter to exclude null geometries.