PostgreSQL and PostGIS - Introduction

This introductory course teaches the basics of the popular and widely-used open-source database software PostgreSQL and its geospatial data extension PostGIS. This combination provides powerful capabilities for effectively storing, organizing, and querying large volumes of geospatial data. PostGIS databases are supported by a wide range of GIS applications, facilitating data access and exchange. The course starts with an introduction to relational database systems and then goes into the extensive features of PostgreSQL. You will learn how to access the database from different GIS systems (e.g. QGIS, ArcGIS) and which settings and adjustments are useful to optimize the database for your purposes.

• Database structures of relational databases, normal forms
• Introduction to SQL (CREATE, SELECT, ALTER, UPDATE, INSERT, DELETE, etc.)
• Database creation and administration using SQL and pgAdmin
• Data import, export and backup
• Database integrity using constraints
• Complex database queries (subqueries, aggregate functions, joins)
• User management in PostgreSQL
• Functions and triggers using SQL and PL/pgSQL
• GIS data in PostgreSQL with the PostGIS extension
• Geoprocessing with SQL using PostgreSQL / PostGIS as an example
• PostgreSQL / PostGIS and QGIS: data visualization, editing, import and export
• QGIS as database management system for PostgreSQL
• Optional: Raster data with PostgreSQL / PostGIS