CODEWITHSUNDEEP
mysqldatabasedistance
Bhumika
Bhumika

Reputation: 159

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.

Upvotes: 2

Views: 402

Answers (1)

Mostafa Vatanpour
Mostafa Vatanpour

Reputation: 1408

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

Upvotes: 1

Related Questions