A two day introduction to PostGIS. Prior GIS experience or attendance on the QGIS Essentials course is a pre-requisite. 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 & a practical exercise aimed at getting started on interacting with databases through pgAdmin.
Loading, Exporting & Using Spatial Data
Loading & 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 & 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 & 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.
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 day 4 CPD points4 CPD points
Accesing Server & 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 & Monitoring
Explores the database server, database objects and events including starting & stopping, server version & uptime. Database folder location, size along wtih logging and monitoring.
Configuration & 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 & 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 & 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, losing service account permissions.
A member of the GeoTools PSC & GeoServer PMC.
A founder & code committer for 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.