H3_SPATIAL_JOIN_FAST

Test whether two H3 cells overlap (equal or one is an ancestor of the other) using a fast hierarchical check.

Category: h3Returns: BOOLEANDialect: Standard

Syntax

H3_SPATIAL_JOIN_FAST(cell_a, cell_b)

Description

## Overview Returns TRUE when two H3 cells overlap in the hierarchical grid. Two cells overlap if they are equal, or if one is an ancestor of the other at a coarser resolution. The test is implemented as a bit-level parent-chain comparison on the H3 index structure and runs in constant time regardless of resolution difference. Use this function as the join predicate for H3-indexed spatial joins. Instead of evaluating geometric containment between raw coordinates and polygon boundaries, both sides of the join carry a pre-computed H3 cell column (often at different resolutions, with the polygon side polyfilled at a coarser resolution). The function then equates point-cells with region-cells through the hierarchy. ## Behavior - Returns a BOOLEAN. - Returns TRUE if the two inputs are the same cell. - Returns TRUE if one input is an ancestor of the other (for example, a resolution 5 cell that contains a resolution 9 cell over the same location). - Returns FALSE if the two cells belong to different ancestor chains, even at close resolutions. - Returns NULL if either input is NULL. - Returns FALSE if either input is not a valid H3 cell. - Accepts BIGINT (Int64), UInt64, and the 15-hex-digit string form on either argument. String inputs may optionally be prefixed with 0x. - Symmetric: H3_SPATIAL_JOIN_FAST(a, b) equals H3_SPATIAL_JOIN_FAST(b, a). ## Why it matters A naive spatial join between N points and M polygons costs O(N*M) geometric tests. By polyfilling polygons into H3 cells at a chosen resolution and pre-indexing each point into an H3 cell at its own resolution, the join becomes a cheap equality or ancestor test on a BIGINT. Planners can often push this predicate into a hash join, bringing the total cost down to O(N + M_cells). ## H3 resolution reference | Res | Average edge length | Typical use for the region side of a join | | --- | --- | --- | | 5 | 8.54 km | Large administrative regions | | 7 | 1.22 km | Postal districts, delivery zones | | 8 | 461 m | City neighborhoods | | 9 | 174 m | Urban blocks | | 10 | 65.9 m | Building-scale features | ## Compatibility - Follows the standard H3 hierarchical hex grid specification for parent-child containment.

Parameters

Name	Type	Description
`cell_a`		Specifies the first H3 cell. May be at any resolution. Also accepts UInt64 or the 15-hex-digit string form (for example '892830926dfffff').
`cell_b`		Specifies the second H3 cell. May be at any resolution and may differ in resolution from cell_a. Also accepts UInt64 or the 15-hex-digit string form.

Examples

-- The same cell overlaps itself, so the test is trivially TRUE.
SELECT H3_SPATIAL_JOIN_FAST(
  H3_LATLNG_TO_CELL(40.7128, -74.0060, 9),
  H3_LATLNG_TO_CELL(40.7128, -74.0060, 9)
) AS overlap;

-- A resolution 5 cell overlaps a resolution 9 cell over the same point (one contains the other).
SELECT H3_SPATIAL_JOIN_FAST(
  H3_LATLNG_TO_CELL(40.7128, -74.0060, 5),
  H3_LATLNG_TO_CELL(40.7128, -74.0060, 9)
) AS overlap;

-- London and Paris at the same resolution share no ancestor chain at that resolution.
SELECT H3_SPATIAL_JOIN_FAST(
  H3_LATLNG_TO_CELL(51.5074, -0.1278, 7),
  H3_LATLNG_TO_CELL(48.8566, 2.3522, 7)
) AS overlap;

-- Points carry a resolution-9 cell; regions carry a resolution-7 cell.
-- The overlap test lets a resolution-9 point match its resolution-7 region.
SELECT p.point_id, r.region_name
FROM telemetry.curated.points_h3 p
JOIN reference.regions_h3 r
  ON H3_SPATIAL_JOIN_FAST(p.h3_res9, r.h3_res7);

SELECT z.zone_name,
       COUNT(*) AS arrivals
FROM mobility.curated.arrivals_h3 a
JOIN reference.delivery_zones_h3 z
  ON H3_SPATIAL_JOIN_FAST(a.h3_res10, z.h3_res8)
GROUP BY z.zone_name;

SELECT H3_SPATIAL_JOIN_FAST('852830a7fffffff', '892830926dfffff') AS overlap;

Pitfalls

This is a hierarchical overlap test, not a geometric overlap test. It tells you whether the two cells share an ancestor chain in the H3 tree, not whether their geometries intersect in the real world. Cells that are geographic neighbors (for example, two res 9 cells across a res 7 boundary) do not overlap under this function.
Resolution mismatch is allowed and handled correctly. Do not filter out rows with resolution mismatches before the join; the function is designed to bridge resolutions.
Boundary coverage of polyfilled regions is not exact. A point that falls inside a polygon but whose H3 cell was not included in the polyfill set (because its center lies outside the polygon) is missed. Increase the polyfill resolution or expand with a ring when boundary exactness matters.
Invalid or corrupted cell values produce FALSE rather than an error, which can mask bad input. Validate with H3_IS_VALID_CELL during ingest.
For exact geometric overlap (is this point inside this polygon with no H3 bucketing error), pair this function with a follow-up ST_CONTAINS or ST_INTERSECTS on the small result set.