Introduction to PostGIS

Getting Started

• This workshop uses a data bundle. Download it and extract to a convenient location.

• The presentation deck that goes with this workshop is available on Google Slides.

• The cheat sheet of SQL commands that are run in this workshop is available here.

Data Bundle

Inside the data bundle, you will find:

data/

a directory containing the data files we will be loading

printing/

a directory containing the exercise pages and data model pages

License & Copying

All the data in the package is public domain and freely redistributable. All the software in the package is open source, and freely redistributable. This workshop is licensed as Creative Commons “share alike with attribution”, and is freely redistributable under the terms of that license.

Workshop Modules

• 1. Welcome
• 2. Introduction
• 3. Installation
• 4. Creating a Spatial Database
• 5. Loading spatial data
• 6. About our data
• 7. Simple SQL
• 8. Simple SQL Exercises
• 9. Geometries
• 10. Geometry Exercises
• 11. Spatial Relationships
• 12. Spatial Relationships Exercises
• 13. Spatial Joins
• 14. Spatial Joins Exercises
• 15. Spatial Indexing
• 16. Projecting Data
• 17. Projection Exercises
• 18. Geography
• 19. Geography Exercises
• 20. Geometry Constructing Functions
• 21. Geometry Constructing Exercises
• 22. More Spatial Joins
• 23. Validity
• 24. Equality
• 25. Linear Referencing
• 26. Dimensionally Extended 9-Intersection Model
• 27. Clustering on Indices
• 28. 3-D
• 29. Nearest-Neighbour Searching
• 30. Rasters
• 31. Topology
• 32. Tracking Edit History using Triggers
• 33. Basic PostgreSQL Tuning
• 34. PostgreSQL Security
• 35. PostgreSQL Schemas
• 36. PostgreSQL Backup and Restore
• 37. Software Upgrades
• 38. Advanced Geometry Constructions
• 39. Appendix A: PostGIS Functions
• 40. Appendix B: Glossary
• 41. Appendix C: License

Links to have on hand

• PostGIS - https://postgis.net/

  • Docs - https://postgis.net/docs/

• PostgreSQL - https://www.postgresql.org/

  • Docs - https://www.postgresql.org/docs/

  • Downloads - https://www.postgresql.org/download/

  • JDBC Driver - https://jdbc.postgresql.org/

  • .Net Driver - https://www.npgsql.org/

  • Python Driver - http://www.pygresql.org/

  • C/C++ Driver - https://www.postgresql.org/docs/current/static/libpq.html

• PgAdmin - https://www.pgadmin.org/

• Open Source Desktop Clients

  • QGIS - https://qgis.org/

  • OpenJUMP - http://openjump.org/

  • uDig - https://udig.github.io/