File Structure & Header Analysis for Spatial SQLite Databases

Skipping binary header validation in a spatial pipeline means the first sign of a corrupted or incomplete .gpkg is an opaque sqlite3.OperationalError deep inside a geometry query — not a clean, actionable rejection at ingestion time. This page shows you how to catch those problems deterministically at the door, before any spatial query runs.

Part of the Core Architecture & Format Standards for Spatial SQLite section, this guide details how to programmatically inspect, validate, and parse the binary headers of SpatiaLite and GeoPackage files using Python’s standard library. The workflow covers the SQLite base format, spatial extension signatures, production-ready parsing patterns, and the failure modes most commonly encountered with field-collected and mobile-synced data.

Prerequisites #

Before implementing header inspection routines, confirm your environment meets these requirements:

• Python 3.9+ — required for modern type hinting, pathlib resolution, and consistent struct unpacking across platforms (ARM field tablets vs. x86 CI runners)
• Standard library only — sqlite3, struct, pathlib, os, and logging; no third-party spatial libraries needed for binary validation
• Read-only file access — target .sqlite, .gpkg, or .db files must be readable; avoid files locked by active write transactions or mobile sync processes
• Familiarity with Core Architecture & Format Standards — specifically how spatial extensions layer atop the base SQLite storage engine without altering the core page structure

Concept & Specification Reference: The 100-Byte SQLite Header #

Every SQLite database — regardless of spatial extension — begins with a fixed 100-byte header. This header dictates page allocation, versioning, and application-specific identifiers. Spatial formats do not alter this base structure; instead, they embed format-specific markers within it or rely on internal metadata tables registered after the header. Understanding these offsets is the foundation for rapid pre-flight validation.

The header is strictly defined in the SQLite File Format specification. Deviations from these offsets are rare but fatal. A robust validation routine reads exactly 100 bytes, verifies the magic string, then unpacks critical fields before attempting any higher-level spatial operation.

Spatial Format Signatures & Extension Markers #

The base SQLite header is identical whether a file contains geospatial data or a to-do list. Spatial formats diverge in how they declare their purpose. That distinction dictates your validation strategy.

GeoPackage (OGC 1.2+) embeds its identity directly into the Application ID field at offset 68–71. The value 0x47504B47 (GPKG) is a hard requirement for GeoPackage 1.2 and later; the superseded 1.0/1.1 releases used 0x47503130 (GP10). Beyond the header, GeoPackage mandates a specific schema layout that includes gpkg_spatial_ref_sys, gpkg_contents, and gpkg_geometry_columns. The GeoPackage Specification Deep Dive covers how these tables interact with header-level markers and what each column must contain under the OGC standard.

SpatiaLite takes a different approach. It leaves the Application ID at 0x00000000 and instead relies on internal metadata tables — geometry_columns, spatial_ref_sys, and spatialite_history — to declare spatial capability. Validation must therefore fall back to a lightweight schema check after the header passes. How those tables are bootstrapped and versioned is covered in SpatiaLite Metadata Tables Explained.

The OGC publishes the authoritative GeoPackage Standard, which explicitly defines header and schema requirements. When building cross-platform validation, always cross-reference header bytes with expected table existence rather than relying on file extensions alone — a field device might rename a plain SQLite file to .gpkg without ever writing the required metadata.

Step-by-Step Implementation #

Step 1 — Read and unpack the header #

Open the file in binary mode and read exactly 100 bytes. Use struct.unpack with big-endian byte order (>) because the SQLite header spec mandates big-endian encoding for all multi-byte integers.

# SpatiaLite / GeoPackage — binary header parsing (Python 3.9+)
import struct
import logging
from pathlib import Path
from typing import Union

logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")

VALID_MAGIC          = b"SQLite format 3\x00"
GEOPACKAGE_APP_ID    = 0x47504B47   # 'GPKG'  — GeoPackage 1.2+
GEOPACKAGE_LEGACY_ID = 0x47503130   # 'GP10'  — GeoPackage 1.0/1.1
SPATIALITE_APP_ID    = 0x00000000   # no Application ID set

