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));