Section 4: Loading spatial data

PostGIS is supported by a wide variety of libraries and applications, providing many options for loading data. This section will focus on the basics – loading shapefiles using PostGIS’s included command line tools. (Loading data using other tools is left for the a later section.) By the end of this section you will have loaded a number of spatial tables into your database and will be ready to experience the joys of spatial SQL.

1. The first step to working with the PostGIS command line tools is to open a command prompt and establish a proper environment. Open the Start menu and navigate to Accessories > Command Prompt.

   

2. Change to the C:\workshops\PostGIS\data directory and execute the batch file set_environment.bat. This script adds the PostgreSQL binaries to the search path and defines the default database, host, and username to those described above:

Note

It is considered good practice to load your spatial data into named schemas. We will use the “public” (i.e. default) schema, but in practice this makes upgrades, migrations and backups more difficult.

1. Now that the PostGIS tools can be used, let’s have a look at the shapefile conversion application, shp2pgsql. This program take a shapefile as an input and outputs SQL commands which can then be run against a database, thus converting a shapefile into a database table. For our first data loading project we will use a small polygon layer. Execute the following commands to convert the medford_parks shapefile into a format PostgreSQL can understand and load it into the database:

shp2pgsql -s 2270 medford_parks.shp medford_parks > medford_parks.sql psql -f medford_parks.sql workshop

Note

Four options are available for table creation:

-d Causes the existing table to be dropped and recreated
-a Leaves the existing table intact and appends new data
-c Creates a new table to load data into
-p Creates a new table but loads no data

1. The first command, shp2pgsql, is given a single parameter, -s 2270. This defines the spatial reference system describing the data (which will be explained in a later section. We also provide the name of the shapefile (medford_parks.shp) and the name of the table to be created (medford_parks). The results are redirected to a file for later use (> medford_parks.sql). The second command, psql, executes the contents of the newly created file medford_parks.sql in the workshop database.

   Looking at the medford_parks.sql file, we can see what is produced. The script contains a CREATE TABLE statement and a number of INSERT statements. While using insert statements can occasionally be more reliable, using the -D argument will generate a script using PostgreSQL’s dump format for much faster loading.

   

2. Run the following commands to convert and load the jacksonco_schools (points) shapefile:

   shp2pgsql -s 2270 -D jacksonco_schools.shp jacksonco_schools > jacksonco_schools.sql
   psql -f jacksonco_schools.sql workshop

   

3. A look at the jacksonco_schools.sql file shows the COPY command instead of the INSERT used in the previous data load.

   

4. In the interest of time, the remaining shapefiles have been converted for you. Simply execute the following command to load the remaining data:

   psql -f medford.sql workshop

   

5. Go back to pgAdmin and navigate to the public schema in the workshop database to verify that new tables have been created.

Things to Try: Spatially Enable an Existing Database

It is not always appropriate to create a fresh database from the PostGIS template, such as when installing from source or adding PostGIS functionality to an existing database. You have already seen how to create a database using PGAdmin III.

Your task in this section is to create a database and add PostGIS types and functions after the fact. There are three scripts that will be needed that can be found in the contrib directory of your PostgreSQL install. For guidance, refer to the PostGIS documentation on installing from source [1].

Things to Try: View data using uDig

uDig, (User-friendly Desktop Internet GIS), is an open source desktop GIS application that we will be using later in this workshop. Use this software to connect your PostGIS database. The application is included in the software folder.

Footnotes

[1]	“Chapter 2. Installation” PostGIS Documentation. 5 May 2009 <http://postgis.refractions.net/documentation/manual-1.3/ch02.html#PGInstall>