CODEWITHSUNDEEP
sqloracle-database
kashi
kashi

Reputation: 13

Lat Long distance calculation

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

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

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

Onur Cete
Onur Cete

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

Related Questions