Skip to content

Module 7 — Some GIS Features

Module 6 built the data scale and the choropleth pattern. Module 7 goes deeper into PostGIS’s analytical capabilities. Distance calculations done correctly. Spatial indexes and the gotchas that make them silently fail. Window functions for sequence-aware queries. Construction operations that produce new geometries.

Four lessons. Each one answers a real operational question; each one produces a working report or visualisation; each one introduces SQL techniques that pay off well beyond GIS work.

Lesson 1 — Distance and projection. The first substantive spatial calculation: “how far is each customer from their nearest depot?” The lesson covers geometry vs geography, the difference between Cartesian and spheroidal distance, and the production pattern of storing geometry but casting to geography for distance work. The output is honest metres, not nonsensical degrees.

Lesson 2 — Indexed proximity. Distance calculations are easy; making them fast at scale is harder. The lesson introduces ST_DWithin and the cast-blocks-index gotcha — the way the geography cast that’s correct for measurement disables the geometry index when used in a WHERE clause. The hybrid pattern (filter on geometry with degree-based bounds, measure on geography for accuracy) is the production solution. EXPLAIN ANALYZE shows the difference: 1880ms without the index, 29ms with it.

Lesson 3 — FO travel analysis. The first window-function lesson. “How far does each field officer travel between consecutive jobs?” requires LAG() (the previous row’s value) combined with spatial distance. The CTE pattern stages the per-job distance computation; the outer query aggregates. The output is a sortable report — and the Components::DataTable component gets a small extension to handle service-shaped hash data alongside its existing AR-based mode.

Lesson 4 — Coverage and territory. The shift from querying geometries to constructing them. ST_Buffer and ST_Union to build customer coverage zones (the area within 3km of any customer); ST_Union again to merge service areas into depot territories; the official-vs-empirical comparison pattern showing where administrative boundaries diverge from operational reality. Validity (ST_IsValid, ST_MakeValid) makes a brief appearance — geometric data from external sources can have subtle structural problems that break construction operations.

What this module produces

Two new reports — FO Travel and Depot Territories — plus the performance discipline that makes them work at scale. The chassis now answers questions that span geographic, temporal, and analytical dimensions.

What this module sets up

  • The window function pattern (LAG, RANK, ROW_NUMBER) for sequence-aware queries — useful far beyond GIS
  • The CTE pattern for staging analytical queries that mix per-row and aggregate operations
  • The construction-vs-query mental distinction — querying existing geometries versus creating new ones — that opens the door to spatial synthesis work
  • The DataTable component’s dual-mode capability for both AR and service-shaped data, making it a properly reusable building block
  • Three reference appendices (SQL, PostGIS, EXPLAIN ANALYZE) and a DataTable component reference, all serving as ongoing reference material rather than one-time reading

Reference material

This module also produces four reference appendices that sit alongside the module content: