I have a working PHP function that takes in latitude/longitude coordinates and a distance in miles, then tests row by row in a MySQL table of zip codes/lat-lon pairs (40,000+ rows) whether each zip code/lat-lon row falls within the specified distance or not.
I know that this method is terribly inefficient and I would like to do all of the trigonometry and distance calculation in the SQL query rather than with PHP. Can anyone help me out here? I feel like I've tried everything but I just can't get the SQL query correct. Any help would be greatly appreciated!
Existing PHP function:
function getZips($lat, $lon, $radius)
{
//MySQL bit
$zipQuery = mysql_query("SELECT * FROM zip_codes");
while ($row = mysql_fetch_assoc($zipQuery))
{
$lat2 = $row['latitude'];
$lon2 = $row['longitude'];
$distance = (3958*3.1415926*sqrt(($lat2-$lat)*($lat2-$lat) + cos($lat2/57.29578)*cos($lat/57.29578)*($lon2-$lon)*($lon2-$lon))/180);
if ($distance <= $radius)
{
$zip = $row['zip'];
echo "Zip in range: <b>" .$zip . "</b><br>";
}
}
}