mysql select closest lat/long to specific location

Question

Possible Duplicate:
Nearest locations with latitude and longitude

I have on my db more than 1000 locations (latitude, longitude) and i want to select the 5 nearest one according to a specific location a(lat, long)! can any one give an idea how to do this! i don't want to make the test to calculate all distances between a and other locations this make too much time!

this is what i thought to do but i'm not sure about!!

    $lat = round (48.89463, 3);
    $lng = round (2.33739,2);

    $sql=mysql_query("SELECT * 
    FROM tbl z
    WHERE ROUND(z.lat,3) LIKE $lat AND ROUND(z.long,2) LIKE $lng
    LIMIT 0,5") or die(mysql_error());
  while($row=mysql_fetch_assoc($sql))
  {
  echo $row[lat].";".$row[long]."<br>";
  }

Thanks!

score 1 · Accepted Answer · answered Jan 02 '12 at 14:51

You can use the great-circle distance.

$lat = round (48.89463, 3);
$lng = round (2.33739,2);

$sql=mysql_query("SELECT * 
FROM tbl z
ORDER BY ACOS(COS(RADIANS($lat))*COS(RADIANS(z.lat))*COS(RADIANS(z.long)RADIANS($lng))+SIN(RADIANS($lat))*SIN(RADIANS(z.lat))
WHERE ROUND(z.lat,3) LIKE $lat AND ROUND(z.long,2) LIKE $lng
LIMIT 0,5") or die(mysql_error());
while($row=mysql_fetch_assoc($sql))
{
echo $row[lat].";".$row[long]."<br>";
}

mysql select closest lat/long to specific location

1 Answers1