Importing shape files using shp2pgsql

ESRI shapefile (SHP) is still the most common exchange format for sharing GIS data. The format itself is made of a few files such as SHP, SHX, DBF, andPRJ, where the first three are the required files and the file with projection information is not obligatory.

The standard PostGIS tool for loading shapefiles is shp2pgsql - you will find it in the bin folder of your postgres installation. shp2pgsql is a command-line utility that can either extract the shapefile data into SQL or pipe the output directly into psql (we'll see both approaches). shp2pgsql also has a GUI version that can be accessed directly in PgAdmin.

In this example, we'll use some NaturalEarth shapefiles we downloaded earlier. We will import the coastlines shapefile using the CMD version of shp2pgsql and then we'll add land masses using the GUI version.

shp2pgsql in cmd

shp2pgsql has an extensive list of parameters that can be accessed by simply typing shp2pgsql in the cmd. We will not use all the options but rather explain the most common ones.

The basic usage of the utility is as follows:

shp2pgsql [<options>] <shapefile> [[<schema>.]<table>]

For example:

shp2pgsql -s 4326 ne_110m_coastline data_import.ne_coastline

Basically you specify what shapefile you want to import and where. If you executed the  earlier command, you would just see the shp2pgsql plain SQL output logged to the console, similar to this:

...
INSERT INTO "data_import"."ne_coastline" ("scalerank","featurecla",geom) VALUES ('3','Country','0105000020E6100000010000000102000000060000006666666666A65AC06766666666665240713D0AD7A3505AC0295C8FC2F56852400000000000205AC07B14AE47E15A5240B91E85EB51585AC0713D0AD7A33052405C8FC2F528BC5AC03E0AD7A3705D52406666666666A65AC06766666666665240');
    COMMIT;
    ANALYZE "data_import"."ne_coastline";

So basically, we need to do something with the utility output in order to make use of it. Let's save the output to an SQL file and let psql read it first:

shp2pgsql -s 4326 ne_110m_coastline data_import.ne_coastline > ne_110m_coastline.sql
psql -h localhost -p 5434 -U postgres -d mastering_postgis -f ne_110m_coastline.sql

You should see a similar output:

    SET
    SET
    BEGIN
    CREATE TABLE
    ALTER TABLE

                            addgeometrycolumn
  ---------------------------------------------------------------------
  data_import.ne_coastline.geom SRID:4326 TYPE:MULTILINESTRING DIMS:2
    (1 row)

    INSERT 0 1
    ...
    INSERT 0 1
    COMMIT
    ANALYZE

I suggest you have a look at the generated SQL so you get an idea of what is actually happening behind the scenes.

Now let's pipe the shp2pgsql output directly to psql:

shp2pgsql -s 4326 ne_110m_coastline data_import.ne_coastline | psql -h localhost -p 5434 -U postgres -d mastering_postgis

The cmd output will be exactly the same as the one we have already seen when reading data from the SQL file.

Note

You will have to drop the data_import.ne_coastline table before importing the data again; otherwise the command in its current shape will fail.

There are some shp2pgsql options that are worth remembering:

• -s SRID: Specifies the shp data projection identifier. When used in the following form: -s SRID1:SRID2 makes the shp2pgsql apply a coordinate system transformation, so projection of the data changes is required.
• -p: Turns on the 'prepare' mode - only a table definition is output.
• -d: Drops and recreates a table.
• -a: Appends data to the existing table, provided its schema is exactly the same as the schema of the incoming data.
• -g: Allows specifying of the geometry column name; the default is geom (or geog if you decide to use geography with the -G param).
• -m <filename>: Specifies a file name that contains column mapping for the DBF file. This way, you can remap dbf column names to your preference.
• -n: Only imports DBF and no spatial data.

Importing data with SRID transformation: -s SRID1:SRID2.

The shp2pgsql GUI version

shp2pgsql also has a GUI version. In order to use it, when in PgAdmin, simply choose Plugins | PostGIS Shapefile and DBF loader 2.2 (the version may vary); the following import wizard will be displayed:

Note

In pgAdmin 4, accessing the shapefile loader GUI may not be so obvious. To trigger the tool, try typing shp2pgsql-gui in the shell/command line.

Similar to the cmd version of the utility, you can specify the schema, and you should specify the SRID.

The nice thing about the GUI version of shp2pgsql is that it lets one import multiple files at once.

In the options dialogue, you can specify data encoding, decide whether or not you would like to create a spatial index after importing, choose geography over geometry, and so on: