Elastisearch and Opensearch have a built in geo_shape type that is a bit more optimal for queries like this.

Before that existed (pre 1.0 actually), I did something similar with geohashes, which are similar to h3 but based on simple string encoded quad trees. I indexed all the street segments in openstreetmap with that (~800 million at the time) and implemented a simple reverse geocoder. Worked shockingly well.

The geo_shape type uses a bkd tree in binary format. It's heavily optimized for this type of intersects/overlaps queries at scale. Basically does the same thing but using a lot less disk space and memory. It's similar to what you would find in proper GIS databases. Elasticsearch/opensearch also support h3 and geohash grid aggregations on top of geo_shape or geo_point types.

I'm guessing the author is using something like postgresql which of course has similar geospatial indexing support via post gis.

Awesome comment, thanks for sharing the details. I love this kind of pragmatic optimization. Also, one dev's "total hack* job" [e.g. yourself, in the past] is another's stroke of genius.

* I'd frame it as "kludge", reserving "hack" for the positive HN sense. :)

Beware that the parent hexagon does not contain its children...

Very cool! And the prefix queries you mention are what I was trying to get at in another comment, but you explained it better :)

Does this effect writes negatively?