SQL query, select nearest places by a given coordinates

As a rule of thumb, always try to do not flood MySQL with heavy queries.

Instead, you can speculate a very-fast and very-optimized SQL query selecting all the places with coordinates inside a simple square of side $radius, instead of selecting suddenly a perfect circle with that radius. Then, PHP or whatever can quickly filter the surplus.

Let me show the concept in PHP:

// your input variables
$lat    = 45.6072;
$lon    = 7.65678;
$radius = 50; // expressed in Km

// every latitude degree° is ~ 111Km
$angle_radius_lat = $radius / 111;

// longitude takes into account equator distance
$angle_radius_lon = $angle_radius_lat * cos( deg2rad( $lat ) );

// define a simple square with your lat/lng as center
$min_lat = $lat - $angle_radius_lat;
$max_lat = $lat + $angle_radius_lat;
$min_lon = $lon - $angle_radius_lon;
$max_lon = $lon + $angle_radius_lon;

// query places inside the square
// use here your own database function
$results_raw = query_results( "SELECT latitude, longitude FROM ... WHERE latitude BETWEEN $min_lat AND $max_lat AND longitude BETWEEN $min_lon AND $max_lon" );

// filter the surplus outside the circle
$results = [];
foreach( $results_raw as $result ) {

    if( getDistanceBetweenPointsNew( $lat, $lon, $result->latitude, $result->longitude, 'Km' ) <= $radius ) {
        $results[] = $result;
    }

}

// these are your places inside the circle of your radius
var_dump( $results );

In this way MySQL runs a very friendly query that does not cause 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.

Important: in order to do not waste the big performance gain, create a database index on your coordinates columns (latitude and longitude). Happy hacking!

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

I've done something similar with a selling houses app, ordering by distance from a given point, place this in your SQL select statement:

((ACOS(SIN(' . **$search_location['lat']** . ' * PI() / 180) * SIN(**map_lat** * PI() / 180) + COS(' . **$search_location['lat']** . ' * PI() / 180) * COS(**map_lat** * PI() / 180) * COS((' . **$search_location['lng']** . ' - **map_lng**) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS "distance"

Replace $search_location with your relevant lat/lng values and the map_lat/map_lng values are the SQL columns which contain the lat/lng values. You can then order the results by distance and either use a where or having clause to filter our properties within a 50km range.

I would recommend using SQL as the approach compared to PHP in the event you require additional functionality such as paging.