A Comprehensive Guide to PostGIS and Spatial Queries

Name: A Comprehensive Guide to PostGIS and Spatial Queries
Rating: 4.4 (90 reviews)

Unlock the Power of Spatial Databases: Master PostGIS and Harness Spatial Queries for Geospatial Excellence.

Created byShoaib Shahzad Obaidi

Last updated 2/2024

English

What you'll learn

Understanding Spatial Data
Setting up PostgreSQL and PostGIS
Spatial Data Management
Spatial Querying with SQL and Geospatial Analysis

Course content

2 sections • 43 lectures • 8h 18m total length

Introduction - Course Overview2:13
What are Postgres and PostGIS16:59

Announcement0:07
Getting Started with PostgreSQL10:04
Creating database and import tables using PostGIS Loader11:05
Explaining the database & SQL terms11:14
Query execuation and specifying statements10:40
SQL Queries - Statements (Continued 1).mp410:58
Master SQL select statements by retrieving specific columns such as code, class, and name, ordering by one or two columns in asc or desc, and measuring name length.
SQL Queries - Statements (Continued 2)11:09
SQL Queries (OR - AND Operators)12:16
SQL Queries (OR-AND-LIMIT)10:07
Master how or and logical operators evaluate left and right conditions to yield true or false results. See sorting by code, five-record limits, and null value handling in examples.
SQL Queries (IN, NOT IN-IS null)7:06
SQL Queries (Alter Statements)11:13
SQL Queries (Delet - Update)8:49
Mathmatical Statements and Functions10:24
Function Based Queries (Part 1)10:12
Master function based queries in PostgreSQL by cleaning a table’s schema—renaming columns and converting data types—then use count and distinct to analyze camps and zones in Muzdalifa.
Function Based Queries (Part 2)10:22
Function Based Queries (Part 3)13:35
SQL Subquery (Part 1)10:26
Master subqueries in SQL, including inner and nested queries, and learn to use single-row and multi-row operators with practical examples of maximum, minimum, average, and second minimum values.
SQL Subquery (Part 2)10:05
SQL Subquery (Part 3)7:02
CRS, EPSG, SRID (Spatial Functions Part 1)16:08
Explore geographic and projected coordinate systems, crs components, and the role of epsg and srid in postgis, with examples like wgs 84, utm, and robinson projections.
Finding EPSG Code in ArcGIS Pro, QGIS and postgresql - basemap (Spatial Fx)16:16
Creating Spatial Table & Inserting Spatial Values into it (Spatial Functions19:01
Spatial Functions Continued (Part 4)16:27
Importing files from computer to the PostGIS DB (Spatial Functions Part 5)15:57
Spatial Functions Continued (Part 6)15:25
Conversion Functions (Spatial Functions Part 7)13:06
Spatial Aggregate Functions (Spatial Functions Part 8)16:56
Spatial Aggregate Functions (Spatial Function Part 9)15:52
Spatial Aggregate Functions (Spatial Function Part 10)5:51
Spatial Join (Part 1)15:59
Spatial Join (Part 2)15:35
Spatial Join (Part 3)13:35
Spatial Constructing Functions14:06
Advanced Spatial Query (Part 1)18:17
Advanced Spatial Query (Part 2)14:09
PostgreSQL Triggers (Part 1)16:15
Master spatial queries in PostGIS by understanding geographic vs projected CRS functions, and learn how PostgreSQL triggers use PL/pgSQL to track edits with a history table.
PostgreSQL Triggers (Part 2)15:41
PostgreSQL Triggers (Part 3)7:07
PostgreSQL Database Back & Restore7:38
Learn how to back up and restore PostgreSQL databases using pgAdmin, configure the binary path, and manage backups for reliable PostGIS workflows.
Course Conclusion2:00
Quick Quizz
For Further Updates0:45

Requirements

No prerequisites required! All you need is a stable internet connection to download and the tool and view the basemaps and a PC or laptop running the Windows operating system.

Description

Are you ready to dive into the world of PostGIS and spatial queries?

This comprehensive course is designed to equip you with the knowledge and skills to harness the potential of geographic information and spatial data analysis. From building and managing databases to executing complex SQL queries, you'll gain hands-on experience in a wide array of topics.

The course begins with an introduction to PostgreSQL, followed by lessons on creating databases and importing tables using PostGIS loader. You'll delve into SQL terminologies and learn to execute and specify SQL statements effectively. Explore various SQL queries, including statements, OR-AND operators, and query limits.

Next, discover the magic of spatial functions, mathematical statements, and CRS, EPSG, and SRID concepts. You'll learn to create spatial tables, insert spatial values, and import files into the PostGIS database seamlessly. Uncover the potential of spatial aggregate functions, spatial joins, and constructing functions for advanced data analysis.

The course also covers essential PostgreSQL features, such as triggers, database backup, and restore. Additionally, you'll find valuable insights on finding EPSG codes in ArcGIS Pro, QGIS, and PostgreSQL base map.

Prepare to unlock the full potential of geospatial data analysis and visualization. Whether you're a GIS enthusiast, a data scientist, or a professional in a related field, this course will equip you with essential skills to leverage spatial information effectively.

Join us on this journey, and let's delve into the world of PostGIS and spatial queries together!

Who this course is for:

Database Developers: Those who already have a simple understanding of database concepts and SQL but want to expand their skills to include spatial data handling and analysis.
GIS Professionals: Geographical Information System (GIS) specialists who want to integrate their expertise with the power of PostgreSQL and PostGIS to manage and analyze spatial data more efficiently.
Data Analysts: Professionals seeking to enhance their analytical capabilities by incorporating location-based insights into their data-driven decision-making processes.
Software Engineers: Developers interested in integrating spatial functionalities into their applications or services using PostgreSQL and PostGIS.
Researchers and Academics: Individuals from various academic disciplines, such as geography, environmental science, urban planning, and more, who wish to leverage spatial data for their research and analysis.
Hobbyists and Enthusiasts: Those who have a personal interest in spatial data and want to explore its applications and potential in various projects or hobbies.

A Comprehensive Guide to PostGIS and Spatial Queries

What you'll learn

Explore related topics

Course content

Postgres Installation and PostGRES Configuration2 lectures • 19min

Hands-on Spatial, Non-Spatial Queries and Aggregate functions41 lectures • 7hr 59min

Requirements

Description

Who this course is for: