Introduction to Spatial Databases with PostGIS and QGIS 3

Name: Introduction to Spatial Databases with PostGIS and QGIS 3
Rating: 4.5 (1352 reviews)

Learn Spatial SQL and database management for GIS Professionals

Highest Rated

Created byMichael Miller

Last updated 5/2020

English

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

Course content

12 sections • 69 lectures • 12h 39m total length

Introduction6:31
What can a spatial database do for you?10:09
What is a spatial database exactly?13:56
Where does a spatial database live?8:09
Explore how a spatial database lives on a server and is accessed via a database connection using host name, port, credentials, and database name.
What is SQL and why would you want to use it?13:14
Explore what SQL is and why use it to interact with spatial databases, covering declarative queries, data definition and manipulation, and spatial functions in PostGIS.

Installing PostGIS on your local computer15:01
Installing QGIS3:17
Loading spatial data into PostGIS16:27
Learn to load spatial data into PostGIS using a standalone loader or QGIS DB Manager, import shapefiles, and assign the spatial reference ID before loading into the public schema.
Loading non-spatial data into PostGIS6:25
Discover how to load non-spatial data into PostgreSQL from Excel via QGIS, importing three Excel tables with the PostGIS importer and CSV copy commands for integrated spatial linking.
Accessing PostGIS from the commandline, the pgAdmin GUI, QGIS, and ArcGIS15:50
Organization of a database9:17

The SQL SELECT statement15:46
Master the basics of the sql select statement for spatial databases by learning how to query data with from, where, and order by, using aliases, expressions, and subqueries.
The WHERE clause8:34
Aggregate functions and the GROUP BY clause8:48
Multi-table Queries Part 115:31
Multi-table Queries Part 213:34
Data Types in PostgreSQL Part 118:08
Data Types in PostreSQL Part 214:30
Learn how PostgreSQL handles data types and casting, including implicit and explicit casts, with hands-on examples of dates, timestamps, numerics, and user-defined types in spatial databases.
The FROM clause implies a loop4:26
SQL Expressions and Functions Part 113:44
Explore expressions, operators, and functions in PostgreSQL, including constants, variables, casting, string operations such as upper and substring, date arithmetic, modulus, concatenation, and user-defined functions.
SQL Expressions and Functions Part 210:08
The CASE Statement6:53
SQL Views10:18
learn how sql views function as named, query-based tables you can create and join with other data to simplify complex queries, stay current, and avoid duplicating data.

The PostGIS Vector Geometry model8:49
The Spatial Reference ID9:50
The Geometry Field4:12
Geometry vs.Geography data type10:27
Spatial Queries - Working with Geometries15:46
Spatial Queries - Measurements12:11
Spatial Queries - Accessing the geometry12:48
Discover how to access a geometry’s coordinates and view them in text and web formats, including wkt, ewkt, json (geojson), gml, and vector tiles.
Spatial Queries - Testing spatial relationships13:07

Multi-table Spatial Queries9:20
Optimizing your queries15:30
Optimize spatial queries in PostGIS by comparing within and distance calculations, casting between geometry and geography, and leveraging spatial indexes to reduce joins and boost performance.
Spatial aggregate functions7:34
Geometry processing functions Part 116:17
Geometry processing functions Part 211:10
Challenge Scenarios Part 114:04
Challenge Scenarios Part 211:27

Creating Roles with SQL7:42
Granting privileges with SQL6:19
Learn to grant and revoke sql privileges to roles or groups, assign table and column permissions (select, insert, update, delete, truncate), and manage public access.
Example - roles and privileges in pgAdmin4 GUI12:45
Explore creating group and user roles in pgAdmin4 GUI, assign cross-database privileges, and apply grant and revoke actions to admin, viewer, field personnel, managers, and clients.
Example - roles , privileges, and multi-user editing in QGIS5:21

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.

Introduction to Spatial Databases with PostGIS and QGIS 3

What you'll learn

Explore related topics

Course content

Introduction5 lectures • 52min

Installing software and loading data6 lectures • 1hr 6min

Data Query Language - SQL queries for non-spatial data12 lectures • 2hr 20min

Making SQL Spatial - Introduction8 lectures • 1hr 27min

Making SQL Spatial - Advanced Topics7 lectures • 1hr 25min

SQL Data Definition Language6 lectures • 1hr 2min

SQL Data Manipulation Language5 lectures • 1hr

SQL Data Control Language4 lectures • 32min

Automation7 lectures • 1hr 2min

Deploying your database6 lectures • 1hr 14min

Requirements

Description

Who this course is for: