GDAL vs sqlite3 Module for GeoPackage Writes

Choose the GDAL/OGR GPKG driver when you want correct metadata tables, spatial-index triggers, and OGC conformance handled for you; choose raw Python sqlite3 when you need full control over transactions, batch sizing, or a minimal dependency footprint and are willing to own the GeoPackage bookkeeping yourself.

This decision guide is part of the End-to-End Spatial Automation Recipes guide. The create and ingest stages of any pipeline face this write-path choice, and it determines how much specification detail your own code must carry.

Why This Matters #

A GeoPackage is a plain SQLite file, which tempts engineers to write it with the standard-library sqlite3 module and skip the heavy GDAL dependency. That works — until a mobile SDK rejects the file because gpkg_contents lacks a row, or a spatial query returns nothing because the R-tree triggers were never created. The GPKG driver exists precisely to prevent those omissions, but it pulls in a large native dependency and abstracts away the transaction control that high-throughput or lock-sensitive pipelines depend on.

The right answer is not universal; it depends on whether correctness-by-default or control-by-default matters more for the stage you are writing. This guide lays out the tradeoffs so you can decide deliberately rather than by whichever import you reached for first.

Why This Comparison #

Choosing a write path early prevents rework. Retrofitting metadata management onto a sqlite3 pipeline after a mobile client rejects the output is far more expensive than accepting GDAL’s dependency at the start — and conversely, wrapping GDAL when you actually needed byte-level transaction control means fighting the abstraction on every commit.

Prerequisites #

• Python 3.9+ with the standard-library sqlite3 module
• GDAL/OGR 3.4+ with the GPKG driver, if evaluating the OGR path (ogrinfo --formats | grep GPKG)
• Familiarity with the mandatory GeoPackage tables from the GeoPackage specification deep dive
• A target feature dataset to benchmark both paths against

Primary Method #

The decision reduces to one question: does the stage need the GeoPackage bookkeeping done for it, or does it need control that the abstraction gets in the way of? The comparison table answers it across the dimensions that actually differ in production.

The OGR write path #

The GPKG driver encodes the specification so your code does not have to. Creating a layer emits the metadata rows and, on request, the spatial index and its triggers; every subsequent write updates them.

# GDAL/OGR 3.4+ — driver manages all GeoPackage metadata and indexing
from osgeo import ogr, osr

def create_layer_ogr(path: str, layer: str, epsg: int = 4326) -> None:
    """Create a conformant GeoPackage layer; the driver owns the bookkeeping."""
    driver = ogr.GetDriverByName("GPKG")
    ds = driver.CreateDataSource(path)
    srs = osr.SpatialReference()
    srs.ImportFromEPSG(epsg)
    lyr = ds.CreateLayer(
        layer, srs=srs, geom_type=ogr.wkbPoint,
        options=["SPATIAL_INDEX=YES", "GEOMETRY_NAME=geom"],
    )
    lyr.CreateField(ogr.FieldDefn("name", ogr.OFTString))
    feat = ogr.Feature(lyr.GetLayerDefn())
    feat.SetField("name", "site-1")
    pt = ogr.Geometry(ogr.wkbPoint)
    pt.AddPoint(4.9, 49.5)
    feat.SetGeometry(pt)
    lyr.CreateFeature(feat)
    ds.FlushCache()
    ds = None  # closing flushes metadata, triggers, and the R-tree

The raw sqlite3 write path #

The standard-library path gives you the connection outright. You control the transaction, but you also own every mandatory table and the GPB-encoded geometry blob.

# Python sqlite3 — full control, full responsibility for GeoPackage bookkeeping
import sqlite3
import struct

def gpb_point(x: float, y: float, srid: int = 4326) -> bytes:
    """Encode a point as GeoPackage Binary: 'GP' magic, version, flags, then WKB."""
    header = struct.pack("<2sBBi", b"GP", 0, 0x01, srid)  # little-endian, no envelope
    wkb = struct.pack("<BIdd", 1, 1, x, y)                # WKB point, byte order 1
    return header + wkb

def insert_point_sqlite(conn: sqlite3.Connection, table: str, name: str,
                        x: float, y: float) -> None:
    """Insert one feature, assuming the metadata and triggers already exist."""
    conn.execute(
        f"INSERT INTO {table} (name, geom) VALUES (?, ?)",
        (name, gpb_point(x, y)),
    )

The insert_point_sqlite function only works if the table, its gpkg_contents and gpkg_geometry_columns rows, and its R-tree triggers already exist — bookkeeping the OGR path would have done for you. That asymmetry is the whole decision.

