How to Configure DVC for PostGIS and GeoJSON

Configure DVC to version PostGIS spatial tables and GeoJSON files by building reproducible export pipelines that serialize database state into tracked, cloud-backed artifacts — part of the Large File Handling in DVC for GIS workflow.

Concept & Context

DVC does not track live relational databases. It versions files. The architectural consequence for PostGIS teams is that every database table you want to version must be serialized to a disk format — GeoJSON, GeoPackage, or GeoParquet — and that export must be deterministic so DVC’s content-addressable cache can detect genuine changes rather than noise introduced by export-order variability.

For GeoJSON files that already exist on disk (boundary layers, reference datasets, manually curated features), the workflow is straightforward: dvc add hashes the file, writes a .dvc pointer, and moves the binary to the configured remote. For live PostGIS tables, a dvc.yaml stage wraps an export script, recording both the script and the connection parameters as dependencies so the pipeline re-runs exactly when the source changes.

This pattern integrates with pointer synchronization for raster datasets — both approaches store lightweight metadata in Git and push binary payloads to cloud storage — but the PostGIS path introduces a critical extra concern: coordinate reference system (CRS) normalization. ST_AsGeoJSON assumes EPSG:4326 output per RFC 7946, but PostGIS tables stored in projected CRS values such as EPSG:27700 or EPSG:3857 must be explicitly reprojected during export or topology errors will silently corrupt downstream consumers.

Mismatched GDAL/Python wheels are the second leading cause of failures: a GDAL 3.3.x wheel combined with a PostGIS 3.1 backend can drop CRS metadata during gpd.read_postgis without raising an exception. Pin your environment precisely.

Pipeline Data-Flow

The diagram below shows how a PostGIS table moves through the DVC pipeline to become a versioned artifact in remote storage.

DVC PostGIS to GeoJSON pipeline data flow Data flows from PostGIS database through an export script, CRS validation, topology check, and dvc.yaml stage into a GeoJSON file tracked by a .dvc pointer in Git, with the binary pushed to remote object storage. PostGIS table (any CRS) export_postgis.py reproject → EPSG:4326 validate_spatial .py (topology) urban_zones .geojson .dvc pointer (Git-tracked) Remote S3 / GCS / Azure dvc.yaml orchestrates stages

Core Algorithmic Pipeline

The six-step sequence below moves from an empty repository to a fully automated, validated, and remotely backed spatial data pipeline.

Step 1 — Initialize DVC and configure remote storage. DVC stores lightweight .dvc pointer files in Git while pushing actual payloads to the remote. For teams managing multi-gigabyte shapefiles or raster mosaics, review Large File Handling in DVC for GIS for chunking, cache eviction, and concurrent pull/push strategies before choosing your remote backend.

dvc init
dvc remote add -d geodata_remote s3://your-bucket/dvc-geospatial
dvc remote modify geodata_remote credentialpath ~/.aws/credentials
git add .dvc .dvc/config .gitignore
git commit -m "Initialize DVC with S3 remote"

Step 2 — Track static GeoJSON files directly. GeoJSON that already exists on disk — boundary layers, administrative divisions, curated reference data — requires no pipeline. Place the file in a structured directory, run dvc add, and commit the pointer. Because GeoJSON follows RFC 7946, coordinate order and the default CRS (EPSG:4326) are preserved without extra configuration.

mkdir -p data/raw
cp administrative_boundaries.geojson data/raw/
dvc add data/raw/administrative_boundaries.geojson
git add data/raw/administrative_boundaries.geojson.dvc .gitignore
git commit -m "Track initial GeoJSON boundary layer"

Step 3 — Author the PostGIS export script with CRS normalization. The script must be deterministic: identical database state must always produce byte-identical output. Key requirements: read from an environment variable (never a hardcoded connection string), enforce a target CRS, fix invalid geometries before writing, and sort the output by a stable key so feature order cannot vary between runs.

# scripts/export_postgis.py
import os
import geopandas as gpd
from sqlalchemy import create_engine

TARGET_CRS = "EPSG:4326"

