Mastering Postgis

From Fundamentals to Advanced Spatial Analysis

What you'll learn

Introduction to PostgreSQL, PostGIS, and pgAdmin

Step-by-step installation and configuration for a smooth start

Learn how to create and manage databases using both pgAdmin (GUI) and the command line interface (CLI).

Understand how to design and manage spatial and non-spatial tables effectively

Upload and manage Shapefiles,GeoJson data and CSV files in your PostGIS databaseConnect and manage your spatial database using QGIS and GeoServer

Learn key concepts like normalization, primary/foreign keys, and relational models for efficient data structuring

Explore vector geometry, raster, and other spatial data types

Upload Shapefiles using both CLI (psql) and pgAdmin’s Import/Export Wizard

Import GeoJSON data into your spatial database

Load KML/KMZ files using the ogr2ogr utility

Import raster data using raster2pgsql for spatial analysis.

Work with Coordinate Reference Systems (CRS) and SRID functions

Write effective SQL queries for spatial and non-spatial data

Perform inserts, updates, and deletions on spatial records

Use spatial joins and spatial relationship queries to extract meaningful insights

Apply spatial indexing, VACUUM, and ANALYZE for optimized performance

Learn how to boost query performance using GiST (Generalized Search Tree) indexes in PostGIS.

Connect and manage your spatial database using QGIS and GeoServer

Dynamically connect PostGIS with web mapping library to insert, query, and display spatial data on interactive maps

Requirements

No prior experience with spatial databases is needed — this course starts from the basics

Basic knowledge of databases or SQL is helpful but not mandatory

A computer with internet access

Willingness to install free software like PostgreSQL, PostGIS, pgAdmin, GeoServer and QGIS

Description

PostGIS is a powerful spatial extension for PostgreSQL that allows you to store, analyze, and manipulate geographic and geometric data with precision and efficiency. This course offers a complete learning journey through PostGIS—updated with the latest features.You'll start with foundational concepts, gradually building up to advanced spatial queries, performance optimization, and real-world applications. Whether you're managing vector or raster data, creating spatial indexes, or performing proximity analysis, each topic is presented in a clear and practical way.QGIS and GeoServer are also introduced in the course as companion tools to visualize your spatial data and explore query results, helping you bridge the gap between spatial theory and practical implementation.In addition to core PostGIS skills, the course includes a hands-on Web GIS web application built using web mapping library, where you'll learn to dynamically insert and retrieve spatial data from PostGIS. This real-world integration demonstrates how to connect your database to modern, interactive web maps—an essential skill for any aspiring Web GIS developer.No previous experience with spatial databases is required. By the end of the course, you’ll be equipped with the skills and confidence to manage and analyze spatial data effectively using PostGIS in a wide range of geospatial projects.

Overview

Section 1: Course Introduction

Lecture 1 Introduction

Section 2: Introduction to PostgreSQL and PostGIS

Lecture 2 Understanding PostgreSQL_ The Backbone of PostGIS

Lecture 3 PostGIS Overview-Extending PostgreSQL for Spatial Data

Section 3: Understanding Spatial Data

Lecture 4 Exploring Data Types in PostGIS- A Beginner’s Guide

Lecture 5 Introduction to Spatial Data Types in PostGIS

Section 4: Setting Up Your Environment

Lecture 6 Complete Installation of PostgreSQL and PostGIS

Section 5: Importing Spatial Data

Lecture 7 Import Shapefiles in PostGIS Using shp2pgsq and Shapefile Import/Export Manager

Lecture 8 Importing Raster Data with raster2pgsql

Lecture 9 Load GeoJSON Data into PostGIS

Lecture 10 Importing KML and KMZ Files into PostGIS with ogr2ogr

Section 6: Creating and Managing Tables

Lecture 11 Database Creation in PostgreSQL-CLI and pgAdmin Methods

Lecture 12 Creating Tables for Spatial and Non-Spatial Data in PostgreSQL/PostGIS

Lecture 13 Database Normalization-Making Tables Efficient

Section 7: Working with PostGIS and SQL

Lecture 14 Understanding SQL-The Foundation for PostGIS

Lecture 15 Working with SQL in PostGIS Practical Examples

Lecture 16 Understanding Regular and Spatial Joins

Lecture 17 Executing Regular and Spatial Joins in PostGIS Practical Examples

Section 8: Indexing and Performance

Lecture 18 Introduction to Spatial Indexing in PostGIS

Lecture 19 Practical Guide to Creating Spatial Indexes in PostGIS

Section 9: Understanding SRID

Lecture 20 Understanding SRID in PostGIS

Lecture 21 SRID in Action-Practical Examples in PostGIS

Section 10: Spatial Analysis Techniques

Lecture 22 Buffering, Distance, and Proximity Analysis-Core Concepts in PostGIS

Lecture 23 How to Perform Buffering, Distance, and Proximity Analysis in PostGIS

Lecture 24 Advanced PostGIS Techniques-Using ST_Union, ST_Intersection and Other Functions

Lecture 25 Mastering Advanced Spatial Functions in PostGIS-A Hands On Guide

Lecture 26 Combining and Splitting Geometries

Section 11: Routing and Network Analysis

Lecture 27 Getting Started with pgRouting in PostGIS

Section 12: QGIS and PostGIS Integration

Lecture 28 Introduction to QGIS_ A Free and Open-Source GIS Tool

Lecture 29 Installing QGIS_ Step-by-Step Guide

Lecture 30 Integrating PostGIS and QGIS_ Load and Visualize Spatial Data

Section 13: GeoServer and PostGIS Integration

Lecture 31 Introduction to GeoServer_ A Powerful Geospatial Server

Lecture 32 GeoServer Installation_ Step-by-Step Guide

Lecture 33 Loading PostGIS Data into GeoServer_ A Practical Guide

Section 14: Building a Web GIS Application with PostGIS Integration

Lecture 34 Capture, Store, and Display Spatial Data with OpenLayers and Postgis

Section 15: Troubleshooting and Reference

Lecture 35 PostGIS Troubleshooting & Debugging Guide

Section 16: Miscellaneous

Lecture 36 PostGIS Cheat Sheet

Section 17: Assessment

Lecture 37 Quiz

Section 18: Closure

Lecture 38 Closure

Beginners and Advanced Learners looking to build a strong foundation in spatial databases using PostGIS.,GIS Professionals and Analysts who want to enhance their skills by learning how to store, query, and manage spatial data efficiently.,QGIS Users who wish to connect their desktop GIS projects with a spatial database for more powerful data management and analysis.,Developers and Data Engineers interested in adding geospatial capabilities to their applications.,Students and Researchers in geography, urban planning, environmental science, or related fields who need practical knowledge of spatial databases.,Anyone Curious About Geospatial Technology, Web GIS, or how to build spatial apps using open-source tools.,The course also introduces GeoServer, teaching learners how to publish and share spatial data on the web, making it ideal for those interested in Web GIS and spatial web services.