GEOG 868 - Spatial Database Management
This is a sample syllabus.
This sample syllabus is a representative example of the information and materials included in this course. Information about course assignments, materials, and dates listed here is subject to change at any time. Definitive course details and materials will be available in the official course syllabus, in Canvas, when the course begins.
Overview
GEOG 868: Spatial Database Management for Geospatial Professionals. Advanced topics in the storage, management, and retrieval of geospatial data using common proprietary and open-source relational database technologies.
Access to accurate data is a cornerstone on which all successful organizations are built. The data stewards who maintain an organization's information systems thus have a crucial role to play. This course is intended for students who want to learn how to create, maintain, and retrieve data from a spatially enabled database.
The course begins by covering relational database topics that are of relevance in both geographic and non-geographic contexts (e.g., Structured Query Language and database design). It then focuses on the special considerations involved in the management of a spatial database by demonstrating two broad approaches. The first utilizes open-source technologies (specifically, the Postgres database management system and its spatial extension PostGIS); the second utilizes technology from a leading vendor (Esri's geodatabase).
Objectives
GEOG 868 will teach students how to create a spatial database and use it to answer geographic questions. Students will learn by using both open-source and proprietary database technologies.
At the successful completion of this course, students should be able to:
- retrieve data from an existing database using SQL Select queries;
- design a database schema from a set of requirements;
- implement that design through the creation of related tables;
- insert and update rows in a table;
- create spatially enabled tables in Postgres/PostGIS;
- work with PostGIS data using open-source desktop GIS software (QGIS);
- answer questions using PostGIS spatial functions;
- create an Esri file geodatabase;
- model real-world entities through subtypes, domains, topology rules, and relationship classes;
- set up and administer an enterprise (ArcSDE) geodatabase;
- manage vector and raster data in an enterprise geodatabase;
- understand enterprise geodatabase editing workflows;
- discuss the suitability of an open-source or proprietary approach to various project scenarios
Required Materials
The materials listed here represent those that may be included in this course. Students will find a definitive list in the course syllabus, in Canvas, when the course begins.
Software
We'll be using a number of different software packages in this course, but the good news is that costs to you should be minimal:
- Microsoft Access 2008 or later (available to PSU students at no cost)
- PostgreSQL (free)
- PostGIS (free)
- QGIS (free)
- ArcGIS Pro (free to PSU students)
- ArcSDE (you'll run this software on your own server in the Amazon cloud; usage costs depend on how long your server is running, but typically range from $20-$50)
Books
You can succeed in the course with just our online materials, but you may find the following book useful as a reference for Lessons 3-4:
PostGIS in Action, 2nd Edition by Regina O. Obe and Leo S. Hsu (ISBN: 9781617291395).
This book can be ordered at www.postgis.us. After submitting your order, you'll be able to immediately download an electronic version. It is also available as an e-book for free through the Penn State Library.
Prerequisites
GEOG 484 or equivalent experience.
Expectations
Like any graduate-level course, you will be challenged to move beyond the knowledge and skills that you bring to the class. However, you'll be glad to know that you don't have to show up for class at a certain time! All you need to do is complete your assignments before the published deadlines. Some of the assignments are one week in length, while others are two.
We have worked hard to make this the most effective and convenient educational experience possible. How much and how well you learn is dependent on your attitude, diligence, and willingness to ask for clarifications or help when you need them. We are here to help you succeed. Please keep up with the class schedule and take advantage of opportunities to communicate with us and with your fellow students. You can expect to spend an average of 10 - 12 hours per week on class work.
Major Assignments
Students earn grades that reflect the extent to which they achieve the learning objectives listed above. Opportunities to demonstrate learning include the following. Grades will be based on points assigned to each of several components of the course, as follows:
8 Individual Projects - 50% of course grade
Each lesson concludes with a hands-on project to be completed individually by the student. Project deliverables sometimes include write-ups of lessons learned,.
8 Lesson Quizzes - 20% of course grade
A quiz will be held at the end of each lesson to test the student's comprehension of class materials and other reading as required. Students are welcome to refer to any available resources, including, but not limited to, the class materials when completing quizzes. The only condition is that the research must be done without the aid of someone else.
Review Quiz - 10% of course grade
A 20-question review quiz at the end of the course will cover all of the course material.
Final Project - 20% of course grade
A final project will be submitted in the last week of the course.
Course Schedule
| Week | Topic | Assignment |
|---|---|---|
| 1 | Lesson 1: Retrieving Data from a Relational Database SQL: Queries and Subqueries, Joining Tables, Aggregating Data | Project 1 |
| 2 | Lesson 2: Relational Database Concepts and Theory RDBMS, Design Concepts, Managing Data in Tables | Project 2 |
| 3 | Lesson 3: Building a Postgres/PostGIS Database PostGIS and QGIS | Project 3 |
| 4 | Lesson 4: Working with PostGIS Spatial Functions Postgres/PostGIS Topics: Management Functions, Geometry, Spatial Functions | Project 4 |
| 5 | Lesson 5: Working with Esri's Desktop Geodatabases Esri's Desktop Geodatabases: Domains, Subtypes, Topology, Relationship Classes | Project 5 |
| 6 | Lesson 6: Enterprise Geodatabase Administration Enterprise Geodatabase introduction, ArcSDE | Project 6 |
| 7 | Lesson 7: Data Management in an Enterprise Geodatabase Data Management in an Enterprise Geodatabase: Vector and Raster | Project 7 |
| 8 | Lesson 8: Editing and Versioning in an Enterprise Geodatabase Versioned Editing in an Enterprise Geodatabase | Project 8 |
| 9 | Final Project Synthesis of previous lessons | Final Project |
| 10 | Final Project Synthesis of previous lessons | Final Project |