Have a question?
Message sent Close
4.62 out of 5
4.62
562 reviews on Udemy

Introduction to Spatial Databases with PostGIS and QGIS 3

Learn Spatial SQL and database management for GIS Professionals
Instructor:
Michael Miller
2,275 students enrolled
English [Auto] More
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

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

Introduction

1
Introduction
2
What can a spatial database do for you?
3
What is a spatial database exactly?
4
Where does a spatial database live?
5
What is SQL and why would you want to use it?

Installing software and loading data

1
Installing PostGIS on your local computer
2
Installing QGIS
3
Loading spatial data into PostGIS
4
Loading non-spatial data into PostGIS
5
Accessing PostGIS from the commandline, the pgAdmin GUI, QGIS, and ArcGIS
6
Organization of a database

Data Query Language - SQL queries for non-spatial data

1
The SQL SELECT statement
2
The WHERE clause
3
Aggregate functions and the GROUP BY clause
4
Multi-table Queries Part 1
5
Multi-table Queries Part 2
6
Data Types in PostgreSQL Part 1
7
Data Types in PostreSQL Part 2
8
The FROM clause implies a loop
9
SQL Expressions and Functions Part 1
10
SQL Expressions and Functions Part 2
11
The CASE Statement
12
SQL Views

Making SQL Spatial - Introduction

1
The PostGIS Vector Geometry model
2
The Spatial Reference ID
3
The Geometry Field
4
Geometry vs.Geography data type
5
Spatial Queries - Wworking with Geometries
6
Spatial Quries - Measurements
7
Spatial Queries - Accessing the geometry
8
Spatial Queries - Testing spatial relationships

Making SQL Spatial - Advanced Topics

1
Multi-table Spatial Queries
2
Optimizing your queries
3
Spatial aggregate functions
4
Geometry processing functions Part 1
5
Geometry processing functions Part 2
6
Challenge Scenarios Part 1
7
Challenge Scenarios Part 2

SQL Data Definition Language

1
Creating tables
2
Primary and Foreign Keys
3
Creating indexes
4
Creating custom data types
5
Modifying data structures with the ALTER statement
6
GUI methods for defining data structures

SQL Data Manipulation Language

1
Adding new records with the INSERT statement
2
Adding geometry to a feature with the INSERT statement
3
Modifying data with the UPDATE statement
4
Deleting records
5
Challenge exercise: Putting it all together

SQL Data Control Language

1
Creating Roles with SQL
2
Granting privileges with SQL
3
Example - roles and privileges in pgAdmin4 GUI
4
Example - roles , privileges, and multi-user editing in QGIS

Automation

1
Transactions
2
User defined functions - SQL
3
User defined functions - PL/pgSQL - Introduction
4
User defined functions PL/pgSQL - Branching and looping
5
User defined Functions - PL/pgSQL - Error handling
6
Triggers
7
Pulling it all together - examples

Deploying your database

1
Introduction
2
Registering for a web hosting service
3
Moving the database to a remote server
4
Deploying to the AcuGIS Cloud
5
Performance considerations
6
Strategies for mitigating slow response times

Miscellaneous

1
Backing up and exporting data
2
Where to go next

Bonus Lecture

1
Bonus Lecture: How to get the most from my series of Udemy courses.
You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.6
4.6 out of 5
562 Ratings

Detailed Rating

Stars 5
313
Stars 4
193
Stars 3
39
Stars 2
16
Stars 1
0
7a1ed6e8929b7269bdddc631f2a6fe17

Includes

13 hours on-demand video
Certificate of Completion

About

AulaGEO is a Ge-engineering specialized academy.

Social Network

Udemy Black Friday Sale! Top Courses From $9.99