Introduction to Spatial Databases with PostGIS and QGIS 3

Install PostGIS on Windows to enable spatial features in PostgreSQL, using Stack Builder to add the PostGIS bundle, configure a database and password, then verify the extension with pgAdmin.

Install QGIS 3.x by downloading the installer, run the setup wizard, accept the license, and finish; Windows, Mac, and Linux offer steps with offline use and no license manager needed.

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.

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.

Explore how to access PostGIS from the command line, pgAdmin, QGIS, and ArcGIS, including loading shapefile and raster data, running SQL queries, and visualizing spatial layers.

Explore how a database instance runs in its own memory with separate data, and how databases, schemas, and tables organize information, including spatial and non-spatial data.

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.

Learn how the where clause filters results by evaluating predicates on each record. Use comparisons, like, in, not equal, and date handling to craft queries in PostGIS and QGIS.

Explore aggregate functions like count, average, minimum, and maximum, plus statistical measures such as standard deviation, variance, covariance, and correlation, using expressions and group by for per-group summaries.

Explore multi-table queries in a relational database, learning how to join tables on common fields, understand one-to-one, one-to-many, and many-to-many cardinalities, and use primary and foreign keys with referential integrity.

Master inner and outer joins, including left, right, full, and cross joins, and learn how to handle unmatched records, nulls, and one-to-many relationships in multi-table queries.

The lecture explains PostgreSQL and SQL data types, covering integers, real and numeric, booleans, fixed and variable length strings (char, varchar, text), and date/time types (date, time, timestamp, interval).

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.

Explain how the from clause drives a row-by-row loop in SQL queries, evaluating expressions and functions for each record, including constants and random values.

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.

Explore PostgreSQL functions and operators, including logical, comparison, string, math, and date/time tools, and learn to use the documentation to discover inputs and outputs.

Master the case statement in SQL by evaluating when predicates and returning the matching expression, with an else fallback, and aliasing as location for use in select or aggregate queries.

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.

Explore the PostGIS vector geometry model, including points, linestrings, polygons, and coordinate dimensions. Relate features and attributes to the Open Geospatial Consortium simple features for sequel specification.

Explore how spatial reference IDs define coordinate reference systems, linking geographic or projected coordinates to data in PostGIS and QGIS, enabling accurate transformations and consistent geometry handling.

Explore how the geometry field turns a non-spatial table into a feature class by storing geometry, geometry type, spatial reference id, and coordinates in binary form.

Explore geometry vs geography data types in PostGIS, comparing flat plane calculations to sphere-based measurements, and learn when to use each for accuracy, performance, and available spatial functions.

Explore PostGIS geometry functions to inspect and manipulate geometries, including geometry type, dimension, SRID, collections, interior rings, validity checks, and transforming between coordinate reference systems.

Explore how to measure geometries and distances in PostGIS and QGIS, using length, area, and distance functions for geometry and geography objects, accounting for curvature with spheroids.

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.

Explore testing spatial relationships with PostGIS predicates, including intersects, contains, and within, and compare geometry vs geography, spatial indexes, and performance considerations.

Explore multi-table spatial queries using spatial joins, combining spatial and non-spatial data across three tables, and compare predicates like intersects and distance to optimize performance.

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.

Learn to summarize spatial data with aggregate functions in PostGIS, counting nests per pipeline, computing average distances, and producing compact project-wide summaries through group by queries.

Explore geometry processing functions in PostGIS, focusing on ST_Buffer, radii in the spatial reference, geography vs geometry handling, including field-based and multi-buffer via union all.

Explore geometry processing functions in PostGIS and QGIS, part 2, including intersection, union, difference, symmetric difference, and convex hull, with emphasis on set theory, buffers, and geometry versus geography.

Learn practical spatial queries in PostGIS and QGIS 3 to measure raptor nest impacts on projects and burning habitat impacts on linear projects.

