Introduction to Spatial Databases with PostGIS and QGIS 3 from Udemy

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

What's inside

Learning objectives

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

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

Syllabus

Introduction

What can a spatial database do for you?

What is a spatial database exactly?

Where does a spatial database live?

What is SQL and why would you want to use it?

Installing software and loading data

Installing PostGIS on your local computer

Installing QGIS

Loading spatial data into PostGIS

Loading non-spatial data into PostGIS

Accessing PostGIS from the commandline, the pgAdmin GUI, QGIS, and ArcGIS

Organization of a database

Data Query Language - SQL queries for non-spatial data

The SQL SELECT statement

The WHERE clause

Aggregate functions and the GROUP BY clause

Multi-table Queries Part 1

Multi-table Queries Part 2

Data Types in PostgreSQL Part 1

Data Types in PostreSQL Part 2

The FROM clause implies a loop

SQL Expressions and Functions Part 1

SQL Expressions and Functions Part 2

The CASE Statement

SQL Views

Making SQL Spatial - Introduction

The PostGIS Vector Geometry model

The Spatial Reference ID

The Geometry Field

Geometry vs.Geography data type

Spatial Queries - Working with Geometries

Spatial Queries - Measurements

Spatial Queries - Accessing the geometry

Spatial Queries - Testing spatial relationships

Making SQL Spatial - Advanced Topics

Multi-table Spatial Queries

Optimizing your queries

Spatial aggregate functions

Geometry processing functions Part 1

Geometry processing functions Part 2

Challenge Scenarios Part 1

Challenge Scenarios Part 2

SQL Data Definition Language

Creating tables

Primary and Foreign Keys

Creating indexes

Creating custom data types

Modifying data structures with the ALTER statement

GUI methods for defining data structures

SQL Data Manipulation Language

Adding new records with the INSERT statement

Adding geometry to a feature with the INSERT statement

Modifying data with the UPDATE statement

Deleting records

Challenge exercise: Putting it all together

SQL Data Control Language

Creating Roles with SQL

Granting privileges with SQL

Example - roles and privileges in pgAdmin4 GUI

Example - roles , privileges, and multi-user editing in QGIS

Automation

Transactions

User defined functions - SQL

User defined functions - PL/pgSQL - Introduction

User defined functions PL/pgSQL - Branching and looping

User defined Functions - PL/pgSQL - Error handling

Triggers

Pulling it all together - examples

Deploying your database

Registering for a web hosting service

Moving the database to a remote server

Deploying to the AcuGIS Cloud

Performance considerations

Strategies for mitigating slow response times

Miscellaneous

Backing up and exporting data

Where to go next

Bonus Lecture

Bonus Lecture: How to get the most from my series of Udemy courses.

Good to know

Know what's good

, what to watch for

, and possible dealbreakers

Develops knowledge of and proficiency in database administration to keep a database operating smoothly

Explores how to optimize queries for the best performance, including spatial aggregate functions, geometry processing functions, and more

Examines data control to manage user access with the tools built-in to PostGIS

Teaches how to work with raster data in PostGIS, which is commonly used in fields like remote sensing and image processing

Builds a strong foundation in SQL, both spatial and non-spatial, for querying geospatial concepts

Focuses on the latest versions of PostgreSQL, PostGIS, and QGIS, ensuring up-to-date knowledge and skills

Activities

Be better prepared before your course. Deepen your understanding during and after it. Supplement your coursework and achieve mastery of the topics covered in Introduction to Spatial Databases with PostGIS and QGIS 3 with these activities:

Practice writing SQL queries to manipulate geospatial data

Show steps

Practicing writing SQL queries to manipulate geospatial data will help you to develop your proficiency in this skill.

Browse courses on SQL

Show steps

Find a set of practice exercises or create your own.
Write SQL queries to solve the exercises.
Check your answers to make sure that they are correct.

Meet with a peer or group for review and Q&A

Show steps

Meeting with a peer or group for review and Q&A will allow you to clarify your understanding of the course material and get support from others.

Show steps

Find a peer or group to meet with.
Decide on a time and place to meet.
Prepare questions and discussion points.
Meet with your peer or group and discuss the course material.

Follow a tutorial to build a web mapping application using PostGIS

Show steps

Following a tutorial to build a web mapping application using PostGIS will help you to gain practical experience and apply your knowledge of the course material.

Browse courses on PostGIS

Show steps

Find a tutorial that is appropriate for your skill level.
Follow the instructions in the tutorial carefully.
Test your application and make sure that it works as expected.

Four other activities

Expand to see all activities and additional details

Show all seven activities

Create a blog post or article

Show steps

Writing a blog post or article will help you to consolidate your knowledge of the course material and identify areas where you need further study.

Browse courses on Geospatial Data

Show steps

Choose a topic for your post or article that is related to the course material.
Research your topic thoroughly.
Write a clear and concise post or article that is informative and engaging.
Publish your post or article online and share it with others.

Create a presentation on a topic related to PostGIS

Show steps

Creating a presentation on a topic related to PostGIS will help you to deepen your understanding of the material and improve your communication skills.

Browse courses on PostGIS

Show steps

Choose a topic for your presentation that is related to the course material.
Research your topic thoroughly.
Create a clear and concise presentation that is informative and engaging.
Practice your presentation.
Deliver your presentation to an audience.

Participate in a hackathon or data science competition that uses PostGIS

Show steps

