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

I don't know of any efficient way to do this using SQL, the way I've done it is to build a kd-tree of all the points, then conduct radius or nearest-neighbour searches around your desired point: http://en.wikipedia.org/wiki/Kd-tree There are various examples of implementing kd-trees around the net, though I've not seen one in PHP.

Tip: try to avoid sqrt() by simply squaring everything and comparing the squared distances rather than the real distances.

Answer 2

This should point you in the right direction - no pun intended.

Mysql function to find distance between two places using lat/long

Sometimes we need to find out list of places that are within a certain radius from a center place where coordinates of the places are saved in the database. Now we have 2 solutions for this – either loop through all the places find the distance from the center point and keep the places that have distance less or equal to the radius, or make an sql function to find the distance of two places, and select the places having distance less or equal to the radius. Obviously the second option is better than the first one. So I have written a Mysql function that does the work for you. In my case, the coordinates were saved in the database as a string of the form "10.1357002, 49.9225563, 0". This is the standard format of coordinates that is used by many (for example Google map). The first element is the longitude, second one is latitude and we can ignore the third (always 0). So here is the Mysql function that returns the distance between 2 coordinates in Miles.

DELIMITER $$

DROP FUNCTION IF EXISTS `GetDistance`$$

CREATE FUNCTION `GetDistance`(coordinate1 VARCHAR(120), coordinate2 VARCHAR(120))
    RETURNS VARCHAR(120)
BEGIN
    DECLARE pos_comma1, pos_comma2 INT;
    DECLARE lon1, lon2, lat1, lat2, distance DECIMAL(12,8);

    select locate(',', coordinate1) into pos_comma1;
    select locate(',', coordinate1, pos_comma1+1) into pos_comma2;
    select CAST(substring(coordinate1, 1, pos_comma1-1) as DECIMAL(12,8)) into lon1;
    select CAST(substring(coordinate1, pos_comma1+1, pos_comma2-pos_comma1-1) as DECIMAL(12,8)) into lat1;

    select locate(',', coordinate2) into pos_comma1;
    select locate(',', coordinate2, pos_comma1+1) into pos_comma2;
    select CAST(substring(coordinate2, 1, pos_comma1-1) as DECIMAL(12,8)) into lon2;
    select CAST(substring(coordinate2, pos_comma1+1, pos_comma2-pos_comma1-1) as DECIMAL(12,8)) into lat2;

        select ((ACOS(SIN(lat1 * PI() / 180) * SIN(lat2 * PI() / 180) + COS(lat1 * PI() / 180) * COS(lat2 * PI() / 180) * COS((lon1 - lon2) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) into distance;
    RETURN distance;

END$$

DELIMITER ;

Example Usage:

Lets say you have to find out all the postcodes that are in 40 miles radius from a place having coordinate "10.1357002, 49.9225563, 0". You have a table POSTCODES having fields id, postcode, coordinates. So your sql should look like this:

  select id, postcode from POSTCODES where GetDistance("10.1357002, 49.9225563, 0", coordinates) <= 40;

Answer 3

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 4

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)