PostGIS Training

Course Outlines

Astun offers two PostGIS courses; a two-day introduction to the essentials, for which prior attendance on the QGIS Essentials course or equivalent GIS experience, is a pre-requisite and a one-day Database Administration course, attendees for which must have first completed the Essentials course. 

PostGIS Essentials - 8 CPD points

1. Introduction and getting started

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.

2. Loading, exporting and use spatial data

Use different methods for loading data and exporting data into and from PostGIS. Includes the shapefile loader and GIS as well as 'Loader' for importing OS MasterMap. Explores use of copy command for importing non-spatial data. The module looks in more detail into the characteristics of spatial data in PostGIS including the Geometry Columns table.

3. Basic Structured Query Language (SQL)

An introduction in using SQL in PostGIS covering practical use of SELECT, WHERE, COUNT, ORDER BY, GROUP BY, operators, user of quotes and wild cards.

4. Further SQL

Builds on module 3 introducing the practical use of joins, views, creating tables, updates, inserts and delete. The module will also start to make use of sub-queries as well guidance for working with multiple tables.

5. Geometry accessors and measurements

Introduces the use of spatial functions in PostGIS and explores key functions for understanding geometry. These include WKB Geometries, WKT Geometries, validity of geometry, functions for breaking down geometries and Transforming Spatial Reference Systems. 

6. Measurements

The modules demonstrate the use of spatial and geometric measurement functions.

7. Spatial relationships and joins

Looks at the use of spatial functions for determining the relationships between geometry, for example the st_intersect and st_within functions. Through use of real life examples the module uses these relationships to create spatial joins.

8. Geoprocessing functions

Advanced use of the spatial functions provides a strong practical element in incorporating buffers as well as other functions for spatial aggregation.

 

PostGIS Database Administration (1 day) - 4 CPD points

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.

Topics include:

1. Accessing the Server and User Management
2. Administration and Monitoring
3. Configuration Controls and Performance Tuning
4. Indexes
5. Database Administration Tasks
6. Maintenance Tasks
7. Backup and Recovery
9. Issues and Further Considerations