SEEALand, end to end

WITH aoi AS (                       -- the ecosystem accounting area, as res-8 hexes
  SELECT DISTINCT h8
  FROM read_parquet('s3://public-overturemaps/2026-02-18.0/countries/hex/h0=*/data_0.parquet')
  WHERE h0 IN (578290339652042751, 578395892768309247)
    AND country = 'CR' AND class = 'land'
)
SELECT f.lc_class,
       ROUND(SUM(f.frac * h3_cell_area(f.h9, 'km^2')), 1) AS area_km2
FROM read_parquet('s3://public-land-cover/cgls-lc100-2019/hex-fractions/h0=*/data_0.parquet') f
SEMI JOIN aoi a ON f.h8 = a.h8
WHERE f.h0 IN (578290339652042751, 578395892768309247)
  AND f.lc_class NOT IN (0, 200, 80, 255)   -- no-data, open sea, water, sentinel
GROUP BY f.lc_class
ORDER BY area_km2 DESC;

frac × cell area is the primitive the whole account rests on. Over Costa Rica it returns 51,078 km² against the country's own land polygon at 51,181 km² — −0.20%. Constraining h0 is partition pruning for speed; the SEMI JOIN is what actually clips to the accounting area.

WITH aoi AS (
  SELECT DISTINCT h8
  FROM read_parquet('s3://public-overturemaps/2026-02-18.0/countries/hex/h0=*/data_0.parquet')
  WHERE h0 IN (578290339652042751, 578395892768309247)
    AND country = 'CR' AND class = 'land'
),
lc AS (                             -- how much of each ET sits inside each res-8 cell
  SELECT h8, lc_class, SUM(frac * h3_cell_area(h9, 'km^2')) AS et_km2
  FROM read_parquet('s3://public-land-cover/cgls-lc100-2019/hex-fractions/h0=*/data_0.parquet')
  WHERE h0 IN (578290339652042751, 578395892768309247)
    AND lc_class NOT IN (0, 200, 80, 255)
  GROUP BY h8, lc_class
)
SELECT lc.lc_class,
       ROUND(SUM(g.msa * lc.et_km2) / SUM(lc.et_km2), 4) AS msa_by_et,
       ROUND(SUM(lc.et_km2), 1) AS et_area_km2
FROM lc
JOIN read_parquet('s3://public-globio/globio-msa-2015-overall/hex/h0=*/data_0.parquet') g
  ON g.h8 = lc.h8 AND g.h0 IN (578290339652042751, 578395892768309247)
SEMI JOIN aoi a ON lc.h8 = a.h8
GROUP BY lc.lc_class
ORDER BY et_area_km2 DESC;

MSA is a bounded intensity, so this is an area-weighted mean, never a sum. The result orders as ecology predicts — closed broadleaf forest 0.544, cropland 0.266, urban 0.241 — which is the real evidence the join is right. MSA needs no rescaling step: it already runs [0,1] against an undisturbed reference, so stage 2b is the identity for this one variable.

WITH aoi AS (
  SELECT DISTINCT h8
  FROM read_parquet('s3://public-overturemaps/2026-02-18.0/countries/hex/h0=*/data_0.parquet')
  WHERE h0 IN (578290339652042751, 578395892768309247)
    AND country = 'CR' AND class = 'land'
),
forest AS (                         -- neighbour universe: NOT clipped, so k-rings aren't truncated
  SELECT h9, h8, SUM(frac) AS forest_frac
  FROM read_parquet('s3://public-land-cover/cgls-lc100-2019/hex-fractions/h0=*/data_0.parquet')
  WHERE h0 IN (578290339652042751, 578395892768309247)
    AND lc_class BETWEEN 111 AND 126
  GROUP BY h9, h8
),
focal AS (                          -- but score only the cells inside the accounting area
  SELECT f.h9 FROM forest f SEMI JOIN aoi a ON f.h8 = a.h8
),
nbr AS (
  SELECT f.h9 AS focal, UNNEST(h3_grid_disk(f.h9, 3)) AS neighbour FROM focal f
)
SELECT n.focal,
       AVG(COALESCE(f2.forest_frac, 0)) AS forest_area_density
FROM nbr n LEFT JOIN forest f2 ON f2.h9 = n.neighbour
GROUP BY n.focal;

This is the variable that carries SEEALand's forest decline — 74% → 59% in the annex. On an H3 grid the metric that normally needs dedicated raster tooling is one UNNEST. Measured: 404,240 focal cells at k=3 in 3.4 s; raising k from 1 to 40 multiplies rows by 700× but wall clock by only 9×, so the neighbourhood radius is effectively free and EAA size is what binds.

WITH aoi AS (
  SELECT DISTINCT h8
  FROM read_parquet('s3://public-overturemaps/2026-02-18.0/countries/hex/h0=*/data_0.parquet')
  WHERE h0 IN (578290339652042751, 578395892768309247)
    AND country = 'CR' AND class = 'land'
)
SELECT ROUND(SUM(c.carbon) / 1e6, 2) AS mt_irrecoverable_c
FROM read_parquet('s3://public-carbon/irrecoverable-carbon-2024/hex/h0=*/data_0.parquet') c
SEMI JOIN aoi a ON c.h8 = a.h8
WHERE c.h0 IN (578290339652042751, 578395892768309247);

SUM is correct here, and it is the exact opposite of the MSA rule above. carbon is a per-cell total (an amount), so it sums; MSA is a per-cell intensity, so it averages. The two look identical in SQL and produce silently wrong accounts if swapped. Costa Rica: 52.93 Mt C irrecoverable, 2024.

To split it by ecosystem type you apportion the amount by frac and then sum — the mirror image of weighting an intensity by area and then averaging. Both layers are native res 9, so that join is exact. The rest of the service list needs the biophysical logic chains and is not yet computable.

Stages 4 and 5 are price × quantity and a discount factor. There is no spatial data to query: once stage 3 has produced physical flows per ecosystem type, the monetary accounts are ordinary arithmetic over a small table.

That is exactly why Phase 1 is blocked on nothing. We can implement and test the entire monetary half of the standard against SEEALand's own numbers without a single catalog layer — and the prices, discount rate and asset life become visible controls in the app rather than buried constants, because the standard itself treats them as illustrative rather than prescribed.

NPV factorises exactly into three terms, per ecosystem type and service:

    Q = expected annual flow × asset life        a = area, hectares
    q = Q / a      quantity per hectare          p = NPV / Q, implied unit price

    NPV = a · q · p

The change is split symmetrically across the three factors — the Shapley
decomposition of a product — so the parts sum exactly to the whole, with
no residual left over to dump somewhere:

    area   = (a₁−a₀) · ( (q₀p₀ + q₁p₁)/3 + (q₀p₁ + q₁p₀)/6 )   → CONVERSION
    volume = (q₁−q₀) · ( (a₀p₀ + a₁p₁)/3 + (a₀p₁ + a₁p₀)/6 )   → DEGRADATION / ENHANCEMENT
    price  = (p₁−p₀) · ( (a₀q₀ + a₁q₁)/3 + (a₀q₁ + a₁q₀)/6 )   → REVALUATION

Forest, all three services summed:

    −43,435  +  −108,111  +  +35,180  =  −116,366

This decomposition is the load-bearing idea of the whole standard. Without it you have a single number for the change in asset value and no way to post it: a conversion, a degradation and a price rise are three different accounting entries that happen to sum to the same total, and only one of them is something a policy did.

The symmetric form matters — a naive p0·q0·(a1−a0) style split leaves a residual that has to be dumped somewhere. These formulae reproduce the annex's forest figures to machine precision, verified against npv-decomposition.csv.