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 

Answer 1

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;

Answer 2

Since there are so few answers for haversine SQL on the web, I am adding a full version of it adapted from https://en.wikipedia.org/wiki/Haversine_formula for your copy-paste workflow:

CREATE FUNCTION haversine(Lat1, Lng1, Lat2, Lng2) AS 
    2 * 6335 
        * asin(sqrt(
            pow(sin((radians(Lat2) - radians(Lat1)) / 2), 2)
            + cos(radians(Lat1))
            * cos(radians(Lat2))
            * pow(sin((radians(Lng2) - radians(Lng1)) / 2), 2)
        ));

I needed it for https://duckdb.org. Hope it helps someone!