MySQL for Geospatial Applications

Course Curriculum

Chapter 1: Introduction

Lecture 1: Introduction

Lecture 2: Why use a spatial database?

Lecture 3: What exactly is a spatial database?

Lecture 4: Where does a spatial database live?

Lecture 5: What is SQL and why use it?

Chapter 2: Installation and loading data

Lecture 1: Installing MySQL and XAMPP on Windows

Lecture 2: Installing QGIS 3.28 on Windows

Lecture 3: Installing MySQL and XAMPP on MacOS

Lecture 4: Installing QGIS 3.28 on MacOS

Lecture 5: Loading non-spatial data into MySQL

Lecture 6: Loading spatial data into MySQL

Lecture 7: Accessing MySQL from the command line

Lecture 8: The organization of a database

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

Lecture 1: The SQL SELECT statement

Lecture 2: The SQL Where clause

Lecture 3: Aggregate functions and the GROUP BY clause

Lecture 4: Multi-table joins

Lecture 5: Multi-table joins (Part 2)

Lecture 6: MySQL data types

Lecture 7: Expressions, functions, and operators

Lecture 8: User defined functions

Lecture 9: The CASE statement

Lecture 10: MySQL views

Chapter 4: Making SQL spatial – Introduction

Lecture 1: The MySQL geometry model

Lecture 2: SRID's

Lecture 3: The MySQL geometry column

Lecture 4: Geometry vs. Geography

Lecture 5: Spatial Functions – Description

Lecture 6: Spatial Functions – Measurements

Lecture 7: Spatial Functions – Coordinate Access

Lecture 8: Spatial Functions – Testing Spatial Relationships

Chapter 5: Making SQL spatial – Advanced

Lecture 1: Multi-table joins with spatial predicates

Lecture 2: Query optimization

Lecture 3: Aggregate functions in multi-table spatial queries

Lecture 4: Buffers in MySQL – Part 1

Lecture 5: Buffers in MySQL – Part 2

Lecture 6: Other geometry processing functions

Lecture 7: Challenge Exercises

Lecture 8: Challenge Exercises – Part 2

Chapter 6: SQL Data Definition Language (DDL)

Lecture 1: Intro to the Data Definition Language

Lecture 2: Creating a new table

Lecture 3: Populating a MySQL spatial table using QGIS digitizing tools

Lecture 4: Primary and Foreign Keys – Intro

Lecture 5: Implementing a lookup table with primary and foreign keys

Lecture 6: Creating indexes

Lecture 7: Changing the structure of existing tables with ALTER

Lecture 8: GUI methods for changing table structure

Chapter 7: SQL Data Manipulation Language (DML)

Lecture 1: Adding new records with the INSERT statement

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

Lecture 3: Modifying existing data with the UPDATE statement

Lecture 4: Deleting records

Lecture 5: Challenge exercise: Putting it all together

Chapter 8: SQL Data Control Language (DCL)

Lecture 1: Creating users and roles

Lecture 2: Granting privileges to roles

Lecture 3: Example Roles for this project

Lecture 4: Working with roles in QGIS

Chapter 9: Deploying your database to a web server

Lecture 1: Registering for a web hosting plan

Lecture 2: Creating a database and a user and import data from localhost

Lecture 3: Setting up remote hosting and accessing from remote clients

Lecture 4: Problem solved and mea culpa

Lecture 5: Challenge scenario – ST_Union and ST_Collect for dissolving features.

Lecture 6: Backing up your data

Chapter 10: Accessing your MySQL data from Python

Lecture 1: Quick Overview of Jupyter Notebooks

Lecture 2: Accessing your MySQL data from Python

Lecture 3: Quick overview of GeoPandas

Lecture 4: Reading your MySQL data into GeoPandas

Lecture 5: Quick overview of web technology

Lecture 6: Very basic client side web map with Leaflet

Lecture 7: Reading MySQL data stored in WGS84 coordinate system onto a web map

Lecture 8: Reading data stored in UTM – Coordinate transformation using Proj4

Lecture 9: Loading web map to the server

Chapter 11: Automation

Lecture 1: Transactions

Lecture 2: User Defined Functions

Lecture 3: Extending the language – Multi function

Lecture 4: Simplifying your code – Wetland system lookup

Lecture 5: Simplifying your code – Getting lastdate and lastsurvey from durvey data

Lecture 6: Introduction to triggers

Lecture 7: After insert trigger

Lecture 8: After update trigger

Lecture 9: After delete trigger

Lecture 10: Triggers for audit logging

Lecture 11: Introduction to Stored Procedures

Lecture 12: Stored procedures program flow controllers – Conditionals and branching

Lecture 13: Stored Procedures program flow – Loops

Lecture 14: Stored procedures . looping through a resultset with cursors

Lecture 15: Stored procedures – Error handling

Lecture 16: Where to go from next

Chapter 12: Background Info

Lecture 1: All about GeoJSON