Loading data into PostGIS from the Command Line

PostGIS includes the shp2pgsql tool for converting a single or multiple shapefiles into database tables.

Note

This section uses the command line utility shp2pgsql and optionally the graphical utility pgAdmin. shp2pgsql is included with the Boundless Server PostGIS package. pgAdmin is provided as part of Boundless Desktop.

Warning

The latest versions of the Ubuntu PostGIS + PostgreSQL package does not include shp2pgsql. If you are using Ubuntu and get the error:

The program 'shp2pgsql' is currently not installed.
To run 'shp2pgsql' please ask your administrator to install the package 'postgis'

Then you should install it using:

sudo apt-get install postgis --no-install-recommends

Note

If you would prefer to use a graphical application to load shapefiles into PostGIS, Boundless Desktop includes QGIS, which can be used for this purpose.

How It Works

shp2pgsql converts a shapefile into a series of SQL commands that can be loaded into a database–it does not perform the actual loading. The output of this command may be captured in a SQL file, or piped directly to the psql command, which will execute the commands against a target database.

Preparation

  1. Select the shapefile you wish to load—you will need all the files: .shp, .shx, and .dbf and so on.
  2. Identify the SRID (“projection”) of your data. If available, this information is easily accessed via the layer metadata in GeoServer. If the projection is unknown, use a service like prj2epsg.org to upload and convert the shapefile’s .prj file to a SRID code.
  3. Either identify the target database where you would like to load the data, or create a new database.

Loading data

  1. Open a terminal or command line window.

    Note

    If the path to the shp2pgsql and psql commands haven’t been included in your PATH system variable, you may wish to add them now. Please consult your operating system help for information on how to change the PATH variable.

  2. Confirm PostGIS is responding to requests by executing the following psql query:

    psql -U postgres -d <DBNAME> -c "SELECT postgis_version()"
    
    Where ``<DBNAME>`` is replaced by the database name, for example opengeo
    
                postgis_version
    ---------------------------------------
     2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
    

    Note

    These examples use the default PostGIS port of 5432. If your PostGIS port is different, use the -p option to specify it.

  3. Run the shp2pgsql command and pipe the output into the psql command to load the shapefile into the database in one step. The recommended syntax is:

    shp2pgsql -I -s <SRID> <PATH/TO/SHAPEFILE> <SCHEMA>.<DBTABLE> | psql -U postgres -d <DBNAME>
    

    The command parameters are:

    • <SRID>—Spatial reference identifier
    • <PATH/TO/SHAPEFILE>—Full path to the shapefile (such as C:\MyData\roads\roads.shp)
    • <SCHEMA>—Target schema where the new table will be created
    • <DBTABLE>—New database table to be created (usually the same name as the source shapefile)
    • <DATABASE>—Target database where the table will be created
    shp2pgsql -I -s 4269 C:\MyData\roads\roads.shp roads | psql -U postgres -d <DBNAME>
    

    The -I option will create a spatial index after the table is created. This is strongly recommended for improved performance. For more information about shp2pgsql command options, please refer to the Using the Loader section of the PostGIS Documentation.

  4. If you want to capture the SQL commands, pipe the output to a file:

    shp2pgsql -I -s <SRID> <PATH/TO/SHAPEFILE> <DBTABLE> > SHAPEFILE.sql
    

    The file can be loaded into the database later by executing the following:

    psql -U postgres -d <DBNAME> -f SHAPEFILE.sql
    

The shapefile has now been imported as a table in your PostGIS database and the last line in your console should say COMMIT. You can verify this by either using pgAdmin to view the list of tables, or by executing the following query at the command line:

psql -U <USERNAME> -d <DBNAME> -c "\d"

Note

The specific command parameters will depend on your local configuration.

 Schema |         Name         |   Type   |  Owner
--------+----------------------+----------+----------
 public | bc_2m_border         | table    | postgres
 public | bc_2m_border_gid_seq | sequence | postgres
 public | geometry_columns     | view     | postgres
 public | spatial_ref_sys      | table    | postgres

Batch loading

Although it is feasible to run the shp2pgsql command as many times as required, it may be more efficient to create a batch file to load a number of shapefiles.

Windows Command (Batch)

Note

This script assumes all the files have the same projection.

Create a batch file, for example loadfiles.cmd, in the same directory as the shapefiles to be loaded. Add the following commands and provide the missing parameters:

for %%f in (*.shp) do shp2pgsql -I -s <SRID> %%f %%~nf > %%~nf.sql
for %%f in (*.sql) do psql -d <DATABASE> -f %%f

Run this batch file to load all the selected shapefiles into the database.

Note

If you are running the Boundless Server virtual machine on Windows, connect to the virtual machine and follow the Bash instructions.

Bash

Note

This script also assumes all the files have the same projection.

Create a shell script file, for example loadfiles.sh, in the same directory as the shapefiles to be loaded. Add the following commands and provide the missing parameters:

#!/bin/bash

for f in *.shp
do
    shp2pgsql -I -s <SRID> $f `basename $f .shp` > `basename $f .shp`.sql
done

for f in *.sql
do
    psql -d <DBNAME> -f $f
done