def export_table(table_name: str, output_path: str, sort_key: str = "id") -> None:
    db_url = os.getenv("POSTGIS_CONNECTION_STRING")
    if not db_url:
        raise EnvironmentError("POSTGIS_CONNECTION_STRING not set")

    engine = create_engine(db_url)
    # Use ST_AsGeoJSON precision=6 for ~0.1 m accuracy; avoids default precision drift
    query = f"""
        SELECT *, ST_AsGeoJSON(geom, 6)::geometry AS geom
        FROM {table_name}
        ORDER BY {sort_key}
    """
    gdf = gpd.read_postgis(query, con=engine, geom_col="geom")

    # Enforce CRS — never assume PostGIS stored data in the target projection
    if gdf.crs is None:
        gdf = gdf.set_crs(TARGET_CRS)
    elif gdf.crs.to_epsg() != int(TARGET_CRS.split(":")[1]):
        gdf = gdf.to_crs(TARGET_CRS)

    # Repair invalid geometries before writing (buffer(0) is a safe, lossless fix
    # for self-intersections introduced by projection rounding)
    invalid = ~gdf.geometry.is_valid
    if invalid.any():
        print(f"Repairing {invalid.sum()} invalid geometries with buffer(0)")
        gdf.loc[invalid, "geometry"] = gdf.loc[invalid, "geometry"].buffer(0)

    gdf.to_file(output_path, driver="GeoJSON")
    print(f"Exported {len(gdf)} features → {output_path} [{TARGET_CRS}]")

if __name__ == "__main__":
    export_table("urban_zones", "data/raw/urban_zones.geojson")

Step 4 — Define the dvc.yaml pipeline stage. Declaring the script as a dependency ensures DVC re-runs the export when the script itself changes, not just when the database changes. This guards against silent schema drift — if you alter the query or add a column, the stage is invalidated automatically.

# dvc.yaml
stages:
  export_postgis:
    cmd: python scripts/export_postgis.py
    deps:
      - scripts/export_postgis.py
    outs:
      - data/raw/urban_zones.geojson

Execute the pipeline:

export POSTGIS_CONNECTION_STRING="postgresql+psycopg2://user:pass@host:5432/dbname"
dvc repro
git add dvc.yaml data/raw/urban_zones.geojson.dvc .gitignore
git commit -m "Add PostGIS export pipeline for urban zones"

Step 5 — Chain a topology validation stage. Versioning spatial data requires more than file tracking. Adding a validation stage as a downstream dependency of the export means every dvc repro produces only topology-sound outputs. The automated conflict detection workflow at merge time depends on valid geometries being present; catching issues here, before a push, is far cheaper than resolving them during a branch merge.

# dvc.yaml (append to existing stages block)
  validate_geojson:
    cmd: python scripts/validate_spatial.py data/raw/urban_zones.geojson
    deps:
      - data/raw/urban_zones.geojson
      - scripts/validate_spatial.py

A minimal validate_spatial.py that fails the stage on invalid features:

# scripts/validate_spatial.py
import sys
import geopandas as gpd

path = sys.argv[1]
gdf = gpd.read_file(path)
invalid = gdf[~gdf.geometry.is_valid]
if not invalid.empty:
    print(f"FAIL: {len(invalid)} invalid geometries in {path}", file=sys.stderr)
    sys.exit(1)
print(f"PASS: all {len(gdf)} geometries valid in {path}")

Step 6 — Wire into CI/CD. The pipeline runs automatically on every push, ensuring that any change to the export script or database credentials triggers a full re-export and re-validation before merge.

# .github/workflows/spatial-pipeline.yml
name: DVC Spatial Pipeline
on: [push, pull_request]
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: iterative/setup-dvc@v1
      - name: Configure AWS credentials
        uses: aws-actions/configure-aws-credentials@v4
        with:
          aws-access-key-id: $
          aws-secret-access-key: $
          aws-region: us-east-1
      - name: Restore DVC cache
        run: dvc pull --run-cache
      - name: Run pipeline
        env:
          POSTGIS_CONNECTION_STRING: $
        run: dvc repro
      - name: Push outputs
        run: dvc push

Working Implementation