Explore real-world spatial queries in PostGIS and QGIS 3 to measure habitat impact, rank projects by total length, and dissolve overlaps to produce accurate area totals, visualized in QGIS.

Learn to create tables with data definition language, define fields and types, apply constraints such as primary and foreign keys, and manage alteration, dropping, indexing, and geometry columns with PostGIS.

Explore primary keys as unique identifiers, auto-incrementing serial keys, and constraints, then master foreign keys with a lookup table and sequences using nextval and setval.

Create and use non-spatial and spatial indexes in PostgreSQL, including unique and primary key indexes, GiST for geometry, and vacuum analyze to optimize query performance.

Create and use domains in Postgres to enforce data constraints across multiple tables, such as postal codes, emails, and rating ranges, with check constraints.

Modify data structures with the alter table statement by adding, dropping, or renaming columns and constraints, including foreign keys and cascade behavior.

Explore gui methods for defining data structures by creating tables in pgadmin and Qgis, specifying columns, data types, primary keys, and geometry with spatial indexes.

Learn to insert new records with insert into, specifying fields or full values, and using defaults. Explore multi-row inserts and select subqueries, with attention to constraints and the now() timestamp.

Learn to insert spatial data into a geometry field by casting well-known text to geometry and using ST_MakePoint with ST_SetSRID. Explore converting to multi geometries and input options like GeoJSON.

Modify existing data with the update statement using set, where, and case expressions, and learn to update from a lookup table via subqueries in postgis and qgis.

Learn how to delete records safely using a where clause, understand when to truncate or drop a table, and identify records by primary key in spatial databases.

Apply PostGIS and QGIS techniques to convert an Excel data table into a spatial table, create geometry, merge datasets, and fix primary key and sequence issues.

Create roles in sql to control access in spatial databases, using group and user roles with login or no-login options, privileges, and inheritance.

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.

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.

Explore role-based access and multi-user editing in QGIS with PostGIS, showing how viewer, field personnel, manager, admin, and client permissions constrain or enable editing, deleting, and adding spatial data.

Discover how transactions protect database integrity by grouping statements between begin and commit, rolling back if any statement fails. Apply these concepts to reliable data updates.

Master writing user defined functions in SQL to encapsulate complex logic and return scalars or records. Use named parameters, call functions in selects, and compare approaches for readability and performance.

Delve into pl/pgsql, the procedural language for PostgreSQL, and master variable declarations, begin-end blocks, and exception handling to create powerful server-side user defined functions.

Explore pl/pgsql branching and looping: if-then-else, case statements, and various loops (for, while, unconditional) to process records from sequence queries.

Master error handling in PL/pgSQL by using exception blocks in user defined functions, testing error codes with when, and returning safe values on division by zero.

Explore how trigger functions automate database logic in PostGIS, enabling data validation, audit logging, and automatic recalculations, with timing options before, after, or instead of and row versus statement triggers.

Demonstrate trigger functions to validate Raptr survey data, auto-fill creation and update fields, and generate audit logs for inserts, updates, and deletes in the database.

Explore deploying a spatial database on a server to enable multi-user access, and compare hosting options—from web hosting and dedicated servers to cloud hosting with AWS, security, and backups.

Learn how to register for a web hosting service, choose Linux with Apache, select a domain, and set up PostgreSQL databases and users, laying groundwork for spatial databases with PostGIS.

Move the database from localhost to a remote server by backing up the entire public schema as plaintext sql, then restoring on the remote server; sequences may need rebuilding.

Explore deploying a PostGIS database to the AcuGIS Cloud, compare cloud hosting options, configure remote databases, install extensions, load data, and access it via QGIS and web clients.

Assess performance implications of remote databases, compare hosting options like ETU hosting and Accuity, and outline bottlenecks, data transfer speeds, and strategies to improve PostGIS-based workflows.

Minimize data transfer by simplifying geometries, reducing coordinates and attributes, and using scale-dependent visibility. Employ local–remote data with master–slave replication to speed redraws and keep edits synchronized.