php mysql compare long and lat, return ones under 10 miles

Question

Hay i want to find the distance (in miles) between 2 locations using lat and long values, and check if they are within a 10 mile radius of each other.

When a user logs in, their lat/long values are saved in a session

$_SESSION['lat']
$_SESSION['long']

I have 2 functions

This one works out the distance in miles and returns a rounded value

function distance($lat1, $lng1, $lat2, $lng2){
    $pi80 = M_PI / 180;
    $lat1 *= $pi80;
    $lng1 *= $pi80;
    $lat2 *= $pi80;
    $lng2 *= $pi80;
    $r = 6372.797; // mean radius of Earth in km
    $dlat = $lat2 - $lat1;
    $dlng = $lng2 - $lng1;
    $a = sin($dlat / 2) * sin($dlat / 2) + cos($lat1) * cos($lat2) * sin($dlng / 2) * sin($dlng / 2);
    $c = 2 * atan2(sqrt($a), sqrt(1 - $a));
    $km = $r * $c;
    return floor($km * 0.621371192);
}

This one returns a bool if the distance between the 2 sets of lat and long's is under 10.

function is_within_10_miles($lat1, $lng1, $lat2, $lng2){
    $d = distance($lat1, $lng1, $lat2, $lng2);
    if( $d <= 10 ){
        return True;
    }else{
        return False;
    }
}

Both functions work as expected, if i give 2 sets of lat/longs and the distance between them is say 20 miles, my is_within_10_miles() function returns false.

Now, I have a database of 'locations' (4 fields - ID, name, lat, long).

I want to find all locations that are within a 10 mile radius.

Any ideas?

EDIT: I can loop through ALL the and perform the is_within_10_miles() on them like this

$query = "SELECT * FROM `locations`";
$result = mysql_query($query);

while($location = mysql_fetch_assoc($result)){
echo $location['name']." is ";
echo distance($lat2, $lon2, $location['lat'], $location['lon']);
echo " miles form your house, is it with a 10 mile radius? ";
if( is_within_10_miles($lat2, $lon2, $location['lat'], $location['lon']) ){
    echo "yeah";
}else{
    echo "no";
}
echo "<br>";

}

A sample result would be

goodison park is 7 miles form your house, is it with a 10 mile radius? yeah

I need to somehow perform the is_within_10_miles function within my query.

EDIT EDIT

This legend from http://www.zcentric.com/blog/2007/03/calculate_distance_in_mysql_wi.html came up with this...

SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM members HAVING distance<='10' ORDER BY distance ASC

It actually works. Problem is that i want to select * rows, rather than selecting them one by one. How do i do that?

Answer 1

You probably don't need to do this in code, you can probably do this all in the DB. if you use a spatial index. MySQL docuemtnation for spatial index

EDIT to reflect your edit:

I think you want something like this:

SELECT *, ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM locations HAVING distance<='10' ORDER BY distance ASC

VIA: http://www.zcentric.com/blog/2007/03/calculate_distance_in_mysql_wi.html: