1

Recently I'm using GeoHash to hash the paired geo-coordinates into a hash value and store it in MySQL. Now I want to find the nearest hash given the other hash. I noticed that MYSQL provide BTree structure to find a range of nearest hashes by using the command "like" in SQL query.

The problem is, how could I find the nearest one instead of giving a range since sometimes I don't know the range.

Could someone give me a hint?

Really appreciate it.

AI_ROBOT
  • 958
  • 1
  • 9
  • 19
  • You'll want to use [MySQL Spatial Extensions](https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html) when dealing with GIS coordinates. – tadman Sep 07 '18 at 20:34

1 Answers1

1

QuadTile (or GeoHash?) reversibly turns latitude + longitude into a single number. But to use it to "find nearest" gets complex and messy. Read about Z-ordering. As I understand it, you would need to run 4 queries with ORDER BY and LIMIT; UNION the results together; then check to see which are best.

But it gets tricky to know what to do if the attempt did not find enough items.

SPATIAL indexes provide a more straightforward way.

Here is a technique for efficiently "finding nearest" for large datasets.

Rick James
  • 135,179
  • 13
  • 127
  • 222