Participating in a hackathon or data science competition that uses PostGIS will challenge you to apply your skills and knowledge in a practical setting.

Show steps

Find a hackathon or data science competition that uses PostGIS.
Form a team or work individually.
Develop a solution to the hackathon or competition challenge.
Submit your solution.

Contribute to an open-source PostGIS project

Show steps

Contributing to an open-source PostGIS project will allow you to make a meaningful contribution to the community and gain experience in working on real-world projects.

Browse courses on Open Source

Show steps

Find an open-source PostGIS project that you are interested in contributing to.
Fork the project and make your changes.
Submit a pull request to the project.

Career center

Learners who complete Introduction to Spatial Databases with PostGIS and QGIS 3 will develop knowledge and skills that may be useful to these careers:

GIS Analyst

GIS Analysts use Geographic Information Systems (GIS) to analyze and visualize spatial data. They use this data to solve problems and make informed decisions. This course is very relevant to GIS Analysts as it will provide them with the technical knowledge and skills they need to perform spatial analysis.

See salaries and explore the career path for GIS Analyst

Geospatial Analyst

Geospatial Analysts use geospatial data to solve problems and make informed decisions. They may work in a variety of industries, including environmental science, urban planning, and transportation. This course may be very helpful for Geospatial Analysts who want to learn more about spatial databases, as it will provide them with the skills they need to manage and analyze this data.

See salaries and explore the career path for Geospatial Analyst

Geospatial Developer

Geospatial Developers use geospatial data to create and develop applications. They may work in a variety of industries, including environmental science, urban planning, and transportation. This course may be helpful for Geospatial Developers who want to learn more about spatial databases, as it will provide them with the skills they need to build applications.

See salaries and explore the career path for Geospatial Developer

Hydrologist

Hydrologists study the movement and distribution of water on, above, and below the surface of the earth. They use spatial data to analyze and visualize water resources. This course may be helpful for Hydrologists who want to learn more about spatial databases, as it will provide them with the skills they need to manage and analyze this data.

See salaries and explore the career path for Hydrologist

Land Surveyor

Land Surveyors measure and map the earth's surface. They use spatial data to create maps and other documents that can be used for a variety of purposes, such as planning and construction. This course may be helpful for Land Surveyors who want to learn more about spatial databases, as it will provide them with the skills they need to manage and analyze this data.

See salaries and explore the career path for Land Surveyor

Remote Sensing Analyst

Remote Sensing Analysts use satellite imagery and other data to study the earth's surface. They use spatial data to analyze and visualize this data. This course may be helpful for Remote Sensing Analysts who want to learn more about spatial databases, as it will provide them with the skills they need to manage and analyze this data.

See salaries and explore the career path for Remote Sensing Analyst

Transportation Planner

Transportation Planners plan and design transportation systems. They use spatial data to analyze and visualize transportation data. This course may be helpful for Transportation Planners who want to learn more about spatial databases, as it will provide them with the skills they need to manage and analyze this data.

See salaries and explore the career path for Transportation Planner

Map Technician

Map Technicians create and maintain maps. They use spatial data to create maps that can be used for a variety of purposes, such as navigation and planning. This course may be helpful for Map Technicians who want to learn more about spatial databases, as it will provide them with the skills they need to manage and analyze this data.

See salaries and explore the career path for Map Technician

Urban Planner

Urban Planners plan and design cities and towns. They use spatial data to analyze and visualize urban data. This course may be helpful for Urban Planners who want to learn more about spatial databases, as it will provide them with the skills they need to manage and analyze this data.

See salaries and explore the career path for Urban Planner

Database Administrator

Database Administrators ensure database systems run smoothly by performing maintenance, backups, repairs, and upgrades. They may also provide guidance and support to other database users, including developers and analysts. A course on spatial databases, like this one, would teach Database Administrators about the tools specific to this kind of database, such as testing spatial relationships, accessing geometry, and writing spatial queries.

See salaries and explore the career path for Database Administrator

Planning Technician

Planning Technicians assist with planning projects, such as land use planning and transportation planning. They use spatial data to create maps and other documents that can be used for a variety of purposes, such as planning and construction. This course may be helpful for Planning Technicians who want to learn more about spatial databases, as it will provide them with the skills they need to manage and analyze this data.

See salaries and explore the career path for Planning Technician

Data Analyst

Data Analysts have the knowledge and skills to collect, clean, and analyze data. They use this data to identify trends and patterns that can help businesses make informed decisions. This course may be helpful for Data Analysts wishing to work with spatial data, as it will provide them with the skills they need to analyze this data.

See salaries and explore the career path for Data Analyst

Web Developer

Web Developers design and develop websites. They use spatial data to create maps and other interactive features for websites. This course may be helpful for Web Developers who want to learn more about spatial databases, as it will provide them with the skills they need to use this data in their work.

See salaries and explore the career path for Web Developer

Data Engineer

Data Engineers design, build, and maintain the systems and infrastructure that store and process data. They work with data scientists and other data professionals to ensure that data is available, reliable, and secure. This course may be helpful for Data Engineers who want to work with spatial data, as it will provide them with the skills they need to design and build systems for this type of data.

See salaries and explore the career path for Data Engineer

Writer

Writers create content for a variety of purposes, such as marketing, journalism, and technical writing. They may use spatial data to create maps and other visuals to illustrate their writing. This course may be helpful for Writers who want to learn more about spatial databases, as it will provide them with the skills they need to use this data in their work.

See salaries and explore the career path for Writer