Translate PostGIS Schemas to an Esri File Geodatabase Jump to heading#

Delivering a PostGIS dataset to an ArcGIS shop means squeezing a permissive schema through the Esri File Geodatabase’s stricter rules. FileGDB caps field names at 10 characters in the classic driver, rejects reserved words like OBJECTID and SHAPE as user fields, has no native boolean or array type, and represents controlled vocabularies as coded-value domains rather than check constraints. A naive ogr2ogr run truncates assessment_value to assessment, collides two long names into one, and drops your CHECK (zoning IN (...)) constraint entirely. This procedure sits inside Cross-Platform Schema Translation and owns the PostGIS-to-FileGDB path: it derives a deterministic field mapping from the source schema, drives the translation with explicit creation options, and verifies the round-trip so nothing is lost silently.

The walkthrough maps 1:1 to the phases this site standardizes on — configure (Steps 1–2, introspect and build the mapping), execute (Step 3, run ogr2ogr), validate (Step 4, read the target schema back), and log (Step 5, mapping report and CI gate). The governing rule is that every field-name and type transformation is decided up front and recorded, never left to the driver’s default truncation. A translation that renames a column must record why — length limit, reserved word, or collision — so the mapping is auditable and, where the schema is versioned, diffable against the previous release per best practices for spatial data dictionary versioning.

Prerequisites checklist Jump to heading#

Confirm the GDAL build and driver support before translating. The modern OpenFileGDB driver writes File Geodatabases without the proprietary Esri SDK, but only recent GDAL versions support write and coded-value domains.

• GDAL >= 3.8 with a writable OpenFileGDB driver (ogrinfo --formats | grep -i openfilegdb shows (rw)).
• psycopg >= 3.1 for introspecting the PostGIS information_schema and geometry_columns.
• PyYAML >= 6.0 for the type-map and reserved-word manifest.
• Connectivity to the source PostGIS database with read access to the target table and its constraints.
• The source table’s geometry column registered in geometry_columns with a known SRID.
• A writable path for the output .gdb, the field-mapping report, and the round-trip verification log.

Step 1: Introspect the source schema Jump to heading#

Never assume the schema — read it. Query information_schema.columns for names and types and geometry_columns for the geometry type and SRID, so the mapping is derived from the live table. Pull CHECK constraints too; they are the source of the coded-value domains FileGDB will need.

# requires: psycopg >=3.1  (Python 3.10+)
import psycopg


def introspect(conn: psycopg.Connection, schema: str, table: str) -> dict:
    cols = conn.execute(
        """
        SELECT column_name, data_type, character_maximum_length
        FROM information_schema.columns
        WHERE table_schema = %s AND table_name = %s
        ORDER BY ordinal_position
        """,
        (schema, table),
    ).fetchall()

    geom = conn.execute(
        "SELECT f_geometry_column, type, srid FROM geometry_columns "
        "WHERE f_table_schema = %s AND f_table_name = %s",
        (schema, table),
    ).fetchone()

    return {
        "columns": [{"name": c[0], "pg_type": c[1], "length": c[2]} for c in cols],
        "geometry": {"column": geom[0], "type": geom[1], "srid": geom[2]} if geom else None,
    }

Introspection rules:

• Read column order from ordinal_position so the FileGDB field order matches the source.
• Resolve the geometry type and SRID from geometry_columns, not from a sampled feature.
• Capture CHECK constraints separately; they become coded-value domains, not silently dropped rules.
• Fail loudly if the geometry column is unregistered — an unknown SRID makes the target CRS untrustworthy.

Step 2: Build the field mapping Jump to heading#

Resolve each PostGIS type to a FileGDB field type and rename any column that breaks a FileGDB rule. The three transformations — type mapping, 10-character name truncation with collision handling, and reserved-word remapping — are all deterministic and recorded. Collision handling matters: truncating both parcel_number and parcel_notes to parcel_num would fuse two fields, so append a disambiguating suffix and log it.

# requires: python >=3.10, PyYAML >=6.0
import yaml
from pathlib import Path

