Lab: Using PostgreSQL With Spatial Data

Learning Outcomes

• Navigate PostgreSQL databases and tables through the administrator software (pgAdmin)
• Create databases, tables and fields in pgAdmin
• Edit data in pgAmin
• Import data from CSV files into a table
• Export data queried from multiple tables

Walk Throughs

• Introduction to PostgreSQL
• Creating Databases
• Creating tables and fields/columns
• Adding Data
  

Lab Assignment

One of the reasons to use a database is to maintain large, complex, sets of spatial data. There are a number of these available on the web and you may need to maintain one of these yourself.

The goal of this lab is to extract the locations of certain species of trees from the Forest Inventory Analysis (FIA) database. First we'll download the data, then we'll bring it into tables in a database, finally, we'll perform queries against the data and bring the results into QGIS. This data will be "Point" data which does not require spatial extensions.

Importing Data to The Database

1. Search for the FIA database on the web

2. Find the documentation (manual) for the data and take a quick look at it. You'll need to review it later to determine the nature of this data.

3. Find the download page and download the following files:

• REF_SPECIES.CSV - this file contains the "species codes" and specific epithets for the trees in the FIA database
• CA_PLOT.CSV - these are the plot locations. You can download another state if desired
• CS_TREES.CSV - these are the trees found within the plots

4. Examine the CSV files in Excel and create new files with a subset of the columns that includes:

• REF_SPECIES: spcd, common_name, genus, species, variety, subspecies
• PLOT: cn, plot, lat, lon
• TREE: cn, plt_cn, tree, spcd, dia

Save these as new CSV files. Check the FIA manual for definitions of these fields.

5. Create tables in a database with fields that match the columns in the Excel files. For the "codes" like "spcd", you'll need to use the "bigint" data type because the numbers can be larger than a 32-bit integer can hold. Do not add the primary key ("id") yet.

6. Execute the "COPY" command on the web site to bring the CSV files into your database.

All tables should have a primary key that is "serial" for speed and editing.

1. Add a new column to one of the tables called "id" and of type "serial".

2. Right clicking on the table and selecting "New Object -> New Primary Key".

3. Name the primary key "<tablename>_pkey". This is a standard database convention for naming primary keys.

4. Click on the "Columns" tab

5. Select "id" from the "Column" popup menu and click "Add".

6. Click "OK" to add the primary key.

Query and Export the Data

1. Open each of the tables and execute some queries until you are comfortable with the data.

2. Use the "INNER JOIN" command to combine the tables together so you can select the locations of tree species.

3. In the "Query" window in PostgreSQL, use the "File -> Export..." command to export the data to a CSV file. Make sure to include the latitude and longitude of the trees.

4. Bring the data into QGIS using the "delimited data" icon (a big comma).

PostgreSQL will complain about not having a primary key in these tables. One solution is to add a primary key numbered 1,2,3... in Excel and then bring this into a primary key column in your table. Note that you cannot make this a "serial" column because they are controlled by PostgreSQL.

Repeat the steps above for at least three tree species.

Take Home Assignment

A short report with maps of at least three tree species of your choosing and from a state of your choosing (or the entire US for extra credit). Include tables with the name, data type, and descriptions of the fields in your tables in a "Database Dictionary" section of your report. Make sure the review the FIA documentation and mention any issues with the data.