haversine formula definition for sql

Question

Hi iam currently working on an android app which stores the details of shops in an sql database and the users use the app to search for the shops around them.

i found a formula called haversine to find distance between two points with there lat and lng values.

    SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians(         lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM     markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

but iam confused with this i don't know which lat and lng value refers to what! if some one could re-write the above code such that my user has position lat1 & lng1 for the shopkeeper it is lat2 and lng2. Also can u tell me what is that a in

  acos

Are you using mysql or sql server? If the latter, it has a native geography data type that will let you do this calculation w/o all the messy math. — Ben Thul
can you give me some information about that..? when using sql,and to use that property in sql server if i use in this math kind of a way does it increase my server cost...? compared to using the service that you told..? @BenThul — user3781907
There's a lot to know. Here's a good start: msdn.microsoft.com/en-us/library/cc280766.aspx. As for doing it the way you're doing it, you're never going to be able to use an index to perform that search. The geospatial datatypes come with indexing, so queries like "get me points within a certain radius" are efficient. — Ben Thul
Yes. It comes down to a concept called SARGability. That is, right now, to satisfy your query, you need to calculate the distance from your given point to every other point and then only return those that meet your criteria (in this case, distance < 25). By using the geography datatype, the engine is smart enough to exclude a lot of points and is able to efficiently narrow in on those points that have a chance of satisfying the criteria. — Ben Thul
I've pointed you at the documentation for this for SQL Server. If you're using that, look at all of that and try some things. If you're not using SQL Server, you'll have to see if there's similar functionality in whatever RDBMS you're using. — Ben Thul

George Mastros George Mastros · Accepted Answer · 2014-12-30T16:15:20

The order of lat/lngs don't matter. Think of it this way... the distance from point A to point B is that same as the distance from point B to point A.

In your code example, the 37 is a latitude point and the -122 is a longitude point.

acos is the arc cosine trigonemetric function. Explanation here: ArcCosine

SELECT id, ( 3959 * acos( cos( radians(Lat1) ) * cos( radians( Lat2 ) ) * cos( radians(Lng2) - radians(Lng1) ) + sin( radians(Lat1) ) * sin( radians(Lat2)))) AS distance 
FROM     markers 
HAVING distance < 25 
ORDER BY distance 
LIMIT 0 , 20;

haversine formula definition for sql

1 Answers