# Precomputed place statistics

One Parquet file per (ERDDAP dataset, variable, gazetteer place), covering the dataset's **full
record** as ERDDAP serves it: NOAA CRW daily SST and bleaching alert area from 1985, the AOML 8-day
Seascapes, and the monthly "sanctuaries series" (CMEMS biogeochemistry and physics, IMERG
precipitation, VGPM NPP over the 18 `NMS:*` places). `DAYS = 'all'` in `precompute/src/targets.ts`;
until 2026-10-12 the daily and 8-day grids covered only the last 365 days. Places outside an ocean
model's domain (the Great Lakes, Mallows Bay) get a valid, empty file. Published at
`https://storage.oceanmetrics.io/gazetteer/stats/`, refreshed daily (each file is published as soon
as it is computed, so a long backfill fills in over several days). Until it is done, each item
link and Item says how far it has got in `erddap-places:status`: `complete` when the file starts at
the dataset's first time step, and `partial` when it starts later. `erddap-places:pending` in
`collection.json` lists the targets with no file yet, and `erddap-places:item_counts` totals all three.

These are the same numbers the [browser app](https://oceanmetrics.io/erddap-places/) computes live:
the place polygon masks the dataset's own grid (a cell is in when its **centre** falls inside, and
boundary cells carry a **partial-area weight**), then `sql/stats_daily.sql` or
`sql/stats_categorical.sql` aggregates per day. The precompute script (`precompute/` in the repo)
imports the app's own `gridMask.ts`, `wkb.ts`, `erddap.ts` and `gazetteer.ts`, so a precomputed row
and a live in-browser row cannot drift. Use these when you want the answer *now*; run the live query
when you want *today*.

## Files

```
stats/
  collection.json                       # this collection
  items/<dataset>_<variable>_<place>.json   # one STAC Item per file
  dhw_5km/CRW_SST/NMS:HIHWNMS.parquet   # the data
  dhw_5km/CRW_SST/NMS:HIHWNMS.provenance.json   # the griddap URLs, mask size and SQL used
```

The object key keeps the place id verbatim, **colon and all** (`NMS:HIHWNMS.parquet`). A colon is a
legal S3 key character and a legal URL path character (RFC 3986 `pchar`), and `aws s3 sync`, `curl`
and DuckDB `httpfs` all round-trip it. STAC Item ids replace the colon with `-`
(`dhw_5km_CRW_SST_NMS-HIHWNMS`) so an id is always safe as a file name and a URL fragment.

## Read one

```sql
SELECT * FROM read_parquet('https://storage.oceanmetrics.io/gazetteer/stats/dhw_5km/CRW_SST/NMS:HIHWNMS.parquet')
ORDER BY date;
```

Read many at once by **listing** the URLs — DuckDB cannot glob over plain HTTPS (there is no
directory listing to expand `*` against), so pass an array. Every file carries `place_id`,
`dataset_id` and `variable`, so the union is self-describing:

```sql
SELECT place_id, avg(mean_wt) AS mean_sst
FROM read_parquet([
  'https://storage.oceanmetrics.io/gazetteer/stats/dhw_5km/CRW_SST/NMS:FKNMS.parquet',
  'https://storage.oceanmetrics.io/gazetteer/stats/dhw_5km/CRW_SST/NMS:HIHWNMS.parquet',
  'https://storage.oceanmetrics.io/gazetteer/stats/dhw_5km/CRW_SST/NMS:PMNM.parquet'])
GROUP BY place_id ORDER BY 2 DESC;
```

The URL list comes from `collection.json` (one `rel: "item"` link per file) or, with bucket
credentials, from a real glob: `read_parquet('s3://oceanmetrics.io-public/gazetteer/stats/dhw_5km/CRW_SST/*.parquet')`.

## Coverage

| dataset | variable | kind | places |
|---------|----------|------|--------|
| `dhw_5km` | `CRW_SST` | continuous (daily) | all 20 |
| `dhw_5km` | `CRW_BAA` | categorical (bleaching alert area 0–4) | all 20 |
| `noaa_aoml_seascapes_8day` | `CLASS` | categorical (seascape class 1–33) | the 5 reef/tropical places: `NMS:FKNMS`, `PSGID:939`, `NMS:HIHWNMS`, `NMS:NMSAS`, `NMS:PMNM` |

`NMS:PMNM` crosses the antimeridian, so it is fetched as **two lobes** (one each side of ±180) and
the two masks are unioned before aggregating. Its Item `bbox` crosses ±180 with west > east
(RFC 7946 §5), and so does the collection extent, instead of spanning the globe (from 2026-10-15).

## Schema

Continuous variables (`sql/stats_daily.sql`):

