GeoPackage Specification Deep Dive

Without a precise understanding of GeoPackage’s mandatory schema contracts, spatial pipelines fail in hard-to-diagnose ways: QGIS silently drops layers, GDAL-based readers return empty feature sets, and mobile runtimes reject containers that pass naive sqlite3 integrity checks. A file that opens without error in Python is not the same as a file that satisfies OGC compliance — the difference lives in four system tables, a binary geometry envelope, and an R-tree registration that most tutorials omit entirely.

This page is part of the Core Architecture & Format Standards for Spatial SQLite reference. It covers the specification constraints that matter for production automation: header validation, mandatory table structures, GeoPackage Binary (GPB) geometry encoding, spatial indexing mechanics, and the failure modes most likely to surface in real field-device or cloud-sync deployments.

Prerequisites #

Before working through the implementation sections below, confirm your environment meets these baselines:

• Python 3.9+ — required for struct.pack with <BB format codes and reliable BLOB round-trips via sqlite3
• SQLite 3.25.0+ — built-in sqlite3 module must expose window functions and the R-Tree module (verify with sqlite3.sqlite_version)
• GDAL/OGR 3.4+ — needed for format translation, ogrinfo validation, and coordinate transformation pipelines
• Shapely 2.0+ — for geometry serialization (wkb_dumps) and topology checks before insertion
• Working SQL DDL/DML knowledge — especially CREATE TABLE … REFERENCES, INSERT OR IGNORE, and PRAGMA statements
• File-system write access to the target .gpkg path, with a plan for concurrent-access scenarios (WAL mode is mandatory, not optional)

Concept & Specification Reference #

File Header Constraints #

A GeoPackage is an SQLite 3 database file with two hard requirements beyond the normal 16-byte SQLite magic string (SQLite format 3\000):

The application ID is what distinguishes a GeoPackage from an ordinary SQLite file. GDAL’s GeoPackage driver checks this field first; a mismatch causes the driver to reject the file before reading any table. The file structure and header analysis page covers the full 100-byte SQLite header layout and how to read these fields programmatically without opening the database.

Mandatory Table Structures #

The OGC specification requires four system tables in every compliant container. Missing or malformed rows in any of them breaks interoperability with QGIS, ArcGIS, and every GDAL-based pipeline.

gpkg_spatial_ref_sys — the coordinate reference system registry. Every container must seed this table with three rows before any geometry data arrives: srs_id = -1 (undefined Cartesian), srs_id = 0 (undefined geographic), and at least one real CRS for your data (e.g. EPSG:4326). Definitions are stored as WKT, which supports custom projections for localized surveys. For the mechanics of registering and querying CRS rows, see Managing Spatial Reference Systems in SQLite.

gpkg_contents — the primary table registry. One row per user data table; must declare the data_type (features, attributes, or tiles), a bounding box, and the srs_id. An out-of-date bounding box causes QGIS to mis-zoom on layer load; an invalid data_type causes GDAL to skip the table entirely. Unlike SpatiaLite’s implicit geometry discovery (compared in SpatiaLite Metadata Tables Explained), GeoPackage requires every table to be explicitly registered here.

gpkg_geometry_columns — maps geometry columns to their parent tables with type constraints (POINT, LINESTRING, POLYGON, MULTIPOLYGON, etc.), dimensionality flags (z, m), and an srs_id. The table_name column is a foreign key into gpkg_contents, so gpkg_contents must be populated first.

gpkg_extensions — tracks enabled extensions by table, column, extension name, and scope (read-write or write-only). If your container uses an R-tree spatial index, the extension row for gpkg_rtree_index must exist here; readers that check the extension table before executing spatial queries will skip indexing otherwise. Extension Compatibility in Spatial SQLite documents how mismatched extension registrations cause silent failures across heterogeneous runtime environments.

GeoPackage Binary (GPB) Geometry Encoding #

This is the single most common source of silent data corruption in GeoPackage automation. Geometry columns do not store raw WKB. The spec requires a GPB envelope prepended to every WKB payload:

The flags byte at offset 3 is critical: bit 0 controls byte order (0 = big-endian header, 1 = little-endian), bits 1–3 encode the envelope type (0 = no envelope, 1 = minx/maxx/miny/maxy, etc.), and bit 4 marks an empty geometry. Storing raw WKB without the GP prefix causes ogrinfo to report the geometry column as None and QGIS to show an empty attribute table — with no error messages.

Step-by-step Implementation #

