Geospatial Schema Standardization & Mapping Automation

How to Map INSPIRE Annex III to Local PostgreSQL Schemas

Mapping complex INSPIRE Annex III GML application schemas into production PostgreSQL/PostGIS environments requires deterministic handling of XML inheritance, coordinate precision decay, and schema drift. Government tech teams and Python ETL engineers frequently encounter silent data truncation, broken foreign key references, and CRS misalignment when flattening themes like Hydrography, Transport Networks, and Land Cover. This procedure sits inside INSPIRE Directive Schema Compliance, part of the wider Geospatial Schema Architecture & Standards Mapping discipline: it owns the runnable steps for turning a published Annex III dataset into a conforming, queryable table, while the parent stage owns the conformance policy and reporting contract.

The walkthrough maps 1:1 to the ETL phases this site standardizes on — configure (Step 1, DDL generation from the XSD), execute (Steps 2-3, CRS lock and bulk ingestion), validate, and log (Step 4, CI drift gate and audit routing) — so each step drops into an existing pipeline stage rather than living as a standalone script. It assumes you have already selected the Annex III themes in scope and the target SRID; geometry repair and reprojection details for mixed authority codes are owned by CRS Normalization & Sync, which this page calls into rather than reimplements.

INSPIRE Annex III to PostgreSQL Mapping Pipeline GML and XSD sources are parsed to flatten gml:AbstractFeature inheritance into a flat column registry, used to generate deterministic PostgreSQL DDL. Geometry is locked to EPSG:25832 and rounded to a per-theme precision tolerance. A schema-drift gate then either promotes conforming rows to the production table or routes malformed payloads and missing mandatory attributes to a JSONB quarantine and audit table. GML + XSD Annex III themes Flatten Inheritance gml:AbstractFeature → flat column registry Deterministic DDL NOT NULL · CHECK _audit_nil_reason CRS Lock + Precision → EPSG:25832 ST_ReducePrecision Drift Gate columns · types · CRS pass · ST_IsValid Production Table inspire_annex3_* fail Quarantine + Audit (JSONB) payload · timestamp · error trace

Prerequisites checklist

Confirm the environment before generating any DDL. Missing PROJ grids and an out-of-date PostGIS build are the two most common causes of silently degraded geometry, so verify both explicitly rather than assuming the container shipped them.

Step 1: Flatten GML inheritance and generate deterministic DDL

INSPIRE Annex III relies on gml:AbstractFeature inheritance and appSchema type extensions. Direct ogr2ogr ingestion often fragments tables or drops nullable constraints because the driver infers columns per file rather than per application schema. Pre-parse the XSD to extract a flat column registry, then generate PostgreSQL DDL with explicit guards so the contract is fixed before any row is read.

  • Strip xsi:nil="true" attributes during XML parsing so they do not coerce into empty strings.
  • Map nilReason values to a dedicated _audit_nil_reason column to preserve why a value is absent.
  • Enforce NOT NULL on primary identifiers and lifecycle timestamps.
  • Apply CHECK constraints to prevent temporal paradoxes (endLifespanVersion before beginLifespanVersion).
sql
-- Deterministic base table for Annex III Transport Network (PostGIS >= 3.3)
CREATE TABLE IF NOT EXISTS inspire_annex3_transport_network (
    gml_id TEXT PRIMARY KEY,
    inspire_id TEXT UNIQUE NOT NULL,
    begin_lifespan_version TIMESTAMPTZ NOT NULL,
    end_lifespan_version TIMESTAMPTZ,
    geometry GEOMETRY(MULTILINESTRING, 25832) NOT NULL,
    feature_type VARCHAR(50) NOT NULL,
    _audit_nil_reason TEXT,
    CONSTRAINT chk_lifespan CHECK (begin_lifespan_version <= COALESCE(end_lifespan_version, CURRENT_TIMESTAMP))
);

Handle missing fields by creating a staging schema that mirrors the target table but allows NULL across all columns except gml_id. Run an INSERT ... SELECT with explicit COALESCE fallbacks before promoting data to production. This prevents pipeline aborts when upstream GML publishers omit optional attributes, and it keeps the production contract strict while tolerating real-world source variance.

DDL generation rules:

  • One table per Annex III feature type; never collapse Transport, Hydrography, and Land Cover into a shared geometry column.
  • Constrain the geometry column to a single geometry type and SRID at the type level (GEOMETRY(MULTILINESTRING, 25832)), not via a runtime CHECK.
  • Treat every voidable INSPIRE attribute as nullable in staging and document its production default in the data contract.

