Loading SHP Files

This post will cover loading shp files to your PostGIS database.

We’ll look at loading files via our shp2pgsql GUI in your control panel, as well as via command line (SSH) and via your desktop.

In this demonstration, we will be using the states_basic21.zip file from the ESRI ArcGIS site, which you can download at:

https://www.arcgis.com/home/item.html?id=f7f805eb65eb4ab787a0a3e1116ca7e5

If you do not already have a PostGIS database ready, you will need to:

1. Create a PostgreSQL database

2. Install PostGIS extensions into the database we created.

(Video tutorials courtesy of our brainfurnace.com site).

Loading via our control panel shp2pgsql tool

With our PostGIS database, we’ll load the ESRI file using the shp2pgsql tool in your control panel.

1. In your control panel, under GIS Tools, click on the shp2pgsql icon:

 

2. Upload the states_basic21.zip file (do not unzip it – you need all of the files!):

 

 

3.  Select the database and select states.shp from the “File to Load” dropdown.

Using the defaults, which is fine for most cases, a new table called ‘states’ will be created with a GEOM column.

Optionally, you can set the table name to something else, set the SRID, append to an existing table, and specify the schema.

4. Log into phpPgAdmin, and you should see the states table has been created:

 

Loading via Command Line (SSH).

You can also load your shp files via the command line.

1. Start and SSH session and upload the zip file (or download it using wget).

2. Unzip the file.

user@demo [~/home]# unzip – q states_21basic.zip

3. Move into the shp fil directory directory :

user@demo [~/home]# cd states_basic

user@demo [~/states_basic]#

4. Issue ls to check all of the file are present.

user@demo [~/states_basic]# ls
./   states.dbf  states.sbn  states.shp      states.shx
../  states.prj  states.sbx  states.shp.xml

5. Issue the following to load the file into your database using the defaults:

user@demo [~/states_basic]# shp2pgsql states states | psql -d demo_db

6. You should see something like below when using defaults:

Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2]
Password:
SET
SET
BEGIN
CREATE TABLE
ALTER TABLE
addgeometrycolumn
—————————————————–
public.states.geom SRID:0 TYPE:MULTIPOLYGON DIMS:2
(1 row)

INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1

……………….
INSERT 0 1
COMMIT
user@demo [~/states_basic]#

For a list shp2pgsql command line options, see:

http://www.bostongis.com/pgsql2shp_shp2pgsql_quickguide.bqg

 

 

Loading via Desktop Client

If you have installed PgAdminIII on your Desktop (or using the stand alone shp2pgsql GUI), follow below:

1. Download the states_basic21.zip file and UNZIP it on your desktop

2. In PgAdminIII, go to plugins > PostGIS Shapefile and DBF Loader:

3. Click on View Connection Details and add your connection information:

 

4. Click on Options (note they are the same as the tool in our control panel):

 

5. Navigate to the location you have unzip the shp file to:

6. Click the Open button and then click on Import.

If all went well, you should see something like below displayed in the panel:

connecting:  host=mydomain.com port=5432 user=demo password=’********’ dbname=demo_db

==============================
Importing with configuration: states, public, geom, C:\Documents and Settings\user\Desktop\db-demo\states_21basic\states, mode=c, dump=1, simple=0, geography=0, index=1, shape=1, srid=0
Shapefile type: Polygon
PostGIS type: MULTIPOLYGON[2]
Shapefile import completed.

 

NOTE: In all the above cases, we did not set a SRID. For our demonstrations with the states_basic21 file, this will not be an issue. In other instances, it will be required.  We will look at setting the SRID, as well as updating it, in subsequent installments.

If you have any questions or require assistance, please create a support ticket.