Reputation: 13
I am trying to get the distance in meters using the below query. But every time I run the query, I get the error below:
EventException for error: ORA-01428 ReasonORA-01428: OracleExecutionException: argument '1.00000000000000000000000000000000000001' is out of range
SELECT station,
name,
id,
a.latitude,
a.longitude,
h.latitude as a_latitude,
h.longitude as a_longitude,
(ACOS(COS(0.0174532925*(90-a.latitude))*COS(0.0174532925*(90-h.latitude))+SIN(0.0174532925*(90-a.latitude))*SIN(0.0174532925*(90-h.latitude))*COS(0.0174532925*(a.longitude- h.longitude)))*6371000)Distance_in_metres
FROM
address1 h,
ADDRESSES a
Upvotes: 1
Views: 1732
Reputation: 59652
As already mention ACOS must be [-1,1]. This query may work:
SELECT station,
name,
id,
a.latitude,
a.longitude,
h.latitude as a_latitude,
h.longitude as a_longitude,
ACOS(ROUND(COS(ACOS(-1)/180*(90-a.latitude))*COS(ACOS(-1)/180*(90-h.latitude))+SIN(ACOS(-1)/180*(90-a.latitude))*SIN(ACOS(-1)/180*(90-h.latitude))*COS(ACOS(-1)/180*(a.longitude-h.longitude)), 20)) * 6371000 as Distance_in_metres
FROM
address1 h,
ADDRESSES a
Upvotes: 2
Reputation: 273
Probably you has a problem about ACOS function.
EX: If you want to use acos function like this; ACOS(x)
The value of x should be in the range [-1,1].
You can read more information in this ORACLE/ACOS article.
Upvotes: 1