Spatial SQL: a language for geographers, GIS and remote sensing experts.

Course Description

This course is part of an entire curriculum based on Free and Open Source GIS (FOSS4g) software, and represents one of the most comprehensive curriculum on FOSS4g. The curriculum are grouped into the categories of Desktop, Server, and Developer parts of the FOSS4g stack. For this course, you will learn the most popular open source database: Postgres 11. We'll start with a basic understanding of PostgreSQL, introduce PostGIS as a spatial plug in, and begin to solve spatial problems using SQL. 

This course uses real data from Tompkins County, New York, and seamlessly integrates with other gisadvisor courses that will teach you QGIS, GDAL, Geoserver, and Python. You'll be working alongside me as we explore the capabilities of SQL, working with raster, vector, and attribute data. And most importantly, after you learn SQL and spatial SQL you will simulate some real-world examples of GIS projects and spatial analysis.

Whether you need to learn SQL within PostgreSQL and PostGIS for your job, or want to get spun up on FOSS4g to nail that big interview, this course will prepare you to work with the ins-and-outs of SQL and spatial SQL, and give you confidence as you interact with other GIS experts.

The curriculum is designed so that you can start with either this course, or any of the other courses. They will refer back and forth with one another, and because we are camping out in the same geographic area, all the courses will use the same data. As you move through each course, you are going to have experience with all the different parts of the FOSS4g stack.

Course curriculum

  1. 01
  2. 02

    • Lesson 7: Why and what is SQL?
  3. 03

    • Lesson 8: Numeric data types

    • Lesson 9: Boolean and character data types

    • Lesson 10: Data and time data types

    • Lesson 11: Spatial data types
  4. 04

    • Lesson 12: SELECT statements and some variations

    • Lesson 13: AGGREGATE and GROUP BY functions

    • Lesson 14: UPDATE, INSERT and DELETE functions in SQL

    • Lesson 15: Conditional statements in SQL - the CASE function
  5. 05

    • Lesson 16: Coordinate systems in SQL

    • Lesson 17: Indexing in SQL - spatial and non-spatial

    • Lesson 18: Spatial operations Part 1

    • Lesson 19: Spatial operations Part 2
  6. 06

    • Lesson 20: Basic problems in geography

    • Lesson 21: Variogram modeling with SQL

    • Lesson 22: Spatial analysis tools - Distance, Adjacency, Interaction

  7. 07

    • Lesson 23: How to create functions with PostgreSQL/PostGIS

    • Lesson 24: Summarize and produce statistics of parcel data with watershed and flood-zone areas
  8. 08

    • Course Slides

    • Course dataset

What will you learn?

  • How to conduct spatial analysis with Postgres 11 and PostGIS

  • Basic SQL statements

  • Spatial SQL statements

  • Creating spatial functions with SQL

  • How to create geographic models with SQL

  • PGAdmin IV to manage Postgres databases

  • QGIS Database module to manage Postgres databases

  • Join multiple tables together

Student Profile?

  • Under/post graduate students

  • Professionals and Companies

  • Master students and PhD candidates

  • Researchers and Academics

Some more information

  • Certificates of Completion

    After you successfully finish the course, you can claim your Certificate of Completion with NO extra cost! You can add it to your CV, LinkedIn profile etc

  • Available at any time! Study at your best time

    We know hard it is to acquire new skills. All our courses are self paced.

  • Online and always accessible

    Even when you finish the course and you get your certificate, you will still have access to course contents! Every time an Instructor makes an update you will be notified and be able to watch it for FREE

Requirements

  • Understanding of what a database is

  • Some exposure to GIS operations

  • Comfortable with using GIS software

About your Instructor

Dr. Arthur J. Lembo, Jr. is an educator with a passion for GIS and almost 30 years of GIS industry experience. He has won numerous teaching awards, including the prestigious New York State Chancellors award for Excellence in Teaching (2006), and the Maryland Board of Regents Teaching Award (2018) - both of these awards are the highest awards in academia for both States. Currently, Dr. Lembo is a Professor in the Department of Geography and Geosciences at Salisbury University, where he is also the Technical Director of the Eastern Shore Regional GIS Cooperative. Dr. Lembo has published numerous academic papers on GIS, authored a leading textbook on Statistical Problem Solving in Geography, and conducted sponsored research for organizations like the National Science Foundation, NASA, the United States Department of Agriculture, and the Kellogg Foundation.

Arthur Lembo

Professor