6373 * acos (
cos ( radians( PASSED_IN_LATITUDE ) )
* cos( radians( X(location) ) )
* cos( radians( Y(location) ) - radians( PASSED_IN_LONGITUDE ) )
+ sin ( radians( PASSED_IN_LATITUDE ) )
* sin( radians( X(location) )
) AS distance
HAVING distance < PASSED_IN_RADIUS
Assuming you have a spatial key on location, you can do something like this:
select * from locations where contains(geomfromtext('polygon($bounding_rect_coords)'),location) and earth_distance(location,point($lat,$lon)) < $radius
The bounding rectangle coordinates should be computed using the following formulas:
$deg_to_rad = $PI/180.0 $rad_to_deg = 1.0/$deg_to_rad $delta_y = $rad_to_deg *($radius / ($earth_radius * cos($lat*$deg_to_rad))) // the length of the parallel = EARTH_R * cos(lat) $delta_x = $rad_to_deg * ($radius/$earth_radius) $x1 = $lat - $delta_x $x2 = $lat + $delta_x $y1 = $lon - $delta_y $y2 = $lon + $delta_y
Then the rectangle is obtained with
geomfromtext('polygon(($x1 $y1,$x2 $y1,$x2 $y2, $x1 $y2, $x1 $y1))')
This is best done in the application to offload the database server.
This rectangle is actually a spherical rectangle, thus the use of the PI constant in its computation. The idea is simple. For the given parallel, convert the search radius into degrees of longitude. That is how many degrees east and west we need to go from the target to cover our candidate points. Then compute the same for degrees of latitude - unlike longitude, this will not be coordinate-dependent as all meridians have the same length. That is how many degrees we need to go north and south.
earth_distance() you have several options:
Your computation itself is OK even though it is two years old - nothing revolutionary has been discovered in the last two years as far as measuring the distance between two points on the earth is concerned as far as I know.
Your original method would work as well except it will be inefficient. The addition of
contains clause allows us to reduce our search to a (hopefully) relatively small set that is guaranteed to be within the search radius very quickly. Then we take each candidate and filter out the ones that did not make the cut on
I must add a standard disclaimer that I inserted variables into SQL that potentially might not have been sanitized. Make sure to validate the resulting SQL queries for SQL injection attacks when writing the actual production code.