RESERVED = {"OBJECTID", "SHAPE", "SHAPE_LENGTH", "SHAPE_AREA", "FID"}

# PostGIS type -> FileGDB (OGR) field type; FileGDB has no bool or array.
TYPE_MAP = {
    "integer": "Integer", "bigint": "Integer64", "smallint": "Integer",
    "double precision": "Real", "numeric": "Real",
    "character varying": "String", "text": "String",
    "boolean": "Integer",           # 0/1; FileGDB has no boolean
    "date": "Date", "timestamp without time zone": "DateTime",
}


def build_mapping(schema_info: dict, maxlen: int = 10) -> list[dict]:
    used: set[str] = set()
    mapping = []
    for col in schema_info["columns"]:
        target = col["name"][:maxlen]
        reason = None
        if len(col["name"]) > maxlen:
            reason = "length"
        if target.upper() in RESERVED:
            target, reason = f"{target[:maxlen - 2]}_F", "reserved"
        # Resolve collisions deterministically with a numeric suffix.
        base, n = target, 1
        while target.upper() in used:
            target, n = f"{base[:maxlen - 1]}{n}", n + 1
            reason = reason or "collision"
        used.add(target.upper())
        mapping.append({
            "source": col["name"],
            "target": target,
            "pg_type": col["pg_type"],
            "gdb_type": TYPE_MAP.get(col["pg_type"], "String"),  # unknown → String, flagged
            "reason": reason,
            "lossy": col["pg_type"] not in TYPE_MAP,
        })
    return mapping

Mapping rules:

• Map every PostGIS type explicitly; an unmapped type falls back to String and is flagged lossy.
• Truncate to 10 characters only when needed, and record the length reason on each rename.
• Remap reserved words (SHAPE, OBJECTID) rather than letting the driver reject or rename them.
• Resolve truncation collisions with a deterministic suffix so two long names never fuse into one.

Step 3: Run the ogr2ogr translation Jump to heading#

Drive the translation with ogr2ogr and the OpenFileGDB driver, feeding the renames through an SQL SELECT ... AS clause so the field map is applied exactly as computed. Set the target SRID explicitly and let the driver create the geometry field; coded-value domains from the source CHECK constraints are attached after the layer exists.

# GDAL >= 3.8 — translate one PostGIS table into a File Geodatabase
ogr2ogr \
  -f OpenFileGDB parcels.gdb \
  "PG:host=db user=gis dbname=cadastre" \
  -sql "SELECT parcel_number AS parcel_num, zoning, geom FROM public.parcels" \
  -nln Parcels \
  -a_srs EPSG:3857 \
  -lco GEOMETRY_NAME=SHAPE \
  -lco FID=OBJECTID \
  -nlt MULTIPOLYGON

# requires: python >=3.10 (subprocess wrapper so the field map drives the SQL)
import subprocess


def build_select(mapping: list[dict], geom_col: str, src: str) -> str:
    fields = [f'{m["source"]} AS {m["target"]}' for m in mapping if m["source"] != geom_col]
    fields.append(geom_col)
    return f'SELECT {", ".join(fields)} FROM {src}'


def translate(gdb: str, pg_dsn: str, sql: str, srid: int) -> None:
    subprocess.run(
        ["ogr2ogr", "-f", "OpenFileGDB", gdb, pg_dsn, "-sql", sql,
         "-nln", "Parcels", "-a_srs", f"EPSG:{srid}",
         "-lco", "GEOMETRY_NAME=SHAPE", "-lco", "FID=OBJECTID", "-nlt", "MULTIPOLYGON"],
        check=True,  # non-zero exit raises; the batch never proceeds on a failed translate
    )

Execution rules:

• Apply renames through the -sql SELECT ... AS clause so the computed field map is authoritative.
• Name the geometry SHAPE and the FID OBJECTID via layer-creation options to match Esri conventions.
• Set the SRID with -a_srs; delegate any actual reprojection to CRS Normalization & Sync upstream.
• Run with check=True so a driver error halts the batch instead of yielding a partial .gdb.

Step 4: Verify the round-trip Jump to heading#

