PostGIS Training

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 days

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

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

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.

View PostGIS training dates

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

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.

Indexes

Understanding what an index is and 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 and 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 or losing service account permissions.

All PostGIS training is now delivered online

Meet the author

Ian Turton

@ijturton

Ian is a charter member of the OSGeo foundation.

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.