CODEWITHSUNDEEP
phpmysqlsqlgoogle-maps
Giovanni
Giovanni

Reputation: 838

Select locations based on distance between geocodes using SQL query

I have an SQL database containing hotel information, some of which is the geocoded lat/lng generated by Googles geocoder.

I want to be able to select (directly using an SQL query) all the hotels within a certain range. This range will never be more than 50km so I dont need to go as detailed as alot of answers on here are suggesting (taking into account earth curvature and the fact its not a perfect sphere isnt an issue over the distances im searching).

Im thinking a simple Pythagorian formula would suffice, but I dont know what the latitude and longitude figures represent (and therefore how to convert to metres) and also ive read on a couple of 'simple' solutions to my problem that there are issues with their formulas and calculating distances between two locations either side of the meridian line (as I am based in London this will be a big issue for me!!)

Any help would be great, thankyou!

----Helpful Information-----

My database stores the geocoded data in the following format:

geo_lat: 51.5033630,
geo_lon; -0.1276250

Upvotes: 2

Views: 1523

Answers (2)

Cybercartel
Cybercartel

Reputation: 12592

You can use a simple map projection and straight distances for example equirectangular projection. In the formula on this website you can also use a simplier formula without the square root:http://www.movable-type.co.uk/scripts/latlong.html. Of course you can use a bounding box to filter the query:How to calculate the bounding box for a given lat/lng location?, https://gis.stackexchange.com/questions/19760/how-do-i-calculate-the-bounding-box-for-given-a-distance-and-latitude-longitude.

Upvotes: 0

Jacob Waller
Jacob Waller

Reputation: 4249

This is a select clause that will get your distance into kilometers. From there you can use a where clause to filter it down to less than 25 kilometers or whatever you want. If you want it in miles just take off the * 1.609344 conversion.


$latitude = [current_latitude];
$longitude = [current_longitude];

SELECT
    ((((acos(sin((".$latitude."*pi()/180)) * sin((`geo_lat`*pi()/180))+cos((".$latitude."*pi()/180)) * cos((`geo_lat`*pi()/180)) * cos(((".$longitude."- `geo_lon`)* pi()/180))))*180/pi())*60*1.1515) * 1.609344) as distance 
FROM
    [table_name]
WHERE distance

Upvotes: 4

Related Questions