Load a Shapefile into PostGIS Safely with shp2pgsql

You will turn a Shapefile into SQL, inspect that SQL, and load it into a PostGIS table with shp2pgsql. Allow 10 to 20 minutes for a small import, plus time to confirm the coordinate reference system. The examples assume a PostgreSQL database called gis, a writable working directory and a Shapefile called roads.

This guide uses the shp2pgsql supplied by Ubuntu's postgis package, version 3.4.2. The installed program is the authority for the commands below. The local manual page is older and does not list newer options such as -t, -n and -Z.

Before you start

A Shapefile is normally a group of files with the same basename, at least roads.shp, roads.shx and roads.dbf. Give shp2pgsql the basename without the extension. If the data came with a .prj file, identify its EPSG code before importing it. A wrong SRID can make otherwise plausible map data appear in the wrong place.

You need the client utilities and a database user with permission to create or insert into the target table. The conversion itself normally runs as your ordinary user. The database load also normally runs without sudo; use database authentication and PostgreSQL privileges, not root privileges.

1. Check the installed contract and input files

First verify which executable will run and read its local help. This catches a different installation earlier in PATH and shows the options supported on this machine.

$ command -v shp2pgsql
/usr/bin/shp2pgsql
$ shp2pgsql -? | sed -n '1,12p'
RELEASE: 3.4.2 (c19ce56)
USAGE: shp2pgsql [<options>] <shapefile> [[<schema>.]<table>]
$ ls -l roads.shp roads.shx roads.dbf

Expected output is one executable path, a release line, usage text and three readable files. Stop if one companion file is missing. Do not guess a basename from a similarly named archive.

2. Generate SQL without changing the database

Use the default create mode to generate a reviewable SQL file. The table argument may be schema-qualified. Add -s when you know the source SRID; here, 27700 is only an example placeholder for British National Grid data.

$ shp2pgsql -s 27700 roads public.roads > roads.sql
$ sed -n '1,35p' roads.sql
$ grep -E 'CREATE TABLE|AddGeometryColumn|INSERT INTO|SRID' roads.sql | head

The command should create roads.sql and print loader progress or warnings on standard error. The SQL should contain table creation and row-loading statements. Review the table name, geometry column, SRID and any unexpected identifiers before loading. If you do not know the source SRID, omit -s rather than assigning a plausible-looking one. The loader's default SRID is 0, which means unknown, not automatically detected.

3. Load a new table

Run the reviewed file with psql. Replace the database and connection options with your own; do not put a password in a shell command that could be saved in history.

$ psql -d gis -f roads.sql
BEGIN
CREATE TABLE
INSERT 0 1
COMMIT

Exact row counts vary. Verify the table and geometry metadata from PostgreSQL:

$ psql -d gis -c 'SELECT count(*) AS rows, ST_SRID(geom) AS srid FROM public.roads GROUP BY ST_SRID(geom);'
 rows | srid
------+------
  128 | 27700
(1 row)

Your geometry column may not be called geom. The generated SQL tells you its name; use that name in the verification query. A zero-row result can mean the input is empty, while a missing-table error means the load did not complete.

4. Choose the mode deliberately

-c creates and populates a new table and is the default. -p creates only table-definition SQL, useful when a DBA must review or run the schema separately. -a appends rows to an existing table, but the source attributes and data types must match that table. It is not a general merge operation.

$ shp2pgsql -p -s 27700 roads public.roads > roads-schema.sql
$ shp2pgsql -a -s 27700 roads public.roads > roads-append.sql
$ psql -d gis -f roads-append.sql

5. Handle larger or awkward imports

For a large dataset, -D uses PostgreSQL dump format and is usually faster than individual insert statements. It cannot be combined with -e, which executes statements individually without a transaction. The individual mode can leave good rows loaded when some geometries fail, so use it only when partial loading is acceptable and you will inspect the result.

$ shp2pgsql -D -s 27700 roads public.roads > roads.dump.sql
$ psql -d gis -f roads.dump.sql

Use -I to generate a GiST spatial index during creation. This adds work to the import but can make later spatial queries usable. Use -g geometry_column when appending to a table whose geometry column is not the loader's default. Use -G only for longitude and latitude data intended for PostGIS geography, and note that the installed help restricts geography to suitable coordinates or a reprojection with -s.

6. Diagnose the common failures

• Cannot open a file: check the basename and companion extensions with ls. Passing roads.shp instead of roads makes the loader look for roads.shp.shp on this version.
• Authentication or permission failure: retry with the correct psql connection options and a database role granted access to the schema. Do not switch to sudo as a database fix.
• Wrong map location: stop using the table until the source and destination CRS are established. An SRID label does not transform coordinates; the from:to form of -s is the reprojection form, and it cannot be used with -D.
• Bad geometry: keep the original files and the generated SQL. Inspect the error and decide whether to correct the source data or deliberately use an import mode that permits partial results.