Introduction to Spatial Databases with PostGIS and QGIS 3

Course Curriculum

Chapter 1: Introduction

Lecture 1: Introduction

Lecture 2: What can a spatial database do for you?

Lecture 3: What is a spatial database exactly?

Lecture 4: Where does a spatial database live?

Lecture 5: What is SQL and why would you want to use it?

Chapter 2: Installing software and loading data

Lecture 1: Installing PostGIS on your local computer

Lecture 2: Installing QGIS

Lecture 3: Loading spatial data into PostGIS

Lecture 4: Loading non-spatial data into PostGIS

Lecture 5: Accessing PostGIS from the commandline, the pgAdmin GUI, QGIS, and ArcGIS

Lecture 6: Organization of a database

Chapter 3: Data Query Language – SQL queries for non-spatial data

Lecture 1: The SQL SELECT statement

Lecture 2: The WHERE clause

Lecture 3: Aggregate functions and the GROUP BY clause

Lecture 4: Multi-table Queries Part 1

Lecture 5: Multi-table Queries Part 2

Lecture 6: Data Types in PostgreSQL Part 1

Lecture 7: Data Types in PostreSQL Part 2

Lecture 8: The FROM clause implies a loop

Lecture 9: SQL Expressions and Functions Part 1

Lecture 10: SQL Expressions and Functions Part 2

Lecture 11: The CASE Statement

Lecture 12: SQL Views

Chapter 4: Making SQL Spatial – Introduction

Lecture 1: The PostGIS Vector Geometry model

Lecture 2: The Spatial Reference ID

Lecture 3: The Geometry Field

Lecture 4: Geometry vs.Geography data type

Lecture 5: Spatial Queries – Working with Geometries

Lecture 6: Spatial Queries – Measurements

Lecture 7: Spatial Queries – Accessing the geometry

Lecture 8: Spatial Queries – Testing spatial relationships

Chapter 5: Making SQL Spatial – Advanced Topics

Lecture 1: Multi-table Spatial Queries

Lecture 2: Optimizing your queries

Lecture 3: Spatial aggregate functions

Lecture 4: Geometry processing functions Part 1

Lecture 5: Geometry processing functions Part 2

Lecture 6: Challenge Scenarios Part 1

Lecture 7: Challenge Scenarios Part 2

Chapter 6: SQL Data Definition Language

Lecture 1: Creating tables

Lecture 2: Primary and Foreign Keys

Lecture 3: Creating indexes

Lecture 4: Creating custom data types

Lecture 5: Modifying data structures with the ALTER statement

Lecture 6: GUI methods for defining data structures

Chapter 7: SQL Data Manipulation Language

Lecture 1: Adding new records with the INSERT statement

Lecture 2: Adding geometry to a feature with the INSERT statement

Lecture 3: Modifying data with the UPDATE statement

Lecture 4: Deleting records

Lecture 5: Challenge exercise: Putting it all together

Chapter 8: SQL Data Control Language

Lecture 1: Creating Roles with SQL

Lecture 2: Granting privileges with SQL

Lecture 3: Example – roles and privileges in pgAdmin4 GUI

Lecture 4: Example – roles , privileges, and multi-user editing in QGIS

Chapter 9: Automation

Lecture 1: Transactions

Lecture 2: User defined functions – SQL

Lecture 3: User defined functions – PL/pgSQL – Introduction

Lecture 4: User defined functions PL/pgSQL – Branching and looping

Lecture 5: User defined Functions – PL/pgSQL – Error handling

Lecture 6: Triggers

Lecture 7: Pulling it all together – examples

Chapter 10: Deploying your database

Lecture 1: Introduction

Lecture 2: Registering for a web hosting service

Lecture 3: Moving the database to a remote server

Lecture 4: Deploying to the AcuGIS Cloud

Lecture 5: Performance considerations

Lecture 6: Strategies for mitigating slow response times

Chapter 11: Miscellaneous

Lecture 1: Backing up and exporting data

Lecture 2: Where to go next

Chapter 12: Bonus Lecture

Lecture 1: Bonus Lecture: How to get the most from my series of Udemy courses.