- Thurs 5:30pm – 8pm
- Anderson 22 (Weeks 1-4)
- Online (After week 4)
- Prof. Lee Hachadoorian
- 104 Gladfelter Hall – In GIS Studio, knock or enter 103A
- Office Hours:
- Virtual 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
- Relational Database Design and Implementation, 4e, by Harrington
- 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, and you are required to purchase a physical copy. I expect that you will get significant use out of it even after the conclusion of the course.
Relational Database Design and Implementation is an expensive book which is not necessary to purchase, as it is available to read online via the Temple Library. However, we will be reading most of the book, so purchasing a physical copy is probably a good idea. (At the moment, January 2021, the publisher is offering the ebook/PDF for free with with the purchase of a physical copy. Use the link above to the publisher’s book page.) For online access, use one of the links from the Temple Library page for this book: https://librarysearch.temple.edu/catalog/991037042739803811. I recommend using the Ebook Central Academic Complete link, as it allows you to download a limited number of PDF pages for the book, but either one will work for online viewing.
The PostgreSQL Documentation is, IMHO, one of the best examples of software documentation that the world has 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. Only one problem from each exercise will be evaluated, and they will earn you bonus points in the quizzes track. We will go over select problems from 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.
Two DataCamp courses (approximately 4 hours each) will be assigned early in the semester. Completing them will earn you bonus points in the quizzes track.
Each topic will conclude with a short, graded SQL quiz, beginning with the third week of class. The quizzes will focus on analysis using SQL SELECT statements. The difficulty level should be comparable or easier than the take-home exercises, so if you complete the exercises, are present for in-class review, and review the problem and answer afterwards, you should be well-prepared for the quizzes.
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 three homework assignments focusing on database design, including entity-relationship diagrams (ERDs), normalization, and an ETL (Extract-Transform-Load) process. 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 (PiA Ch. 7 & 12)
- pgRouting (PiA Ch. 16.1)
- NoSQL databases (RDD&I Ch. 28)
- Geocoding (PiA Ch. 8)
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 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)
- 34 + 10 points each for quizzes 1 through 5, and 3 points each for quizzes 6 through 8.
- Exercises (8)
- ½ bonus point each added to the SQL Quizzes track.
- DataCamp (2)
- 1 bonus point each added to the SQL Quizzes track.
- Homework Assignments (3)
- 65 + 10 points for each assignment
- 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 elaborated at https://sites.temple.edu/spatialdb/term-project/.
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. 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.
Temple and COVID-19
Temple University’s motto is Perseverance Conquers, and we will meet the challenges of the COVID pandemic with flexibility and resilience. The university has made plans for multiple eventualities. Working together as a community to deliver a meaningful learning experience is a responsibility we all share: we’re in this together so we can be together.
How This Course Will Be Taught
This course meets in person at the time and place indicated above for the first four weeks of the term. The remainder of the class meetings will be conducted remotely.
This course has no final exam.
Attendance Protocol and Your Health
If you feel unwell, you should not come to campus, and you will not be penalized for your absence. Instructors are required to ensure that attendance is recorded for each in-person or synchronous class session. The primary reason for documentation of attendance is to facilitate contact tracing, so that if a student or instructor with whom you have had close contact tests positive for COVID-19, the university can contact you. Recording of attendance will also provide an opportunity for outreach from student services and/or academic support units to support students should they become ill. Faculty and students agree to act in good faith and work with mutual flexibility. The expectation is that students will be honest in representing class attendance.
Student Support Services
If you are experiencing food insecurity or financial struggles, Temple provides resources and support. Notably, the Temple University Cherry Pantry and the Temple University Emergency Student Aid Program are in operation as well as a variety of resources from the Office of Student Affairs.
Technology specifications for this course
Limited resources are available for students who do not have the technology they need for class. Students with educational technology needs, including no computer or camera or insufficient Wifi-access, should submit a request outlining their needs using the Student Emergency Aid Fund form. The University will endeavor to meet needs, such as with a long-term loan of a laptop or Mifi device, a refurbished computer, or subsidized internet access.
Note that some software is available for free download on the ITS Academic Support page. Other specialty software may be available for remote access through ITS.
Remote Proctoring Statement
Zoom, Proctorio or a similar proctoring tool may be used to proctor exams and quizzes in this course. These tools verify your identity and record online actions and surroundings. It is your responsibility to have the necessary government or school issued ID, a laptop or desktop computer with a reliable internet connection, the Google Chrome and Proctorio extension, a webcam/built-in camera and microphone, and system requirements for using Proctorio, Zoom, or a similar proctoring tool. Before the exam begins, the proctor may require a scan of the room in which you are taking the exam.
Statement on Recording of Class Sessions
Class sessions may not be recorded, whether in person or remote, except in cases of an approved accommodation from the Office of Disability Resources (DRS).
Expectations for Class Conduct
In order to maintain a safe and focused learning environment, we must all comply with the four public health pillars: wearing face coverings, maintaining physical distancing, washing our hands and monitoring our health. It is also important to foster a respectful and productive learning environment that includes all students in our diverse community of learners. Our differences, some of which are outlined in the University’s nondiscrimination statement, will add richness to this learning experience. Therefore, all opinions and experiences, no matter how different or controversial they may be perceived, must be respected in the tolerant spirit of academic discourse.
Treat your classmates and instructor with respect in all communication, class activities, and meetings. You are encouraged to comment, question, or critique an idea but you are not to attack an individual. Please consider that sarcasm, humor and slang can be misconstrued in online interactions and generate unintended disruptions. Profanity should be avoided as should the use of all capital letters when composing responses in discussion threads, which can be construed as “shouting” online. Remember to be careful with your own and others’ privacy. In general, have your behavior mirror how you would like to be treated by others.
Please bear in mind that COVID-19 may result in a need for new or additional accommodations.