PostGIS Training

PostGIS Essentials

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 days

8 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

Further SQL

Practical use of joins, views, creating tables, updates, inserts and deletes, sub-queries as well as guidance for working with multiple tables.

Geometry Accessors

Spatial & key geometry functions. WKB / WKB / WKT Geometries, geometry validity, breaking down geometries & Transforming Spatial Reference Systems.

Measurements

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.

GeoProcessing Functions

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.

Training Schedule

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 points

4 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.

Indexes

Understanding what an index is & how to create one.  Using clusters to improve query performance.  Locating indexes and using the Vacuum Analyse command.

Administration Tasks

Writing psql scripts allowing for the altering of tables by adding / removing columns, changing datatypes or moving between schemas and tablespaces.

Maintenance Tasks

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.

Further Considerations

Issues and further considerations including “what ifs” such as running out of disc space, losing service account permissions.

Training Schedule

Primary Trainer

Ian Turton

@ijturton

Ian is a charter member of the OSGeo foundation.

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.