CODEWITHSUNDEEP
mysqlsqlgeospatial
Little Bobby Drop Tables
Little Bobby Drop Tables

Reputation: 171

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

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?

Upvotes: 0

Views: 148

Answers (2)

Little Bobby Drop Tables
Little Bobby Drop Tables

Reputation: 171

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

Upvotes: 0

Kez
Kez

Reputation: 760

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

Upvotes: 1

Related Questions