Load a .shp file into a postgis enabled database

[bmordan]

Introduction

.shp or shape files are encoded polygons with data that can be added to maps. There are lots of reasons you might want to put that data into a database. This How-To is the list of steps to get a postgres datatable ready and how to import your shape file.

Set up the database

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. For me I'm exporting the following from my .zshrc or .bashrc equivalent file.

export PATH="$PATH:/Applications/Postgres.app/Contents/Versions/latest/bin"

get into psql by running

psql

then

CREATE USER <username> WITH PASSWORD '<password>';
CREATE DATABASE <dbname> OWNER <username>;
\q

now connect to that database as

psql -U <username> -d <dbname>

now extend that database as follows

CREATE EXTENSION postgis;
CREATE SCHEMA <schemaname>;
GRANT USAGE ON SCHEMA staging TO <username>;
GRANT SELECT ON ALL TABLES IN SCHEMA staging TO <username>;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA staging TO <username>;

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

psql -U <username> -d <dbname> -c "SELECT postgis_version()"

Import the shape file

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:

— Spatial reference identifier <PATH/TO/SHAPEFILE> — Full path to the shapefile (such as C:\MyData\roads\roads.shp) — Target schema where the new table will be created — New database table to be created (usually the same name as the source shapefile) — Target database where the table will be created

shp2pgsql -I -s 4269 C:\MyData\roads\roads.shp roads | psql -U postgres -d <DBNAME>

Other options

The -I option will create a spatial index after the table is created. This is strongly recommended for improved performance. 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 <username> -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"