0

I have a MySQL table with rows latitude, longitude and radius. Latitude and longitude are of type DOUBLE; radius is of type INT and measured in meters. Essentially, each row represents a circle on Earth.

Given values of user_latitude and user_longitude, how can I select all the rows that include the user in their circles?

I made a quick diagram to illustrate in case my question wasn't clear. Each red dot represents a user_latitude and user_longitude point, each circle represents a latitude, longitude and radius row in the database.

diagram

  • 1
    What is your current query? – Matheno Apr 23 '15 at 10:37
  • can use something like where ( lat_x - $x ) + ( lat_y - $y ) < $r and ( lat_x - $x ) + ( lat_y - $y ) > -$r. [ this is a crude approximation of a circle.. ] – amdixon Apr 23 '15 at 10:40
  • you dont need to worry about the circles, you just need to care if the distance between the points is less than the radius – pala_ Apr 23 '15 at 11:10
  • @pala_ that is the definition of a circle – amdixon Apr 23 '15 at 11:19
  • yes, but you dont need to think about generating the circle. – pala_ Apr 23 '15 at 11:19
  • Find distance between two points using latitude and longitude in mysql http://stackoverflow.com/questions/24370975/find-distance-between-two-points-using-latitude-and-longitude-in-mysql – Dharmang Apr 23 '15 at 11:26

1 Answers1

0

step1: implement haversine distance formula

delimiter //
create function DistanceInKm(
        lat1 FLOAT, lon1 FLOAT,
        lat2 FLOAT, lon2 FLOAT
     ) returns float
    NO SQL DETERMINISTIC
begin
    return degrees(acos(
              cos(radians(lat1)) *
              cos(radians(lat2)) *
              cos(radians(lon2) - radians(lon1)) +
              sin(radians(lat1)) * sin(radians(lat2))
            )) * 111.045;
END//
delimiter ;

(the magic number 111.045 converts it to km, use 69 for miles)

step 2: use the function

select * from areas where distance(lat, long, USERLAT, USERLONG) < radius

reference

pala_
  • 8,901
  • 1
  • 15
  • 32
  • Thanks for the answer @pala_, one question though. What would the "magic number" be for a radius measured in meters? –  Apr 24 '15 at 05:28
  • i'm guessing 111045, since a kilometer is a thousand meters – pala_ Apr 24 '15 at 05:30
  • thanks for the answer, it seems to work based on my testing so far :) –  Apr 24 '15 at 05:50