GBIF and OBIS hex: h0 is not the H3 parent of the native cell (8.7% / 7.7% of rows); cng-datasets builds are exact

[cboettig]

Revised 2026-09-20. First filed as GBIF-only ("every other dataset is exact"). A survey
found OBIS shares it, and a control isolated the real discriminator — it is not point vs
polygon geometry, it is how the pipeline derives the parent columns. Title and scope updated.

Summary

In two hand-written occurrence pipelines, the h0 hive partition key is not the H3 parent
of the row's native cell. Everything cng-datasets builds is exact.

collection	built by	native	rows	cell_to_parent(native,0) <> h0
gbif-derived 2026-09	bespoke script	h10	3,570,154,958	8.713%
obis-derived 2026-09-09	bespoke script	h8	228,567,957	7.675%
inaturalist-ranges	cng-datasets	h4	377,963,890	0
unep-wcmc-coral-reefs-points	cng-datasets	h8	925	0

(GBIF's densest single h0 reads 21.754% — the rate varies strongly by geography.)

The coral-points row is the control that settles the cause. It is the same shape as GBIF
and OBIS — point geometry, native resolution 8, h0 as partition key — and it is exact. So
the discriminator is not geometry type and not point-ness; it is that cng-datasets derives
parents with cell_to_parent from a single native cell, while the two occurrence pipelines
recompute every resolution independently from the coordinate:

-- catalog/gbif/*/process_gbif_h3.py
h3_latlng_to_cell(decimallatitude, decimallongitude, 0)  AS h0,
h3_latlng_to_cell(decimallatitude, decimallongitude, 1)  AS h1,
...

H3's parent relation is combinatorial, not geometric: an aperture-7 hex grid does not nest
exactly, so a res-8 cell's area straddles several res-7 cells. A point near a boundary
therefore lands in a res-0 cell that is not cellToParent of its res-8 cell. Every h_k value
here is individually the correct cell for that point — this is correct H3 behaviour being used
in a way that breaks an invariant the rest of the catalog holds.

inaturalist-ranges is the positive control for the other direction: cell_to_parent(h4,2) <> h2
is 0 of 378 M, so cng-datasets' whole derived chain is internally consistent.

⚠️ What is NOT broken — read this before changing any query

h8 = h8 joins are exact and correct, including GBIF↔OBIS. Both pipelines compute h8
from the same lat/lng with the same call, so cells agree. The obis-derived description
advertises h8 as the resolution at which OBIS and GBIF can be compared, and that comparison
is sound. Using h8 directly as the join key is what AGENTS.md prescribes and what the
catalog actually does.

Only deriving a coarser cell via cell_to_parent breaks. A reader who takes away "the H3
columns are unreliable" will drop a join that is correct.

Why it matters

1. Partition pruning by derived parent silently under-reads. catalog/gbif/README.md sells
   "Partition pruning: h0 partitioning eliminates entire directories from query". A consumer
   holding an h8 and pruning to h0 = h3_cell_to_parent(h8,0) misses ~8.7% of GBIF rows and
   ~7.7% of OBIS rows — and 0% in every cng-datasets collection. That asymmetry is the
   trap: a pattern validated on WDPA, IUCN or coral-reefs transfers to GBIF/OBIS and returns
   quietly incomplete results, with no error raised.
2. OBIS is the more exposed of the two. Its h3:parent_resolutions is [0], so h0 is
   purely a partition key with no intermediate resolution to absorb the discrepancy. GBIF at
   least carries h1–h10.
3. Hierarchical rollup disagrees with itself in the affected collections: GROUP BY h5 then
   aggregating to h2 via cell_to_parent does not match a direct GROUP BY h2.

Options

• Document it (cheapest): state the semantics in the GBIF and OBIS hex asset descriptions,
  warn against deriving the partition key via cell_to_parent, and say explicitly that h8
  joins are unaffected. Does not fix rollup.
• Change the build to derive non-native resolutions with cell_to_parent from a single
  native cell, matching the rest of the catalog. Makes all resolutions mutually consistent and
  pruning-safe, at the cost of each h_k no longer being the exact cell containing the point at
  resolution k. That is a real semantic trade, not a pure fix — decide it deliberately.
• Cheap landing spot: public-obis has an open re-hex to native h10 (Re-hex OBIS to native h10 (built at h8), from the staged 2026-09-09 raw #664) and an open
  ingest PR (OBIS native ingest: 228.6M marine occurrence records at H3 resolution 8 (#660) #661). That re-hex rebuilds these columns regardless, so if the fix is
  cell_to_parent, it costs nothing extra there.
• Either way, verify-stac.py could assert the parent-chain property so a future bespoke
  pipeline does not reintroduce it silently.

Provenance

Surfaced from #695 (which needs only that its grouping key be a prefix of the sort key, and is
unaffected by this). Raised by a parallel audit session; the survey above was contributed there
and every figure was re-measured independently here via the duckdb-geo MCP.

[cboettig]

The open question, answered: OBIS shares it. It is not GBIF-only — it is the two bespoke point-occurrence ingests, and only those.

I checked the remaining point candidates. The split is exactly along how the collection was built, not
along geometry type:

collection	built by	rows	cell_to_parent(native, 0) <> h0
gbif-derived 2026-09	bespoke process_gbif_h3.py	3,570,154,958	8.713%	(this issue)
obis-derived 2026-09-09	bespoke	228,567,957	7.675%	new — 17,543,064 rows
inaturalist-ranges range-maps (h4 native)	cng-datasets	377,963,890	0	also parent(h4,2) <> h2: 0
unep-wcmc-coral-reefs-points (h8 native)	cng-datasets	925	0	point geometry, still exact
wdpa	cng-datasets	207,833,890	0	(already in this issue)
iucn-ranges-2025	cng-datasets	4,077,008,576	0	(already in this issue)

The coral-reef points matter as the control that isolates the cause: point geometry, native
resolution 8, h0 partition key — the same shape as GBIF and OBIS — and exact, because
cng-datasets derives the parent with cell_to_parent. So this is not "point datasets are
inconsistent". It is "independently recomputing each resolution from the coordinate is
inconsistent", and only the two hand-written occurrence pipelines do that.

Why OBIS is the more exposed of the two

OBIS is built at native 8 with h3:parent_resolutions: [0] — h0 and h8 and nothing between — so
h0 is only a partition key. Every spatial query that prunes partitions and then works at h8 is
in the failure mode this issue describes, with no intermediate resolution to absorb it.

It also sits directly on the comparison this catalog advertises. The OBIS collection description says
resolution 8 "is also the resolution at which this data can be compared with the GBIF occurrence
collection". That comparison is safe — both derive h8 from the same lat/lng with the same H3
call, so h8 = h8 joins are exact in both directions. What is not safe is reaching h0 from h8 by
cell_to_parent in either collection. Worth stating explicitly wherever this gets documented, because
the two facts look contradictory and a reader who only takes away "GBIF/OBIS H3 columns are
unreliable" will abandon a join that is actually correct.

Scope note

public-obis has an open re-hex to native h10 (#664) and an open ingest PR (#661). If the fix here is
to derive parents with cell_to_parent in the bespoke pipelines, the OBIS re-hex is the cheap moment
to land it there — it is rebuilding those columns anyway.

Method, for anyone reproducing: h8/h4 are uint64 and h0 is int64 in these schemas, so both
sides need a cast — h3_cell_to_parent(CAST(h8 AS UBIGINT), 0) <> CAST(h0 AS UBIGINT). Read with
hive_partitioning=true so the partition h0 is the one being tested. All counts above are full
scans via the duckdb-geo MCP, not samples.