Introduction
  • Introduction
  • What can a spatial database do for you?
  • What is a spatial database exactly?
  • Where does a spatial database live?
  • What is SQL and why would you want to use it?
Installing software and loading data
  • Installing PostGIS on your local computer
  • Installing QGIS
  • Loading spatial data into PostGIS
  • Loading non-spatial data into PostGIS
  • Accessing PostGIS from the commandline, the pgAdmin GUI, QGIS, and ArcGIS
  • Organization of a database
Data Query Language - SQL queries for non-spatial data
  • The SQL SELECT statement
  • The WHERE clause
  • Aggregate functions and the GROUP BY clause
  • Multi-table Queries Part 1
  • Multi-table Queries Part 2
  • Data Types in PostgreSQL Part 1
  • Data Types in PostreSQL Part 2
  • The FROM clause implies a loop
  • SQL Expressions and Functions Part 1
  • SQL Expressions and Functions Part 2
  • The CASE Statement
  • SQL Views
Making SQL Spatial - Introduction
  • The PostGIS Vector Geometry model
  • The Spatial Reference ID
  • The Geometry Field
  • Geometry vs.Geography data type
  • Spatial Queries - Working with Geometries
  • Spatial Queries - Measurements
  • Spatial Queries - Accessing the geometry
  • Spatial Queries - Testing spatial relationships
Making SQL Spatial - Advanced Topics
  • Multi-table Spatial Queries
  • Optimizing your queries
  • Spatial aggregate functions
  • Geometry processing functions Part 1
  • Geometry processing functions Part 2
  • Challenge Scenarios Part 1
  • Challenge Scenarios Part 2
SQL Data Definition Language
  • Creating tables
  • Primary and Foreign Keys
  • Creating indexes
  • Creating custom data types
  • Modifying data structures with the ALTER statement
  • GUI methods for defining data structures
SQL Data Manipulation Language
  • Adding new records with the INSERT statement
  • Adding geometry to a feature with the INSERT statement
  • Modifying data with the UPDATE statement
  • Deleting records
  • Challenge exercise: Putting it all together
SQL Data Control Language
  • Creating Roles with SQL
  • Granting privileges with SQL
  • Example - roles and privileges in pgAdmin4 GUI
  • Example - roles , privileges, and multi-user editing in QGIS
Automation
  • Transactions
  • User defined functions - SQL
  • User defined functions - PL/pgSQL - Introduction
  • User defined functions PL/pgSQL - Branching and looping
  • User defined Functions - PL/pgSQL - Error handling
  • Triggers
  • Pulling it all together - examples
Deploying your database
  • Introduction
  • Registering for a web hosting service
  • Moving the database to a remote server
  • Deploying to the AcuGIS Cloud
  • Performance considerations
  • Strategies for mitigating slow response times
Miscellaneous
  • Backing up and exporting data
  • Where to go next
Bonus Lecture
  • Bonus Lecture: How to get the most from my series of Udemy courses.