Lab: Working with Large Spatial Data Sets

Relational databases are key to creating enterprise-level GIS systems. The databases form the central location from which data flows out to users through web services and web pages. This lab will introduce you to using spatial databases to manage large vector data sets.

In the labs that follow this one, you'll be creating web pages and web services that disseminate data out to a potentially huge audience.

Learning Objectives

• Build spatial relational databases from large vector data sets
• Use views to query data

Data Sets

This lab will use the National Hydrography Dataset which is available at:

ftp://ftp.ftw.nrcs.usda.gov/wbd/WBD_Latest_Version_March2014/

However, it can take a while to download so the data for California has also been placed on the "X" drive.

In addition, below are shapefiles for the data we'll be using in case you get into trouble with either of the methods above.

• California Watersheds 12
• Californio Watersheds 6
• California Streams
• Humboldt Watershed Streams

Walk Throughs

1. PostGIS
2. PostgreSQL with Spatial Data
3. Using Views to Filter Data
4. Example Queries
5. BlueSpray and PostgreSQL
6. Moving PostgreSQL files with backup and restore

Skill Drill

Create a relational database with at least 3 tables and 3 views of the data that contain spatial data. Two of the views should use at least one spatial SQL function. This can be your own data or the provided NHD data.

Note that the NHD data is in a Geodatabase. You'll need to use ArcGIS to convert this data into shapefiles and then load it into a PostGIS enabled, PostgreSQL database. Then, use views to filter the data. I also recommend cropping these data sets down to a smaller area, like a county to reduce your time to finish the lab.

Take Home Assignment

There is no report due this week. All that is required is to load up the layers you have created in QGIS at the start of the lab one week after this lab is assigned.