PHP Sorting nearest coordinates

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

Answers


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.


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.


It is more efficient to embed the sort in the query.

This wonderful tutorial will help you (and provides a query that serves your needs): https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql


Need Your Help

Dead simple example of using Multiprocessing Queue, Pool and Locking

python python-2.7 multiprocessing

I tried to read the documentation at http://docs.python.org/dev/library/multiprocessing.html but I'm still struggling with multiprocessing Queue, Pool and Locking. And for now I was able to build ...

C memcpy() a function

c memory linux-kernel function-pointers memcpy

Is there any method to calculate size of a function? I have a pointer to a function and I have to copy entire function using memcpy. I have to malloc some space and know 3rd parameter of memcpy - s...