0

I have a large database of location points and their corresponding res 10 hexes.

I need to query this database and identify how many points are in a certain res 4, 5, 6, 7, 8, and 9 hex.

Is this possible without adding additional res indexes in the database? Is there a certain format/pattern in the hex naming convention I could use?

Aidan Curry
  • 1

1 Answers1

2

All of the children of a res N index at res M fall within a range, so you can do a range query to find them. This takes a little wrangling, but only to construct the query, not to run it.

To find all the res 10 children of a res 4 index, e.g. 841e001ffffffff:

  • Take cellToCenterChild('841e001ffffffff'), which evaluates to 8a1e00000007fff. This is the bottom of the range.

  • The top of the range is a little trickier. We don't currently expose a function for it, but you can construct it by swapping the resolution bits of the parent from 4 to 10. In hexidecimal, this is conveniently just the second character, so you can swap 4 for a yielding 8a1e001ffffffff. This is not a valid index, but will work for a range query.

  • Use a range query to find child indexes:

select * from my_data 
  where h3_index between "8a1e00000007fff" and "8a1e001ffffffff";

Assuming you have an appropriate index on h3_index, this should be fairly fast.

nrabinowitz
  • 55,314
  • 10
  • 149
  • 165