- W 5:30pm – 8pm, Gladfelter 341
- Prof. Lee Hachadoorian
- 104 Gladfelter Hall – In GIS Studio, knock or enter 103A
- Office Hours:
- Mondays & Wednesdays, Noon – 2pm
- Tuesdays, 3:30pm – 4:30pm
- Other times by appointment
Purpose of the Course
This course is an introduction to the design and management of spatial databases. The relational database model underlies most modern databases. Because of increasing demand to store, analyze, and display geographic data, many major RDBMSes have added spatial features. The course will begin with aspatial database theory and practice, including SQL (Structured Query Language, the language specification that underlies most RDBMSes), the database design process, normalization, and entity-relationship diagramming. It will then move on to spatial data types, spatial indexing, and spatial querying. Specific issues related to spatial data storage, including denormalization, will be addressed.
The course will use PostGIS, a spatial extension to the open source PostgreSQL RDBMS. SQL and the SQL/MM Spatial extension, moreso than other programming languages, are widely adopted standards, and the use of any particular implementation will introduce skills that will be directly portable to other implementations, including Oracle, SQL Server, and SpatiaLite/SQLite.
At the end of the course students are expected to know how to design relational and object-relational schemas for GIS databases, implement database designs in spatial database management systems (RDBMS), and retrieve and manage spatial data in a GIS database. If time permits, we will also discuss recent trends such as unstructured data and column-oriented databases.
- Fundamentals of GIS (GUS 5062) or equivalent
- A laptop on which you can install software and data
This course meets once a week. Missing any class meetings will hamper your ability to complete the work in this course. Your attendance percentage will also indicate the maximum final grade you can earn in this course. If you miss 3 classes, you have attended 78.6% of class meetings. Accordingly, your final grade will not be higher than a C+, regardless of any other work completed. Please see my attendance policy at my Temple web page: https://sites.temple.edu/hachadoorian/specification-grading/.
- PostGIS in Action, 2e, by Obe & Hsu
- Spatial Database Systems, by Yeung & Hall
- The official PostgreSQL Documentation
PostGIS in Action is available directly from the publisher at the link above. The publisher regularly runs sales. If you sign up for their Deal of the Day email far enough in advance, there is a good chance you will be able to purchase the text at 40%-50% off. This is a very hands-on book, with many code examples and implementation recommendations. I expect that you will get significant use out of it even after the conclusion of the course.
Spatial Database Systems is an expensive book which is not necessary to purchase, as it is available at as a free download (individual chapters or the entire book) through the Temple Library. Follow the link above to the publisher’s website while on campus. From off campus try using the library proxy server as follows: http://link.springer.com.libproxy.temple.edu/book/10.1007%2F1-4020-5392-4.
The PostgreSQL Documentation is, IMHO, one of the best examples of software documentation that the world has ever seen. Since Postgres strives to be a standards-compliant implementation of SQL, the docs, in addition to providing instruction on how to use Postgres, make clear what the standard requires and where and why Postgres SQL sometimes diverges from it. I will be assigning sections from the documentation to read, but you should become familiar with it beyond the specifically assigned sections, as you will refer back to it over and over in your use of Postgres and PostGIS.
Exercises will be assigned each week during the first half of the semester. These exercises will not be evaluated. Instead, we will go over the exercises at the beginning of the following class. In order to benefit from them, you must have completed them before we review them in class.
Beginning with the third class meeting, each class will conclude with a short, graded SQL quiz. The topic will be based on the lecture and reading from two weeks prior. The difficulty level should be comparable or easier than the take-home exercise, so if you complete the exercise, are present for in-class review, and review the problem and answer afterwards, you should be well-prepared for the quiz.
The quizzes are cumulative. You will not be allowed to progress to the Quiz 2 if you do not pass Quiz 1.
There will be two homeworks including a normalization exercise and more complex queries with a discussion of optimization. These will be the equivalent of a short paper in a writing-oriented course.
Students will form into groups and sign up to present advanced topics in geodatabases. Possible topics include:
- Raster storage and analysis
- Column-oriented datatbase (possibly using the cstore_fdw Postgres extension)
- Geocoding (possibly with the TIGER geocoder)
Students may select other approved topics based on their interests.
A more complex project involving planning and executing a database design. Last year’s requirements are available at https://sites.temple.edu/spatialdb/term-project/. There may be modifications, but we will discuss in class.
You will earn points along several tracks. Each track is worth up to 100 points. Your must progress along ALL tracks to be successful in this course. Your final grade is the based on the lowest score earned along any track.
- 0-100 points. Your attendance score is a straight percentage of class session you are present for.
- SQL Quizzes (8)
- 50 + 6.25 points per quiz completed, based on 8 planned quizzes. If I have to reduce the number of quizzes, individual quizzes will be inflated so that the score for completing all quizzes is 100.
- Homework (2)
- 70 + 15 points for each assignment completed fully. Partially complete submissions will be awarded 0-10 points and returned for revision.
- 70-100 points. The tutorial will be awarded up to 30 points based on requirements announced separately.
- Term Project
- 50-100 points. The term project will be awarded up to 50 points based on requirements announced separately.
Each week will include about an hour of lecture. The lecture is intended, as much as possible, to be an interactive environment. Please feel free at any time during lecture to ask a question or make a comment. Conversely, you are expected to respond to discussion questions asked in class.
Lecture will cover some topics not covered in the texts. Lecture slides for the entire term are available for download. The lecture slides are not intended to substitute for attendance.
The remainder of each class following the lecture will be devoted to hands-on exercises. During lab, students work on lab assignments that provide practical experience in applying the concepts learned in lecture.
This course is open to all students who meet the academic requirements for participation. Any student who has a need for accommodation based on the impact of a disability should contact the instructor privately to discuss the specific situation as soon as possible. Contact Disability Resources and Services at 215-204-1280 in 100 Ritter Annex to coordinate reasonable accommodations for students with documented disabilities.
All submitted work should be your own. Please read my guide to Academic Integrity at https://sites.temple.edu/hachadoorian/course-policies/.
All persons participating in the course should be respectful of other students and the instructor in order to facilitate a civil learning environment. All persons participating in the course have a right to expect respectful treatment in the classroom.
Statement on Academic Freedom
Freedom to teach and freedom to learn are inseparable facets of academic freedom. The University has adopted a policy on Student and Faculty Academic Rights and Responsibilities (Policy # 03.70.02) which can be downloaded from http://policies.temple.edu/getdoc.asp?policy_no=03.70.02.
Working with Other Students
I encourage students to work together on assignments and assist each other in understanding the course material. However, except for explicit group assignments, all contents of each student’s work must be authored solely by that student.
Each exercise will be marked Complete or Incomplete. Incomplete exercises will receive comments and can (should) be resubmitted until they are Complete.