H3 GPS Fleet Tracker, 10K Pings, 21 H3 Functions

10,000 deterministic GPS pings across 5 world cities, indexed with H3 resolution-9 cells and joined against polyfilled city polygons for O(1) spatial joins, exercises all 21 H3 functions with known-value assertions.

Category: spatial

Syntax

-- ============================================================================
-- SETUP, zone, schema, 3 Delta tables + 2 views
-- ============================================================================
CREATE ZONE IF NOT EXISTS {{zone_name}} TYPE EXTERNAL
    COMMENT 'External and Delta tables, demo datasets';

CREATE SCHEMA IF NOT EXISTS {{zone_name}}.spatial_demos
    COMMENT 'H3 spatial indexing and geographic analysis tables';

CREATE DELTA TABLE IF NOT EXISTS {{zone_name}}.spatial_demos.landmarks (
    id INT, name VARCHAR, city VARCHAR, country VARCHAR,
    lat DOUBLE, lng DOUBLE
) LOCATION '{{data_path}}/landmarks';

CREATE DELTA TABLE IF NOT EXISTS {{zone_name}}.spatial_demos.regions (
    region_id INT, region_name VARCHAR, country VARCHAR,
    polygon_wkt VARCHAR, timezone VARCHAR
) LOCATION '{{data_path}}/regions';

CREATE DELTA TABLE IF NOT EXISTS {{zone_name}}.spatial_demos.gps_points (
    id BIGINT, lat DOUBLE, lng DOUBLE, device_id VARCHAR, city VARCHAR
) LOCATION '{{data_path}}/gps_points';

-- Deterministic point generation (golden-ratio quasi-random, 2000 per city)
INSERT INTO {{zone_name}}.spatial_demos.gps_points
SELECT id,
       37.70  + (0.12 * ((CAST(id AS DOUBLE) * 0.618033988749895) % 1.0)) AS lat,
       -122.52 + (0.17 * ((CAST(id AS DOUBLE) * 0.381966011250105) % 1.0)) AS lng,
       'device_sf_' || (id % 50) AS device_id,
       'San Francisco' AS city
FROM generate_series(1, 2000) AS t(id);
-- (repeat for Manhattan, Paris, London, Tokyo with offset IDs)

CREATE OR REPLACE VIEW {{zone_name}}.spatial_demos.points_h3 AS
SELECT id, lat, lng, device_id, city,
       h3_latlng_to_cell(lat, lng, 9) AS h3_cell
FROM {{zone_name}}.spatial_demos.gps_points;

CREATE OR REPLACE VIEW {{zone_name}}.spatial_demos.region_cells AS
SELECT region_id, region_name, country, timezone,
       UNNEST(h3_polyfill(polygon_wkt, 9)) AS h3_cell
FROM {{zone_name}}.spatial_demos.regions;

-- ============================================================================
-- QUERIES, 21 H3 functions, all asserted against known values
-- ============================================================================
-- Coordinate conversion and validation
SELECT name, lat, lng,
       h3_cell_to_string (h3_latlng_to_cell(lat, lng, 9)) AS h3_hex,
       h3_is_valid_cell  (h3_latlng_to_cell(lat, lng, 9)) AS is_valid,
       h3_get_resolution (h3_latlng_to_cell(lat, lng, 9)) AS resolution,
       h3_is_res_class_iii(h3_latlng_to_cell(lat, lng, 9)) AS is_class_iii
FROM {{zone_name}}.spatial_demos.landmarks ORDER BY id;

-- Grid topology, 6 neighbors at k=1, 12 at k=2, 7 disk at k=1
SELECT (SELECT COUNT(*) FROM (SELECT UNNEST(h3_hex_ring(h3_latlng_to_cell(37.7792,-122.4191,9),1)) AS c)) AS ring_k1,
       (SELECT COUNT(*) FROM (SELECT UNNEST(h3_hex_ring(h3_latlng_to_cell(37.7792,-122.4191,9),2)) AS c)) AS ring_k2,
       (SELECT COUNT(*) FROM (SELECT UNNEST(h3_hex_disk(h3_latlng_to_cell(37.7792,-122.4191,9),1)) AS c)) AS disk_k1;

-- Cell metrics, area and edge length (res 9 = ~105,000 m^2, ~174.4 m edge)
SELECT ROUND(h3_cell_area  (h3_latlng_to_cell(37.7792,-122.4191,9)), 0) AS area_m2,
       ROUND(h3_cell_area_km2(h3_latlng_to_cell(37.7792,-122.4191,9)), 6) AS area_km2,
       ROUND(h3_edge_length(h3_latlng_to_cell(37.7792,-122.4191,9)), 1) AS edge_m;

-- Polyfill: SF bounding box at res 9 should yield 1500+ cells
SELECT COUNT(*) AS cell_count
FROM (SELECT UNNEST(h3_polyfill(
        'POLYGON((-122.52 37.70, -122.35 37.70, -122.35 37.82, -122.52 37.82, -122.52 37.70))',
        9)) AS cell);

-- O(1) spatial join, points_h3.h3_cell = region_cells.h3_cell
SELECT r.region_name, r.country,
       COUNT(DISTINCT p.id)        AS points_in_region,
       COUNT(DISTINCT p.device_id) AS unique_devices
FROM {{zone_name}}.spatial_demos.points_h3  p
INNER JOIN {{zone_name}}.spatial_demos.region_cells r ON p.h3_cell = r.h3_cell
GROUP BY r.region_name, r.country
ORDER BY points_in_region DESC;

