The short answer

The reliable way to load a shapefile into PostGIS is ogr2ogr when you want reprojection and scripting, or shp2pgsql when you want an inspectable SQL dump. Either way the three things that decide whether the import is correct are: getting the SRID right (and knowing whether you are assigning or reprojecting it), getting the character encoding right (so accented attribute values survive), and promoting geometries to MULTI so mixed single/multi parts do not fail the load. Everything after that — indexing, validation, automation — is straightforward.

Why the loader choice matters

A shapefile is really a set of files: .shp (geometry), .dbf (attributes), .shx (index), and ideally .prj (CRS as WKT) and .cpg (encoding). PostGIS stores geometry in a typed column with an SRID, backed by PostgreSQL's transactional engine and GiST spatial indexing. The loader's job is to map the shapefile's CRS, schema, and encoding into that model faithfully. Both standard tools do this well; they differ in ergonomics.

Importing with ogr2ogr

ogr2ogr (from GDAL) is the most flexible and the one to reach for in pipelines:

ogr2ogr -f PostgreSQL \
  PG:"host=localhost dbname=gis user=gisuser" \
  parcels.shp \
  -nln parcels \
  -nlt PROMOTE_TO_MULTI \
  -lco GEOMETRY_NAME=geom \
  -lco FID=id \
  -lco SPATIAL_INDEX=GIST \
  -t_srs EPSG:2154

What each part does:

  • -nln parcels — names the target table.
  • -nlt PROMOTE_TO_MULTI — coerces a layer that mixes POLYGON and MULTIPOLYGON into MULTIPOLYGON so the load does not abort on type conflict (the single most common ogr2ogr failure).
  • -lco GEOMETRY_NAME=geom — names the geometry column geom rather than the default wkb_geometry.
  • -lco FID=id — names the primary key column.
  • -lco SPATIAL_INDEX=GIST — builds the spatial index at load time.
  • -t_srs EPSG:2154 — reprojects to Lambert-93 during load. If the data is already in the right CRS and the .prj is correct, omit this. If the .prj is missing or wrong but the coordinates are already correct, use -a_srs EPSG:2154 to assign without moving coordinates, or -s_srs to declare a wrong-labelled source before reprojecting.

Useful extras: -overwrite to replace an existing table, -append to add to one, -progress for feedback, and --config PG_USE_COPY YES to load via COPY for a large speed gain on big files.

Importing with shp2pgsql

shp2pgsql ships with PostGIS and emits SQL, which you pipe to psql:

shp2pgsql -s 2154 -I -D -W LATIN1 \
  parcels.shp public.parcels | psql -d gis

Flags:

  • -s 2154 — sets the SRID. Use -s 4326:2154 to reproject from one to another.
  • -I — creates a GiST spatial index after load.
  • -D — uses the PostgreSQL dump format (faster than INSERT statements).
  • -W LATIN1 — declares the source encoding so accented text converts correctly to the database's UTF-8.
  • -a appends, -d drops and recreates, -p produces schema only.

Because it produces plain SQL, shp2pgsql -p is handy for previewing the exact table definition before committing.

Getting the SRID right

This is where silently-wrong results come from. Two distinct operations:

  • Assign SRID (-a_srs in ogr2ogr; matching -s value in shp2pgsql) — tags the geometry with an SRID without changing coordinates. Correct when the numbers are already in that CRS. In SQL after the fact: UPDATE parcels SET geom = ST_SetSRID(geom, 2154); — but note ST_SetSRID only relabels, it does not move geometry.
  • Reproject (-t_srs in ogr2ogr; -s FROM:TO in shp2pgsql) — recomputes coordinates into a new CRS. In SQL: ST_Transform(geom, 2154). Use when the coordinates are genuinely in another CRS.

Confusing ST_SetSRID (relabel) with ST_Transform (recompute) is the classic PostGIS error that produces data which looks fine in metadata but sits in the wrong place. Always confirm the .prj exists and is correct before trusting an automatic SRID — a missing .prj leaves ogr2ogr loading geometry as SRID 0.

Fixing encoding

Garbled accents mean the shapefile's .dbf encoding did not match the loader's assumption. The .cpg file, if present, names the encoding; if absent, legacy shapefiles are often LATIN1, ISO-8859-1, or a Windows codepage like CP1252. With ogr2ogr, set it via the environment or open option:

PGCLIENTENCODING=UTF8 ogr2ogr ... --config SHAPE_ENCODING "ISO-8859-1" ...

With shp2pgsql, use -W as shown. The target database should be UTF-8; the loader transcodes from the declared source encoding into it.

Validate the import

Never assume success because a table appeared. Check:

-- row count vs the source feature count from ogrinfo -al -so
SELECT count(*) FROM parcels;

-- SRID is what you intended, and consistent
SELECT DISTINCT ST_SRID(geom) FROM parcels;

-- geometry type and validity
SELECT GeometryType(geom), count(*) FROM parcels GROUP BY 1;
SELECT count(*) FROM parcels WHERE NOT ST_IsValid(geom);

-- extent looks right for the region
SELECT ST_Extent(geom) FROM parcels;

Compare the row count against ogrinfo -al -so parcels.shp (the "Feature Count"). Repair invalid geometries with UPDATE parcels SET geom = ST_MakeValid(geom) WHERE NOT ST_IsValid(geom);. Confirm the spatial index exists (\d parcels in psql) and, after a large load, run ANALYZE parcels; so the planner has fresh statistics.

Common pitfalls and why they happen

  • Mixed single/multi geometry abort. Shapefiles allow both in one file; PostGIS columns are typed. -nlt PROMOTE_TO_MULTI fixes it.
  • SRID 0 after load. The .prj was missing, so the CRS was never set. Assign the correct SRID afterwards — but only after verifying the coordinates match it.
  • Using ST_SetSRID when transformation was needed. Relabels without moving, leaving data in the wrong place. Use ST_Transform when coordinates must change.
  • Truncated field names. DBF column names are capped at 10 characters; long attribute names arrive truncated, which can collide. Rename in PostGIS or remap with an OGR SQL clause.
  • Encoding mismatch. Accented text becomes mojibake when the source codepage is not declared.
  • No index, slow queries. Forgetting -I / SPATIAL_INDEX=GIST makes every spatial query a sequential scan.

Bathyl perspective

We load shapefiles through scripted ogr2ogr calls so the SRID, encoding, geometry promotion, and index are explicit and re-runnable, and every load ends with the validation queries above written to a log. A pipeline that asserts row count, SRID, and geometry validity catches the silent failures long before the data reaches a map or an analysis.

Related reading

Sources