PostGIS Spatial Database Design and Querying Training Course

Course overview

Spatial data teams often inherit PostgreSQL databases that store geometry without enforcing coordinate reference systems, topology rules, indexing standards, or repeatable loading procedures. The result is slow map services, duplicate assets, incorrect proximity results, and analysts exporting data into desktop GIS to complete work that should be governed in the database. This course equips GIS, data, and application professionals to design PostGIS databases that support reliable operational queries, analytical workflows, and web mapping applications.

Participants build spatial schemas in PostgreSQL, select appropriate geometry and geography types, manage SRIDs, load vector data, and define constraints that protect data quality. They write and tune SQL using PostGIS functions for intersections, buffers, nearest-neighbour searches, spatial joins, overlays, routing-ready networks, and raster-vector analysis. The course also covers GiST indexing, query plans, partitioning decisions, role-based access, backup and restore, and publishing database layers to QGIS for validation and operational use.

Instruction combines short technical demonstrations with progressively structured labs using a realistic local-government asset, land-use, and service-area dataset. Participants create a documented PostGIS database design, implement loading and validation scripts, and assemble a tested query library for common spatial questions. They leave with a capstone database package containing schema DDL, sample data-loading workflow, spatial indexes, quality rules, and performance-tested SQL queries that can be adapted to their organisation's own spatial data estate.

The course is best suited to professionals who already work with GIS data or SQL and need to move from file-based analysis or basic PostgreSQL use into production-quality spatial database design.

Course objectives

By the end of this course, participants will be able to:

• Design a normalized PostGIS schema with geometry types, SRIDs, primary keys, and spatial relationships
• Load Shapefile, GeoJSON, CSV, and GeoPackage data into PostgreSQL using repeatable import workflows
• Apply geometry constraints, validity checks, and topology rules to prevent spatial data quality failures
• Write PostGIS SQL for buffers, overlays, spatial joins, nearest-neighbour searches, and distance analysis
• Create and evaluate GiST spatial indexes using EXPLAIN ANALYZE and query execution evidence
• Choose between geometry and geography storage models for projected, global, and distance-based datasets
• Administer spatial database access, backup, restore, and controlled data-update procedures
• Produce a documented PostGIS capstone database with DDL, loading scripts, indexed queries, and validation tests

Benefits of attending

Target competencies

Who should attend

• GIS Analysts — who need to replace file-based geoprocessing with reusable database queries
• GIS Database Administrators — who manage spatial data quality, performance, permissions, and recovery
• Spatial Data Engineers — who build ingestion pipelines and analytical data stores for location data
• Geospatial Developers — who need efficient PostGIS queries behind maps, APIs, and location-aware applications
• Data Analysts — who analyse customer, asset, environmental, or operational data with a geographic dimension
• Technical GIS Managers — who must establish maintainable spatial data standards across teams and systems

Requirements and prerequisites

Participants should be comfortable writing basic SQL statements, including SELECT, WHERE, JOIN, GROUP BY, CREATE TABLE, and INSERT or UPDATE. They should understand common vector GIS concepts such as points, lines, polygons, attribute tables, coordinate reference systems, and map layers. Prior use of PostgreSQL, QGIS, or another desktop GIS is helpful, but participants do not need prior PostGIS administration or programming experience. Python, web development, raster processing, and advanced database tuning are not required. Participants should be ready to work with SQL scripts and inspect query results throughout practical labs.

Training methodology

Each day alternates instructor-led design walkthroughs with guided SQL labs in PostgreSQL and PostGIS. Participants inspect real query plans, compare spatial predicates against mapped results in QGIS, and correct deliberately flawed source data such as mixed SRIDs, invalid polygons, and duplicate assets. Short case discussions address design choices for operational asset registers, planning datasets, and public-facing map services. Teams review each other's schema and query decisions against stated requirements. The final sessions are a capstone build and application-planning workshop, producing an implementation backlog for participants' own databases.

Course outline

Day 1: PostGIS architecture and spatial data modelling

• PostgreSQL schemas, tablespaces, extensions, and database roles
• PostGIS geometry columns and metadata registration
• Vector feature classes, attributes, identifiers, and relationship modelling
• Geometry versus geography data types
• Coordinate reference systems, SRIDs, and projected coordinate selection
• Normalized spatial schemas versus single-layer data stores
• Creating databases, schemas, tables, and spatial columns with SQL

Workshop: Create the initial asset-management PostGIS database and produce an entity relationship diagram with spatial column specifications.

Day 2: Spatial data loading and transformation

• Source data assessment for Shapefile, GeoJSON, CSV, and GeoPackage inputs
• Importing vector layers with ogr2ogr workflows
• Using pgAdmin Import/Export for tabular and spatial-adjacent data
• Attribute type mapping, null handling, and field-name standardisation
• ST_SetSRID, ST_Transform, and coordinate system conversion
• Loading coordinates from CSV into point geometries
• Staging tables and repeatable SQL loading scripts

Workshop: Load roads, parcels, service locations, and inspection records from mixed formats and produce a repeatable staging-to-production import script.

Day 3: Geometry quality and integrity controls

