Ranking a council's suburbs by mapped hazard exposure

This is one run, on 2 September 2026, published the way it came out of the app. The report below, its figures, its captions and its notes on the data are the agent's own words; nothing has been re-run to look better, and the two attempts its own checks rejected are shown where they happened.

The prompt

14 tool calls5s analysis3 datasets

One sentence that is really three jobs: build an address-level exposure measure out of three separate hazard datasets, roll it up to suburbs, and then say which suburbs that ranking actually favours. The hard part is not the join. It is that "exposed" has to mean the same thing in three datasets that were mapped by different agencies, at different times, for different purposes.

What the agent did

Two things in that list are worth stopping on.

The run opens by loading expertise, twice: the climate-risk screening pack and the writing pack. The first is where the hazard vocabulary and the coverage caveats live, the second is what turns a set of query results into a report with a standfirst and a findings list. Neither is a search.

Then, near the end, two document renders fail. The agent wrote the report, its own publishing checks refused it, and it rewrote it twice before the third passed. That is the run's quality gate working, and it is left in because a run that only ever shows its successful calls is not showing its working.

What it produced

How it got there

One shell cell to see the shape of the data, then four queries. It is short because the joins it needs are pre-built: the address profile already carries each address's hazard flags, so the work is aggregation and honest denominators rather than spatial processing.

python3 - <<'EOF'
import zn
con=zn.lake_con()
A=zn.read('address_profile')
F=zn.read('hazard_flood_extents', file='flood_extents_addresses')
C=zn.read('coastal', file='coastal_sepp_h3r9')
q=f"""
COPY (
WITH sites AS (
 SELECT a.gnaf_pid, a.suburb, a.suburb_code, a.h3_r9,
        (a.bushfire_category IS NOT NULL) AS bushfire_exposed,
        EXISTS (SELECT 1 FROM {F} f WHERE f.gnaf_pid=a.gnaf_pid) AS flood_exposed,
        EXISTS (SELECT 1 FROM {C} c WHERE c.h3_r9=a.h3_r9 AND c.layer='local_coastal_hazard') AS coastal_exposed
 FROM {A} a
 WHERE a.lga_name='Port Macquarie-Hastings' AND a.is_residential IS TRUE
), suburb AS (
 SELECT suburb, suburb_code,
        count(*) AS residential_addresses,
        sum(CASE WHEN bushfire_exposed THEN 1 ELSE 0 END) AS bushfire_addresses,
        sum(CASE WHEN flood_exposed THEN 1 ELSE 0 END) AS flood_addresses,
        sum(CASE WHEN coastal_exposed THEN 1 ELSE 0 END) AS coastal_addresses,
        sum(CASE WHEN bushfire_exposed OR flood_exposed OR coastal_exposed THEN 1 ELSE 0 END) AS exposed_addresses
 FROM sites GROUP BY 1,2
), ranked AS (
 SELECT *,
   100.0*bushfire_addresses/residential_addresses AS bushfire_share_pct,
   100.0*flood_addresses/residential_addresses AS flood_share_pct,
   100.0*coastal_addresses/residential_addresses AS coastal_share_pct,
   100.0*exposed_addresses/residential_addresses AS exposed_share_pct,
   row_number() OVER (ORDER BY exposed_addresses DESC, suburb) AS exposure_rank
 FROM suburb
)
SELECT * FROM ranked ORDER BY exposure_rank
) TO '/workspace/out/pmh_suburb_exposure.csv' (HEADER)
"""
con.execute(q)
print(con.sql("SELECT count(*) AS suburbs, sum(residential_addresses) AS addresses, sum(exposed_addresses) AS exposed FROM '/workspace/out/pmh_suburb_exposure.csv'"))
EOF

SELECT suburb, suburb_code, residential_addresses, exposed_addresses, exposed_share_pct, bushfire_addresses, bushfire_share_pct, flood_addresses, flood_share_pct, coastal_addresses, coastal_share_pct FROM read_csv_auto('/workspace/out/pmh_suburb_exposure.csv') WHERE exposure_rank <= 10 ORDER BY exposure_rank

SELECT suburb, suburb_code, bushfire_share_pct, flood_share_pct, coastal_share_pct FROM read_csv_auto('/workspace/out/pmh_suburb_exposure.csv') WHERE exposure_rank <= 10 ORDER BY exposure_rank

SELECT suburb, suburb_code, exposed_share_pct, exposed_addresses, residential_addresses FROM read_csv_auto('/workspace/out/pmh_suburb_exposure.csv') ORDER BY suburb

SELECT sum(residential_addresses) AS residential_addresses, sum(exposed_addresses) AS exposed_addresses, 100.0*sum(exposed_addresses)/sum(residential_addresses) AS exposed_share_pct, sum(bushfire_addresses) AS bushfire_addresses, 100.0*sum(bushfire_addresses)/sum(residential_addresses) AS bushfire_share_pct, sum(flood_addresses) AS flood_addresses, 100.0*sum(flood_addresses)/sum(residential_addresses) AS flood_share_pct, sum(coastal_addresses) AS coastal_addresses, 100.0*sum(coastal_addresses)/sum(residential_addresses) AS coastal_share_pct FROM read_csv_auto('/workspace/out/pmh_suburb_exposure.csv')

What this run does not tell you

The report carries its own notes on the data, and they are reproduced here exactly as the agent wrote them:

• This is a screening result, not a property-level determination. The analysis joins each G-NAF address to mapped hazard areas at resolution 9; the flood result uses the exact mapped-address intersection where available.
• Bushfire exposure uses the NSW statewide bushfire-prone-land map. Flood exposure uses the openly available NSW flood-extents collection, which combines mapped sources and vintages; it is a mapped extent, not a depth, velocity or probability result. Coastal exposure is limited to the mapped local coastal hazard layer. Absence from a mapped area does not mean absence of physical hazard.
• The any-hazard total is a union across hazards, so an address exposed to more than one hazard is counted once. The figures describe mapped exposure for residential addresses in the Port Macquarie-Hastings LGA, not all buildings or residents.

Two things to add to them. A mapped extent is a screening result, not a property assessment — an address inside a mapped flood extent has not been told how deep, how often, or whether the building on it is at risk, and an address outside one has not been told it is safe. And the three hazards are not equally mapped. Bushfire-prone land is a statewide layer. Flood extents are a collection of council and agency mapping with different vintages and licences, and only what is openly republishable is in the lake at all. The coastal layer is narrower still. A suburb's any-hazard share is therefore partly a measure of how well that suburb has been mapped.

Run it on your own portfolio

The unit here is a council area because the prompt named one. The same query runs over a list of your own addresses, a portfolio, or a state, and produces the same three-hazard split with the same denominators. If you want to see it against yours, request access — or read how this is used for physical climate risk assessment and by corporate reporters.