res-8/res-9 features: MAX rollup of the res-10 conserved layer over-counts 1.5x (32.6% vs 21.0%)

[cboettig]

The defect. Features stored at H3 res-8 (ACE ranks, plant/endemic richness, freshwater richness) or res-9 (connectivity) must be overlaid against a conserved-areas layer stored at res-10. Coarsening the conserved layer with MAX over the ~49 res-10 children of each res-8 cell inflates the result ~1.5x. The correct reducer is an area-weighted mean.

MAX is right when combining overlapping conservation units within a single cell, and wrong when coarsening across child cells. Same keyword, two operations — which is why models get it wrong.

Verified 2026-08-02, ACE statewide BioRank rank-5 (BioRankSW=5):

reducer	result
MAX over the 49 res-10 children	32.56%
area-weighted mean (SUM/49)	21.04%
2025 Assessment	21.09%

WITH cons10 AS (
  SELECT h10, LEAST(SUM((Final_g1_p+Final_g2_p)/100.0),1) AS w12
  FROM read_parquet('s3://public-ca30x30/conserved-areas-terrestrial-2025/hex/h0=*/data_0.parquet')
  GROUP BY h10),
cons8 AS (
  SELECT h3_cell_to_parent(h10,8) AS h8,
         MAX(w12)      AS w12_max,       -- wrong when coarsening
         SUM(w12)/49.0 AS w12_areawt     -- correct
  FROM cons10 GROUP BY 1),
feat AS (
  SELECT DISTINCT h8
  FROM read_parquet('s3://public-cdfw/ace/terrestrial-biodiversity-summary/hex/h0=*/data_0.parquet')
  WHERE BioRankSW = 5)
SELECT ROUND(100*AVG(COALESCE(c.w12_max,0)),2)    AS pct_using_MAX_rollup,
       ROUND(100*AVG(COALESCE(c.w12_areawt,0)),2) AS pct_using_area_weighted_mean
FROM feat f LEFT JOIN cons8 c USING (h8);
-- 32.56 | 21.04

An equivalent correct approach avoids coarsening entirely: filter the res-10 conserved layer to the feature's h8 set and sum at res-10.

Impact on shipping models, 2 trials each — 32.7 is exactly the MAX artifact:

model	t1	t2
glm-5.2	21.2	21.2
kimi-k3	21.2	32.7
claude-sonnet-5	wrong method, no %	32.7

res-10 fractional features (CWHR habitats) are unaffected — 9/9 exact across all three models.

Fix belongs upstream, in the MCP tool's H3 guidance, not in this app's system prompt: boettiger-lab/mcp-data-server#312. Do not work around it here.

Done when: the guidance lands and a headless matrix (ACE BioRankSW rank-5, 3 trials x 3 shipping models) returns 21.x in every cell.