SQL query, select nearest places by a given coordinates [duplicate]

Question

I have $latitude = 29.6815400 and $longitude = 64.3647100, now in MySQL I would like to take the 15 nearest places to these coordinates and I'm planning to do this query:

SELECT *
FROM places
WHERE latitude  BETWEEN($latitude  - 1, $latitude  + 1)
AND   longitude BETWEEN($longitude - 1, $logintude + 1)
LIMIT 15;

Do you think it's correct or do you suggest something else?

How to do the BEETWEEN, since I want to search trough a maximum of 50Km range the near places?

I forgot to say that I can also use PHP for do anything before to run the query.

Note: I can't use stored procedures.

Answer 1

here’s the PHP formula for calculating the distance between two points:

function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'Mi') 
{
   $theta = $longitude1 - $longitude2;
   $distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))+
               (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)));
   $distance = acos($distance); $distance = rad2deg($distance); 
   $distance = $distance * 60 * 1.1515;

   switch($unit) 
   { 
     case 'Mi': break;
     case 'Km' : $distance = $distance * 1.609344; 
   } 
   return (round($distance,2)); 
}

then add a query to get all the records with distance less or equal to the one above:

$qry = "SELECT * 
        FROM (SELECT *, (((acos(sin((".$latitude."*pi()/180)) *
        sin((`geo_latitude`*pi()/180))+cos((".$latitude."*pi()/180)) *
        cos((`geo_latitude`*pi()/180)) * cos(((".$longitude."-
        `geo_longitude`)*pi()/180))))*180/pi())*60*1.1515*1.609344) 
        as distance
        FROM `ci_geo`)myTable 
        WHERE distance <= ".$distance." 
        LIMIT 15";

and you can take a look here for similar computations.

and you can read more here

Update:

you have to take in mind that to calculate longitude2 and longitude2 you need to know that:

Each degree of latitude is approximately 69 miles (111 kilometers) apart. The range varies (due to the earth's slightly ellipsoid shape) from 68.703 miles (110.567 km) at the equator to 69.407 (111.699 km) at the poles. This is convenient because each minute (1/60th of a degree) is approximately one mile.

A degree of longitude is widest at the equator at 69.172 miles (111.321) and gradually shrinks to zero at the poles. At 40° north or south the distance between a degree of longitude is 53 miles (85 km).

so to calculate $longitude2 $latitude2 according to 50km then approximately:

$longitude2 = $longitude1 + 0.449; //0.449 = 50km/111.321km
$latitude2 = $latitude1 + 0.450; // 0.450 = 50km/111km

Answer 2

You have to consider that flooding any DBMS like MySQL with heavy queries should not be the best solution.

Instead you can speculate a very-fast SQL query selecting all the places with coordinates inside the simple square of side $radius, instead of selecting suddently a perfect circle radius. PHP can filter the surplus.

Let me show the concept:

$lat    = 45.0.6072;
$lon    = 7.65678;
$radius = 50; // Km

 // Every lat|lon degree° is ~ 111Km
$angle_radius = $radius / ( 111 * cos( $lat ) );

$min_lat = $lat - $angle_radius;
$max_lat = $lat + $angle_radius;
$min_lon = $lon - $angle_radius;
$max_lon = $lon + $angle_radius;

$results = $db->getResults("... WHERE latitude BETWEEN $min_lat AND $max_lat AND longitude BETWEEN $min_lon AND $max_lon"); // your own function that return your results (please sanitize your variables)

$filtereds = [];
foreach( $results as $result ) {
    if( getDistanceBetweenPointsNew( $lat, $lon, $result->latitude, $result->longitude, 'Km' ) <= $radius ) {
        // This is in "perfect" circle radius. Strip it out.
        $filtereds[] = $result;
    }
}

// Now do something with your result set
var_dump( $filtereds );

In this way MySQL runs a very friendly query that does not require a full table scan while PHP strips out the surplus with something similar to the getDistanceBetweenPointsNew() function posted in this page, comparing the distance from the coordinates of the result set to the center of your radius.

In order to do not waste the (big) performance gain, index your coordinates columns in the database.

Happy hacking!