Step-by-Step Walkthrough #

1. Decide whether the stage creates or appends #

Creating a new GeoPackage from scratch is where OGR pays for itself: it writes dozens of mandatory rows and triggers correctly the first time. Appending rows to a GeoPackage that OGR already created is where sqlite3 shines, because the triggers are already in place and you only need control over batching and locking.

2. Weigh correctness risk against control #

If the output is consumed by third-party tools — QGIS, ArcGIS, a mobile SDK — a single missing trigger or metadata row makes the file nonconformant, and OGR eliminates that class of bug. If the output stays inside your own controlled pipeline and correctness is validated by a gate anyway, the sqlite3 control advantage wins.

3. Weigh dependency footprint #

On a constrained field device or a minimal container, the multi-tens-of-megabyte GDAL native library is a real cost. A sqlite3-only writer ships with Python and adds nothing. If the deployment already bundles GDAL for reads, this factor is moot.

4. Benchmark the transaction path you need #

For high-volume inserts, sqlite3 with a single explicit transaction and tuned PRAGMAs often outperforms per-feature OGR CreateFeature calls, because you control exactly when commits happen. Wrap the load in one transaction rather than committing per row.

import sqlite3

def bulk_insert_sqlite(path: str, table: str, rows: list[tuple]) -> None:
    """Load many features in one transaction with write-tuned PRAGMAs."""
    conn = sqlite3.connect(path)
    try:
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA synchronous=NORMAL;")
        with conn:  # single transaction: commit once at block exit
            conn.executemany(
                f"INSERT INTO {table} (name, geom) VALUES (?, ?)", rows
            )
    finally:
        conn.close()

A bulk sqlite3 insert bypasses OGR’s index triggers, so it must be followed by an index rebuild — see how to automate R-tree index rebuilds after bulk load.

Recommendation #

Default to the OGR GPKG driver for any stage that creates a GeoPackage from scratch or converts between formats: it makes the file conformant by construction and removes an entire category of silent bugs. Reach for raw sqlite3 when a stage appends to a GeoPackage OGR already created, when you need BEGIN IMMEDIATE or custom batch boundaries for lock-sensitive field sync, or when the deployment cannot afford the GDAL dependency. Many production pipelines use both — OGR for the create stage, sqlite3 for high-throughput append and index maintenance — which is exactly the division the recipes in this guide adopt. For the driver configuration side of the OGR path, see Fiona & OGR driver configuration; for the low-level sqlite3 side, see native sqlite3 spatial extensions.

Alternative Approaches or Edge Cases #

Fiona as a middle path. Fiona wraps OGR with a Pythonic streaming API, giving you record-by-record writes with OGR’s correctness. It is the pragmatic default for the ingest stage when you want OGR’s guarantees without the verbose osgeo.ogr object model.

GeoPandas for whole-DataFrame writes. When the data already lives in a GeoDataFrame and fits in memory, to_file(driver="GPKG") is the fewest lines. It routes through OGR, inheriting the correctness benefits, at the cost of the same control limitations discussed above.

Troubleshooting #

Mobile SDK reports the GeoPackage as invalid after a sqlite3 write #

Cause: The sqlite3 path created a feature table but never inserted the matching gpkg_contents and gpkg_geometry_columns rows, so the file is nonconformant.

Fix: Either create the layer with OGR first and append with sqlite3, or write the mandatory metadata rows explicitly following the GeoPackage specification deep dive.

Spatial queries return nothing after a raw insert #

Cause: Raw sqlite3 inserts bypass the R-tree maintenance triggers, leaving the index empty.

Fix: Run an index rebuild after the bulk load, as covered in the sibling recipe, then verify with EXPLAIN QUERY PLAN.

struct.error when encoding the GPB geometry blob #

Cause: The format string passed to struct.pack does not match the byte order flag in the WKB, producing a malformed blob that readers reject.

Fix: Keep the byte-order byte in the WKB consistent with the endianness in the format string — the example above uses little-endian (<) with WKB byte-order 1 throughout.

Related #

• End-to-End Spatial Automation Recipes — parent guide where this choice shapes the create and ingest stages
• Native sqlite3 Spatial Extensions — the low-level write path, extension loading, and PRAGMA tuning
• Fiona & OGR Driver Configuration — configuring the OGR write path with creation options and schema enforcement
• How to Automate R-tree Index Rebuilds After Bulk Load — the mandatory follow-up after any raw sqlite3 bulk insert