Query that finds all locations within a certain radius of a given coordinate

Question

My database has an organisation tables with two decimal columns lat and lon that indicate the location of the organisation. I'm trying to find all organisations within 800km of the coordinate 53.6771, -1.62958 (this roughly corresponds to Leeds in the UK).

The query I'm using is

select * 
from organisation 
where (3959 * acos(cos(radians(53.6771)) * 
      cos(radians(lat)) * 
      cos(radians(lon) - radians(-1.62958)) + sin(radians(53.6771)) * 
      sin(radians(lat)))) < 800

However this returns locations in Lyon, France which is about 970km from Leeds, UK. I realise that formulae such as the above make some simplifying assumptions (e.g. treating the shape of the Earth as a sphere), so I don't expect the results to be absolutely accurate, but I should be able to do better than this?

@a_horse_with_no_name I'm using MySQL, but I can't change the definition of the `organisation` table — Little Bobby Drop Tables, Jan 05 '16 at 11:49

score 1 · Accepted Answer · edited May 23 '17 at 11:59

I found a formula here for calculating the distance in km between two points, and I have tried to convert it to mysql:

WHERE (6371 * 2 * 
ATAN2(
  SQRT(
    SIN(RADIANS((lat-53.6771)/2)) * SIN(RADIANS((lat-53.6771)/2)) + SIN(RADIANS((lon+1.62958)/2)) * SIN(RADIANS((lon+1.62958)/2)) * COS(RADIANS(lat)) * COS(RADIANS(53.6771))
  ),
  SQRT(
    1-(SIN(RADIANS((lat-53.6771)/2)) * SIN(RADIANS((lat-53.6771)/2)) + SIN(RADIANS((lon+1.62958)/2)) * SIN(RADIANS((lon+1.62958)/2)) * COS(RADIANS(lat)) * COS(RADIANS(53.6771)))
  )
)) < 800

score 0 · Answer 2 · answered Jan 05 '16 at 12:27

The problem was caused by using the multiplier for miles (3959) instead of kilometers (6371). The correct query is shown below

select * 
from   organisation 
where  (6371 * acos(cos(radians(53.6771)) * 
       cos(radians(lat)) * 
       cos(radians(lon) - radians(-1.62958)) + sin(radians(53.6771)) * 
       sin(radians(lat)))) < 800

Query that finds all locations within a certain radius of a given coordinate

2 Answers2