PostgreSQL geo-spatial data management with PostGIS

• Successfully install and configure PostGIS.
• Use and apply spatial data types and indices.
• Use complex spatial functions to analyse and process data.
• Gain practical experience in handling spatial data sets.

Target audience

• Database Administrator
• GIS Analysts
• Developers

Prerequisites

Basic knowledge of geodata:

• Participants should have a basic understanding of geographic information systems (GIS) and spatial data. This includes knowledge of different types of geospatial data, coordinate systems and spatial relationships.

Familiarity with PostgreSQL:

• A basic knowledge of PostgreSQL is required, especially with regard to database structures, queries and basic SQL skills. Participants should be familiar with the PostgreSQL database environment.

Recommendation: Participation in the course ‘PostgreSQL Administration & Performance Tuning’:

• It is recommended to complete the PostgreSQL Administration and Performance Tuning course beforehand. This ensures that participants have the necessary administrative skills and knowledge.

What you will learn

• Proficiency in the installation and maintenance of PostGIS: Students will be able to install, configure and regularly maintain PostGIS in PostgreSQL environments to ensure system performance and stability.
• Competence in using spatial data types and indices: Ability to identify and efficiently use various spatial data types, such as Geometry and Geography, and implement GIST, SP-GIST, and BRIN indexes, to optimise query speed.
• Demonstrate advanced skills in the application of spatial functions: Master the use of a variety of spatial functions for data analysis, including topological relationships, measurement functions and special functions for geospatial data processing.
• Practical experience in processing and validating spatial data: Ability to import, validate and clean spatial data to ensure that data integrity and quality is maintained.
• Competence to analyse and visualise spatial data: Learn to analyse spatial data and effectively present the results, using appropriate visualisation techniques to gain insights from the data and communicate these clearly.
• Architecture and functionality of PostGIS: Gain knowledge of the internal architecture of PostGIS, including the integration of spatial data types and indexes into the PostgreSQL database, and their interaction with other GIS tools and applications.
• Application of spatial functions for data analysis: Become familiar with the various spatial functions available in PostGIS and understand how these functions can be used to solve complex geographic problems.

Course content

Module 1: Introduction to PostGIS and spatial data processing

• Introduction to PostGIS and its importance in spatial data processing.
• Installation and configuration of PostGIS in a PostgreSQL environment.
• Overview of tools for import, export and ETL (Extract, Transform, Load).

Module 2: Spatial data types, indices and projections

• Introduction to the various spatial data types:
  • Geometry (2D and 3D)
  • Geography (geodetic data)
  • Raster
• Introduction to projections and transformations:
  • Using functions such as ST_Transform, to project spatial data into different coordinate systems.
  • Introduction to the use of SRIDs (Spatial Reference IDs) for the definition of projections.
• Understanding and implementation of spatial indices:
  • GIST (Generalised Search Tree)
  • SP-GIST and BRIN (Block Range INdex)

Module 3: Raster data processing

• Introduction to the raster data type and its properties
• Dealing with raster files:
  • Importing raster data (e.g. GeoTIFF).
  • Management of raster data in PostGIS.
  • Processing of raster data.

Module 4: Spatial functions

• Introduction to the core functions of PostGIS:
  • Output, construction, access and setter functions.
  • Measurement, composition, decomposition and simplification functions.
• Application of topological functions:
  • Bounding boxes, equality checks and spatial relationships.
  • Spatial joins for linking data sets.
• Special functions for processing and analysis:
  • Spatial aggregation, clipping, splitting, tessellation, segmentation, as well as translation, scaling and rotation.

Module 5: Practical application and exercises

• Introduction to the data set and practical exercises:
  • Import and export of spatial and raster data.
  • Validation and correction of spatial and raster data.
  • Analysis of spatial data to gain insights.

Optional, not included by default

Module 6: Custom data sets

• Bring your own data for
  • Import
  • Analysis
  • Visualisation

Course materials and environment

• We will provide access to a dedicated tab environment and virtual machines running a Linux distribution.
• Upon completion of the course, participants will receive a PDF of the slide deck that was used during the training sessions.
• All scripts and configuration files present on the virtual machines during training will be available for download and use.