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: ${{ secrets.AWS_ACCESS_KEY_ID }}
          aws-secret-access-key: ${{ secrets.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: ${{ secrets.POSTGIS_CONN }}
        run: dvc repro
      - name: Push outputs
        run: dvc push
From an empty repository to a validated, remote-backed export Six steps in order: initialise the repository and remote, write the deterministic export script, declare the export as a pipeline stage, chain a validation stage behind it, reproduce the pipeline, and push the tracked outputs to remote storage. 1 Initialise the repository and remote git init, dvc init, dvc remote add 2 Write a deterministic export fixed column and row ordering 3 Declare the export stage dvc.yaml records deps, params and outs 4 Chain a validation stage geometry validity and CRS on the exported file 5 Reproduce the pipeline dvc repro re-runs only what changed 6 Push tracked outputs dvc push, then commit the pointers Determinism in step two is what makes step five a no-op when nothing really changed.

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.

Why a PostGIS export churns when the data has not changed A grid of three symptoms with cause and fix: a diff on every run comes from an unordered query, a coordinate-precision diff comes from unpinned serialisation settings, and a pipeline that always re-runs comes from declaring the database itself as a dependency. Root cause Fix New hash on every export query has no ORDER BY, so row order drifts sort by primary key in the query itself Diff shows only decimals coordinate precision left to the driver default set COORDINATE_PRECISION explicitly Stage never reports up to date a live database declared as a dependency depend on the script and params; snapshot the database All three produce real hash changes, so no amount of cache tuning will settle them.