Php mysql compare long and lat, return less than 10 miles

Question

Hay. I want to find the distance (in miles) between two points using the lat and long values, and check if they are within 10 miles of each other.

When a user logs in, their lat / long values ​​are stored in the session

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

I have 2 functions

This sets 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 returns bool if the distance between the two sets of lat and long is less than 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 lats / lengths and say the distance between them is 20 miles, the is_within_10_miles () function returns false.

Now I have a database of "location" (4 fields - identifier, name, lat, long).

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

Any ideas?

EDIT: I can scroll ALL and execute on them as is_within_10_miles () 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>"; 

}

Sample result will be

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

I need to somehow execute the is_within_10_miles function in my request.

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 really works. The problem is that I want to select * rows, and not select them one at a time. How to do it?

Answer 1

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

EDIT to reflect your edit:

I think you need 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 you request, 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 excludes any lines that go beyond the approximate 10 km box.

Then the complex real distance, where the sentence checks only those that are inside the square to see if they are inside the circle.

Read your DBMS docs to see if a lazy score will be applied or if you need to sort 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) 

Answer 3

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

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

Sometimes we need to find out a list of places that are within a certain radius from the center, where the coordinates of places are stored in a database. Now we have 2 solutions for this: either go through all the places to find the distance from the center point, and save the places whose distance is less than or equal to the radius, or make the sql function to find the distance to two places, and select the places with the distance less than or equal to the radius. Obviously, the second option is better than the first. So I wrote a Mysql function that does the job for you. In my case, the coordinates were saved in the database as a line of the form "10.1357002, 49.9225563, 0". This is the standard coordinate format used by many (for example, the Google map). The first element is longitude, the second is latitude, and we can ignore the third (always 0). So, here is the Mysql function, which returns the distance between two 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 ; 

Usage example:

Suppose you have to find out all the zip codes that are within a radius of 40 miles from a place with coordinates "10.1357002, 49.9225563, 0". You have a POSTCODES table with field identifiers, zip code, coordinates. So your sql should look like this:

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

Answer 4

Point of latitude and longitude

 $orig_lat=23.04988655049843; $orig_lon= 72.51734018325806; $dist=10; // Radius; //Latitude = database field name; //Longitude = database field name; $query = SELECT *, 3956 * 2 * ASIN(SQRT( POWER(SIN(($orig_lat -abs(dest.Latitude)) * pi()/180 / 2),2) + COS($orig_lat * pi()/180 ) * COS( abs(dest.Latitude) * pi()/180) * POWER(SIN(($orig_lon - dest.Longitude) * pi()/180 / 2), 2) )) as distance FROM tbl_name dest having distance < $dist ORDER BY distance limit 10 

Find an entire location within a 10 mile radius.
To work well!

Answer 5

I'm just going to leave this here:

https://gist.github.com/899413

this is an example of how to query squared (BETWEEN) for integers and shorten the results using SQRT () so that the result returns a round radius of the results. (it also returns the distance in kilometer)

To do this, you need to convert all the points when inserting into kilometers to lay them out on a flat map. (see php functions)

the result is an incredible fast search on an integer - MUCH faster than doing all the calculations in the query, as each individual answer says.

(I built it exactly 10 years ago ... it looks like it's still the smartest solution with mysql)