-- Accuracy check: SF City Hall inside SF region (hit), Statue of Liberty is not
SELECT (SELECT COUNT(*) FROM {{zone_name}}.spatial_demos.region_cells
        WHERE region_name = 'San Francisco'
          AND h3_cell = h3_latlng_to_cell(37.7792, -122.4191, 9)) AS sf_match,
       (SELECT COUNT(*) FROM {{zone_name}}.spatial_demos.region_cells
        WHERE region_name = 'San Francisco'
          AND h3_cell = h3_latlng_to_cell(40.6892,  -74.0445, 9)) AS ny_match;

-- ============================================================================
-- CLEANUP
-- ============================================================================
DROP VIEW       IF EXISTS {{zone_name}}.spatial_demos.region_cells;
DROP VIEW       IF EXISTS {{zone_name}}.spatial_demos.points_h3;
DROP DELTA TABLE IF EXISTS {{zone_name}}.spatial_demos.gps_points WITH FILES;
DROP DELTA TABLE IF EXISTS {{zone_name}}.spatial_demos.regions    WITH FILES;
DROP DELTA TABLE IF EXISTS {{zone_name}}.spatial_demos.landmarks  WITH FILES;
DROP SCHEMA     IF EXISTS {{zone_name}}.spatial_demos;
DROP ZONE       IF EXISTS {{zone_name}};

Description

## When to Use Reach for this demo when verifying that DeltaForge's H3 layer matches the reference Uber H3 library across every public function, coordinate conversion, validation, grid topology, hierarchy, metrics, polyfill, and string conversion. It is the authoritative regression suite: every assertion is grounded in an H3 constant (6 neighbors at k=1, 7-children subdivision, 174.4 m edge at res 9, 105,000 m² cell area) rather than an empirical measurement. ## What You Will Learn 1. How to generate 10,000 deterministic GPS pings with `generate_series` and a golden-ratio quasi-random distribution that avoids lattice artifacts 2. How to index points with `h3_latlng_to_cell(lat, lng, 9)` and round-trip through `h3_cell_to_lat` / `h3_cell_to_lng` 3. How to validate cells with `h3_is_valid_cell`, `h3_is_pentagon`, `h3_is_res_class_iii`, and `h3_get_resolution` 4. How to enumerate grid topology with `h3_hex_ring(cell, k)` (6 at k=1, 12 at k=2), `h3_hex_disk(cell, k)` (7 at k=1, 19 at k=2), `h3_grid_distance`, and `h3_grid_path` 5. How to walk the H3 hierarchy with `h3_cell_to_parent`, `h3_cell_to_children` (7 children per hex), and `h3_cell_to_center_child` 6. How to read geodesic metrics with `h3_cell_area` (m²), `h3_cell_area_km2`, and `h3_edge_length` (m, from a static per-resolution table) 7. How to convert WKT polygons into H3 coverage with `h3_polyfill(polygon_wkt, res)` and `h3_cell_to_boundary` 8. How to perform an O(1) spatial join by matching `points_h3.h3_cell` against a polyfilled `region_cells` view, no geometry math at query time ## Prerequisites - DeltaForge GUI running with a workspace open - Write access to an EXTERNAL zone and permission to CREATE SCHEMA and VIEW - Variables: `{{zone_name}}` (defaults to `external`), `{{data_path}}` for three Delta tables (`landmarks`, `regions`, `gps_points`)

Pitfalls

Resolution 9 is the Class-III (odd) level in H3's alternating class sequence; a few functions such as h3_exact_edge_length behave differently on Class II vs Class III because the cell shape rotates 30° between classes. If you assert edge length or area against a flat value you must pin the resolution and reassert at each res when switching.
h3_edge_length uses a static per-resolution lookup table (174.3757361 m at res 9), not a per-cell calculation, the value is constant across the entire globe at that resolution, so 'edge_m varies by latitude' assertions will all fail.
h3_polyfill places a cell into the result only when the cell's center is inside the polygon, so a long thin WKT polygon narrower than one hexagon can produce zero cells. Remedy: polyfill at a finer resolution and coarsen with h3_cell_to_parent, or use h3_polyfill_k_ring to widen coverage.
Hexagon cells have exactly 6 neighbors (hex_ring k=1 = 6); the 12 pentagon cells per resolution have only 5 neighbors, so hex_ring k=1 returns a 5-element array. None of this demo's landmarks are on pentagon cells, but production data near Hawaii, South Africa, or 60°N can and does hit them, assert ring count in [5,6] rather than = 6 on adversarial data.
UNNEST inside a subquery argument to h3_grid_path can cause DataFusion to decorrelate and compute paths for every ring cell, exploding row counts. The demo's Query 10 uses array indexing (h3_hex_ring(cell, 1))[1] specifically to avoid this, copy the pattern when composing.
Golden-ratio point generation ((id * PHI) % 1.0) pairs one coordinate with PHI and the other with 1-PHI (0.381966...), making the fractional parts sum to exactly 1 for every id. This produces a perfect anti-correlation: no point has both fractional parts near 0.5 at the same time. Any zone that requires mid-range lat AND mid-range lng will receive zero matches (the point-in-polygon sibling demo documents this explicitly for CDG, Heathrow, etc.).
CREATE OR REPLACE VIEW on a Delta-backed table computes H3 cells lazily on every read; 10K rows is cheap but repeating this pattern with 10M rows and multiple window functions means the H3 call is re-evaluated per query. Materialize into a Delta column with ALTER TABLE ADD COLUMN + UPDATE when the dataset grows.
h3_cell_to_boundary returns a WKT POLYGON with 7 vertices (6 unique + closing); a hand-crafted 6-vertex string feeds into st_area as an unclosed ring and fails with 'invalid geometry'.
DROP VIEW must precede DROP DELTA TABLE because the view depends on the table; reversing the order errors on dependency, the setup intentionally orders cleanup views-first-then-tables.

H3 GPS Fleet Tracker, 10K Pings, 21 H3 Functions

Syntax

Description

Pitfalls

See Also