Load data into PostGIS with GDAL: staging and validation before publishing

A repeatable load preserves the received file, records its characteristics and publishes only after validation. A staging schema lets you inspect an import without changing tables used by consumers. Use a laboratory database and public or synthetic data; these commands create records.

Inspect the input

Record layer name, record count, CRS, field types, encoding and size. A column named area does not explain its units. An apparently numeric identifier may need text storage to retain leading zeros. Decide these details before loading.

ogrinfo -ro -so -al input.gpkg
ogr2ogr -f PostgreSQL 'PG:service=gis_lab' input.gpkg points -nln staging.points_import

The gis_lab connection must point exclusively to the test database. The staging schema must exist and the user must be allowed to create the new table. Do not add -overwrite to solve a naming error: choose a new identifier and inspect the previous load.

Check geometry

With a geom column and code key, these queries detect common defects. If GDAL generated another geometry-column name, substitute it after inspecting the table.

SELECT count(*) AS rows,
       count(*) FILTER (WHERE geom IS NULL) AS missing_geometry,
       count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalid_geometry
FROM staging.points_import;
SELECT ST_SRID(geom), GeometryType(geom), count(*)
FROM staging.points_import GROUP BY 1, 2;
SELECT code, count(*) FROM staging.points_import
GROUP BY code HAVING count(*) > 1;

Expected results depend on the contract: if keys must be unique, the final query should return zero rows. Matching counts are insufficient; compare aggregates, dates and samples selected by key. Confirm that assigning a CRS code was not mistaken for transforming coordinates.

Controlled publication

Design the operational table separately with constraints and permissions. Insert or update from staging transactionally using business keys, without accidentally changing external identifiers. Validate references before committing. For large loads, measure locking and duration on a representative copy.

Keep a rejected-row report with enough information for correction while avoiding unnecessary personal-data duplication. If a required field disappears or changes type, stop publication and seek clarification from the producer. Do not silently convert malformed dates to nulls.

Repeatability test

Load the same file twice into independent staging tables. An idempotent publication design should not duplicate operational rows. Also test a duplicate key, an empty file and invalid geometry. Operators must distinguish a valid delivery with no changes from an incomplete import; this prevents automation from accidentally removing information.

Treat a load as a controlled data change

Define the source snapshot, destination schema and expected feature count before importing. For a repeatable exercise, use the synthetic lab data. Keep its published expected counts separate from assumptions about an operational source. Record the file checksum or an equivalent immutable version so that two analysts can establish whether they loaded the same input.

Use a staging schema without public services pointing to it. The loader account needs enough permissions to create or populate the intended objects; it does not need ownership of unrelated operational schemas. Store connection information in a PostgreSQL service file and password handling outside command history.

Inspect first, load second

Run the installed tool's version and schema inspection before choosing conversion options:

ogrinfo --version
ogrinfo -ro -so -al incoming.gpkg

Inspect field types, identifiers, geometry dimensions, CRS and layer names. -a_srs assigns a description; -t_srs transforms coordinates. Using assignment to “fix” coordinates that need transformation changes their interpretation without moving the numbers correctly. Likewise, coercing every geometry to a single type can conceal multipart or dimensional information the application needs.

Make each conversion explicit. For a known polygon source, promoting to a multipart target can be reasonable, but test the resulting type and dimensions. Do not add -skipfailures to a first migration run just to obtain a successful exit. Rejected rows are part of the result and require a decision.

Reconcile the staging table

The following checks assume a staging table named staging.assets with business key asset_id and geometry column geom; adapt names to the inspected schema, not by guessing.

SELECT count(*) AS rows,
       count(DISTINCT asset_id) AS distinct_ids,
       count(*) FILTER (WHERE asset_id IS NULL) AS missing_ids,
       count(*) FILTER (WHERE geom IS NULL) AS missing_geometry
FROM staging.assets;

SELECT ST_SRID(geom), GeometryType(geom), count(*)
FROM staging.assets
GROUP BY ST_SRID(geom), GeometryType(geom);

SELECT asset_id, ST_IsValidReason(geom)
FROM staging.assets
WHERE geom IS NOT NULL AND NOT ST_IsValid(geom);

A matching count is necessary but insufficient: duplicates can compensate for missing features. Compare source and target key sets, then inspect values for identifiers with leading zeros, accented text, nulls, dates near midnight and maximum numeric precision. If the source includes time zones, decide how the target preserves them.

Publish only after checking the consumer

Load the staged layer in QGIS with the expected unique key and CRS. Run a representative service query if the destination will be published. Validate a date, category label and multipart feature through the actual consuming application. A correct database row can still be misinterpreted by a client.

For a live replacement, design publication around dependencies. Dropping and recreating an operational table can break views, privileges and identifiers. Prefer a reviewed transaction or a versioned publication strategy that preserves the intended contract. Test it first with dependent views and a read-only consumer in a disposable environment.

Make retries predictable

Decide whether a job creates a new batch, replaces a bounded batch or updates by stable key. A plain append is not safe to retry when a network interruption leaves uncertainty about committed rows. Reconcile the batch identifier before retrying and reject duplicates through the intended constraints. Keep input, command parameters, counts and rejection reasons together; that is enough to investigate a failed load without logging connection secrets.

Reference: GDAL ogr2ogr options.

Sources and documentation

• PostGIS reference
• PostgreSQL documentation
• ogr2ogr reference

Next step

Continue in the Open GIS collection. For a specific project, use the total-cost calculator and request an assessment.

Related reading

• PostGIS for cadastre: a minimal model, geometry and quality controls
• PostGIS spatial indexes: measure before optimizing