Translation is not done until the target schema is read back and compared to the contract. Open the .gdb with the same GDAL and assert field names, types, geometry type, and feature count against the source. A round-trip check is the only way to catch a silent truncation or a dropped field before it reaches the ArcGIS consumer.

# requires: GDAL >=3.8 python bindings  (Python 3.10+)
from osgeo import ogr


def verify(gdb: str, layer: str, mapping: list[dict], expected_count: int) -> list[str]:
    ds = ogr.Open(gdb)
    lyr = ds.GetLayerByName(layer)
    defn = lyr.GetLayerDefn()
    present = {defn.GetFieldDefn(i).GetName() for i in range(defn.GetFieldCount())}

    problems = []
    for m in mapping:
        if m["source"] == "geom":
            continue
        if m["target"] not in present:
            problems.append(f"missing_field: {m['source']} -> {m['target']}")
    if lyr.GetFeatureCount() != expected_count:
        problems.append(f"count_mismatch: {lyr.GetFeatureCount()} != {expected_count}")
    return problems

Verification rules:

• Assert every mapped target field exists in the written FileGDB — a missing one is a silent drop.
• Compare feature counts against the source table so a partial write is caught immediately.
• Confirm the geometry type matches the -nlt promotion (POLYGON vs MULTIPOLYGON).
• Treat any lossy field from Step 2 as requiring an explicit sign-off, not a silent pass.

Step 5: Log the mapping and gate in CI Jump to heading#

Emit the field-mapping report and fail CI on any lossy or unmapped field. The report is the diffable record a versioned data dictionary consumes, and it is the same gate convention the sibling standards clusters use, so one CI pattern covers every target platform.

# .github/workflows/postgis-to-filegdb.yml
name: PostGIS to FileGDB Translation
on: [pull_request]
jobs:
  translate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.11"
      - name: Install GDAL and deps
        run: pip install "gdal>=3.8" "psycopg[binary]>=3.1" "PyYAML>=6.0"
      - name: Translate and verify round-trip
        run: python -m pipeline.translate_gdb public.parcels --out parcels.gdb --fail-on-lossy
      - name: Upload field-mapping report
        if: always()
        uses: actions/upload-artifact@v4
        with:
          name: filegdb-mapping-report
          path: reports/mapping_*.json

CI rules:

• Fail the build on any field flagged lossy unless it carries an explicit reviewed override.
• Emit the mapping report even on failure so a reviewer sees every rename and its reason.
• Pin the type-map and reserved-word manifest commits in the report for reproducibility.
• Diff the report against the previous release so a schema change is a reviewed event, not a surprise.

Verification Jump to heading#

Confirm the translation preserves the schema contract — an exit code of 0 from ogr2ogr does not mean the fields survived. Assert on the written FileGDB and on the recorded rename reasons:

mapping = build_mapping(introspect(conn, "public", "parcels"))

# A long name must be truncated deterministically and its reason recorded.
long = next(m for m in mapping if m["source"] == "assessment_value")
assert long["target"] == "assessment", f"unexpected target {long['target']}"
assert long["reason"] == "length"

problems = verify("parcels.gdb", "Parcels", mapping, expected_count=18422)
assert not problems, f"round-trip mismatch: {problems}"

A healthy run prints no Warning 1: Normalized/laundered field name surprises that were not already in the mapping, and no count_mismatch. On the CLI, ogrinfo -so parcels.gdb Parcels should list SHAPE and OBJECTID plus every mapped field at its computed name, and its feature count should equal SELECT count(*) FROM public.parcels.

Troubleshooting Jump to heading#

Related Jump to heading#

• Cross-Platform Schema Translation — the parent discipline for deterministic schema translation across platform targets
• Best practices for spatial data dictionary versioning — diffing the field-mapping report across schema releases
• CRS Normalization & Sync — resolving and reprojecting the source SRID before translation
• INSPIRE Directive Schema Compliance — the conformant output this translation may need to emit for European targets
• Local Government Data Dictionaries — the canonical field names that seed the PostGIS source schema