Introduction to PostGIS

Day 1

1. Welcome
2. 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
3. Installation of PostgreSQL, pgAdmin, PostGIS and QGIS
   • on Windows, Linux or OS X
4. Creating a Spatial Database
   • create a new database and spatially enable it with PostGIS
5. Loading spatial data
   • PostGIS shapefile loading tool
   • QGIS DBManager
   • other tools
6. Overview of the exercise data
7. 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
8. Simple SQL Exercises
   • Practising basic SQL
9. Geometries
   • Understanding OGC simple feature geometries and how to store and manipulate them in the database
10. Geometry Exercises
    • Practising geometry functions
11. 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!
12. Spatial Relationships Exercises
    • Practising spatial relationship functions
13. Spatial Joins
    • one of the core abilities in a GIS, doing spatial joins in a database is incredibly flexible and powerful.
14. Spatial Joins Exercises
    • Practising spatial joins
15. Spatial Indexing
    • spatial indexing speeds up queries of spatial data

Day 2

1. Projecting Data
   • Transforming geometries between different coordinate reference systems.
2. Projection Exercises
3. Geography
   • store your data as long-lat (unprojected) yet do very accurate spatial operations using spherical geometry (on the WGS84 spheroid)
4. Geometry Constructing Functions
   • Functions that result in new geometries, e.g.
   • Buffer
   • Centroid
   • Intersection
   • Union (aka dissolve or merge)
5. 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
6. Validity
   • Testing the validity of geometries
7. Equality
   • a more in-depth look at what equality between geometries means
8. Linear Referencing
   • reference features based on their position along a line instead of using x,y or polar coordinates.
9. Dimensionally Extended 9-Intersection Model
   • understanding, testing and exploiting all the possible relationships between geometries
10. Clustering on Indices
    • database clustering is a performance-enhancing tool that physically puts related data close together on disk
11. 3-D
    • working with 3D geometries in PostGIS
12. Nearest-Neighbour Searching
    • never mind the distance, what is the nearest? More complicated than you think. PostGIS solves it neatly
13. Tracking Edit History using Triggers
    • use standard PostgreSQL triggers to keep a record of who created/edited/deleted a record
14. Advanced Geometry Constructions
    • learn some advanced SQL functions and approaches to solve some tricky problems
15. 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
16. PostgreSQL Security
    • How to protect the integrity and security of your data and database
    • authentication and roles
    • database connections
    • encryption
17. PostgreSQL Schemas
    • schemas are like folders - how and why to use schemas
18. PostgreSQL Backup and Restore
    • very important!
19. 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.