Using shp2pgsql to import Shapefiles to PostGIS

I covered some of this in a previous post on Converting Lat/Long data to PostGIS. But now my problem is to import Shapefiles into PostGIS.

First, why do this. While Shapefiles are good for distributing and looking at data, it is difficult to do any analysis. To do anything, you end up using OGR/GDAL to examine the shapefile and then do something with it. PostGIS takes the spatial and data components of the Shapefile and exposes them in database format, allowing anything that can be done with a database to be done with the Shapefile. In addition, it enables Simple Features for SQL which allows for using set operations on geographic objects (as opposed to only sets). Think of all the Venn diagram pictures back in High School. And other spatial operations that may come to mind when dealing with points, lines and shapes.

The key, of course, is to get the PostGIS database started. So, first, within PostgreSQL, create a database

CREATE DATABASE PA;

Next, load PostGIS onto the database. The first step is to load PL/PGSQL

CREATE LANGUAGE plpgsql;

Next, within the database, load the lwpostgis.sql and spatial_ref_sys.sql into the database. I used the pgAdmin III GUI.

Next, use the shp2pgsql tool The format is:

shp2pgsql [] > .sql

This should be run from the directory with the .shp file. refers to the name of the shapefile. is the name of the table to be created within the database. You can also enter this in schema.tablename form if you want. database name is the name of the database on the local server that it is being entered into. Usual practice is to pipe this to an SQL file that will be read in later (with all the necessary permissions. For option, the common one is to enter the -s of the EPSG (if known).

So, for example, using the NHPN shapefile for Pennsylvania (FIPS: 42), which is to be imported into a database named PACities, in the 'public' schema as the table 'NHPN' with the projection NAG 83 (ft) (Pennsyvania South) EPSG=2272, I get:

shp2pgsql -s 2272 S42NHPN public.NHPN PACities > nhpnImport.sql

And now it is in.