Training Item Code: Geospatial Data in PostGIS
Geospatial Data in PostGIS
PostGIS is a spatial database that is more than just a spatial data store. With PostGIS, users, web map servers and other applications can all run off the same database. And beware! Doing spatial analysis in PostGIS can become addictive.
PostGIS and PostgreSQL provide a powerful, enterprise-ready geospatial relational database management system. With PostGIS you can insert geometries into a database and then perform spatial queries and analysis on that data. We will show you how to import and export data into PostGIS, manipulate that data using SQL queries and of course, visualise the resulting datasets with QGIS. Note that the content in this module will be technical, but no basic working knowledge of database systems (tables, fields, primary and foreign keys etc.) is assumed, so we are 'starting from scratch'. We will also look at how PostGIS is used in the context of rolling out an integrated FOSS-based GIS enterprise solution and how it can work in a mixed environment. PostGIS is the cornerstone providing an enterprise platform from which to carry out desktop GIS, in-database analysis and data processing, web mapping and the provision of OGC-compliant web services.
The assessment is based on the 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.
The use of personal Laptops are required for this course.
One or more of the following;
1 point (category 1b)
Price includes VAT, software, course material and data and a certificate of attendance.
11 - 12 May 2023
|On-site Training||12 – 13 October 2023|
Topics for the day
- 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
- Practising basic SQL
- 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
- Practising spatial joins
- Spatial Indexing
- spatial indexing speeds up queries of spatial data
Topics for the day
- Projecting Data
- Transforming geometries between different coordinate reference systems.
- Projection Exercises
- 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.
- 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
- Testing the validity of geometries
- 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
- 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
- PostgreSQL Schemas
- schemas are like folders - how and why to use schemas
- PostgreSQL Backup and Restore
- very important!
- Software Upgrades
- How to handle upgrades of PostgreSQL or PostGIS
Geospatial Data in PostGIS Course