def read_sqlite_header(path: Union[str, Path]) -> bytes:
    path = Path(path)
    if not path.is_file():
        raise FileNotFoundError(f"File not found: {path}")
    with open(path, "rb") as fh:
        header = fh.read(100)
    if len(header) < 100:
        raise ValueError(f"Truncated file: only {len(header)} bytes (expected ≥100).")
    return header

Step 2 — Validate the magic string #

# SpatiaLite / GeoPackage — magic string check
def check_magic(header: bytes) -> None:
    magic = header[0:16]
    if magic != VALID_MAGIC:
        raise ValueError(
            f"Invalid SQLite magic. "
            f"Got {magic!r}, expected {VALID_MAGIC!r}. "
            "File may be corrupt, encrypted, or not a SQLite database."
        )

Step 3 — Unpack critical fields #

# SpatiaLite / GeoPackage — field extraction
def parse_header_fields(header: bytes) -> dict:
    page_size_raw = struct.unpack(">H", header[16:18])[0]
    # Per SQLite spec, raw value 1 encodes a 65536-byte page
    page_size = 65536 if page_size_raw == 1 else page_size_raw

    if page_size < 512 or (page_size & (page_size - 1)) != 0:
        raise ValueError(
            f"Invalid page size: {page_size}. "
            "Must be a power of two between 512 and 65536."
        )

    change_counter  = struct.unpack(">I", header[24:28])[0]
    db_size_pages   = struct.unpack(">I", header[28:32])[0]
    app_id          = struct.unpack(">I", header[68:72])[0]
    version_valid   = struct.unpack(">I", header[92:96])[0]
    sqlite_version  = struct.unpack(">I", header[96:100])[0]

    return {
        "page_size":       page_size,
        "change_counter":  change_counter,
        "db_size_pages":   db_size_pages,
        "app_id":          app_id,
        "version_valid":   version_valid,
        "sqlite_version":  sqlite_version,
        "is_geopackage":   app_id in (GEOPACKAGE_APP_ID, GEOPACKAGE_LEGACY_ID),
        "is_spatialite":   app_id == SPATIALITE_APP_ID,
    }

Step 4 — Detect format and route to schema validation #

# SpatiaLite / GeoPackage — format routing
import sqlite3

REQUIRED_GPKG_TABLES = {
    "gpkg_spatial_ref_sys",
    "gpkg_contents",
    "gpkg_geometry_columns",
}

REQUIRED_SPATIALITE_TABLES = {
    "geometry_columns",
    "spatial_ref_sys",
}

def get_table_names(db_path: Union[str, Path]) -> set[str]:
    conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)
    try:
        rows = conn.execute(
            "SELECT name FROM sqlite_master WHERE type='table';"
        ).fetchall()
        return {row[0] for row in rows}
    finally:
        conn.close()

def validate_spatial_format(db_path: Union[str, Path]) -> dict:
    path = Path(db_path)
    header = read_sqlite_header(path)
    check_magic(header)
    fields = parse_header_fields(header)

    tables = get_table_names(path)

    if fields["is_geopackage"]:
        missing = REQUIRED_GPKG_TABLES - tables
        if missing:
            raise ValueError(f"GeoPackage header detected but missing tables: {missing}")
        fields["format"] = "GeoPackage"
        logging.info("GeoPackage validated: %s", path.name)

    elif fields["is_spatialite"]:
        missing = REQUIRED_SPATIALITE_TABLES - tables
        if missing:
            raise ValueError(
                f"SpatiaLite App ID (0x0) detected but missing tables: {missing}. "
                "File may be a plain SQLite database, not a SpatiaLite database."
            )
        fields["format"] = "SpatiaLite"
        logging.info("SpatiaLite validated: %s", path.name)

    else:
        raise ValueError(
            f"Unknown spatial format. App ID 0x{fields['app_id']:08X} "
            "is neither GeoPackage (GPKG/GP10) nor SpatiaLite (0x0)."
        )

    return fields

