Different result for Haversine formulas

Question

I am using mysql to count the proximity and for that i have created one procedure named distance which is as follows but that procedure is not working properly but the sql statement is working so what is the difference over here as both are i guess Haversine formulas but not giving me the proper result. i really don't know wht i am missing in formula one.

Data structure of my table is as follows

for formula one

id  varchar(100)    
userid  varchar(100)    
username varchar(100)
currLoc point           
radius  int(10)

for formula two

id  varchar(30)
userid  varchar(30)
username varchar(40)
lat float(10,6)
lan float(10,6)
radius  varchar(100)

Formula One: reference

sql statement to execute distance function

SELECT userid, username, distance(userstatus.currLoc, 
 GeomFromText('POINT(23.039574  72.56602)')) AS cdist 
    FROM userstatus HAVING cdist <= 0.6 ORDER BY cdist LIMIT 10


 RETURN 6371 * 2 *
    ASIN( SQRT(POWER(SIN(RADIANS(ABS(X(a)) - ABS(X(b)))), 2) + 
               COS(RADIANS(ABS(X(a)))) * COS(RADIANS(ABS(X(b)))) *
                    POWER(SIN(RADIANS(Y(a) - Y(b))), 2)));

Formula two: reference

SELECT *,(((acos(sin((23.039574*pi()/180)) * 

        sin((lat *pi()/180))+cos((23.039574*pi()/180)) * 

         cos((lat *pi()/180)) * cos(((72.56602- lon)*pi()/180))))*

     180/pi())*60*1.1515*1.609344) as distance

FROM status HAVING distance <= 0.6

here 0.6 is a radius in kilometers

Answer 1

One version of the expression is using ABS(X(a)) etc and the other is not. The one using ABS is suspect. You can't afford to ignore the sign on the angles. You'll get different results in some areas of the world (near the equator or the prime meridian, for example, or near the poles).

Your constants are also different.

60*1.1515*1.609344

vs

6371 * 2

One expression involves SQRT, the other does not.

One expression involves ASIN and the other uses ACOS.

There is essentially nothing in common between the two...

See the discussion at Wikipedia 'Haversine Formula', and in particular the references to numerical stability when the distance between the points is small.

You could also improve the chances of people helping you by making the formulae you're using semi-readable, by splitting them over lines.

For example:

 RETURN 6371 * 2 *
        ASIN( SQRT(POWER(SIN(RADIANS(ABS(X(a)) - ABS(X(b)))), 2) + 
                   COS(RADIANS(ABS(X(a)))) * COS(RADIANS(ABS(X(b)))) *
                        POWER(SIN(RADIANS(Y(a) - Y(b))), 2)));

And:

(((acos(sin((23.039574*pi()/180)) * sin((lat *pi()/180)) +
        cos((23.039574*pi()/180)) * cos((lat *pi()/180)) *
        cos(((72.56602-lan)*pi()/180))
       )
   ) * 180/pi()) * 60 * 1.1515 * 1.609344)

The latter references 'lan'; is that meant to be 'lon'? In the second example, you appear to have encoded one of the two positions as 23.039574°N and 72.56602°W, and lat and lan come from the table in the SQL query.