The following implementation builds a fully compliant GeoPackage from scratch using Python’s sqlite3 module. Table creation order matters because of foreign key constraints; the sequence below is the only safe order.

Step 1 — Set PRAGMAs and application ID

# GeoPackage context — set mandatory file-level metadata
import sqlite3
import struct

conn = sqlite3.connect("field_survey.gpkg")
conn.execute("PRAGMA journal_mode=WAL;")
conn.execute("PRAGMA foreign_keys=ON;")
# GeoPackage 1.2+ application ID (decimal: 1196444487)
conn.execute("PRAGMA application_id=1196444487;")
# GeoPackage 1.2 user_version: major*10000 + minor*100 + patch → 10200
conn.execute("PRAGMA user_version=10200;")

Step 2 — Create gpkg_spatial_ref_sys first

# GeoPackage context — CRS registry; must exist before contents or geometry_columns
conn.execute("""
    CREATE TABLE IF NOT EXISTS gpkg_spatial_ref_sys (
        srs_name                 TEXT    NOT NULL,
        srs_id                   INTEGER NOT NULL PRIMARY KEY,
        organization             TEXT    NOT NULL,
        organization_coordsys_id INTEGER NOT NULL,
        definition               TEXT    NOT NULL,
        description              TEXT
    );
""")

conn.execute("""
    INSERT OR IGNORE INTO gpkg_spatial_ref_sys VALUES
        ('Undefined Cartesian',   -1, 'NONE', -1, 'undefined', ''),
        ('Undefined Geographic',   0, 'NONE',  0, 'undefined', ''),
        ('WGS 84 geodetic',     4326, 'EPSG', 4326,
         'GEOGCS["WGS 84",DATUM["World Geodetic System 1984",'
         'SPHEROID["WGS 84",6378137,298.257223563]],'
         'PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]]',
         'WGS 84 geographic 2D');
""")

Step 3 — Create gpkg_contents

# GeoPackage context
conn.execute("""
    CREATE TABLE IF NOT EXISTS gpkg_contents (
        table_name  TEXT     NOT NULL PRIMARY KEY,
        data_type   TEXT     NOT NULL,
        identifier  TEXT     UNIQUE,
        description TEXT     DEFAULT '',
        last_change DATETIME DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
        min_x REAL, min_y REAL, max_x REAL, max_y REAL,
        srs_id INTEGER REFERENCES gpkg_spatial_ref_sys(srs_id)
    );
""")

Step 4 — Create gpkg_geometry_columns and gpkg_extensions

# GeoPackage context
conn.execute("""
    CREATE TABLE IF NOT EXISTS gpkg_geometry_columns (
        table_name         TEXT    NOT NULL,
        column_name        TEXT    NOT NULL,
        geometry_type_name TEXT    NOT NULL,
        srs_id             INTEGER NOT NULL,
        z                  TINYINT NOT NULL,
        m                  TINYINT NOT NULL,
        CONSTRAINT pk_geom_cols PRIMARY KEY (table_name, column_name),
        CONSTRAINT fk_gc_tn  FOREIGN KEY (table_name)
            REFERENCES gpkg_contents(table_name),
        CONSTRAINT fk_gc_srs FOREIGN KEY (srs_id)
            REFERENCES gpkg_spatial_ref_sys(srs_id)
    );
""")

conn.execute("""
    CREATE TABLE IF NOT EXISTS gpkg_extensions (
        table_name     TEXT,
        column_name    TEXT,
        extension_name TEXT NOT NULL,
        definition     TEXT NOT NULL,
        scope          TEXT NOT NULL,
        CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name)
    );
""")

Step 5 — Create user feature table, then register it

# GeoPackage context — register in contents before geometry_columns (FK order)
conn.execute("""
    CREATE TABLE IF NOT EXISTS field_observations (
        id   INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT    NOT NULL,
        geom BLOB
    );
""")

conn.execute("""
    INSERT OR IGNORE INTO gpkg_contents
        (table_name, data_type, identifier, srs_id, min_x, min_y, max_x, max_y)
    VALUES ('field_observations', 'features', 'Field Observations',
            4326, -180.0, -90.0, 180.0, 90.0);
""")

conn.execute("""
    INSERT OR IGNORE INTO gpkg_geometry_columns
        (table_name, column_name, geometry_type_name, srs_id, z, m)
    VALUES ('field_observations', 'geom', 'POINT', 4326, 0, 0);
""")
conn.commit()

Step 6 — Create the R-tree spatial index and register the extension

