find nearby places

within a radius of x kms by lat and longitude

[curioustoddler]

I have a mysql database which contains entries of locations with latitudes and longitudes. If i need to find all other locations within a certain radius( say 5 kms within a location X). Now i have longitudes and latitudes for all including this X. So how do i do this. I have seen lots of sites, dating, real estate etc doing this.
But i do not know how to do this.
Thanks for your help.

[Readie]

Well, the best way I can think of to do this is to use the pythagoras theorem - a² + b² = c².

$current_lon = 47;
$current_lat = 12; // Static for example only

$accuracy = 15; // Degree of accuracy desired

$sql = 'SELECT location_id, longitude, latitude FROM locations';
$result = mysql_query($sql);
$rows = mysql_num_rows($result);
$eligible = array();

for($i = 0; $i < $rows; $i++) {
  $lon = mysql_result($result, $i, "longitude");
  $lat = mysql_result($result, $i, "latitude");
  $loca = mysql_result($result, $i, "location_id");

  $lon_diff = abs($current_lon - $lon);
  $lat_diff = abs($current_lat - $lat);

  $distance = sqrt(pow($lon_diff, 2) + pow($lan_diff, 2));

  if($distance <= $accuracy) {
    $eligible[$loca] = $distance;
  }
}

arsort($eligible); /* may need to use asort() instead of arsort() - not sure */

foreach($eligible as $loc => $dist) {
  $sql = 'SELECT location_name FROM locations WHERE location_id = "' . $loc . '"';
  $result = mysql_query($sql);
  // Build your table here
}

[curioustoddler]

Thanks for your help. I will try it today and will post whether i succees or face any problems. Thanks again for your help.

[curioustoddler]

I am sorry i did not read the code properly earlier. I need to loop through all the records for this. But the data is so huge. The only possible solutions is when i know the range of latitude and longitudes i am looking. For my example location X, say lat is LT and Longitude is LG. So how can i calculate the range of latitude and longitude for a radious of 5 kms.

[Readie]

Well, I suppose we can take a square (via the select query), then use pythagoras to get it down to a circle of radius x, where x is the maximum distance between current location and the desired locations.

Do this just by altering the select menu, may be worth sticking an index on the longitude and latitude columns in the database.

$sql = 'SELECT location_id, longitude, latitude FROM locations WHERE longitude <= ' . ($current_lon + $accuracy) . ' AND longitude >= ' . ($current_lon - $accuracy) . ' AND latitude <= ' . ($current_lat + $accuracy) . ' AND latitude >= ' . ($current_lat - $accuracy); 

[curioustoddler]

Thanks. But how do i decide on this $accuracy. For example i need a radius of 5 kms then how should i calculate this for 5 kms.

[Readie]

You will need to develop some conversion formula. Pick two places that you know the exact distance between in km, use pythagoras to work out the distance for your scale of longitude/latitude. You should then be able to work out what 1km is equal to in terms of your longitude/latitude scale.