Step 2: Lock CRS and enforce coordinate precision

Annex III mandates ETRS89 derivatives. Local deployments frequently default to EPSG:4326, causing metric distortion in spatial joins and unstable ST_DWithin results. Lock the target schema to the correct projected SRID and enforce decimal precision at ingestion so precision decay cannot propagate into spatial indexes. The numeric deviation matrices behind these per-theme grids are standardized in Unit Conversion & Tolerance Thresholds; this step only enforces them.

  • Transport Networks: ≤ 0.01 m tolerance
  • Hydrography: ≤ 0.10 m tolerance
  • Land Cover: ≤ 1.00 m tolerance
Annex III Per-Theme Precision Tolerance Ladder Three Annex III themes mapped to their ST_ReducePrecision grid size in EPSG:25832. Transport Networks use the tightest 0.01 metre grid for centreline accuracy, Hydrography a 0.10 metre grid, and Land Cover a coarse 1.00 metre grid because polygon area dominates over node precision. Tolerance grows as feature geometry becomes more areal and less linear. ST_ReducePrecision grid size — EPSG:25832 tighter snap (more vertices kept) coarser snap Transport Networks linear · centreline accuracy 0.01 m Hydrography linear + areal · network topology 0.10 m Land Cover areal · polygon area dominates 1.00 m
python
# psycopg2-binary >= 2.9, PostGIS >= 3.3
import psycopg2
from psycopg2 import sql

PRECISION_THRESHOLDS = {
    "inspire_annex3_transport_network": 0.01,
    "inspire_annex3_hydrography": 0.10,
    "inspire_annex3_land_cover": 1.00,
}


def enforce_crs_and_precision(conn, table_name: str, target_srid: int = 25832) -> None:
    """Repair, reproject, and precision-reduce geometry in place for one Annex III table."""
    threshold = PRECISION_THRESHOLDS.get(table_name, 0.1)
    stmt = sql.SQL(
        "ALTER TABLE {table} "
        "ALTER COLUMN geometry SET DATA TYPE GEOMETRY(MULTILINESTRING, {srid}) "
        "USING ST_ReducePrecision("
        "    ST_Transform(ST_MakeValid(geometry), {srid}), {tol})"
    ).format(
        table=sql.Identifier(table_name),
        srid=sql.Literal(target_srid),
        tol=sql.Literal(threshold),
    )
    with conn.cursor() as cur:
        cur.execute(stmt)
    conn.commit()

Address CRS mismatches by validating the source GML srsName attribute before transformation. If the source lacks an explicit CRS, default to EPSG:25832 but log a warning to the audit table rather than transforming silently. Run ST_MakeValid before ST_ReducePrecision so self-intersections introduced by the publisher are repaired before snapping. Consult the official PostGIS ST_ReducePrecision documentation for tolerance behaviour across different geometry types.

Step 3: Bulk-ingest with audit routing

Use psycopg2.extras.execute_values or COPY for high-throughput ingestion. Map nilReason explicitly to avoid psycopg2 type coercion errors, and pre-validate every payload against the target XSD so malformed GML is intercepted before it reaches the table rather than after.

  • Pre-validate XML payloads with lxml against the target XSD.
  • Batch inserts in chunks of 5,000 records to manage transaction logs.
  • Commit only after ST_IsValid returns true for all geometries in the batch.
python
# psycopg2-binary >= 2.9, lxml >= 5.0
from psycopg2.extras import execute_values


def bulk_insert_features(conn, table_name: str, records: list) -> None:
    """Insert validated Annex III features; conflicts are skipped, not overwritten."""
    # records format: list of tuples matching table column order
    with conn.cursor() as cur:
        execute_values(
            cur,
            f"INSERT INTO {table_name} "
            "(gml_id, inspire_id, begin_lifespan_version, geometry, _audit_nil_reason) "
            "VALUES %s ON CONFLICT (gml_id) DO NOTHING",
            records,
            page_size=5000,
        )
    conn.commit()

Implement a fallback routing mechanism for malformed GML. When lxml validation fails, serialize the payload to a JSONB audit table and trigger an alert to the data stewardship queue. This prevents silent truncation and ensures full compliance traceability. Refer to the official GDAL GML Driver documentation for advanced XSD parsing configurations and namespace handling.

Step 4: Gate schema drift in CI

