Source: MySQL for Geospatial Applications | Udemy
Build your own multi-user enterprise GIS for free
What you’ll learn
-
Understand the advantages of storing spatial information in spatially enabled databases
-
Install MySQL locally for development purposes and/or access an instance of MySQL that is running on their organizations intranet, a web host, or the cloud
-
Write SQL queries to retrieve and analyze spatial data.
-
Use the tools available in MySQL to validate data and control user access
-
Perform basic database administration functions to keep your spatial database running smoothly
-
Customize your database with your business logic using stored procedures, user defined functions, and triggers
-
Access your data from a variety of clients including, QGIS, ArcGIS, PhPMyAdmin, MySQL Workbench, the MySQL command line, Python, and web maps
Course content
-
Preview15:03
-
Preview15:02
-
Preview16:48
-
Preview10:24
-
Preview15:23
-
Installing MySQL and XAMPP on Windows
15:04
-
Installing QGIS 3.28 on Windows
09:03
-
Installing MySQL and XAMPP on MacOS
13:20
-
Installing QGIS 3.28 on MacOS
05:10
-
Loading non-spatial data into MySQL
13:58
-
Loading spatial data into MySQL
20:06
-
Accessing MySQL from the command line
06:56
-
The organization of a database
08:08
-
The SQL SELECT statement
17:15
-
The SQL Where clause
12:45
-
Aggregate functions and the GROUP BY clause
09:39
-
Multi-table joins
17:38
-
Multi-table joins (Part 2)
05:45
-
MySQL data types
19:03
-
Expressions, functions, and operators
07:12
-
User defined functions
06:29
-
The CASE statement
04:31
-
MySQL views
10:45
-
The MySQL geometry model
08:49
-
SRID’s
18:59
-
The MySQL geometry column
07:45
-
Geometry vs. Geography
08:07
-
Spatial Functions – Description
12:30
-
Spatial Functions – Measurements
08:21
-
Spatial Functions – Coordinate Access
11:45
-
Spatial Functions – Testing Spatial Relationships
05:53
-
Multi-table joins with spatial predicates
14:51
-
Query optimization
09:10
-
Aggregate functions in multi-table spatial queries
07:27
-
Buffers in MySQL – Part 1
11:03
-
Buffers in MySQL – Part 2
14:16
-
Other geometry processing functions
13:59
-
Challenge Exercises
17:26
-
Challenge Exercises – Part 2
05:20
-
Intro to the Data Definition Language
12:37
-
Creating a new table
16:37
-
Populating a MySQL spatial table using QGIS digitizing tools
12:29
-
Primary and Foreign Keys – Intro
17:31
-
Implementing a lookup table with primary and foreign keys
17:00
-
Creating indexes
07:35
-
Changing the structure of existing tables with ALTER
06:29
-
GUI methods for changing table structure
10:53
-
Adding new records with the INSERT statement
10:45
-
Adding geometry to a feature with the INSERT statement
08:42
-
Modifying existing data with the UPDATE statement
07:53
-
Deleting records
04:37
-
Challenge exercise: Putting it all together
12:01
-
Creating users and roles
11:05
-
Granting privileges to roles
12:26
-
Example Roles for this project
13:47
-
Working with roles in QGIS
12:44
-
Registering for a web hosting plan
07:13
-
Creating a database and a user and import data from localhost
07:27
-
Setting up remote hosting and accessing from remote clients
09:21
-
Problem solved and mea culpa
15:51
-
Challenge scenario – ST_Union and ST_Collect for dissolving features.
17:43
-
Backing up your data
10:14
-
Quick Overview of Jupyter Notebooks
08:35
-
Accessing your MySQL data from Python
15:10
-
Quick overview of GeoPandas
07:49
-
Reading your MySQL data into GeoPandas
19:34
-
Quick overview of web technology
14:29
-
Very basic client side web map with Leaflet
15:28
-
Reading MySQL data stored in WGS84 coordinate system onto a web map
19:59
-
Reading data stored in UTM – Coordinate transformation using Proj4
11:50
-
Loading web map to the server
05:57
-
Transactions
04:56
-
User Defined Functions
12:04
-
Extending the language – Multi function
10:12
-
Simplifying your code – Wetland system lookup
06:40
-
Simplifying your code – Getting lastdate and lastsurvey from durvey data
13:00
-
Introduction to triggers
11:56
-
After insert trigger
11:38
-
After update trigger
06:02
-
After delete trigger
03:45
-
All about GeoJSON
13:47
Requirements
-
You should be familiar with GIS concepts and be willing to learn QGIS. My course QGIS 3.10 for GIS professionals will provide all the background that is necessary but it is not a formal pre-requisite.
-
Some prior exposure to SQL for non-spatial data will be useful as we will go through that section fairly quickly so we can focus on spatial data.
Description
This course is intended to provide an introduction to spatial databases in general and MySQL in particular to GIS professionals who are interested in expanding their skillset to multi-user enterprise level spatial databases. MySQL is available on almost all web-hosting platforms and geospatial capabilities have been part of the core MySQL distribution since version 5.6. This means that is far easier and less expensive to setup and deploy a spatial database with MySQL than other options such as PostGIS. At this point it does not have all the bells and whistles that PostGIS has but all of the important core functionality is there. This course uses the latest (as of 11/22) versions of MySQL (8.0, and QGIS (3.28). I believe it is the most current and thorough course on spatial databases available today. You will learn
- What a spatial database is and why you would want to use one.
- What SQL is, why you would want to use it, and how it can be applied to geospatial concepts.
- How to install MySQL locally for development purposes and how to access a production version via a network or the internet.
- How to load your spatial data into MySQL and access it from a variety of clients, especially QGIS
- The basics of SQL for both spatial and non-spatial queries
- How to validate data and control user access with the tools built-in to MySQL
- Optimizing your queries for the best performance
- The basics of programming custom functions with the MySQL stored procedure language
- The basics of database administration to keep your database operating smoothly
- Deploy your database on a web’hosting service so that it is available to anyone with an internet connection (assuming they have been granted access priveleges)
Who this course is for:
- GIS professionals who are interested in expanding their GIS skillsets into multi-user enterprise level spatial databases.
- GIS professionals who want a simple and inexpensive way to get started with multi-user enterprise level spatial databases.