Best Practices for Branching GeoPackage Projects

Branching GeoPackage projects requires treating the .gpkg file as a versioned SQLite database rather than a monolithic binary. The most reliable approach uses a copy-on-write strategy where each branch exists as an isolated file, synchronized via a lightweight manifest or Git LFS. Avoid in-place branching; instead, use Python and GDAL workflows to export feature subsets, enforce strict schema parity, and merge changes through primary-key-aligned diff/patch operations. This methodology aligns with established Feature Branching for GIS Development Teams patterns while respecting the OGC GeoPackage specification and SQLite transactional boundaries.

Why GeoPackage Branching Differs from Code Repos

GeoPackage is built on SQLite, which lacks native distributed branching or three-way merge capabilities. Unlike text-based repositories, you cannot diff a .gpkg at the byte level and expect meaningful spatial reconciliation. Spatial indexes (rtree), geometry validation triggers, and CRS metadata complicate direct file manipulation. Concurrent writes to a single file risk index corruption, partial transactions, or trigger deadlocks.

SQLite’s default Write-Ahead Logging (WAL) mode further complicates branching. WAL leaves behind -wal and -shm files that contain uncommitted transactions. Copying the .gpkg without first checkpointing the database will produce an inconsistent snapshot. For a complete technical breakdown of how SQLite handles concurrent writes and transaction isolation, consult the official SQLite WAL documentation. Teams must isolate branch state to separate files, track lineage via external metadata, and enforce schema parity before attempting merges. Understanding these constraints is foundational when evaluating Branching & Merge Strategies for Spatial Datasets for production pipelines.

Core Best Practices

  • Isolate Branches as Separate Files: Never modify a shared .gpkg concurrently. Use deterministic naming (main.gpkg, feat/road-network.gpkg) and store files in Git LFS, S3, or a versioned data lake.
  • Enforce Schema Parity: Validate that column types, geometry types, and CRS codes match across branches before merge attempts. Use ogrinfo -so or pyogrio.read_info() to assert compatibility. Mismatched schemas cause silent geometry truncation or CRS drift.
  • Checkpoint WAL Before Copying: Run PRAGMA wal_checkpoint(TRUNCATE); to flush pending transactions and remove temporary files before creating a branch snapshot.
  • Automate Index Rebuilds: Spatial indexes (gpkg_<table>_geom) do not migrate cleanly across copies. Drop and recreate indexes post-merge using GDAL’s -lco SPATIAL_INDEX=YES or SQL triggers.
  • Track Lineage with Metadata: Populate gpkg_metadata and gpkg_metadata_reference tables per the OGC GeoPackage standard, or maintain an external YAML manifest mapping branch names to commit hashes, authors, and source file paths.
  • Diff by Primary Key, Not Row Order: GeoPackage rowid values shift during VACUUM or DELETE operations. Always use stable identifiers (ogc_fid, UUIDs, or business keys) for delta extraction.

Production-Ready Python Workflow

The following workflow demonstrates how to safely branch, extract deltas, and prepare for reconciliation using pyogrio and sqlite3. It assumes a stable primary key (ogc_fid) exists on all feature tables.

import sqlite3
import shutil
from pathlib import Path
import pyogrio

def checkpoint_and_branch(source_gpkg: Path, branch_gpkg: Path) -> None:
    """Safely copy a GeoPackage after flushing WAL transactions."""
    if not source_gpkg.exists():
        raise FileNotFoundError(f"Source not found: {source_gpkg}")
    
    # 1. Checkpoint WAL to ensure a consistent snapshot
    conn = sqlite3.connect(str(source_gpkg))
    conn.execute("PRAGMA wal_checkpoint(TRUNCATE);")
    conn.close()
    
    # 2. Copy the main database file only
    shutil.copy2(source_gpkg, branch_gpkg)

def extract_delta(base_gpkg: Path, branch_gpkg: Path, table: str, pk: str = "ogc_fid") -> dict:
    """Compare branch against base and return inserted, updated, and deleted feature IDs."""
    base_df = pyogrio.read_dataframe(base_gpkg, layer=table, columns=[pk])
    branch_df = pyogrio.read_dataframe(branch_gpkg, layer=table, columns=[pk])
    
    base_ids = set(base_df[pk])
    branch_ids = set(branch_df[pk])
    
    return {
        "inserted": branch_ids - base_ids,
        "deleted": base_ids - branch_ids,
        "updated": base_ids & branch_ids  # Requires geometry/attribute diff for full validation
    }

Safe Merge & Reconciliation

Merging spatial data requires deterministic conflict resolution. Because GeoPackage lacks native merge semantics, you must apply changes sequentially using primary-key-aligned operations.

  1. Apply Inserts First: Use INSERT OR IGNORE to add new features from the branch to the main file. This prevents duplicate key violations.
  2. Handle Updates: For overlapping primary keys, export the branch geometries and attributes, then run UPDATE statements on the main database. Validate topology changes using ST_IsValid() before committing.
  3. Process Deletes: Remove features marked as deleted in the branch using DELETE FROM <table> WHERE <pk> IN (...).
  4. Rebuild Indexes & Vacuum: After applying all deltas, run VACUUM to reclaim space and reorganize pages. Recreate spatial indexes immediately afterward to restore query performance.

Always wrap merge operations in explicit transactions (BEGIN; ... COMMIT;) to guarantee atomicity. If a merge fails mid-process, the database rolls back cleanly without corrupting spatial relationships. For complex attribute conflicts, implement a deterministic tie-breaker (e.g., updated_at timestamp or branch priority flag) before executing the final COMMIT.

When to Avoid File-Based Branching

File-level branching works well for isolated feature development, but it scales poorly for high-frequency edits, large raster datasets, or multi-team concurrent workflows. In those cases, consider migrating to a spatially enabled relational database (PostGIS, SpatiaLite) or adopting a cloud-native format like FlatGeobuf or Delta Lake. These systems provide row-level versioning, concurrent locking, and native diff capabilities that bypass the manual reconciliation steps required for .gpkg files.

By treating GeoPackage as a portable SQLite container rather than a version-controlled artifact, teams can maintain data integrity while leveraging standard Git workflows for metadata and code.