Bhumika Bhumika - 5 months ago 23
SQL Question

Haversine formula returns null in Query

SELECT
id,
( 3959 * acos( cos( radians(51.509980) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-0.133700 ) ) + sin( radians(51.509980) ) * sin(radians(lat)) ) ) AS distance
FROM tbl_event
HAVING distance < 5
ORDER BY distance


Here -0.133700 is creating problem, other minus values like -122 etc. are working fine with this.

Please help if anyone is aware of this issue.

Answer

It returns null because acos function get an argument greater than 1 or lower than -1. Try this :

Select id, 3959 * acos(if(d>1, 1, if(d<-1, -1, d))) as distance
From (SELECT id,
 cos( radians(51.509980) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-0.133700 ) ) + sin( radians(51.509980) ) * sin(radians(lat))  AS d
FROM tbl_event ) t1
HAVING distance < 5 
ORDER BY distance 
Comments