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:

Answer 2

Before your query, calculate the maximum and minimum latitude and longitude of your ten-mile circle. This will give you four numbers so the first part of your where clause will exclude any rows which fall outside of an approximate 10 mile-each way box.

Then the complicated real distance where clause checks only the ones inside the square, to see if they're also inside the circle.

Read your DBMS's docs to see whether lazy evaluation will apply or if you will need to arrange it as

SELECT *
FROM (SELECT *
      FROM table
      WHERE (simple rough box clause)
     )
WHERE (complex clause)

instead of the usual

SELECT * FROM table WHERE (simple clause) AND (complex clause)