Comparing GeoPackage and PostGIS for Field-to-Archive Handoff #

The single most consequential architecture decision in a mobile heritage workflow is which backend holds the data at each stage. Reach for a server database in the trench and you have nothing when the signal drops; keep everything in a single file and you cannot let three recorders and a specialist edit concurrently once the data reaches the office. GeoPackage and PostGIS are not competitors so much as the two ends of the same lifecycle, and the skill is knowing exactly where the handoff sits. This guide lays out the trade-offs and shows the ogr2ogr conversions that move data across the boundary in both directions. It is part of Participatory GIS and Mobile Field Sync.

Context & When to Use #

GeoPackage is an OGC-standard SQLite file: one portable artefact, no server, opens on any tablet, and travels as an email attachment or a USB copy. That portability is exactly why it is the field format — and exactly why it fails as a shared archive. SQLite takes a single file-level write lock, so two simultaneous writers are impossible; the second gets database is locked. PostGIS, by contrast, is built for concurrency, row-level locking, and MVCC, which is what a multi-user archive with a live web map needs, at the cost of a server to run and administer. The rule of thumb: file where it is offline and single-user, server where it is concurrent and shared. The heavier merge and provenance logic that lives on the PostGIS side is covered in merging field audit trails into the master database, and the offline packaging that produces the field GeoPackage in the first place is in packaging offline field projects with QFieldSync.

Implementation #

Moving field data into the archive is a load; re-cutting a fresh field package from the archive is the reverse. Both are single ogr2ogr invocations.

# GDAL/OGR 3.8.4 (system binary)

# GeoPackage --> PostGIS: load a device layer into the archive
# PostGIS 3.4 target; -t_srs guarantees the archive SRID even if the file drifted
ogr2ogr -f PostgreSQL \
  "PG:host=db.internal dbname=heritage user=archivist" \
  /data/sync/tablet_07/survey.gpkg finds \
  -nln finds_incoming \
  -t_srs EPSG:27700 \            # substitute your site's EPSG
  -lco GEOMETRY_NAME=geom \
  -lco FID=fid \
  -nlt POINT \
  --config PG_USE_COPY YES       # bulk COPY, far faster than row-by-row INSERT

# PostGIS --> GeoPackage: re-package an area of interest for the next field session
# Only the current trench block, so the device file stays small
ogr2ogr -f GPKG \
  /data/field_packages/trench_B.gpkg \
  "PG:host=db.internal dbname=heritage user=archivist" \
  -sql "SELECT uuid, site_id, feature_type, geom FROM finds_master \
        WHERE site_id = 'AVN-2026' AND ST_Intersects(geom, \
        ST_MakeEnvelope(451200,213400,451260,213470,27700))" \
  -nln finds \
  -a_srs EPSG:27700              # substitute your site's EPSG

For a repeatable check that a round trip preserved feature counts and geometry, inspect both ends with ogrinfo -so and compare the reported Feature Count and Extent rather than trusting the load silently succeeded.

Verification #

Confirm the SRID survived the handoff and both stores agree on feature count:

# GDAL/OGR 3.8.4 — field file side
ogrinfo -so /data/field_packages/trench_B.gpkg finds | grep -E "SRS|Feature Count|Extent"

-- PostgreSQL 16 / PostGIS 3.4 — archive side
SELECT ST_SRID(geom) AS srid, count(*) AS n
FROM finds_master
WHERE site_id = 'AVN-2026'
GROUP BY ST_SRID(geom);

Expected: the ogrinfo output reports SRS: ... 27700 and a Feature Count equal to the archive query’s n, and the SQL returns a single row with srid = 27700. Two SRID rows in the archive result mean mixed projections crept in — reproject the offending features before the next re-package.

Common Errors & Fixes #

• sqlite3_step() failed: database is locked (5) — a second process (often a still-open QGIS session or a sync client) holds the GeoPackage write lock. Close every reader/writer; GeoPackage genuinely cannot support concurrent writes, which is the signal you have outgrown the file format and should move that stage to PostGIS.
• ERROR 1: Cannot find OGRSpatialReference ... could not parse SRS on load — the source GeoPackage geometry column has SRID 0 or a custom local grid with no EPSG. Assign the correct code with -a_srs or reproject with -t_srs; never let a 0 SRID enter the archive.
• ogr2ogr load is extremely slow — the default row-by-row insert path is in use. Add --config PG_USE_COPY YES to switch to PostgreSQL COPY, and drop non-essential indexes before a large bulk load, recreating them afterward.

Related #

• Participatory GIS and Mobile Field Sync — the section overview and full field-to-archive loop.
• Merging Field Audit Trails into the Master Database — the provenance-stamped upsert on the PostGIS side.
• Packaging Offline Field Projects with QFieldSync — producing the offline GeoPackage the field stage relies on.
• Choosing QGIS or ArcGIS Pro for Heritage Compliance Workflows — the desktop platform decision that sits above these backend choices.

Part of Heritage GIS Architecture & Fundamentals.