Validation & Verification #

After implementing the parser, confirm it behaves correctly against known files before deploying to production pipelines.

Check a GeoPackage with ogrinfo:

# GeoPackage — quick OGC compliance pre-check
ogrinfo -al -so your_file.gpkg

If ogrinfo returns layer metadata without errors, the GeoPackage Application ID and required tables are intact.

Check Application ID directly with sqlite3 PRAGMA:

sqlite3 your_file.gpkg "PRAGMA application_id;"
# Expected: 1196444487  (= 0x47504B47 as a signed 32-bit integer)

sqlite3 your_db.sqlite "PRAGMA application_id;"
# Expected: 0  (SpatiaLite leaves this unset)

Assert table presence in Python:

# SpatiaLite / GeoPackage — schema assertion
import sqlite3

def assert_geopackage_schema(db_path: str) -> None:
    conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)
    tables = {
        r[0] for r in conn.execute(
            "SELECT name FROM sqlite_master WHERE type='table';"
        )
    }
    conn.close()
    required = {"gpkg_spatial_ref_sys", "gpkg_contents", "gpkg_geometry_columns"}
    assert required <= tables, f"Missing GeoPackage tables: {required - tables}"

Verify page-size consistency for large spatial datasets:

sqlite3 your_file.gpkg "PRAGMA page_size; PRAGMA page_count;"
# Multiply the two values and compare with: du -b your_file.gpkg

A mismatch between page_size * page_count and actual file size on disk is a strong indicator of a truncated sync.

Common Failure Modes & Fixes #

1. Truncated sync — physical file shorter than header claims #

Symptom: validate_spatial_file() raises ValueError("Truncated file") or page-count arithmetic diverges from os.path.getsize().

Diagnosis:

import os
from pathlib import Path

def check_page_count_consistency(db_path: Union[str, Path]) -> bool:
    path = Path(db_path)
    header = read_sqlite_header(path)
    fields = parse_header_fields(header)
    actual_bytes = path.stat().st_size
    expected_bytes = fields["page_size"] * fields["db_size_pages"]
    if expected_bytes != actual_bytes:
        logging.warning(
            "Size mismatch: header claims %d bytes, disk shows %d bytes.",
            expected_bytes, actual_bytes,
        )
        return False
    return True

Fix: Re-export or re-download the file. Do not attempt spatial queries on a file with a page-count mismatch — the B-tree may point to pages that do not exist.

2. Extension misidentification — wrong file renamed to .gpkg #

Symptom: validate_spatial_format() raises ValueError("GeoPackage header detected but missing tables: {'gpkg_contents', ...}").

Diagnosis:

sqlite3 suspect.gpkg ".tables"
# Returns nothing or only user tables — no gpkg_* tables present

Fix: Re-create the file through a compliant GeoPackage writer (GDAL/OGR, Connection Pooling & Lifecycle Management patterns, or any OGC-certified SDK). Renaming is never a substitute for proper format initialization.

3. Concurrent write corruption — change counter desync #

Symptom: Abnormal jumps in the file change counter between polling intervals; SQLite reports SQLITE_BUSY or journal files are left behind after a device crash.

Diagnosis:

import time

def poll_change_counter(db_path: Union[str, Path], interval: float = 1.0) -> None:
    prev = None
    while True:
        header = read_sqlite_header(db_path)
        fields = parse_header_fields(header)
        cc = fields["change_counter"]
        if prev is not None and cc - prev > 50:
            logging.warning("Change counter jumped %d in %.1fs — possible crash.", cc - prev, interval)
        prev = cc
        time.sleep(interval)

