Heritage GIS

Automating CSV to Spatial Table Imports with Python: Resolving Coordinate Drift and Constraint Violations in Heritage Workflows

Field archaeologists and heritage managers routinely generate daily CSV logs from total stations, RTK receivers, and photogrammetry control surveys. Automating their ingestion into a spatial database is a critical operational bottleneck when pipelines fail due to silent CRS mismatches, malformed coordinate strings, or primary key collisions. This guide provides a reproducible Python workflow to diagnose, recover, and validate these imports while maintaining strict compliance with archaeological data standards.

Before implementing any ingestion pipeline, your target schema must align with established Artifact & Feature Spatial Database Design principles. Excavation unit boundaries, stratigraphic contexts, and artifact point geometries require strict spatial indexing, foreign key constraints, and standardized coordinate reference systems. When raw CSVs bypass validation, downstream processes like Automating Artifact Attribute Synchronization fail due to orphaned records, mismatched foreign keys, or attribute drift across seasonal campaigns.

1. Pre-Ingestion Diagnostic Sequence for Silent Failures

When a Python import script throws psycopg2.errors.UniqueViolation, psycopg2.errors.InvalidParameterValue, or silently drops geometries, execute the following diagnostic sequence before modifying the pipeline.

1.1 Coordinate Format & Axis Order Audit

Swapped X/Y columns and unconverted DMS strings are the primary drivers of coordinate drift. Inspect the raw DataFrame:

import pandas as pd
df = pd.read_csv('/srv/heritage/data/raw/daily_survey_20241015.csv', dtype=str)
# Check for non-numeric coordinate columns
print(df[['Easting', 'Northing']].apply(lambda x: pd.to_numeric(x, errors='coerce').isna().sum()))

If isna().sum() > 0, the CSV contains degree symbols, commas, or DMS notation. Convert using pandas string methods before numeric casting. Verify axis order against your project standard: British National Grid (EPSG:27700) expects Easting (X) first, Northing (Y) second. UTM zones (e.g., EPSG:32633) follow the same convention.

1.2 CRS Verification & Projection Drift

Confirm the source CRS matches the database expectation. Mismatched projections cause ST_Intersects failures against PostGIS Schema Design for Excavation Units and invalidate spatial joins.

# Verify file encoding to prevent silent character corruption
file -i /srv/heritage/data/raw/daily_survey_20241015.csv

Expected output: text/csv; charset=utf-8. If charset=iso-8859-1 or windows-1252, decode explicitly during ingestion: pd.read_csv(..., encoding='cp1252').

1.3 Constraint & Primary Key Collision Scan

Hidden whitespace and non-breaking spaces (\xa0) in artifact IDs trigger UNIQUE constraint violations during batch inserts.

# Detect invisible characters in primary key fields
has_whitespace = df['Artifact_ID'].str.contains(r'[\s\xa0]', regex=True).any()
if has_whitespace:
    df['Artifact_ID'] = df['Artifact_ID'].str.strip().str.replace(r'[\s\xa0]+', '', regex=True)

1.4 Geometry Null & Topology Validation

After initial GeoDataFrame creation, verify geometry integrity:

import geopandas as gpd
gdf = gpd.GeoDataFrame(df, geometry=gpd.points_from_xy(df['Easting'], df['Northing']), crs='EPSG:27700')
null_geom_count = gdf['geometry'].isna().sum()
invalid_geom_count = (~gdf['geometry'].is_valid).sum()

Any null_geom_count > 0 indicates malformed coordinate parsing or missing elevation/depth fields required for 3D heritage modeling.

2. Transactional Pipeline Architecture & Fallback Routing

Fast recovery requires a staging-table architecture with explicit transaction boundaries. Configure your environment with geopandas>=0.14.0, psycopg2-binary>=2.9.9, and shapely>=2.0.0. The pipeline must route valid records to the production table while isolating failures in a quarantine table.

import psycopg2
from psycopg2.extras import execute_values

