\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
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
You must log in to post a comment.