Fix: After detecting a desync, run sqlite3 your_file.gpkg "PRAGMA integrity_check;" and, if clean, PRAGMA wal_checkpoint(FULL);. If integrity_check returns errors, restore from the last known-good backup.

4. Invalid page size — non-power-of-two or out-of-range value #

Symptom: parse_header_fields() raises ValueError("Invalid page size: ...").

Diagnosis:

raw = struct.unpack(">H", header[16:18])[0]
print(f"Raw page size bytes: {raw}")  # 1 = 65536; anything else must be 512–32768 and power-of-two

Fix: The page size is set at database creation time and cannot be changed without rebuilding. Re-export through a properly configured GDAL driver or use sqlite3 :memory: with PRAGMA page_size = 4096; before attaching the data.

5. SpatiaLite detected but geometry_columns uses legacy schema #

Symptom: Validation passes header and table-presence checks, but SELECT * FROM geometry_columns returns unexpected column names (e.g. f_table_name vs. table_name in very old SpatiaLite 2.x files).

Diagnosis:

# SpatiaLite — check geometry_columns column names
conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)
cols = [row[1] for row in conn.execute("PRAGMA table_info(geometry_columns);")]
conn.close()
print(cols)
# Modern SpatiaLite 4+: ['f_table_name', 'f_geometry_column', 'geometry_type', 'coord_dimension', 'srid', 'spatial_index_enabled']

Fix: Upgrade the metadata schema by loading mod_spatialite and calling UpdateLayerStatistics() or re-initializing with InitSpatialMetadata(1). Schema migration details are in SpatiaLite Metadata Tables Explained.

Performance Notes #

Header validation adds less than one millisecond per file — even on ARM field hardware — because it reads only 100 bytes and makes at most one schema query. In batch pipelines processing thousands of files, the cost is dominated by filesystem stat calls, not I/O.

Generator pattern for batch validation:

# SpatiaLite / GeoPackage — validated-path generator for batch pipelines
from pathlib import Path
from typing import Iterator

def valid_spatial_files(directory: Union[str, Path]) -> Iterator[Path]:
    for path in Path(directory).glob("**/*.gpkg"):
        try:
            validate_spatial_format(path)
            yield path
        except (ValueError, FileNotFoundError) as exc:
            logging.warning("Skipping %s: %s", path.name, exc)

Pass this generator directly to ogr2ogr, pyogrio, or GeoPandas GeoPackage Integration batch loaders. Only verified files enter the spatial processing pipeline, eliminating sqlite3.OperationalError exceptions mid-run.

Page cache considerations: If your validation routine opens a sqlite3.connect() connection to inspect tables, set PRAGMA cache_size = -2000 (2 MB) for the validation connection — large caches are wasteful when you only need sqlite_master, and the default 2 MB is already generous for schema inspection.

VACUUM timing: Header validation is a read-only operation and does not interact with VACUUM. However, if your validation reveals db_size_pages is much larger than the actual row count suggests (a sign of many deleted geometry rows), schedule a VACUUM ANALYZE before the next batch import to reclaim free pages and keep B-tree traversal efficient.

Child Pages #

• How to Validate GeoPackage OGC Compliance — step-by-step OGC conformance testing using ogrinfo and Python assertions
• Reading Spatial Metadata with Python — querying geometry_columns, spatial_ref_sys, and SpatiaLite history tables programmatically

Related #

• Core Architecture & Format Standards for Spatial SQLite — the parent section: three-layer storage model, extension loading, and format overview
• GeoPackage Specification Deep Dive — OGC-mandated tables, trigger logic, and binary geometry encoding in detail
• SpatiaLite Metadata Tables Explained — geometry_columns, spatial_ref_sys, R-tree registration, and schema migration
• Security Boundaries & Access Controls — read-only URI connections, file-permission hardening, and encryption options for field-deployed databases
• Connection Pooling & Lifecycle Management — structuring Python connection lifetimes so header-validated files are opened exactly once per pipeline run