I need to get all MySQL geotags within 10 miles of my current location.
Given that my current location is latitude = 34.034940, longitude = -117.950060 and I have a MySQL table of geotag rows with columns for lat and lng, then my query will be:
SELECT `idtags`, `lat`, `lng` FROM `mygeotags` WHERE (((acos(sin((34.034940*pi()/180)) * sin((`lat`*pi()/180)) + cos((34.034940*pi()/180)) * cos((`lat`*pi()/180)) * cos(((-117.950060 - `lng`)*pi()/180)))) * 180/pi()) * 60 * 1.1515) <= 10
ORDER BY `idtags` ASC
I derived this query from an example at https://martech.zone/calculate-great-circle-distance/
Another query using radians can be found at https://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula
This is a version going against my geotag table substituting $lat, $lng for my current location.
SELECT `idtags`, `lat `, `lng` FROM `mygeotags` WHERE ( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin(radians(lat)) ) ) <= 10
ORDER BY `idtags` ASC
Helps to visualize parallels of latitude and meridians of longitude.
No comments:
Post a Comment