PostGIS Essentials training
A two day introduction to PostGIS. Prior GIS experience or attendance on the QGIS Essentials course is a pre-requisite for this PostGIS Essentials course. Knowledge of databases and SQL is desirable.
2 days8 CPD points
Getting Started & Practical Exercise
Introduction to PostgreSQL, PostGIS, pgAdminIII, servers, databases and database objects.
Includes best practice and a practical exercise aimed at getting started on interacting with databases through pgAdmin.
Loading, Exporting & Using Spatial Data
Loading and exporting data from PostGIS. Includes the shapefile loader, GIS & ‘Loader’ for OS MasterMap.
Use ‘copy’ for importing non-spatial data. Characteristics of PostGIS spatial data in PostGIS inc the Geometry Columns table.
Basic Structured Query Language (SQL)
An introduction in using basic SQL in PostGIS covering practical use of quotes, wild cards and keywords.
Fundamental constructs such as SELECT, WHERE, COUNT, ORDER BY, GROUP BY operators.
Practical use of joins, views, creating tables, updates, inserts and deletes, sub-queries as well as guidance for working with multiple tables.
Spatial and key geometry functions. WKB / WKB / WKT Geometries, geometry validity, breaking down geometries & Transforming Spatial Reference Systems.
The modules demonstrate the use of spatial and geometric measurement functions covering LENGTH, PERIMETER, AREA and SIMPLIFY.
Spatial Relationships and Joins
Spatial functions for determining the relationships between geometry, for example the st_intersect and st_within functions. Real life examples using these relationships to create spatial joins.
Advanced use of the spatial functions provides a strong practical element in incorporating buffers as well as other functions for spatial aggregation and casting geometry types.
Find a PostGIS course
PostGIS DBA Essentials
This course is a basic introduction to the administration tasks required for a PostGIS database. It covers basic administration tasks as well as a step-by-step guide for PostgreSQL and PostGIS installation in the Windows environment. The course explores the different elements of user access including connecting from a remote server and provides guidance and practical examples on roles and role groups for managing security on the PostgreSQL server.
1 day4 CPD points
Accesing Server and User Management
Explores types of users and groups available within PostgreSQL and the architecture in which a user can gain access to the database and the various different methods for determining access.
Administration and Monitoring
Explores the database server, database objects and events including starting and stopping, server version and uptime. Database folder location, size along wtih logging and monitoring.
Configuration and Performance Tuning
Managing database parameters whether the default or for a session. Performance tuning for spatial data according to your server RAM resources and backing up the database.
Understanding what an index is and how to create one. Using clusters to improve query performance. Locating indexes and using the Vacuum Analyse command.
Writing psql scripts allowing for the altering of tables by adding / removing columns, changing datatypes or moving between schemas and tablespaces.
One of the most important commands is the Vacuum command and when to use. Also covered is the loading of “bulky” data and the impacts this has.
Backup and Recovery
Use of commands such as pg_dump & pg_restore and other backup procedures. Changing database versions and password management.
Issues and further considerations including “what ifs” such as running out of disc space or losing service account permissions.
All PostGIS training is now delivered online
Meet the author
Ian is a member of the GeoTools PSC & GeoServer PMC.
He is the founder and code committer for the GeoTools java library which provides the underpinning spatial tools for GeoServer.
He has extensive experience working with Open Source software (PostGIS, GeoServer, QGIS and others) spanning over 25 years.