Best Practices for Branching GeoPackage Projects

Treat a .gpkg file as a versioned SQLite database — not a binary blob — and enforce strict file-isolation, WAL checkpointing, and primary-key-aligned delta operations at every stage. Part of Feature Branching for GIS Development Teams.

Concept & Context

GeoPackage is built on SQLite, which provides strong transactional guarantees within a single process but offers no native distributed branching or three-way merge primitives. Unlike text-based repositories, you cannot diff a .gpkg at the byte level and recover meaningful spatial semantics. Spatial indexes (rtree), geometry validation triggers, and CRS metadata bound tightly to internal table structures make in-place forking unsafe. Concurrent writes to a single file risk index corruption, partial transactions, and trigger deadlocks.

SQLite’s Write-Ahead Logging (WAL) mode adds a second constraint: pending transactions live in a separate -wal file that travels with the database. Copying the .gpkg without first checkpointing produces an inconsistent snapshot — features committed after the last checkpoint exist only in the sidecar file, not in your branch copy. This constraint applies equally whether you store the file in Git LFS, an S3 versioned bucket, or a shared NFS mount. Teams applying automated conflict detection in merge requests downstream depend on each branch snapshot being a fully consistent, self-contained SQLite database.

The diagram below shows the complete branching lifecycle from a shared main.gpkg through isolated branch files to a reconciled merge:

Core Algorithmic Pipeline

The six-step sequence below must be followed in order; skipping any step can produce silently corrupted branch files or broken spatial indexes.

1. Checkpoint WAL before branching. Open the source .gpkg with sqlite3, run PRAGMA wal_checkpoint(TRUNCATE);, and close the connection. This flushes all pending transactions into the main database file and removes the -wal and -shm sidecars before the copy.

2. Copy the main file only. Use shutil.copy2(source_gpkg, branch_gpkg) on the .gpkg file only. Never copy -wal or -shm files — they are session-specific and will corrupt the branch if present.

3. Validate schema parity. Before any edits, assert that the branch and base share the same column names, data types, geometry column names, geometry types, and EPSG codes. Use pyogrio.read_info() or ogrinfo -so to extract this metadata. A mismatch here causes silent geometry truncation or CRS drift when the delta is applied.

4. Extract delta by stable primary key. Compare ogc_fid (or your business key / UUID column) sets between base and branch. Compute inserted IDs (branch − base), deleted IDs (base − branch), and the intersection for potential updates. Do not rely on rowid — it shifts under VACUUM and DELETE operations.

5. Apply delta atomically. Wrap all INSERT OR IGNORE, UPDATE, and DELETE statements in a single BEGIN IMMEDIATE; … COMMIT; block against the target file. If any statement fails, the database rolls back cleanly without leaving partial spatial changes.

6. Rebuild indexes and vacuum. After committing, drop and recreate rtree spatial indexes (gpkg_<table>_geom) using GDAL’s -lco SPATIAL_INDEX=YES or manual SQL triggers. Then run VACUUM to reclaim deleted pages and re-sequence internal page identifiers.

Working Implementation

The following module implements steps 1–4 end-to-end. It is self-contained and assumes a stable ogc_fid primary key exists on every feature table in the GeoPackage.

import sqlite3
import shutil
from pathlib import Path
from typing import NamedTuple

import pyogrio


class SchemaMeta(NamedTuple):
    geometry_type: str
    crs_wkt: str
    columns: tuple[str, ...]


class Delta(NamedTuple):
    inserted: set[int]
    deleted: set[int]
    candidate_updates: set[int]


def assert_wal_checkpoint(gpkg: Path) -> None:
    """Flush pending WAL transactions and remove sidecar files."""
    conn = sqlite3.connect(str(gpkg))
    try:
        result = conn.execute("PRAGMA wal_checkpoint(TRUNCATE);").fetchone()
        # result: (busy, log, checkpointed) — busy > 0 means active readers blocked
        if result[0] != 0:
            raise RuntimeError(
                f"WAL checkpoint blocked ({result}); close all connections first."
            )
    finally:
        conn.close()


def branch_geopackage(source: Path, branch: Path) -> None:
    """
    Checkpoint source WAL, then copy to branch path.
    Raises if source has active WAL readers or is missing.
    """
    if not source.exists():
        raise FileNotFoundError(f"Source GeoPackage not found: {source}")
    assert_wal_checkpoint(source)
    shutil.copy2(source, branch)


def read_schema(gpkg: Path, layer: str) -> SchemaMeta:
    """Return geometry type, CRS WKT, and column names for a layer."""
    info = pyogrio.read_info(gpkg, layer=layer)
    columns = tuple(info["fields"])
    return SchemaMeta(
        geometry_type=info["geometry_type"],
        crs_wkt=info["crs_wkt"],
        columns=columns,
    )


def validate_schema_parity(base: Path, branch: Path, layer: str) -> None:
    """
    Assert that base and branch share the same geometry type, CRS, and columns.
    Raises ValueError with a descriptive message on any mismatch.
    """
    base_schema = read_schema(base, layer)
    branch_schema = read_schema(branch, layer)

    if base_schema.geometry_type != branch_schema.geometry_type:
        raise ValueError(
            f"Geometry type mismatch: base={base_schema.geometry_type!r}, "
            f"branch={branch_schema.geometry_type!r}"
        )
    if base_schema.crs_wkt != branch_schema.crs_wkt:
        raise ValueError("CRS mismatch between base and branch — reproject before merging.")
    if set(base_schema.columns) != set(branch_schema.columns):
        only_base = set(base_schema.columns) - set(branch_schema.columns)
        only_branch = set(branch_schema.columns) - set(base_schema.columns)
        raise ValueError(
            f"Column mismatch — base only: {only_base}, branch only: {only_branch}"
        )