The complete dvc.yaml for a two-table workflow with chained validation shows how the dependency graph ensures correct execution order without manual orchestration:

# dvc.yaml — full two-table pipeline
stages:
  export_urban_zones:
    cmd: python scripts/export_postgis.py urban_zones data/raw/urban_zones.geojson
    deps:
      - scripts/export_postgis.py
    outs:
      - data/raw/urban_zones.geojson

  export_admin_boundaries:
    cmd: python scripts/export_postgis.py admin_boundaries data/raw/admin_boundaries.geojson
    deps:
      - scripts/export_postgis.py
    outs:
      - data/raw/admin_boundaries.geojson

  validate_all:
    cmd: >
      python scripts/validate_spatial.py data/raw/urban_zones.geojson &&
      python scripts/validate_spatial.py data/raw/admin_boundaries.geojson
    deps:
      - data/raw/urban_zones.geojson
      - data/raw/admin_boundaries.geojson
      - scripts/validate_spatial.py

Update export_postgis.py to accept table name and output path as CLI arguments:

if __name__ == "__main__":
    import sys
    if len(sys.argv) != 3:
        print("Usage: export_postgis.py <table_name> <output_path>")
        sys.exit(1)
    export_table(sys.argv[1], sys.argv[2])

Validation & Output Verification

After dvc repro completes, verify outputs before pushing to remote storage.

Row-count assertion — compare expected feature counts against a baseline:

python - <<'EOF'
import geopandas as gpd
gdf = gpd.read_file("data/raw/urban_zones.geojson")
assert len(gdf) > 0, "Empty export"
assert gdf.crs.to_epsg() == 4326, f"Wrong CRS: {gdf.crs}"
assert gdf.geometry.is_valid.all(), "Invalid geometries present"
print(f"OK: {len(gdf)} features, EPSG:4326, all geometries valid")
EOF

Bounding-box sanity check — ensures reprojection did not shift coordinates outside expected extents:

python - <<'EOF'
import geopandas as gpd
gdf = gpd.read_file("data/raw/urban_zones.geojson")
minx, miny, maxx, maxy = gdf.total_bounds
# Adjust these bounds for your region
assert -180 <= minx <= 180 and -90 <= miny <= 90, f"Coordinates outside WGS84 range: {gdf.total_bounds}"
print(f"Bounds OK: {minx:.4f},{miny:.4f} → {maxx:.4f},{maxy:.4f}")
EOF

DVC cache hash comparison — confirm the cached artifact matches what is tracked in Git:

dvc status data/raw/urban_zones.geojson.dvc
# Expected output: "Data and pipelines are up to date."

GDAL/OGR format validation — catch truncated or malformed GeoJSON before the CI push:

ogrinfo -al -so data/raw/urban_zones.geojson
# Look for: Geometry Type, Feature Count, Extent, Layer SRS

Failure Modes

  • Symptom: dvc repro completes but dvc status reports changed outs on every run. Root cause: Non-deterministic feature ordering (PostGIS SELECT without ORDER BY). Fix: Add ORDER BY to the export query using a stable primary key column.

  • Symptom: CRS reported as None after gpd.read_postgis. Root cause: GDAL wheel version mismatch; some 3.3.x builds silently discard the SRID metadata when reading via SQLAlchemy. Fix: Pin GDAL==3.6.* and geopandas>=0.12 in requirements.txt; test with gdf.crs is not None immediately after the read.

  • Symptom: ST_AsGeoJSON output has coordinate values rounded to 5 decimal places (~1 m precision). Root cause: PostGIS default precision parameter. Fix: Pass explicit precision: ST_AsGeoJSON(geom, 6) for six decimal places (~0.1 m), or ST_AsGeoJSON(geom, 9) for sub-millimetre cadastral work.

  • Symptom: dvc push fails with 403 Forbidden in CI but succeeds locally. Root cause: IAM role or service account lacks s3:PutObject permission on the target bucket prefix. Fix: Scope the IAM policy to arn:aws:s3:::your-bucket/dvc-geospatial/* with s3:PutObject, s3:GetObject, and s3:ListBucket.