PostGIS Training

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.

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.