Geospatial joins using predicates like ST_Intersects become prohibitively slow at scale due to quadratic complexity and expensive spatial operations. By automatically rewriting these queries to use H3 hierarchical hexagonal cell indexes, spatial predicates are transformed into fast integer equi-joins on cell IDs. The approach generates H3 coverage for geometries, performs a hash join on matching cells, then applies exact predicates only to filtered candidates. Benchmarks show 400× speedup at optimal resolution (resolution 3), reducing 37.6 million comparisons to ~200k. The technique works on-the-fly without materialized indexes, supporting views and subqueries while avoiding storage overhead.
Post #2141
9.86K

How we made geo joins 400× faster with H3 indexes
- ❤ 6
- 👍 2