def extract_delta(
    base: Path,
    branch: Path,
    layer: str,
    pk: str = "ogc_fid",
) -> Delta:
    """
    Return inserted, deleted, and candidate-update feature ID sets.
    'candidate_updates' includes all shared IDs; a subsequent geometry/attribute
    comparison is needed to confirm which actually changed.
    """
    base_df = pyogrio.read_dataframe(base, layer=layer, columns=[pk])
    branch_df = pyogrio.read_dataframe(branch, layer=layer, columns=[pk])

    base_ids: set[int] = set(base_df[pk].dropna().astype(int))
    branch_ids: set[int] = set(branch_df[pk].dropna().astype(int))

    return Delta(
        inserted=branch_ids - base_ids,
        deleted=base_ids - branch_ids,
        candidate_updates=base_ids & branch_ids,
    )


def apply_delta_to_base(
    base: Path,
    branch: Path,
    layer: str,
    delta: Delta,
) -> None:
    """
    Apply a pre-computed Delta from branch into base using atomic SQLite operations.
    Inserts and updates use branch geometry/attributes; deletes remove from base.
    """
    base_conn = sqlite3.connect(str(base))
    branch_conn = sqlite3.connect(str(branch))

    try:
        base_conn.execute("BEGIN IMMEDIATE;")

        # Inserts: copy full rows from branch into base
        if delta.inserted:
            placeholders = ",".join("?" * len(delta.inserted))
            rows = branch_conn.execute(
                f"SELECT * FROM {layer} WHERE ogc_fid IN ({placeholders});",
                list(delta.inserted),
            ).fetchall()
            cols = [d[0] for d in branch_conn.execute(
                f"PRAGMA table_info({layer});"
            ).fetchall()]
            col_str = ", ".join(cols)
            val_str = ", ".join("?" * len(cols))
            base_conn.executemany(
                f"INSERT OR IGNORE INTO {layer} ({col_str}) VALUES ({val_str});", rows
            )

        # Deletes
        if delta.deleted:
            placeholders = ",".join("?" * len(delta.deleted))
            base_conn.execute(
                f"DELETE FROM {layer} WHERE ogc_fid IN ({placeholders});",
                list(delta.deleted),
            )

        base_conn.execute("COMMIT;")
    except Exception:
        base_conn.execute("ROLLBACK;")
        raise
    finally:
        base_conn.close()
        branch_conn.close()

Validation & Output Verification

After applying a delta, run these checks before marking the merge complete:

Row-count assertion. Query SELECT COUNT(*) FROM <layer> on both files and reconcile against your delta arithmetic: expected_base_count = original_base_count + len(delta.inserted) - len(delta.deleted). A discrepancy indicates a duplicate INSERT slipped through or a DELETE matched the wrong rows.

Spatial index integrity. Run PRAGMA integrity_check; on the merged file. If the rtree index is corrupt, GDAL’s ogrinfo will report ERROR 1: SQLite error result_code ... while executing SELECT rowid, minx, .... Rebuild with:

ogr2ogr -f GPKG rebuilt.gpkg merged.gpkg -lco SPATIAL_INDEX=YES

CRS round-trip check. Re-read the merged file’s CRS via pyogrio.read_info(merged_gpkg, layer=layer)["crs_wkt"] and compare the EPSG authority code to your project standard. A CRS mutation during merge indicates the schema parity check was skipped or bypassed.

Geometry validity scan. For datasets that passed through topology-sensitive operations (union, difference, buffer), run a validity assertion using spatial diff algorithms as the final gate:

import geopandas as gpd
from shapely.validation import make_valid

gdf = gpd.read_file(merged_gpkg, layer=layer)
invalid = gdf[~gdf.geometry.is_valid]
if not invalid.empty:
    print(f"{len(invalid)} invalid geometries — run make_valid before committing")

Failure Modes

• Symptom: Branch copy opens but spatial queries return no rows. Cause: Copy was made while a -wal file held uncommitted inserts; the main .gpkg file does not contain those features. Fix: Delete the branch copy, run PRAGMA wal_checkpoint(TRUNCATE); on the source, then re-copy.

• Symptom: ogrinfo reports Geometry type: Unknown (wkbUnknown) after merge. Cause: Schema parity was not validated; the branch was created from a different layer definition. Fix: Drop the merged layer, recreate it using ogr2ogr from a known-good source, then re-apply the delta.

• Symptom: PRAGMA integrity_check; returns wrong # of entries in index. Cause: The rtree spatial index was not rebuilt after the atomic delta apply. Fix: Rebuild the index via ogr2ogr -lco SPATIAL_INDEX=YES or execute DROP TABLE gpkg_<layer>_geom; CREATE VIRTUAL TABLE ... manually.

• Symptom: rowid-based queries return mismatched features after VACUUM. Cause: VACUUM reassigns rowid values; code relied on rowid instead of ogc_fid. Fix: Replace all rowid-based lookups with stable ogc_fid or UUID column references throughout the pipeline.

Related

• Feature Branching for GIS Development Teams — parent: branching conventions, team workflow, and toolchain overview
• Automated Conflict Detection in Merge Requests — how to surface geometry and attribute conflicts before merge
• Resolving Topology Errors During Branch Merges — step-by-step topology repair for post-merge artifacts
• Spatial Diff Algorithms for Polygon Data — the delta-computation layer that feeds GeoPackage merge operations