I have data of locations of thousands of sensors in MySQL. I want to identify the sensor closest to the user's location and show that specific sensor's data. All the location data is available as lat lng.

I understand that one approach can be to find displacements between the origin and all the sensors using Haversine formula and select the one with the shortest distance. The problem here is that there are tens of thousands of sensors.

Any suggestions/leads?

CodePudding user response：

Spatial index allows efficient query of points within any specific distance. The problem of course is one might not know the search radius needed in specific case. Unfortunately, a large radius causes inefficient queries, and a small radius might result in no match at all.

A possible solution is to search with increasing radius, until the search returns some results, and then find the closest result among those.

This article describes this solution for BigQuery, would require some adaptation for MySQL script dialect: https://mentin.medium.com/nearest-neighbor-using-bq-scripting-373241f5b2f5

CodePudding user response：

Not the MySQL answer you are looking for but Postgresql's popular PostGIS extension has an inbuilt K Nearest Neighbor operator class). Also, see its documentation. It works great!

Also, I am aware of this Go library that allows you to do KNN in memory after building a Quadtree with your sensor locations.