Schema drift occurs when upstream data publishers modify XSD definitions without versioning. Embed automated drift checks into your CI/CD pipeline to catch mismatches before deployment, and reserve retry logic for transient connection failures using the Error Handling & Retry Logic patterns rather than swallowing real schema breaks.

  • Compare column counts and data types between staging and production.
  • Validate geometry type consistency (MULTILINESTRING vs LINESTRING).
  • Fail CI builds if mandatory INSPIRE attributes are absent.
python
# ci_drift_check.py — psycopg2-binary >= 2.9
import sys


def validate_schema_drift(conn, table_name: str, expected_columns: list) -> None:
    """Fail the build with exit code 1 when a mandatory column has drifted out."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT column_name, data_type
            FROM information_schema.columns
            WHERE table_name = %s AND table_schema = 'public'
            ORDER BY ordinal_position;
            """,
            (table_name,),
        )
        actual = {row[0]: row[1] for row in cur.fetchall()}

    missing = set(expected_columns) - set(actual.keys())
    if missing:
        print(f"DRIFT DETECTED: Missing columns in {table_name}: {missing}")
        sys.exit(1)
    print("Schema validation passed.")

Route CI failures to a metadata fallback queue. Store rejected records in a schema_drift_quarantine table with the original GML payload, timestamp, and error trace. This preserves data lineage while unblocking downstream analytics, and it keeps quarantine routing aligned with the INSPIRE Directive Schema Compliance reporting standards required during national submissions.

yaml
# .github/workflows/inspire-annex3-drift.yml
name: Annex III Schema Drift Gate
on: [pull_request]
jobs:
  drift:
    runs-on: ubuntu-latest
    services:
      postgis:
        image: postgis/postgis:16-3.4
        env:
          POSTGRES_PASSWORD: ci
        ports: ["5432:5432"]
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.11"
      - name: Install dependencies
        run: pip install "psycopg2-binary>=2.9" "lxml>=5.0"
      - name: Run drift check
        run: python ci_drift_check.py

Verification

Confirm the mapping actually succeeded — do not trust an exit code of 0 alone. Assert the geometry SRID and type, the absence of unprojected coordinates, and a clean quarantine ledger directly in SQL:

sql
-- 1. Geometry is locked to the mandated projected SRID, not 4326.
SELECT DISTINCT ST_SRID(geometry) AS srid, GeometryType(geometry) AS gtype
FROM inspire_annex3_transport_network;
--  expected: srid = 25832, gtype = 'MULTILINESTRING'

-- 2. No invalid geometry survived precision reduction.
SELECT count(*) AS invalid_geoms
FROM inspire_annex3_transport_network
WHERE NOT ST_IsValid(geometry);
--  expected: 0

-- 3. Every mandatory lifecycle field is populated.
SELECT count(*) AS missing_mandatory
FROM inspire_annex3_transport_network
WHERE inspire_id IS NULL OR begin_lifespan_version IS NULL;
--  expected: 0

A healthy ingestion run logs no lxml validation errors and leaves schema_drift_quarantine empty. On the CLI, ogrinfo -so source.gml | grep "SRS WKT" should report an ETRS89-based CRS, and the CI drift job should print Schema validation passed. rather than DRIFT DETECTED.

Troubleshooting

Symptom Likely cause Fix
ogr2ogr splits one feature type across several tables The GML driver inferred columns per file instead of from the application schema Supply the Annex III XSD to the driver (or a cached .gfs) and generate DDL from the flat registry in Step 1 before loading.
ST_ReducePrecision raises GEOS exception: IllegalArgument Geometry is self-intersecting and was snapped before repair Wrap the geometry in ST_MakeValid(...) inside the USING clause, as in Step 2, before ST_ReducePrecision.
Coordinates land in the wrong hemisphere or are swapped srsName axis order ignored; GML parsed as lon/lat when authority is lat/lon Set OGR_GML_INVERT_AXIS_ORDER_IF_LAT_LONG consistently and verify srsName before transforming; re-run the SRID assertion.
INSERT aborts with null value in column "inspire_id" A voidable attribute arrived as xsi:nil, coerced to NULL on a NOT NULL column Load into the nullable staging table first, then INSERT ... SELECT with COALESCE and a _audit_nil_reason value (Step 1).
CI drift gate passes but production rows are missing Records were silently dropped by ON CONFLICT (gml_id) DO NOTHING on a re-run with reused identifiers Confirm gml_id uniqueness upstream; route duplicate gml_id payloads to schema_drift_quarantine instead of discarding them.