Introduction to Spatial Databases with PostGIS and QGIS 3
4.4 (500 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,987 students enrolled

Introduction to Spatial Databases with PostGIS and QGIS 3

Learn Spatial SQL and database management for GIS Professionals
Bestseller
4.4 (500 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
1,987 students enrolled
Created by Michael Miller
Last updated 11/2019
English
English [Auto], Spanish [Auto]
Current price: $69.99 Original price: $99.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 12.5 hours on-demand video
  • 3 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Understand the advantages of storing spatial information in spatially enabled databases
  • Install PostGIS locally for development purposes and/or access an instance of PostGIS 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 PostGIS to validate data and control user access
  • Perform basic database administration functions to keep your spatial database running smoothly
Requirements
  • You should be familiar with GIS concepts and be willing to learn QGIS. My course QGIS 3.0 for GIS professionals will provide all the background that is necessary but it is not a formal pre-requisite.
Description

This course is intended to provide an introduction to spatial databases in general and PostGIS in particular to GIS professionals who are interested in expanding their skillset to multi-user enterprise level spatial databases. This course uses the latest (as of 2/18) versions of PostgreSQL (10.1), PostGIS (2.4), and QGIS (3.0). 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 PostGIS locally for development purposes and how to access a production version via a network or the internet.
  • How to load your spatial data into PostGIS 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 PostGIS
  • Optimizing your queries for the best performance
  • How to work with raster data in PostGIS
  • The basics of programming custom functions with PL/pgSQL
  • The basics of database administration to keep your database operating smoothly
Who this course is for:
  • GIS professionals who are interested in expanding their GIS skillsets into multi-user enterprise level spatial databases.
Course content
Expand all 69 lectures 12:39:13
+ Introduction
5 lectures 51:59
What can a spatial database do for you?
10:09
What is a spatial database exactly?
13:56
Where does a spatial database live?
08:09
What is SQL and why would you want to use it?
13:14
+ Installing software and loading data
6 lectures 01:06:17
Installing QGIS
03:17
Loading spatial data into PostGIS
16:27
Loading non-spatial data into PostGIS
06:25
Accessing PostGIS from the commandline, the pgAdmin GUI, QGIS, and ArcGIS
15:50
Organization of a database
09:17
+ Data Query Language - SQL queries for non-spatial data
12 lectures 02:20:20
The SQL SELECT statement
15:46
The WHERE clause
08:34
Aggregate functions and the GROUP BY clause
08:48
Multi-table Queries Part 1
15:31
Multi-table Queries Part 2
13:34
Data Types in PostgreSQL Part 1
18:08
Data Types in PostreSQL Part 2
14:30
The FROM clause implies a loop
04:26
SQL Expressions and Functions Part 1
13:44
SQL Expressions and Functions Part 2
10:08
The CASE Statement
06:53
SQL Views
10:18
+ Making SQL Spatial - Introduction
8 lectures 01:27:10
The PostGIS Vector Geometry model
08:49
The Spatial Reference ID
09:50
The Geometry Field
04:12
Geometry vs.Geography data type
10:27
Spatial Queries - Wworking with Geometries
15:46
Spatial Quries - Measurements
12:11
Spatial Queries - Accessing the geometry
12:48
Spatial Queries - Testing spatial relationships
13:07
+ Making SQL Spatial - Advanced Topics
7 lectures 01:25:22
Multi-table Spatial Queries
09:20
Optimizing your queries
15:30
Spatial aggregate functions
07:34
Geometry processing functions Part 1
16:17
Geometry processing functions Part 2
11:10
Challenge Scenarios Part 1
14:04
Challenge Scenarios Part 2
11:27
+ SQL Data Definition Language
6 lectures 01:02:03
Creating tables
19:53
Primary and Foreign Keys
17:00
Creating indexes
06:55
Creating custom data types
07:46
Modifying data structures with the ALTER statement
03:38
GUI methods for defining data structures
06:51
+ SQL Data Manipulation Language
5 lectures 59:32
Adding new records with the INSERT statement
10:06
Adding geometry to a feature with the INSERT statement
15:40
Modifying data with the UPDATE statement
11:13
Deleting records
04:47
Challenge exercise: Putting it all together
17:46
+ SQL Data Control Language
4 lectures 32:07
Creating Roles with SQL
07:42
Granting privileges with SQL
06:19
Example - roles and privileges in pgAdmin4 GUI
12:45
Example - roles , privileges, and multi-user editing in QGIS
05:21
+ Automation
7 lectures 01:01:32
Transactions
04:04
User defined functions - SQL
13:53
User defined functions - PL/pgSQL - Introduction
10:58
User defined functions PL/pgSQL - Branching and looping
08:12
User defined Functions - PL/pgSQL - Error handling
09:27
Triggers
06:32
Pulling it all together - examples
08:26
+ Deploying your database
6 lectures 01:13:31
Introduction
08:17
Registering for a web hosting service
10:14
Moving the database to a remote server
08:23
Deploying to the AcuGIS Cloud
17:55
Performance considerations
15:08
Strategies for mitigating slow response times
13:34