Loading data into PostGIS from the Command Line¶

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 of 5432, but substitute your own PostGIS port if different with the -p option.

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"

 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.

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

#!/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