PHP Sorting nearest coordinates
Solution 1
Using Google's algorithm:
$lon = //your longitude
$lat = //your latitude
$miles = //your search radius
$query = "SELECT *,
( 3959 * acos( cos( radians('$lat') ) *
cos( radians( latitude ) ) *
cos( radians( longitude ) -
radians('$lon') ) +
sin( radians('$lat') ) *
sin( radians( latitude ) ) ) )
AS distance FROM yourtable HAVING distance < '$miles' ORDER BY distance ASC LIMIT 0, 5"
latitude
and longitude
in this query are going to be your lat/lon column names.
Solution 2
I've encountered same problem lately. What i've decided was to write a mysql function to calculate distance and then use it in sql query. Mysql function:
CREATE FUNCTION distance(lat1 float, lon1 float, lat2 float, lon2 float)
RETURNS float
RETURN ACOS(SIN(RADIANS(lat1))*SIN(RADIANS(lat2))+COS(RADIANS(lat1))*COS(RADIANS(lat2))*COS(RADIANS(lon2-lon1)))*6371
If your Coordinates
table has columns f.e. latitude
and longitude
then the code might look like this:
$q = mysql_query("SELECT * FROM Coordinates ORDER BY
distance(latitude, longitude, $lat, $lon) LIMIT 5";
Where $lat
and $lon
contain provided location.
La bla bla
Updated on June 04, 2022Comments
-
La bla bla almost 2 years
I have a MySQL table in a PHP webservice containing longitude and latitude. I want to send the user only the, let's say, 5 closest coordinates. I wrote the method which calculates a distance from coordinates to the ones the user sent in the POST request, but I'm not sure on how to sort it and only send back a few.
Here is the distance method:
function distance($longToCompare,$latToCompare) { $dlong = $request_long - $longToCompare; $dlat = $request_lat - $latToCompare; $a = pow(sin($dlat/2)) + cos($latToCompare)*cos($request_lat)*pow(sin($dlong/2)); $c = 2*atan2(sqrt($a),sqrt(1-$a)); return 6373*$c; }
and the user currently gets the whole DB (for now, while developing it's small, but in the future it could be rather big)
$q = mysql_query("SELECT * FROM Coordinates"); $coordinates = array (); while ($e = mysql_fetch_assoc($q)) { $coordinates[] = $e; } print (json_encode($coordinates));
Can anyone point me to the right direction? I'm rather new to PHP, I know I can create a custom sorting using uasort, but i'm not quite sure on how to use it using this distance function.
EDIT: Using @Norse 's solution, the current query is:
$request_long = $_POST['longitude']; $request_lat = $_POST['latitude']; $km = 0.5; $query = "SELECT *, ( 6373 * acos( cos( radians('$request_lat') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('$request_long') ) + sin( radians('$request_lat') ) * sin( radians( latitude ) ) ) ) AS distance FROM Coordinates HAVING distance < '$km' ORDER BY distance ASC LIMIT 0, 5"; $coordinates = array (); while ($e = mysql_fetch_assoc($query)) { $coordinates[] = $e; } print (json_encode($coordinates));