Select locations based on distance between geocodes using SQL query

Question

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

Check this answer on [SO](http://stackoverflow.com/questions/1110565/distance-between-2-geocodes) — djluis, Feb 19 '14 at 16:21
I saw that post, but they are either too complex for my use (ie taking into account the 'sphere factor') or they are not using the SQL query SELECT * WHERE... etc — Giovanni, Feb 19 '14 at 16:26
Look at there these links http://stackoverflow.com/questions/10126404/calculate-distance-between-two-points-directly-in-sqlite — Amitsharma, Feb 19 '14 at 16:26
You can either calculate it yourself as others have said using Pythagoras' OR since you're already using google you can plot all your hotels on a google map then use circle overlays to show all markers within a distance something like this http://s14.postimg.org/sd5h4dcpd/Untitled.png — Dave, Feb 19 '14 at 16:38

score 4 · Accepted Answer · answered Feb 19 '14 at 16:35

4

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

answered Feb 19 '14 at 16:35

Jacob Waller

4,119
3
26
32

Excellent - exactly what I was after (although if im honest it is still quite a complex formula - I was hoping to be able to understand what it does :) ) And can this be used either side of the meridian line? ive read comparing -0.1 to +0.1 longitude throws up some large errors in some formulas! – Giovanni Feb 19 '14 at 16:42
I don't think the meridian line comes into effect. This is using geo locations. I suppose understanding it is a different story. This is a common way of doing it in SQL. I did not come up with the entire idea myself. It works great though. Please give up vote or mark it as the answer if you are happy with it. – Jacob Waller Feb 20 '14 at 03:26

score 0 · Answer 2 · edited May 23 '17 at 11:46

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.

Select locations based on distance between geocodes using SQL query

2 Answers2