Learning Open Source GIS: Spatial SQL w/ Postgres/PosGIS

Spatial SQL: a language for geographers

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 Postgres, introduce PostGIS as a spatial plug in, and begin to solve spatial problems using SQL.

What you’ll learn

  • How to conduct spatial analysis with Postgres 11 and PostGIS.
  • Traditional 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.

Course Content

  • Section 1: Loading our Software and Data –> 7 lectures • 39min.
  • Section 2: Overview of SQL –> 1 lecture • 18min.
  • Section 3: SQL Data Types –> 4 lectures • 51min.
  • Section 4: Traditional SQL –> 4 lectures • 1hr 7min.
  • Section 5: Spatial SQL –> 4 lectures • 57min.
  • Section 6: Geographic Models with Spatial SQL –> 3 lectures • 36min.
  • Real World Applications with Spatial SQL –> 2 lectures • 18min.

Auto Draft

Requirements

  • Understanding of what a database is.
  • Some exposure to GIS operations.
  • Comfortable with using GIS software.

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 Postgres, 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 willl simulate some real-world examples of GIS projects and spatial analysis.

Whether you need to learn SQL within Postgres 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.

Get Tutorial