H3 + GIS Delivery Optimization: Cross-Function Spatial Analytics

Combines H3 hexagonal indexing with GIS distance/bearing functions across 3 warehouses and 15 stores to rank delivery routes and detect suboptimal warehouse assignments.

Syntax

-- ============================================================================
-- SETUP, zone, schema, 2 Delta tables (warehouses, stores)
-- ============================================================================
CREATE ZONE IF NOT EXISTS {{zone_name}} TYPE DELTA
    COMMENT 'Delta tables for demo datasets';

CREATE SCHEMA IF NOT EXISTS {{zone_name}}.logistics
    COMMENT 'Delivery logistics, warehouses, stores, delivery analytics';

CREATE DELTA TABLE IF NOT EXISTS {{zone_name}}.logistics.warehouses (
    warehouse_id INT, warehouse_name VARCHAR,
    lat DOUBLE, lng DOUBLE, capacity_pallets INT
) LOCATION '{{data_path}}/logistics_warehouses';

CREATE DELTA TABLE IF NOT EXISTS {{zone_name}}.logistics.stores (
    store_id INT, store_name VARCHAR, lat DOUBLE, lng DOUBLE,
    warehouse_id INT, monthly_orders INT
) LOCATION '{{data_path}}/logistics_stores';

-- ============================================================================
-- QUERIES: H3 + GIS combined
-- ============================================================================
-- H3 cell assignment at resolution 7
SELECT warehouse_id, warehouse_name,
       h3_cell_to_string(h3_latlng_to_cell(lat, lng, 7))     AS h3_hex,
       h3_is_valid_cell (h3_latlng_to_cell(lat, lng, 7))     AS is_valid,
       h3_get_resolution(h3_latlng_to_cell(lat, lng, 7))     AS resolution,
       h3_is_pentagon    (h3_latlng_to_cell(lat, lng, 7))    AS is_pentagon,
       h3_is_res_class_iii(h3_latlng_to_cell(lat, lng, 7))   AS is_class_iii
FROM {{zone_name}}.logistics.warehouses ORDER BY warehouse_id;

-- GIS distance + bearing per assigned route
SELECT w.warehouse_name, s.store_name,
       ROUND(st_distance(w.lat, w.lng, s.lat, s.lng) / 1000.0, 0) AS distance_km,
       ROUND(st_bearing (w.lat, w.lng, s.lat, s.lng), 2)         AS bearing_deg
FROM {{zone_name}}.logistics.stores     s
JOIN {{zone_name}}.logistics.warehouses w ON w.warehouse_id = s.warehouse_id
ORDER BY w.warehouse_id, s.store_id;

-- H3 grid distance, lift both cells to res 5 (~252 km^2) for long-range topology
SELECT w.warehouse_name, s.store_name,
       h3_grid_distance(
           h3_cell_to_parent(h3_latlng_to_cell(w.lat, w.lng, 7), 5),
           h3_cell_to_parent(h3_latlng_to_cell(s.lat, s.lng, 7), 5)
       ) AS grid_dist,
       ROUND(st_distance(w.lat, w.lng, s.lat, s.lng) / 1000.0, 0) AS gis_dist_km
FROM {{zone_name}}.logistics.stores s
JOIN {{zone_name}}.logistics.warehouses w ON w.warehouse_id = s.warehouse_id
ORDER BY w.warehouse_id, s.store_id;

-- Hierarchy + topology, res-7 children of Chicago's parent and ring/disk counts
SELECT ROUND(h3_cell_area_km2(h3_latlng_to_cell(41.8781, -87.6298, 7)), 2)                  AS area_km2,
       (SELECT COUNT(*) FROM (SELECT UNNEST(h3_hex_ring(h3_latlng_to_cell(41.8781,-87.6298,7),1)) AS c)) AS ring_k1,
       (SELECT COUNT(*) FROM (SELECT UNNEST(h3_hex_disk(h3_latlng_to_cell(41.8781,-87.6298,7),1)) AS c)) AS disk_k1,
       (SELECT COUNT(*) FROM (SELECT UNNEST(h3_hex_disk(h3_latlng_to_cell(41.8781,-87.6298,7),2)) AS c)) AS disk_k2;

-- Suboptimal assignment detection, nearest warehouse != assigned warehouse
WITH distance_ranked AS (
    SELECT s.store_id, s.store_name, s.warehouse_id AS assigned_wh_id,
           w_assigned.warehouse_name AS assigned_warehouse,
           w_all.warehouse_id        AS candidate_wh_id,
           w_all.warehouse_name      AS candidate_warehouse,
           ROUND(st_distance(s.lat, s.lng, w_all.lat, w_all.lng) / 1000.0, 0) AS distance_km,
           h3_cell_to_string(h3_latlng_to_cell(s.lat, s.lng, 7))              AS store_h3,
           ROW_NUMBER() OVER (PARTITION BY s.store_id
                              ORDER BY st_distance(s.lat, s.lng, w_all.lat, w_all.lng)) AS rn
    FROM {{zone_name}}.logistics.stores     s
    JOIN {{zone_name}}.logistics.warehouses w_assigned ON w_assigned.warehouse_id = s.warehouse_id
    CROSS JOIN {{zone_name}}.logistics.warehouses w_all
)
SELECT store_id, store_name, assigned_warehouse,
       candidate_warehouse AS nearest_warehouse,
       distance_km          AS nearest_distance_km,
       store_h3
