Search code examples
sqldistancepostal-code

SQL distances and radius highlight

In my database I have a list of 1000 locations throughout the UK, in here I have the postcode, latitude and longitude just within three columns. I need to highlight any that are within a 2 mile circular radius of each other, but I am hitting a brick wall here.

Is there some simple calculation I can run on the table to highlight those I need? Any help simple or complex would be really appreciated.

Solution 
  • SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) 
      + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` 
FROM `members` 
HAVING `distance`<=’10′ 
ORDER BY `distance` ASC

    Got from the link http://zcentric.com/2010/03/11/calculate-distance-in-mysql-with-latitude-and-longitude/

    It's for MySQL but you can adapt the code for any desired DB.