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.

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.