| Column | Type | Description |
|--------|------|-------------|
| date | date | UTC day of the time step |
| n | int64 | grid cells with a non-null value that day |
| mean | double | unweighted mean over the masked cells |
| mean_wt | double | area-weighted mean (partial boundary cells count for their overlap) |
| sd | double | sample standard deviation |
| min / max | double | extremes |
| p10 / p90 | double | 10th / 90th percentile |
| weight_sum | double | sum of the contributing cell weights |
| place_id, dataset_id, variable | string | so the files union cleanly |

Categorical variables (`sql/stats_categorical.sql`):

| Column | Type | Description |
|--------|------|-------------|
| date | date | UTC day of the time step |
| class | int64 | the class value (labels live in the dataset collection's `erddap-places:classes`) |
| n | int64 | cells in that class |
| weight | double | sum of those cells' area weights |
| frac_area | double | area-weighted share of the place; sums to 1 over a date (the SQL template calls this `fraction`) |
| pct_cells | double | unweighted percent of masked cells (the template calls this `percent_cells`) |
| place_id, dataset_id, variable | string | so the files union cleanly |

## Known gaps and one accepted warning

- `NMS:MNMS` (Monitor NMS, 2 km²) is **smaller than one 5 km grid cell** and no cell centre falls
  inside it, so its mask is empty and its files have 0 rows. That is the same answer the live app
  gives; a finer dataset (`jplMURSST41`, 0.01°) would resolve it.
- The Great Lakes sanctuaries (`NMS:TBNMS`, `NMS:LONMS`, `NMS:WSCNMS`) mask cells fine, but
  `dhw_5km` is an ocean product and is all-null there, so those files are 0 rows too. 4 of the 20
  `dhw_5km` places are empty for this reason; the other 16 have a full 364-day series.
- `rashid check` reports one warning on this collection, **PTL-CAT-001** ("45 children with no
  subcatalog grouping them"). Grouping the items into per-dataset subcatalogs would break the flat
  `stats/{dataset}/{variable}/{place_id}.parquet` path convention that makes the data guessable from
  a place id, so the warning is accepted deliberately. The catalog is at 0 errors.

## Provenance

Every parquet has a sibling `*.provenance.json` with the exact griddap request URLs, the number of
lobes and masked cells, the rendered SQL and the generation timestamp. `axes` (from 2026-10-09) lists,
per lobe in `placeLobes()` order, the lobe's `bbox` and the server's `lon` / `lat` axis values over it:
what the mask was computed from, so a client can rebuild the mask (and the app its map request) without
asking ERDDAP for the axes. `end_datetime` is the last time step used, an instant the server holds. The same URL list is on the
Item as `erddap-places:griddap_urls` when there are at most 24 requests. A full daily record has
hundreds (one per ≤90-day chunk and lobe), so the provenance always carries `griddap_url_templates`
(one per lobe, the time constraint written `({t0}):1:({t1})`) and `griddap_chunks` (every request's
`[t0, t1]` instants): together they give every URL exactly. The Item carries the templates and
`erddap-places:griddap_requests`.

## Regenerating

The parquet files are **not in git** (see `.gitignore`); the STAC Items and this collection are.
Rebuild them from the repo root:

```bash
cd precompute && npm ci && npm run stats       # polite: <=2 concurrent griddap requests
rashid check catalog/gazetteer
```

Runs are **incremental**. Each target is fetched in chunks of at most 90 days (fewer for a large
place, so one request stays under 100,000 values, the `n_max_vals_per_req` of marinebon/extractr's
`ed_extract()`: many light requests keep an ERDDAP server responsive), each chunk is aggregated to its daily rows as soon
as it arrives, and those rows are kept in `PRECOMPUTE_CHUNKS` (default `precompute/.cache/chunks/`;
the runner keeps it outside the checkout), keyed by a hash of the mask. The raw slabs are deleted.
A later run asks ERDDAP only for chunks it has not done, which after the first full-record run is the
last one, and an interrupted run resumes where it stopped. A changed polygon or grid changes the
key, so old rows are never reused for a different mask. The first full-record run is long (hours:
thousands of requests to PacIOOS, most of them for `NMS:PMNM`).

Runs are **polite**. Each (dataset, variable, place) is a unit, scheduled per ERDDAP server: one lane
per server, the lanes side by side, places one after another within a lane, at most 2 requests in
flight. When a unit fails, its server rests (10 minutes, then 30, 60 and 120 on consecutive failures)
and the unit goes to the back of the queue; the other servers keep working. A unit is given up for the
run after 3 tries (its finished chunks stay), and a run starts no new work after `PRECOMPUTE_HOURS`
(21 h in CI), stopping a large place at its next chunk. With `PRECOMPUTE_PUBLISH` set, every finished
file, its Item and the updated `collection.json` are uploaded at once.

CI does exactly this daily — see `.github/workflows/stats.yml`.

## License

CC-BY-4.0 for these derived files. The upstream grids are CC0-1.0 (NOAA CRW, NOAA AOML).