FROM distance_ranked WHERE rn = 1 AND candidate_wh_id != assigned_wh_id
ORDER BY store_id;

-- ============================================================================
-- CLEANUP
-- ============================================================================
DROP DELTA TABLE IF EXISTS {{zone_name}}.logistics.stores     WITH FILES;
DROP DELTA TABLE IF EXISTS {{zone_name}}.logistics.warehouses WITH FILES;
DROP SCHEMA IF EXISTS {{zone_name}}.logistics;

Description

## When to Use Reach for this demo when you need both H3 cell semantics (grid topology, multi-resolution hierarchy, coverage zones) and GIS geodesic metrics (great-circle distance, compass bearing) in the same query. Logistics optimization is the canonical cross-function scenario: H3 is the right tool for 'which region does this fall into' and 'how many cell hops away', while GIS is the right tool for 'how many kilometers of actual driving'. The demo intentionally mis-assigns Memphis to Dallas (Atlanta is closer) so the suboptimal-assignment detection query has a non-empty result. ## What You Will Learn 1. How to index every point with `h3_latlng_to_cell(lat, lng, resolution)` at res 7 (~5.2 km² hexagons) and validate via `h3_is_valid_cell`, `h3_get_resolution`, `h3_is_pentagon`, and `h3_is_res_class_iii` 2. How to navigate the H3 hierarchy with `h3_cell_to_parent(cell, coarser_res)` and `h3_cell_to_children(cell, finer_res)`, and why resolution 5 (parent) is better for inter-warehouse topology than res 7 3. How to compute grid topology with `h3_grid_distance`, `h3_hex_ring(cell, k)`, `h3_hex_disk(cell, k)`, and `h3_cell_to_center_child` 4. How to round-trip cell IDs via `h3_cell_to_string` / `h3_string_to_cell` and extract hex boundaries with `h3_cell_to_boundary` (returns a WKT POLYGON) 5. How to rank candidate warehouses per store with `ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY st_distance(...))` and flag rows where assigned ≠ nearest 6. How to score delivery routes by combining `st_distance` (km), `h3_grid_distance` (hops), and `st_bearing` (deg) in a single SELECT ## Prerequisites - DeltaForge GUI running with a workspace open - Write access to a DELTA zone and permission to CREATE SCHEMA - Variables: `{{zone_name}}` (defaults to `external`), `{{data_path}}` for two Delta tables (`warehouses`, `stores`)

Pitfalls

• H3 resolution choice dominates everything downstream: res 7 (~5.2 km^2) is right for warehouse neighborhoods but gives absurdly large grid_distance values for inter-warehouse routes (Chicago → Dallas is ~40+ hops at res 7 but only ~9 hops at res 5). The query that combines grid_distance and st_distance lifts both cells to res 5 via h3_cell_to_parent for exactly this reason.
• H3 grid_distance is NOT proportional to geodesic distance, it counts cell hops. Two points that are 10 km apart on different sides of an icosahedron edge can have a larger grid_distance than two points 50 km apart within the same class-II pentagon neighborhood. Use st_distance when you need kilometers.
• h3_cell_to_parent(cell, finer_res_than_source) returns NULL (not an error) because 'parent at a finer resolution' is meaningless; the caller then sees NULL propagated into grid_distance and the whole route silently sorts to the bottom. Always verify target_res < source_res before calling.
• There are exactly 12 pentagon cells at every resolution (on the icosahedron vertices); they have 5 children at the next finer res, not 7, and h3_cell_to_children returns a short array. This demo's US warehouses are far from any pentagon, but a production dataset near Hawaii, South Africa, or the 60°N latitude band can and will include pentagons.
• h3_polyfill is based on cell centers falling inside the polygon, so a long thin polygon (<= one cell wide) can produce zero cells. The workaround is to polyfill at a finer resolution and then coarsen with h3_cell_to_parent. This demo does not polyfill, but the H3 GPS and point-in-polygon demos do and show the failure mode.
• h3_cell_to_boundary returns a WKT POLYGON with 7 vertices (6 unique + closing), not 6, downstream st_area / st_contains assume the closing vertex and will error on a hand-crafted 6-vertex string.
• Suboptimal-assignment detection uses ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY st_distance) on a CROSS JOIN; the CROSS JOIN is O(stores × warehouses), fine at 15 × 3 but quadratic at production scale, pre-filter with an H3 disk of a fixed k to reduce candidates before the window function runs.
• h3_grid_distance on cells from different icosahedral faces returns -1 when the path crosses a pentagon or is ambiguous; this demo's warehouses are all in the US interior so it never triggers, but assert grid_dist >= 0 before using the value in arithmetic.
• UNNEST(h3_hex_ring(...)) in a subquery can be decorrelated by DataFusion and compute the ring for every outer row; if you see one ring computed per join partner instead of per cell, use array indexing (h3_hex_ring(...))[1] or pre-compute into a CTE.
• DROP DELTA TABLE ... WITH FILES removes the on-disk Delta log; plain DROP TABLE targets DataFusion in-memory tables and leaves storage behind, which fails the next CREATE with 'table already exists'.

See Also

• demo_spatial_gis_maritime_shipping
• demo_spatial_gis_emergency_response
• demo_spatial_h3_gps_fleet_tracker
• demo_spatial_h3_point_in_polygon