PostGIS spatial queries for council work

A small set of well-commented PostGIS queries that solve everyday problems a territorial authority actually faces: who to consult, what to prioritise, where coverage falls short, and which properties need a closer look. Each query is built around a real council scenario, not just a function demo, and each ships with sample data so you can run it and see the result in a few minutes.

Written by a GIS specialist working in Canterbury, using data drawn from LINZ and the Christchurch City Council Open Data Portal.

Why this repo

Spatial SQL is easy to show off badly (a wall of ST_Intersects with no context) and harder to show off well (a query that answers a question a council officer would actually ask). This repo aims for the second. For each query the README explains the problem in plain language first, then the query solves it, then the comments in the SQL file walk through how.

The queries lean on a handful of core PostGIS functions used the way they come up in practice:

• ST_DWithin for "within N metres of" questions (consultation catchments, access to services, coverage gaps)
• ST_Intersects for "does this cross / fall inside" questions (overlays, zones, encroachments)
• ST_Buffer for building a corridor around a line and then testing against it
• supporting functions ST_Contains, ST_Area, ST_Intersection, ST_Distance, and the NOT EXISTS anti-join pattern for "where is there nothing nearby" questions

Why PostGIS, and why NZTM2000

PostGIS is the most portable choice for a public repo: it is free, standards based, and the same SQL runs on any Postgres instance. The sample data and the queries all use NZGD2000 / NZTM2000 (EPSG:2193), the metric projection that LINZ and Christchurch City Council publish in. Because it is in metres, a distance of 100 in ST_DWithin means 100 metres, with no reprojection step to get wrong.

Quick start

You need PostgreSQL with the PostGIS extension available.

createdb council
psql -d council -f setup/01_schema.sql        # tables, SRID 2193, GiST indexes
psql -d council -f setup/02_sample_data.sql   # small sample dataset
psql -d council -f queries/01_cycleway_consultation.sql

Then run any other file in queries/. Each query prints the rows it is expected to return in its trailing comments, so you can confirm your setup is working.

To point the queries at real council data instead of the sample, see DATA_SOURCES.md, which maps every table to the LINZ or CCC layer it stands in for.

The queries

01. Properties within 100 m of a proposed cycleway

The transport team is consulting on a new route and has to notify every property within 100 m of it. This builds the mailing list, and just as importantly it is a defensible record of how "nearby" was defined if anyone later asks why they were or were not notified. Uses ST_DWithin.

02. Parks with no playground within walking distance

The parks team wants the next playground budget to close a real gap rather than top up parks that already have equipment. This flags parks with no playground within 800 m (a roughly ten-minute walk), which becomes the evidence in the funding bid. Uses ST_DWithin in a NOT EXISTS anti-join.

03. Roads overdue for resurfacing that pass through a school zone

The resurfacing budget cannot cover every overdue road in a year, so overdue roads that run through a school zone are prioritised on safety grounds. Combines an attribute test (last resurfaced over 15 years ago) with a spatial one. Uses ST_Intersects.

04. Properties in a flood overlay with no recent building consent

Properties in the High Flood Hazard Management Area carry specific building rules. This targets flood-overlay properties that have not had a building consent in the last five years, on the basis that their flood resilience is least likely to have been reviewed recently. Uses ST_Intersects plus a dated NOT EXISTS.

05. Buildings with no fire hydrant within 90 m

Fire-fighting water supply depends on hydrants being close enough to buildings. This finds buildings no hydrant reaches within 90 m, feeding gaps into the hydrant renewal programme. Uses ST_DWithin in an anti-join.

06. Residential addresses more than 400 m from a bus stop

A public transport review wants to see which homes fall outside the 400 m catchment of every bus stop. The query is honest about being a straight-line proxy for a longer real walk, so it reads as a first-pass screen rather than a final answer. Uses ST_DWithin in an anti-join.

07. Open resource consents inside a heritage area

Work inside a heritage area needs extra scrutiny. This catches any live consent application that falls inside a heritage area so it does not slip through on the standard track. Uses ST_Intersects.

08. Buildings encroaching onto reserve land

Sheds, garages and fences creep onto public reserve land over time. This finds building footprints that overlap a reserve, and reports how many square metres overlap, so each can be checked. Uses ST_Intersects, with ST_Intersection and ST_Area to measure the overlap, and a note on ST_Overlaps for the boundary-crossing-only variant.

09. Street tree count and density by suburb

Supports an urban forest target by showing how street trees are shared across suburbs, as a density per square kilometre so suburbs of different sizes compare fairly. Ranks suburbs so planting budgets can go where canopy is thinnest. Uses ST_Contains for the spatial join and ST_Area for the density.

10. Parcels affected by a proposed road designation corridor

A future road widening is safeguarded as a centreline. Planners need the properties inside the working corridor (the line plus a 20 m allowance either side) for notification and possible land acquisition. Uses ST_Buffer then ST_Intersects, the classic buffer-then-intersect pattern.

Notes for anyone reading the SQL

• Thresholds are policy, not magic numbers. 100 m, 400 m, 800 m, 90 m, "15 years", "5 years": these stand in for real council standards and are meant to be swapped for the actual figures. They live in the query where they are easy to find and change.
• Straight-line distance is a proxy. ST_DWithin measures as the crow flies. For walking-access questions the true footpath distance is longer, so those queries are screens rather than final answers. Query 06 says so in its header; the same caveat applies wherever a walk is implied.
• Indexes matter. The schema puts a GiST index on every geometry column, because on real council data volumes ST_DWithin and ST_Intersects are only fast with one. That is a deliberate part of the setup, not an afterthought.
• The sample data is arranged to teach. Each query returns a clear hit and a control row that is correctly excluded, and the setup file comments say which is which, so you can see the logic working rather than just trusting it.

Licence

Query SQL and sample data in this repo: MIT (see LICENSE). Real data referenced in DATA_SOURCES.md is licensed by its publishers, mostly CC BY 4.0 for LINZ; check each source before redistributing.