0

I've got a table with a column of long geohash keys. My goal is to write a (Snowflake) SQL query to select those rows that match a set of bounding boxes. For the corner of each bounding box I have computed a short geohash (length dictated by the size of the bounding box).

I've deduplicated those bounding box geohash keys to improve efficiency. As a result there are no longer strictly 4 keys associated with each bounding box.

The goal would be a query that generates a table like the one below. The geohash and corner_keys columns are inputs and the within column is the output.

The corner_keys column is a list(varchar) datatype that I wish existed but probably doesn't.

geohash corner_keys within
9rv4a0nnq8 9rv,c4u TRUE
c4x99uq10x 9rv,c4u FALSE
9rv4a0nnq8 9rx,9rq,9rm,9rv TRUE
c4x99uq10x c4x TRUE

The SQL would look something like

any(startswith(geohash, corner_keys)) as within

How does one compare a geohash to a unspecified number of short keys?

Keith
  • 1,777
  • 3
  • 15
  • 20
  • Please update your question with with your table ddl, some sample data and the result you want to achieve from that data – NickW Feb 26 '22 at 09:56
  • @NickW Thank you for the suggestion. I've added an example table and pseudocode. – Keith Feb 27 '22 at 15:01

0 Answers0