SQL distances and radius highlight

Question

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.

Although this won't solve your problem, it may give you some ideas: http://stackoverflow.com/a/13951139/1504882 — Elias, Mar 17 '14 at 12:05

score 1 · Accepted Answer · answered Mar 17 '14 at 12:12

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.

SQL distances and radius highlight

1 Answers1