MySQL Query to get radius of longitude and latitude

///MySQL Query to get radius of longitude and latitude

MySQL Query to get radius of longitude and latitude

Lately I have been doing a lot of work with longitude and latitude references, of which I needed to search my Database for points of interest surrounding a particular reference. At first it was quite difficult, as I have never done something to that extreme query wise, and I couldn’t find something via google or other resource that really helped for my needs, and the ones I did find were either broken or just not the right fit. So between that and a few hours of working on it I came up with something that is repetitively fast checking against upwards of almost 100,000 records. So with that I know Geo-location is a rising star as its becoming easier and easier for people to use it with tech supporting it better now a days and all else, and a lot of people need to search there DB for data similar to like what I am. So without any more trouble of having to hear me babble on here is that query. Simplified a bit more from how I use it but none the less just as effective (I usually tie this into a query that has server INNER JOIN and or LEFT JOIN references). Note: Calculation is based on Miles in this version easily changed to Nautical or Kilometers by changing a couple numbers but, in this example currently just miles (maybe Ill update it later so one can know what to change in order to do one of the others).\r\n\r\n
\r\n$distance = 10;\r\n$latitude = 37.295092;\r\n$longitude = -121.896490;\r\n\r\n$sql = "SELECT loc.*, (((acos(sin(($latitude*pi()/180)) * sin((`latitude`*pi()/180))+cos(($latitude*pi()/180)) * cos((`latitude`*pi()/180)) * cos((($longitude - `longitude`)*pi()/180))))*180/pi())*60*1.1515) AS `distance` FROM table_with_lonlat_ref loc HAVING distance < $distance"\r\n
By |2014-01-05T10:22:20+00:00March 8th, 2012|Code, MySQL|3 Comments

About the Author:

Not much to know about me, I'm a 35+ year old coder, geek, gamer..

3 Comments

  1. Sean May 25, 2012 at 2:30 pm

    Hi,\r\n\r\nCan you please explain the numbers used in this formula? i.e. the *60*1.1515 at the end?\r\n\r\nCheers

    • chris June 1, 2012 at 8:28 pm

      Honestly I can’t remember all to well. I know its a means of calculating distance. If you were to change I believe the 60 in the equation to something else you could convert it from miles to kilometers or to nautical miles. I’d have to go find my notes and rediscover the reason. But in all its all part of the math that takes the longitude/latitude and finds a distance outward from that.\n\nBy the way sorry for the delayed response hope I was able to at the least give you some idea to go on, if you haven’t already found your answer else where.

  2. Alex December 12, 2013 at 3:35 pm

    Thanks for sharing your code, can you please mention what engine your MySQL is using, and what is the datatype for latitude and longitude ? Also, are those two fields indexed ?

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: