I'm writing an app that needs to allow a user to select elements that are within some radius of their location. There is no way to know how many locations there will be ultimately, but it might be tens of thousands. The user doing the search is one of the location nodes (and not just an arbitrary position submitted by their phone or whatnot)
I see answers like this: mysql lat lon calulation to show locations within radius but I'm concerned that this is a really serious undertaking given that the math involved will need to be calculated for every single "other" location.
The other approach I was considering is to have a relational table that identifies the distance between each location (that I'd populate every time a location was added), granted it will have a ton of rows to define every possible relationship, but then selecting against *that table will be super fast, especially if the distances are indexed.
Would love to get some advice from someone who's done this in mySQL and can warn/advise me for or against the best approach.
The following SQL query uses Spherical Law of Cosines to calculate the distance between a coordinate and coordinates in a table. It limits result to 10 and orders by distance. This is used instead of the Haversine Formula which is too complicated to perform with MySQL
Spherical Law of Cosines
Where R = 3,959 miles or 6,371km
d = acos( sin(lat1).sin(lat2) + cos(lat1).cos(lat2).cos(lng2-lng1) ).R
SQL
SELECT name, lat, lng, ( 3959 * acos( cos( radians($center_lat) )
* cos( radians( lat ) ) * cos( radians( lng )
- radians($center_lng) ) + sin( radians($center_lat) )
* sin( radians( lat ) ) ) ) AS distance FROM table
ORDER BY distance LIMIT 0 , 10
Where $center_lat & $center_lng are coordinates of location.
The query uses SQL Math functions
Query took 0.2506 sec on database with 50,068 rows
The spatial functions avaialable in MySQL are not suitable for your porpose see this Blog