wont that completely negate the value of the indices that lat/lon may/may not sit on and result in a complete tablescan?
Any other ideas?
One solution I saw used more simple arithmetic to calculate a range of coordinates within levels of distance. That could be pre-cached, but it's a lot less accurate.
(Edit: to be more specific, you can get a pretty good distance measurement using Geohash and comparing strings. Obviously, indexing strings is something databases do well. The exact distance a single character corresponds to depends on longitude & latitude, but there are lookup tables for that. There are also edge conditions to be aware of which may affect your application)
Or, use Postgres which has geospatial indexes.
Using a B-Tree on a Geohash (like MongoDB does) is a bit more efficient that just indexing min/max values, but not by much. MySQL, PostgreSQL and even SQLite have R-Tree indices that perform 10x better.
If you're already using MongoDB, it's really dead-easy to setup (see the docs).
You can basically reduce the filtering done to something like: WHERE lat BETWEEN val1 AND val2 AND lon BETWEEN val3 AND val4.
So indexing will work.
What I'm doing, given a max distance and a search point, is to calculate the bounding box in which I want to search in filter results with
WHERE lat BETWEEN lat_min AND lat_max AND lng BETWEEN lng_min AND lng_max
Calculating latitude min/max is trivial knowing that 1 latitude degree is 111.2KM. Longitude is a bit more convoluted because longitude degree size changes moving north/south.
$lat_min = $lat - $range_km * (1 / 111.2); $lat_max = $lat + $range_km * (1 / 111.2);
$k = $range_km/6371.04; $lng_min = $lng - rad2deg($k/cos(deg2rad($lat))); $lng_max = $lng + rad2deg($k/cos(deg2rad($lat)));