A published agent run. Five NSW irrigation councils compared on rainfall normals, projected heat, topsoil and mapped land use, then broken below the council average to statistical areas — with the report the agent wrote and every cell it ran.
Zenancy
This is one run, on 2 September 2026, published the way it came out of the app. The report, its figures, its captions and its notes on the data are the agent's own; the passes it made and replaced are folded away under the report rather than deleted.
The prompt
The prompt, as typed
›
Compare the Riverina irrigation LGAs — Griffith, Leeton, Narrandera, Murrumbidgee and Murray River — on their 30-year rainfall normal, mean maximum temperature, days over 35 °C today and projected for 2050, topsoil carbon and clay, and the split of irrigated cropping, dryland cropping and grazing. Table by LGA, chart the projected change in hot days, and map the share of irrigated land by SA2.
Datasetscell_profileclimate_projections
22 tool calls23s analysis2 datasets
Nine measures across five councils, from four different kinds of source: a thirty-year rainfall normal, an observed and a modelled heat count, two soil attributes and a three-way land-use split. Then one instruction that changes the shape of the answer — map the irrigated share by statistical area, not by council. That last clause is what turns an averages table into a question about whether the averages mean anything.
What the agent did
Loaded expertiseLoaded expertise: drought & water security.
Ran a cell in the sandboxexited 1: _duckdb.BinderException: Binder Error: Cannot mix aggregates with non-aggregated columns!failed
Ran a cell in the sandboxwrote riverina_lga_table.csv — 5 rows5.2s
Rendered a tableRendered `data_3` as a table (5 rows) titled "Riverina irrigation LGAs: climate, soil and agricultural land use".
Rendered a chartRendered `data_1` as a grouped-bar chart titled "Projected hot days in 2050 across Riverina irrigation LGAs" (10 rows).
Rendered a mapTOOL ERROR: cannot map `data_2` — `113011257` appears 4 times in `sa2_code` — a region map paints each area once, so two rows for one sa2 means the shading would be arbitrary. Aggregate to ONE row perfailed
Ran a cell in the sandboxwrote riverina_irrigated_sa2_map.csv — 8 rows1.9s
Rendered a mapRendered `data_4` as a sa2 region map titled "Share of mapped agricultural land classified as irrigated by SA2" (8 sa2 areas).
Ran a cell in the sandboxriverina_lga_table
Ran a cell in the sandboxwrote riverina_lga_table.csv — 5 rows3.7s
Rendered a tableRendered `data_5` as a table (5 rows) titled "Riverina irrigation LGAs: climate, soil and agricultural land-use shares".
Ran a cell in the sandboxLGA r9 land cells 30-year rainfall normal (mm) Mean maximum temperature (°C) Observed days over 35 °C (1991-2020 normal) Model days over 35 °C, 2050 (SSP2-4.5) Model days over 35 °C, 2050 (SSP5-
Rendered a tableRendered `data_5` as a table (5 rows) titled "Riverina irrigation LGAs: climate, soil and agricultural land-use shares".
Rendered a tableRendered `data_5` as a table (5 rows) titled "Riverina irrigation LGAs: climate, soil and agricultural land-use shares". Note — a document may reject this table as it stands (an id first column or a p
Ran a cell in the sandboxwrote riverina_lga_table.csv — 5 rows5.2s
Rendered a tableRendered `data_7` as a table (5 rows) titled "Riverina irrigation LGAs: climate, soil and agricultural land-use shares".
Rendered a chartRendered `data_6` as a grouped-bar chart titled "Change in hot days by 2050 across Riverina irrigation LGAs" (10 rows).
Wrote the documentTOOL ERROR: this document is not publishable yet — 1 thing to fix:failed
Wrote the documentRendered a report titled "Riverina irrigation LGAs: climate and land-use comparison" embedding 3 blocks: data_7 (table), data_6 (chart), data_4 (map). Their standalone copies from this turn have been
3 calls failed and the agent recovered within the same run. Failures are shown because they happened.
This is a longer run than most, and the length is the interesting part. It describes the data before querying it, builds a first table, renders a map that fails, fixes it, and then rebuilds the table three more times as it settles on the columns and the wording. A document render is rejected by its own publishing checks before the last one passes.
None of that is hidden below. What the report places is the agent's final answer; the earlier passes sit under it, collapsed.
What it produced
Riverina irrigation LGAs: climate and land-use comparison
Across Griffith, Leeton, Narrandera, Murrumbidgee and Murray River LGAs, the 1991–2020 rainfall normal ranges from 340.7 mm/year in Murray River to 433.9 mm/year in Narrandera. By the 2050s, hot days increase by 23.4 to 33.3 days/year under SSP5-8.5, while irrigated cropping ranges from 4.5% of mapped agricultural land in Narrandera to 62.5% in Griffith.
Key findings
Narrandera has the highest rainfall normal at 433.9 mm/year, 93.2 mm above Murray River's 340.7 mm/year, but also the largest SSP5-8.5 increase in hot days at 33.3 days/year.
Griffith has the highest irrigated-cropping share at 62.5% of mapped agricultural land, compared with 4.5% in Narrandera.
Murray River has the highest clay content at 34.2%, compared with 28.2% in Narrandera, while Leeton has the highest topsoil organic carbon at 2.09%.
Under SSP5-8.5, projected 2050 hot days reach 68.6 per year in Griffith versus 56.3 in Murray River, a difference of 12.3 days/year.
SA2 irrigated-land shares range from 90.5% in Griffith (NSW) to 7.6% in Narrandera, with Griffith Surrounds at 79.9% between them.
Irrigated cropping is concentrated in Griffith and Leeton
Griffith records a 62.5% irrigated-cropping share of mapped agricultural land and Leeton 58.4%. Murray River is lower at 23.5%, while Narrandera is 4.5%. The contrast separates the strongly irrigated western Murrumbidgee production areas from the more dryland-oriented Narrandera and Murray River landscapes. Murrumbidgee sits between these patterns at 34.6%, with grazing contributing 46.5% of its mapped agricultural land. These shares describe dominant land-use classes, so mixed-use cells are assigned to their dominant category.
Riverina irrigation LGAs: climate, soil and agricultural land-use shares
LGA
Rainfall normal, 1991–2020 (mm/year)
Mean maximum temperature, 1991–2020 (°C)
Observed days over 35 °C, 1991–2020 (days/year)
Projected days over 35 °C, 2050 SSP2 4.5 (days/year)
Projected days over 35 °C, 2050 SSP5 8.5 (days/year)
Change in hot days, 2050 SSP5 8.5 (days/year)
Topsoil organic carbon, 0–5 cm (%)
Clay, 0–5 cm (%)
Irrigated cropping share of mapped agricultural land (%)
Dryland cropping share of mapped agricultural land (%)
Grazing share of mapped agricultural land (%)
Griffith
383.3
24.3
37
63.1
68.6
31.6
1.86
30.2
62.5
7.5
8.5
Leeton
405.4
24.1
35.4
61.3
67.3
31.9
2.09
29.8
58.4
13
13
Murray River
340.7
23.7
32.9
54.1
56.3
23.4
1.96
34.2
23.5
30.2
30.4
Murrumbidgee
372.3
23.8
33.8
57.6
61.9
28.2
1.92
35.6
34.6
11.1
46.5
Narrandera
433.9
23.8
33.5
60.9
66.8
33.3
1.97
28.2
4.5
73.9
15.2
Climate normals, projected hot days, soil attributes and mapped agricultural land-use shares for the five named LGAs · SILO/NSW land and soil profiles plus ACCESS-CM2 projections · 1991–2020 baseline and 2050 projections
Heat increases across every LGA by 2050
The 1991–2020 observed normal is 37.0 hot days/year in Griffith and 32.9 in Murray River. Under SSP5-8.5, the corresponding 2050 values are 68.6 and 56.3, increases of 31.6 and 23.4 days/year. SSP2-4.5 is lower in every LGA, but still raises the projected count to 54.1–63.1 days/year. Narrandera records the largest SSP5-8.5 change, 33.3 days/year, despite its lower baseline of 33.5 days/year. The comparison is consistent across all five LGAs rather than being driven by one outlier.
Change in hot days by 2050 across Riverina irrigation LGAs
Change in annual days over 35 °C relative to the 1991–2020 observed normal · ACCESS-CM2 projections · 2050 under SSP2-4.5 and SSP5-8.5
Rainfall, soils and irrigated land vary within the region
The rainfall normal is highest in Narrandera at 433.9 mm/year and lowest in Murray River at 340.7 mm/year. Leeton has the highest topsoil organic carbon at 2.09%, while Murray River has the highest clay content at 34.2%. At SA2 level, irrigated land reaches 90.5% in Griffith (NSW) and falls to 7.6% in Narrandera, showing that LGA averages conceal substantial local variation. Griffith Surrounds records 79.9%, demonstrating that the irrigated pattern extends beyond the Griffith SA2 itself. Narrandera's 73.9% dryland-cropping share in the table provides the counterpoint to its 4.5% irrigated-cropping share.
Share of mapped agricultural land classified as irrigated by SA2
Share of mapped agricultural land classified as irrigated by SA2 across the five named LGAs · NSW land-use profile · current mapped classification
The land-use split should be read as a mapped agricultural pattern, not as evidence of water availability. Rainfall does not establish allocation security, storage levels, streamflow, groundwater status or farm yield; those questions require water-register, storage and river-flow data.
How it got there
Eight cells, in order, one of which failed. They are here in full because a comparison you cannot audit is a comparison you have to take on trust.
bashcell 1 of 8
python3 - <<'EOF'
import zn
con=zn.lake_con()
cp=zn.read('cell_profile')
q=f"""
SELECT substr(CAST(alum_code AS VARCHAR),1,1) AS fam, alum_label, count(*) AS n
FROM {cp}
WHERE lga_name IN ('Griffith','Leeton','Narrandera','Murrumbidgee','Murray River')
GROUP BY 1,2 ORDER BY 1,3 DESC
"""
print(con.sql(q).df().to_string(index=False))
EOF
bashthis cell failed
python3 - <<'EOF'
import zn
con=zn.lake_con()
cp=zn.read('cell_profile')
p45=zn.read('climate_projections',file='projections_ssp245')
p85=zn.read('climate_projections',file='projections_ssp585')
lgas="('Griffith','Leeton','Narrandera','Murrumbidgee','Murray River')"
# Table: observed and SSP5 projection, plus land-use shares among agricultural mapped cells
q=f"""
WITH base AS (
SELECT lga_name, count(*) AS n_cells,
avg(ann_rainfall_mm) AS rainfall_mm_1991_2020,
avg(mean_tmax_c) AS mean_tmax_c_1991_2020,
avg(normal_days_ge_35) AS observed_hot_days,
avg(soc_pct) AS topsoil_carbon_pct,
avg(clay_pct) AS clay_pct,
count(*) FILTER (WHERE substr(CAST(alum_code AS VARCHAR),1,1) IN ('3','4')) AS ag_cells,
count(*) FILTER (WHERE substr(CAST(alum_code AS VARCHAR),1,1)='4') AS irrigated_cells,
count(*) FILTER (WHERE substr(CAST(alum_code AS VARCHAR),1,1)='3' AND alum_label='Cropping') AS dryland_cropping_cells,
count(*) FILTER (WHERE substr(CAST(alum_code AS VARCHAR),1,1)='4' AND alum_label='Irrigated cropping') AS irrigated_cropping_cells,
count(*) FILTER (WHERE substr(CAST(alum_code AS VARCHAR),1,1)='2' AND alum_label LIKE 'Grazing%') AS grazing_cells
FROM {cp} WHERE lga_name IN {lgas} GROUP BY 1
), proj AS (
SELECT 'SSP2-4.5' AS scenario, c.lga_name, avg(p.days_over_35) AS model_hot_days_2050
FROM {cp} c JOIN {p45} p ON p.h3_r4=h3_cell_to_parent(c.h3_r9,4)
WHERE c.lga_name IN {lgas} AND p.decade=2050 GROUP BY 1,2
UNION ALL
SELECT 'SSP5-8.5', c.lga_name, avg(p.days_over_35)
FROM {cp} c JOIN {p85} p ON p.h3_r4=h3_cell_to_parent(c.h3_r9,4)
WHERE c.lga_name IN {lgas} AND p.decade=2050 GROUP BY 1,2
)
SELECT b.lga_name AS "LGA", b.n_cells AS "r9 land cells",
round(b.rainfall_mm_1991_2020,1) AS "30-year rainfall normal (mm)",
round(b.mean_tmax_c_1991_2020,1) AS "Mean maximum temperature (°C)",
round(b.observed_hot_days,1) AS "Observed days over 35 °C (1991-2020 normal)",
round(max(CASE WHEN p.scenario='SSP5-8.5' THEN p.model_hot_days_2050 END),1) AS "Model days over 35 °C, 2050 (SSP5-8.5)",
round(max(CASE WHEN p.scenario='SSP5-8.5' THEN p.model_hot_days_2050 END)-b.observed_hot_days,1) AS "Change in hot days (days)",
round(b.topsoil_carbon_pct,2) AS "Topsoil carbon (%)", round(b.clay_pct,1) AS "Clay (%)",
round(100.0*b.irrigated_cropping_cells/nullif(b.ag_cells,0),1) AS "Irrigated cropping share of agricultural land (%)",
round(100.0*b.dryland_cropping_cells/nullif(b.ag_cells,0),1) AS "Dryland cropping share of agricultural land (%)",
round(100.0*b.grazing_cells/nullif(b.ag_cells,0),1) AS "Grazing share of agricultural land (%)"
FROM base b JOIN proj p USING(lga_name) GROUP BY ALL ORDER BY b.lga_name
"""
# chart data long
qc=f"""
WITH p AS (
SELECT 'SSP2-4.5' AS scenario,c.lga_name,avg(x.days_over_35) AS projected_days
FROM {cp} c JOIN {p45} x ON x.h3_r4=h3_cell_to_parent(c.h3_r9,4)
WHERE c.lga_name IN {lgas} AND x.decade=2050 GROUP BY 1,2
UNION ALL
SELECT 'SSP5-8.5',c.lga_name,avg(x.days_over_35)
FROM {cp} c JOIN {p85} x ON x.h3_r4=h3_cell_to_parent(c.h3_r9,4)
WHERE c.lga_name IN {lgas} AND x.decade=2050 GROUP BY 1,2
)
SELECT c.lga_name AS "LGA", p.scenario AS "Scenario", avg(c.normal_days_ge_35) AS "Observed days over 35 °C", p.projected_days AS "Projected days over 35 °C (2050)"
FROM {cp} c JOIN p USING(lga_name) WHERE c.lga_name IN {lgas} GROUP BY ALL ORDER BY 1,2
"""
qm=f"""
SELECT sa2_code AS "sa2_code", sa2_name AS "SA2", lga_name AS "LGA",
100.0*count(*) FILTER (WHERE substr(CAST(alum_code AS VARCHAR),1,1)='4')/nullif(count(*) FILTER (WHERE substr(CAST(alum_code AS VARCHAR),1,1) IN ('3','4')),0) AS "Irrigated land share (%)"
FROM {cp} WHERE lga_name IN {lgas} GROUP BY 1,2,3 HAVING count(*) FILTER (WHERE substr(CAST(alum_code AS VARCHAR),1,1) IN ('3','4'))>0 ORDER BY 4 DESC
"""
for name,sql in [('riverina_lga_table',q),('riverina_hot_days_chart',qc),('riverina_irrigated_sa2_map',qm)]:
zn.write_out(con,name,sql)
EOF
bashcell 3 of 8
python3 - <<'EOF'
import zn
con=zn.lake_con(); cp=zn.read('cell_profile'); p45=zn.read('climate_projections',file='projections_ssp245'); p85=zn.read('climate_projections',file='projections_ssp585')
lgas="('Griffith','Leeton','Narrandera','Murrumbidgee','Murray River')"
base=f"""SELECT lga_name, count(*) n_cells, avg(ann_rainfall_mm) rain, avg(mean_tmax_c) tmax, avg(normal_days_ge_35) hot, avg(soc_pct) soc, avg(clay_pct) clay, count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1) IN('3','4')) ag, count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1)='4' AND alum_label='Irrigated cropping') irr_crop, count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1)='3' AND alum_label='Cropping') dry_crop, count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1)='2' AND alum_label LIKE 'Grazing%') graze FROM {cp} WHERE lga_name IN {lgas} GROUP BY lga_name"""
proj45=f"""SELECT c.lga_name, avg(p.days_over_35) d FROM {cp} c JOIN {p45} p ON p.h3_r4=h3_cell_to_parent(c.h3_r9,4) WHERE c.lga_name IN {lgas} AND p.decade=2050 GROUP BY c.lga_name"""
proj85=f"""SELECT c.lga_name, avg(p.days_over_35) d FROM {cp} c JOIN {p85} p ON p.h3_r4=h3_cell_to_parent(c.h3_r9,4) WHERE c.lga_name IN {lgas} AND p.decade=2050 GROUP BY c.lga_name"""
q=f"""WITH b AS ({base}), a AS ({proj45}), z AS ({proj85}) SELECT b.lga_name AS \"LGA\",b.n_cells AS \"r9 land cells\",round(b.rain,1) AS \"30-year rainfall normal (mm)\",round(b.tmax,1) AS \"Mean maximum temperature (°C)\",round(b.hot,1) AS \"Observed days over 35 °C (1991-2020 normal)\",round(a.d,1) AS \"Model days over 35 °C, 2050 (SSP2-4.5)\",round(z.d,1) AS \"Model days over 35 °C, 2050 (SSP5-8.5)\",round(z.d-b.hot,1) AS \"Change in hot days, 2050 SSP5-8.5 (days)\",round(b.soc,2) AS \"Topsoil carbon (%)\",round(b.clay,1) AS \"Clay (%)\",round(100*b.irr_crop/nullif(b.ag,0),1) AS \"Irrigated cropping share of agricultural land (%)\",round(100*b.dry_crop/nullif(b.ag,0),1) AS \"Dryland cropping share of agricultural land (%)\",round(100*b.graze/nullif(b.ag,0),1) AS \"Grazing share of agricultural land (%)\" FROM b JOIN a USING(lga_name) JOIN z USING(lga_name) ORDER BY b.lga_name"""
qc=f"""WITH b AS (SELECT lga_name,avg(normal_days_ge_35) hot FROM {cp} WHERE lga_name IN {lgas} GROUP BY lga_name), a AS ({proj45}), z AS ({proj85}) SELECT b.lga_name AS \"LGA\",'SSP2-4.5' AS \"Scenario\",b.hot AS \"Observed days over 35 °C\",a.d AS \"Projected days over 35 °C (2050)\" FROM b JOIN a USING(lga_name) UNION ALL SELECT b.lga_name,'SSP5-8.5',b.hot,z.d FROM b JOIN z USING(lga_name) ORDER BY 1,2"""
qm=f"""SELECT sa2_code AS \"sa2_code\",sa2_name AS \"SA2\",lga_name AS \"LGA\",100.0*count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1)='4')/nullif(count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1) IN('3','4')),0) AS \"Irrigated land share (%)\" FROM {cp} WHERE lga_name IN {lgas} GROUP BY sa2_code,sa2_name,lga_name HAVING count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1) IN('3','4'))>0 ORDER BY 4 DESC"""
for n,s in [('riverina_lga_table',q),('riverina_hot_days_chart',qc),('riverina_irrigated_sa2_map',qm)]: zn.write_out(con,n,s)
EOF
bashcell 4 of 8
python3 - <<'EOF'
import zn
con=zn.lake_con(); cp=zn.read('cell_profile')
lgas="('Griffith','Leeton','Narrandera','Murrumbidgee','Murray River')"
q=f"""SELECT sa2_code AS \"sa2_code\",max(sa2_name) AS \"SA2\",max(lga_name) AS \"LGA\",100.0*count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1)='4')/nullif(count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1) IN('3','4')),0) AS \"Irrigated land share (%)\" FROM {cp} WHERE lga_name IN {lgas} GROUP BY sa2_code HAVING count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1) IN('3','4'))>0 ORDER BY 4 DESC"""
zn.write_out(con,'riverina_irrigated_sa2_map',q)
EOF
bashcell 5 of 8
python3 - <<'EOF'
import duckdb
for f in ['riverina_lga_table','riverina_hot_days_chart','riverina_irrigated_sa2_map']:
print('\n'+f)
print(duckdb.sql(f"SELECT * FROM read_csv_auto('/workspace/out/{f}.csv')").df().to_string(index=False))
EOF
bashcell 6 of 8
python3 - <<'EOF'
import zn
con=zn.lake_con(); cp=zn.read('cell_profile'); p45=zn.read('climate_projections',file='projections_ssp245'); p85=zn.read('climate_projections',file='projections_ssp585')
lgas="('Griffith','Leeton','Narrandera','Murrumbidgee','Murray River')"
base=f"""SELECT lga_name,count(*) n_cells,avg(ann_rainfall_mm) rain,avg(mean_tmax_c) tmax,avg(normal_days_ge_35) hot,avg(soc_pct) soc,avg(clay_pct) clay,count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1) IN('2','3','4')) ag,count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1)='4' AND alum_label='Irrigated cropping') irr_crop,count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1)='3' AND alum_label='Cropping') dry_crop,count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1)='2' AND alum_label LIKE 'Grazing%') graze FROM {cp} WHERE lga_name IN {lgas} GROUP BY lga_name"""
a=f"SELECT c.lga_name,avg(p.days_over_35) d FROM {cp} c JOIN {p45} p ON p.h3_r4=h3_cell_to_parent(c.h3_r9,4) WHERE c.lga_name IN {lgas} AND p.decade=2050 GROUP BY c.lga_name"""; z=f"SELECT c.lga_name,avg(p.days_over_35) d FROM {cp} c JOIN {p85} p ON p.h3_r4=h3_cell_to_parent(c.h3_r9,4) WHERE c.lga_name IN {lgas} AND p.decade=2050 GROUP BY c.lga_name"
q=f"""WITH b AS({base}),a AS({a}),z AS({z}) SELECT b.lga_name AS \"LGA\",b.n_cells AS \"r9 land cells\",round(b.rain,1) AS \"30-year rainfall normal (mm)\",round(b.tmax,1) AS \"Mean maximum temperature (°C)\",round(b.hot,1) AS \"Observed days over 35 °C (1991-2020 normal)\",round(a.d,1) AS \"Model days over 35 °C, 2050 (SSP2-4.5)\",round(z.d,1) AS \"Model days over 35 °C, 2050 (SSP5-8.5)\",round(z.d-b.hot,1) AS \"Change in hot days, 2050 SSP5-8.5 (days)\",round(b.soc,2) AS \"Topsoil carbon (%)\",round(b.clay,1) AS \"Clay (%)\",round(100*b.irr_crop/nullif(b.ag,0),1) AS \"Irrigated cropping share of mapped agricultural land (%)\",round(100*b.dry_crop/nullif(b.ag,0),1) AS \"Dryland cropping share of mapped agricultural land (%)\",round(100*b.graze/nullif(b.ag,0),1) AS \"Grazing share of mapped agricultural land (%)\" FROM b JOIN a USING(lga_name) JOIN z USING(lga_name) ORDER BY b.lga_name"""
zn.write_out(con,'riverina_lga_table',q)
EOF
bashcell 7 of 8
python3 - <<'EOF'
import duckdb
print(duckdb.sql("select * from read_csv_auto('/workspace/out/riverina_lga_table.csv')").df().to_string(index=False))
EOF
bashcell 8 of 8
python3 - <<'EOF'
import zn
con=zn.lake_con(); cp=zn.read('cell_profile'); p45=zn.read('climate_projections',file='projections_ssp245'); p85=zn.read('climate_projections',file='projections_ssp585')
lgas="('Griffith','Leeton','Narrandera','Murrumbidgee','Murray River')"
q=f"""WITH b AS (SELECT lga_name,avg(ann_rainfall_mm) rain,avg(mean_tmax_c) tmax,avg(normal_days_ge_35) hot,avg(soc_pct) soc,avg(clay_pct) clay,count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1) IN('2','3','4')) ag,count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1)='4' AND alum_label='Irrigated cropping') irr_crop,count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1)='3' AND alum_label='Cropping') dry_crop,count(*) FILTER(WHERE substr(CAST(alum_code AS VARCHAR),1,1)='2' AND alum_label LIKE 'Grazing%') graze FROM {cp} WHERE lga_name IN {lgas} GROUP BY lga_name),a AS (SELECT c.lga_name,avg(p.days_over_35) d FROM {cp} c JOIN {p45} p ON p.h3_r4=h3_cell_to_parent(c.h3_r9,4) WHERE c.lga_name IN {lgas} AND p.decade=2050 GROUP BY c.lga_name),z AS (SELECT c.lga_name,avg(p.days_over_35) d FROM {cp} c JOIN {p85} p ON p.h3_r4=h3_cell_to_parent(c.h3_r9,4) WHERE c.lga_name IN {lgas} AND p.decade=2050 GROUP BY c.lga_name) SELECT b.lga_name AS \"LGA\",round(b.rain,1) AS \"Rainfall normal, 1991–2020 (mm/year)\",round(b.tmax,1) AS \"Mean maximum temperature, 1991–2020 (°C)\",round(b.hot,1) AS \"Observed days over 35 °C, 1991–2020 (days/year)\",round(a.d,1) AS \"Projected days over 35 °C, 2050 SSP2-4.5 (days/year)\",round(z.d,1) AS \"Projected days over 35 °C, 2050 SSP5-8.5 (days/year)\",round(z.d-b.hot,1) AS \"Change in hot days, 2050 SSP5-8.5 (days/year)\",round(b.soc,2) AS \"Topsoil organic carbon, 0–5 cm (%)\",round(b.clay,1) AS \"Clay, 0–5 cm (%)\",round(100*b.irr_crop/nullif(b.ag,0),1) AS \"Irrigated cropping share of mapped agricultural land (%)\",round(100*b.dry_crop/nullif(b.ag,0),1) AS \"Dryland cropping share of mapped agricultural land (%)\",round(100*b.graze/nullif(b.ag,0),1) AS \"Grazing share of mapped agricultural land (%)\" FROM b JOIN a USING(lga_name) JOIN z USING(lga_name) ORDER BY b.lga_name"""
qc=f"""WITH b AS (SELECT lga_name,avg(normal_days_ge_35) hot FROM {cp} WHERE lga_name IN {lgas} GROUP BY lga_name),a AS (SELECT c.lga_name,avg(p.days_over_35)-avg(c.normal_days_ge_35) d FROM {cp} c JOIN {p45} p ON p.h3_r4=h3_cell_to_parent(c.h3_r9,4) WHERE c.lga_name IN {lgas} AND p.decade=2050 GROUP BY c.lga_name),z AS (SELECT c.lga_name,avg(p.days_over_35)-avg(c.normal_days_ge_35) d FROM {cp} c JOIN {p85} p ON p.h3_r4=h3_cell_to_parent(c.h3_r9,4) WHERE c.lga_name IN {lgas} AND p.decade=2050 GROUP BY c.lga_name) SELECT a.lga_name AS \"LGA\",'SSP2-4.5' AS \"Scenario\",round(a.d,1) AS \"Change in hot days (days/year)\" FROM a UNION ALL SELECT z.lga_name,'SSP5-8.5',round(z.d,1) FROM z ORDER BY 1,2"""
zn.write_out(con,'riverina_lga_table',q); zn.write_out(con,'riverina_hot_days_change_chart',qc)
EOF
What this run does not tell you
The report carries its own notes on the data, reproduced here exactly as the agent wrote them:
Rainfall and soil values are area averages for the five named NSW LGAs. The baseline is the 1991–2020 climate normal, not a current-year observation.
Projected hot days are ACCESS-CM2 output for the five-year window centred on 2050 under separate SSP2-4.5 and SSP5-8.5 scenarios. The model is one CMIP6 model, not an ensemble.
Land-use shares use dominant mapped ABARES CLUM classes. Irrigated cropping means class 4 cropping, dryland cropping means class 3 cropping, and grazing includes class 2 grazing; these are mapped land-use categories, not water entitlements, current irrigation use or farm output.
The one worth restating in the sharpest possible terms: mapped land use is not water. A cell classified as irrigated cropping says a mapping programme classified it that way. It says nothing about entitlement volume, allocation in any given year, storage levels, groundwater, or whether a hectare was actually watered. A run that answered "how much water do these councils have" from this data would be inventing an answer, and this one says so instead.
Two more. Dominant-class mapping loses the mix — each cell is assigned one land use, so a farm running irrigated cropping alongside grazing is counted as whichever dominates its cells. And one model is not a projection range: the 2050 heat counts are a single CMIP6 model under two scenarios, which is enough to show direction and nowhere near enough to bound uncertainty.
Run it on your own region
Five councils here because the prompt named five. The same comparison runs over any set of council areas, any statistical-area breakdown beneath them, and any of the climate, soil and land-use measures in the library. If you want to see it against your region, request access — or read how this is used for agriculture, water and carbon and by agribusiness banking and rural lending.