Introduction to PostGIS
Duration: 5 Days
Course Background
This is a 5 day course for those who are new to relational databases and to PostGIS but who have some exeperience with Geographic Information Systems. Starting by laying a firm foundation in PostgreSQL and SQL it covers the extensions to PostgreSQL provided by PostGIS and how to build and query PostGIS databases.
Course Prerequisites and Target Audience
Attendees are expected to have some programming experience as well as some experience working with IT systems and Geographic Information Systems
Course Outline
- PostGIS - Introduction
- Databases and PostgreSQL
- Modeling data via entities, attributes and relations
- Entity, Attribute, Relationship diagrams (EAR diagrams)
- Overview of PostgreSQL - history and capabilities
- Introduction to pgAdminIII
- Relational database components and concepts
- Records and collections of records
- Tables, Columns and Fields
- Datatypes
- Primary keys and foreign keys and their use in capturing relationships
- Some database theory
- Normalisation
- Indexes and sequences
- Overview of PostgreSQL - history and capabilities
- Database analysis and design using object oriented and entity life cycle techniques
- Referential integrity constraints
- SQL
- SQL as a declarative language
- SQL as a Data Definition Language
- Creating and dropping tables
- Creating and dropping keys
- Creating and dropping constraints
- NULL and NOT NULL fields
- SQL as a Data Manipulation Language
- Basic SQL queries using SELECT and WHERE
- Queries spanning multiple tables
- Cartesian products of tables
- Joins
- Basic joins
- Inner and outer joins
- Grouping and aggregating data
- GROUP BY
- Restricing GROUP BY results with HAVING
- Removing duplicates with DISTINCT
- Aggregate functions - COUNT, AVG, MAX, ...
- Inserting records into tables - INSERT
- Modifying records in tables - UPDATE
- Deleting records in tables - DELETE
- More advanced SQL
- Views
- The concept and purpose of Views
- Creating and dropping views
- Rule based views (materialised views)
- Nested and correlated subqueries
- SQL scripting
- SQL transaction processing
- The Concept of a Transaction
- Transactions as atomic operations
- ROLLBACK
- COMMIT
- Object relational databases
- Classes as new data types
- Objects as instances of a class
- What makes PostgreSQL an Object Relational database
- PostGIS
- What is a GIS
- PostGIS - overview and history
- Introduction to GIS geometries
- Components of PostGIS and how PostGIS builds on top of PostgreSQL
- Installation and setup of PostGIS
- Stored procedures and functions in PostgreSQL
- Data types and functions provided by PostGIS - an overview
- The OGC (Open Geospatial Consortium) standards
- OGC's Simple Features for SQL (SFS) specification
- Overview of the SFS model
- PostGIS and OGC
- PostGIS and SFS
- Geometries
- Adding a geometry to a field table
- Adding constraints based on geometry type
- Populating a geometry_columns tables
- Using SQL to add a geometry record to a table
- WKT - Well Known Text representations of a geometry
- Viewing a point as a WKT
- Using Geometries
- Spatial queries
- Spatial operators
- Spatial indexes
- Constructing geometries
- Linestrings
- Polygons
- GIS data processing
- Clipping
- Constructing geometries from other geometries
- Cleaning geometries
- Tools for data import and export
- shp2pgsql
- pgsql2shp
- ogr2ogr
- spit