def ingest_to_postgis(gdf, conn_str, staging_table='staging_survey_points', target_table='artifact_locations'):
    conn = psycopg2.connect(conn_str)
    cur = conn.cursor()
    
    try:
        # 1. Clear staging table
        cur.execute(f"TRUNCATE TABLE {staging_table};")
        
        # 2. Bulk insert to staging with explicit column mapping
        # Ensure gdf columns match staging schema exactly
        cols = ['artifact_id', 'context_ref', 'easting', 'northing', 'elevation', 'geom']
        records = gdf[cols].values.tolist()
        
        insert_sql = f"""
            INSERT INTO {staging_table} (artifact_id, context_ref, easting, northing, elevation, geom)
            VALUES %s;
        """
        execute_values(cur, insert_sql, records, page_size=500)
        
        # 3. Transactional merge with constraint handling
        merge_sql = f"""
            INSERT INTO {target_table} (artifact_id, context_ref, geom)
            SELECT 
                s.artifact_id, 
                s.context_ref, 
                ST_Transform(ST_SetSRID(s.geom, 27700), 27700)
            FROM {staging_table} s
            ON CONFLICT (artifact_id) DO UPDATE SET
                context_ref = EXCLUDED.context_ref,
                geom = EXCLUDED.geom,
                updated_at = NOW()
            RETURNING artifact_id;
        """
        cur.execute(merge_sql)
        conn.commit()
        print(f"Successfully merged {cur.rowcount} records.")
        
    except Exception as e:
        conn.rollback()
        # Route failed batch to quarantine for manual review
        cur.execute(f"""
            INSERT INTO quarantine_import_errors (error_msg, raw_csv_path, failed_at)
            VALUES (%s, %s, NOW());
        """, (str(e), '/srv/heritage/data/raw/daily_survey_20241015.csv'))
        conn.commit()
        raise RuntimeError(f"Ingestion failed: {e}")
    finally:
        cur.close()
        conn.close()

3. Spatial Tolerance Enforcement & Constraint Resolution

Heritage spatial data requires deterministic tolerance thresholds to prevent micro-drift from breaking spatial relationships. Apply the following tolerances during geometry construction:

  • Planar Tolerance: 0.001 meters (1 mm) for total station/RTK points.
  • Vertical Tolerance: 0.005 meters (5 mm) for stratigraphic depth/elevation.
  • Topology Snapping: Use ST_SnapToGrid to align coordinates to a fixed grid before indexing.
-- Enforce grid snapping and precision reduction in staging
UPDATE staging_survey_points 
SET geom = ST_SetSRID(
    ST_SnapToGrid(geom, 0.001), 
    27700
);

-- Validate spatial integrity before promotion
SELECT artifact_id, ST_IsValid(geom) AS is_valid, ST_NPoints(geom) AS points
FROM staging_survey_points
WHERE NOT ST_IsValid(geom) OR ST_NPoints(geom) = 0;

When ST_Intersects or ST_DWithin queries fail during spatial joins, verify that the target table uses a GiST index on the geometry column: CREATE INDEX idx_artifact_geom ON artifact_locations USING GIST (geom);. This directly supports Spatial Relationship Modeling in Heritage DBs by ensuring deterministic spatial predicates.

4. Downstream Integration & Workflow Continuity

Once the pipeline executes without constraint violations, the imported geometries must integrate seamlessly with broader heritage data infrastructure.

  1. Attribute Synchronization: Ensure foreign keys (context_id, unit_id) reference valid parent records before committing. Broken references will cascade into synchronization failures during Automating Artifact Attribute Synchronization.
  2. Query Performance: Large seasonal campaigns generate millions of points. Implement partitioning by excavation season or stratigraphic phase, and maintain updated table statistics via ANALYZE artifact_locations;. This aligns with Query Optimization for Large Excavation Datasets and prevents sequential scan degradation.
  3. Emergency Protocols: If coordinate drift exceeds 0.05 meters post-import, trigger the Emergency Data Freeze & Recovery Protocols. Halt all write operations, export the affected batch to /srv/heritage/data/quarantine/, and re-run the diagnostic sequence against the original survey instrument logs.
  4. External Validation Standards: Cross-reference transformed geometries against official OGC coordinate transformation matrices and verify CRS definitions using the EPSG Geodetic Parameter Dataset. For Python-level spatial operations, consult the official GeoPandas Documentation and PostGIS Reference to ensure version compatibility.

By enforcing strict diagnostic gates, transactional isolation, and deterministic spatial tolerances, heritage teams can eliminate silent coordinate drift and maintain referential integrity across multi-season excavation campaigns.