Day 1
- Welcome
- Introduction
- Background and history of spatial databases, PostgreSQL and PostGIS
- Explanation of relational database management systems
- advantage of a database over files such as shapefiles
- applications that support PostGIS
- real-world implementations of PostGIS
- comparison with proprietary spatial database solutions
- Installation of PostgreSQL, pgAdmin, PostGIS and QGIS
- on Windows, Linux or OS X
- Creating a Spatial Database
- create a new database and spatially enable it with PostGIS
- Loading spatial data
- PostGIS shapefile loading tool
- QGIS DBManager
- other tools
- Overview of the exercise data
- Simple SQL
- Everything in PostGIS, is done with SQL (structured query language) so if you don't know any, this will give you the basics. All databases use SQL so it's a critical skill
- Simple SQL Exercises
- Geometries
- Understanding OGC simple feature geometries and how to store and manipulate them in the database
- Geometry Exercises
- Practising geometry functions
- Spatial Relationships
- Functions to test and describe relationships, such as Equals, Intersects, Within, etc. If you've done GIS you know how to do these in a desktop GIS - now do them in a database!
- Spatial Relationships Exercises
- Practising spatial relationship functions
- Spatial Joins
- one of the core abilities in a GIS, doing spatial joins in a database is incredibly flexible and powerful.
- Spatial Joins Exercises
- Spatial Indexing
- spatial indexing speeds up queries of spatial data
Day 2
- Projecting Data
- Transforming geometries between different coordinate reference systems.
- Projection Exercises
- Geography
- store your data as long-lat (unprojected) yet do very accurate spatial operations using spherical geometry (on the WGS84 spheroid)
- Geometry Constructing Functions
- Functions that result in new geometries, e.g.
- Buffer
- Centroid
- Intersection
- Union (aka dissolve or merge)
- More Spatial Joins
- a more in-depth look at spatial joins, incorporation other functions covered since the first spatial join section
- loading and doing joins with non-spatial tables
- Validity
- Testing the validity of geometries
- Equality
- a more in-depth look at what equality between geometries means
- Linear Referencing
- reference features based on their position along a line instead of using x,y or polar coordinates.
- Dimensionally Extended 9-Intersection Model
- understanding, testing and exploiting all the possible relationships between geometries
- Clustering on Indices
- database clustering is a performance-enhancing tool that physically puts related data close together on disk
- 3-D
- working with 3D geometries in PostGIS
- Nearest-Neighbour Searching
- never mind the distance, what is the nearest? More complicated than you think. PostGIS solves it neatly
- Tracking Edit History using Triggers
- use standard PostgreSQL triggers to keep a record of who created/edited/deleted a record
- Advanced Geometry Constructions
- learn some advanced SQL functions and approaches to solve some tricky problems
- Tuning PostgreSQL for Spatial
- PostgreSQL as a RDBMS can be and is used to store anything hence its default configuration is for generic performance. Spatial data poses unique challenges - learn how to configure the database for optimum performance
- PostgreSQL Security
- How to protect the integrity and security of your data and database
- authentication and roles
- database connections
- encryption
- PostgreSQL Schemas
- schemas are like folders - how and why to use schemas
- PostgreSQL Backup and Restore
- Software Upgrades
- How to handle upgrades of PostgreSQL or PostGIS
Assessment is based on successful completion of the exercises interspersed among the teaching modules.
QGIS is used extensively throughout the course to visualise results, as a SQL client (via the DB Manager) and to load data into PostGIS. pgAdmin and other clients are also introduced. SQL commands written during the course are saved by the student as a reference and so they can review or re-use the queries back at home or work.
We encourage students to bring their own data so that if they have the time and capability during the course, they can implement on their own data the skills they learn in the course and even go back to the office with a ready-to use spatial database.
This course is based to a large extent on the open BoundlessGeo course at http://workshops.boundlessgeo.com/postgis-intro/, with modifications and extensions by Kartoza.