• ST_IsValid, ST_IsValidReason, and invalid geometry diagnosis
• ST_MakeValid and controlled geometry repair
• CHECK constraints for geometry type, SRID, and dimensionality
• NOT NULL, unique, foreign key, and domain constraints for spatial records
• Duplicate feature detection using identifiers and spatial comparison
• Precision management with ST_SnapToGrid and ST_ReducePrecision
• Topology-oriented checks for gaps, overlaps, and boundary consistency

Workshop: Audit a flawed parcel and asset dataset, repair identified defects, and deliver a data-quality rule set with validation queries.

Day 4: Core spatial SQL and predicate selection

• Bounding-box operators and exact geometry evaluation
• ST_Intersects, ST_Contains, ST_Within, and ST_Covers
• ST_DWithin and distance-unit interpretation
• ST_Buffer and buffer parameter choices
• ST_Distance and ST_Length for operational measures
• ST_Area and ST_Perimeter for land and facility reporting
• Spatial joins with JOIN, LATERAL, and aggregate SQL patterns

Workshop: Write a query set that assigns assets to administrative areas, identifies features inside risk zones, and calculates maintenance quantities.

Day 5: Overlay, proximity, and analytical query patterns

• ST_Intersection for shared-area and clipped-feature analysis
• ST_Union, ST_Collect, and ST_UnaryUnion for feature aggregation
• ST_Difference and ST_SymDifference for change and exclusion analysis
• Nearest-neighbour searches using the KNN distance operator
• LATERAL joins for nearest facility and nearest road queries
• Spatial aggregation by district, catchment, and service zone
• Common table expressions for multi-stage spatial analysis

Workshop: Build a service-access analysis that identifies underserved parcels, nearest facilities, and candidate locations outside exclusion zones.

Day 6: Spatial indexing and query performance

• GiST indexes and R-tree-style spatial search behaviour
• Creating and maintaining GiST indexes on geometry columns
• B-tree indexes for identifiers, dates, and categorical filters
• EXPLAIN and EXPLAIN ANALYZE for spatial query plans
• Index selectivity, statistics, and ANALYZE maintenance
• Query rewrites that preserve index use
• Table partitioning considerations for large spatial event datasets

Workshop: Benchmark slow proximity and overlay queries, add appropriate indexes, and document measured improvements using EXPLAIN ANALYZE.

Day 7: Advanced geometry operations and network-ready data

• ST_Dump, ST_Multi, and multipart geometry management
• ST_LineMerge, ST_Split, and ST_Snap for linear feature maintenance
• ST_ClosestPoint and ST_ShortestLine for network association
• ST_Azimuth and directional analysis methods
• Generating grids and tessellations with ST_SquareGrid
• Linear referencing concepts and measured geometries
• Preparing connected road and utility geometries for routing tools

Workshop: Clean and connect a road network, attach inspection points to the nearest segments, and produce a network-readiness exception report.

Day 8: Raster, 3D, and desktop GIS integration

• PostGIS raster storage concepts and raster metadata
• Raster-vector intersection and zonal summary patterns
• ST_Value and ST_SummaryStats for raster-derived measures
• Z and M dimensions in geometry data
• QGIS connections to PostgreSQL and PostGIS layers
• Editing PostGIS layers from QGIS with transaction awareness
• Database views for controlled GIS publication

Workshop: Publish indexed PostGIS views to QGIS and create a flood-exposure layer that combines asset points with raster-derived values.

Day 9: Security, operations, and maintainable database services

• Role-based access control with PostgreSQL roles and grants
• Read-only views and controlled editing patterns
• Transaction management, locking, and concurrent updates
• PostgreSQL backup with pg_dump and restore with pg_restore
• Schema migration scripts and version-control practices
• Audit columns, change tracking, and data stewardship responsibilities
• Operational monitoring for storage, index health, and failed loads

Workshop: Define role permissions and an operational runbook for a shared spatial database, including backup, restore, and update procedures.

Day 10: Capstone spatial database implementation

• Translating business questions into spatial database requirements
• Schema review against access, quality, and performance criteria
• Selecting indexes and validation controls for production tables
• Building reusable views and parameter-ready query patterns
• Testing expected spatial results against sample scenarios
• Documenting DDL, assumptions, and query performance evidence
• Implementation roadmap and handover planning

Workshop: Complete and present a PostGIS capstone package containing schema DDL, loading scripts, quality checks, indexed analytical queries, and an implementation roadmap.

Tools & standards covered

PostgreSQL, PostGIS, QGIS, pgAdmin

A typical training day

08:30 – 10:30	First session
10:30 – 10:45	Refreshment break
10:45 – 12:30	Second session
12:30 – 13:30	Lunch and networking
13:30 – 15:00	Third session
15:00 – 15:15	Refreshment break
15:15 – 16:30	Workshop and daily review

Live online deliveries follow the same structure in the East Africa Time zone, with shorter screen blocks and longer breaks.

What the fee includes

How you can take this course

Certification

Participants who complete the full five days receive the Skillset Development Certificate of Completion, stating the course title, course code, dates and delivery format — suitable for professional-development records and employer reimbursement.

Frequently asked questions