# GeoPackage context — R-tree via SQLite rtree module
conn.execute("""
    CREATE VIRTUAL TABLE IF NOT EXISTS rtree_field_observations_geom
    USING rtree(id, minx, maxx, miny, maxy);
""")

# Register the extension so readers know the index exists
conn.execute("""
    INSERT OR IGNORE INTO gpkg_extensions
        (table_name, column_name, extension_name, definition, scope)
    VALUES ('field_observations', 'geom',
            'gpkg_rtree_index',
            'http://www.geopackage.org/spec/#extension_rtree_index',
            'write-only');
""")
conn.commit()

Step 7 — Insert features using GPB-encoded geometry

from shapely.geometry import Point
from shapely.wkb import dumps as wkb_dumps

def to_gpkg_blob(geom, srs_id: int = 4326) -> bytes:
    """Wrap a Shapely geometry in a GeoPackage Binary (GPB) header.

    Layout: 'GP' | version=0x00 | flags=0x01 (little-endian, no envelope)
            | srs_id (little-endian int32) | ISO WKB payload.
    Raw WKB without this header is silently rejected by GDAL and QGIS.
    """
    wkb = wkb_dumps(geom, hex=False, include_srid=False)
    header = b"GP" + struct.pack("<BB", 0x00, 0x01) + struct.pack("<i", srs_id)
    return header + wkb

def insert_feature(conn: sqlite3.Connection,
                   name: str, lat: float, lon: float,
                   srs_id: int = 4326) -> int:
    geom_blob = to_gpkg_blob(Point(lon, lat), srs_id)
    cur = conn.execute(
        "INSERT INTO field_observations (name, geom) VALUES (?, ?);",
        (name, geom_blob),
    )
    row_id = cur.lastrowid
    # Keep R-tree in sync: extract envelope from Point (minx=maxx, miny=maxy)
    conn.execute(
        "INSERT OR REPLACE INTO rtree_field_observations_geom "
        "VALUES (?, ?, ?, ?, ?);",
        (row_id, lon, lon, lat, lat),
    )
    return row_id

For advanced connection pooling and lifecycle management across concurrent workers, pass a single WAL-mode connection per thread or use check_same_thread=False with explicit locking — do not open multiple write connections to the same GeoPackage without coordinating WAL checkpoints.

Validation & Verification #

After building a GeoPackage programmatically, run these checks before distributing the file to field devices or downstream consumers.

Check application ID and user version:

# SQLite CLI — verify GeoPackage header PRAGMAs
sqlite3 field_survey.gpkg "PRAGMA application_id; PRAGMA user_version;"
# Expected: 1196444487 then 10200 (or higher)

Verify mandatory tables exist:

-- GeoPackage context — confirm all four system tables are present
SELECT name FROM sqlite_master
WHERE type='table'
  AND name IN (
      'gpkg_spatial_ref_sys',
      'gpkg_contents',
      'gpkg_geometry_columns',
      'gpkg_extensions'
  )
ORDER BY name;
-- Must return all four rows

Check contents registration:

-- GeoPackage context — every feature table must appear here
SELECT table_name, data_type, srs_id FROM gpkg_contents;

Verify geometry column registration:

-- GeoPackage context — must match gpkg_contents rows 1-to-1
SELECT table_name, column_name, geometry_type_name, srs_id
FROM gpkg_geometry_columns;

Use ogrinfo for end-to-end OGC validation:

# Validate feature layer discovery via GDAL (OGR driver)
ogrinfo -al -so field_survey.gpkg
# Expect: layer name, geometry type, feature count, SRS info
# A missing GPB header causes geometry type to print as "None"

For a full compliance audit including tile matrix validation and extension-scope checks, see How to Validate GeoPackage OGC Compliance.

Common Failure Modes & Fixes #

Missing gpkg_extensions row for R-tree #

Symptom: Spatial queries run but return all features regardless of bounding box; ogrinfo -al -so shows no spatial index.

Diagnosis:

-- GeoPackage context
SELECT * FROM gpkg_extensions WHERE extension_name = 'gpkg_rtree_index';
-- Returns zero rows if the extension was never registered

Fix: Insert the missing registration row (shown in Step 6 above), then verify the R-tree virtual table exists with SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'rtree_%';.

R-tree out of sync after bulk insert #

Symptom: Spatial queries miss features inserted via raw INSERT without updating the R-tree shadow tables.

Diagnosis:

-- GeoPackage context — compare row counts
SELECT COUNT(*) FROM field_observations;
SELECT COUNT(*) FROM rtree_field_observations_geom;
-- If counts differ, the R-tree is stale

