Bhumika - 1 year ago 81

SQL Question

`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 Source

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
```