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