Fix: Rebuild the index by deleting and repopulating the R-tree from the geometry BLOBs. Because triggers on the feature table update the R-tree automatically only if GDAL created the index (it installs triggers), a manually created R-tree requires explicit updates in your Python insertion loop, as shown in insert_feature() above.

Wrong geometry blob format (raw WKB stored instead of GPB) #

Symptom: ogrinfo reports geometry type as None; QGIS shows an empty layer with no error.

Diagnosis:

# Read back the first geometry blob and inspect the magic bytes
conn = sqlite3.connect("field_survey.gpkg")
row = conn.execute("SELECT geom FROM field_observations LIMIT 1;").fetchone()
blob = row[0]
print(blob[:2])  # Must be b'GP'; if b'\x01' or b'\x00\x00', it is raw WKB

Fix: Re-encode all rows with to_gpkg_blob() and update in place using UPDATE field_observations SET geom = ? WHERE id = ?.

gpkg_contents and gpkg_geometry_columns out of sync #

Symptom: GDAL opens the file but finds no layers; ogrinfo lists zero feature classes.

Diagnosis:

-- GeoPackage context
SELECT gc.table_name FROM gpkg_geometry_columns gc
LEFT JOIN gpkg_contents c ON gc.table_name = c.table_name
WHERE c.table_name IS NULL;
-- Any row returned means a geometry column has no contents registration

Fix: Insert the missing row into gpkg_contents with the correct data_type = 'features' and a valid srs_id. GDAL uses gpkg_contents as its layer discovery table; gpkg_geometry_columns alone is insufficient.

Undefined SRS rows missing #

Symptom: PRAGMA foreign_keys=ON; combined with INSERT INTO gpkg_contents throws FOREIGN KEY constraint failed.

Diagnosis:

-- GeoPackage context
SELECT srs_id FROM gpkg_spatial_ref_sys WHERE srs_id IN (-1, 0);
-- Must return two rows before any contents can be inserted

Fix: Run the three-row seed INSERT OR IGNORE from Step 2 before creating any other tables.

Performance Notes #

R-tree rebuild cost. Rebuilding the R-tree after a bulk insert that bypassed trigger-based updates scales as O(N log N) against feature count. For imports of over 100 000 features, batch-insert into the feature table first, then rebuild the R-tree in one pass using a single INSERT INTO rtree_field_observations_geom SELECT ... statement rather than per-row updates.

WAL checkpoint timing. In WAL mode, write-ahead log files accumulate until a checkpoint runs. For GeoPackage files synced to field devices over limited bandwidth, trigger PRAGMA wal_checkpoint(TRUNCATE); after each major import batch to keep the .gpkg-wal file small and avoid shipping oversized files.

Page-cache sizing. GeoPackage spatial queries scan R-tree shadow tables (_node, _parent) as B-tree pages. Setting PRAGMA cache_size=-16000; (16 MB) before spatial query sessions keeps hot index pages in memory and reduces read amplification on datasets with dense geometry clustering. For a wider treatment of WAL tuning and page-cache configuration, see the Connection Pooling & Lifecycle Management reference.

VACUUM and spatial indexes. Running VACUUM on a GeoPackage compacts the file but does not rebuild R-tree statistics. After vacuuming a large container, run INSERT INTO rtree_field_observations_geom(rtree_field_observations_geom) VALUES('optimize'); to defragment the R-tree nodes and restore query selectivity.

Pages in This Section #

• How to Validate GeoPackage OGC Compliance — step-by-step audit using ogrinfo, PRAGMA assertions, and Python schema checks to confirm a container satisfies OGC requirements before field distribution
• SpatiaLite vs GeoPackage Performance Benchmarks — empirical comparison of read/write latency, R-tree index rebuild times, and memory footprint across varying dataset scales

Related #

• Core Architecture & Format Standards for Spatial SQLite — the parent reference covering the three-layer storage model, WAL mode, ACID guarantees, and the full landscape of SQLite-based spatial formats
• SpatiaLite Metadata Tables Explained — how SpatiaLite organises its geometry columns and CRS definitions differently from GeoPackage’s explicit registration model
• Extension Compatibility in Spatial SQLite — how to detect and handle mismatched extension versions across mobile runtimes, GDAL builds, and desktop GIS environments
• Spatial Data Serialization Patterns — WKB, WKT, and GPB encoding strategies for batch geometry pipelines in Python
• Transaction Scoping & Rollback Strategies — how to scope transactions around multi-table GeoPackage writes so that partial failures leave the container in a consistent OGC-compliant state