Knowledge Drop

# What to do if you try to calculate the distance between identical locations and get ACOS values that are out of the [-1,1] range?

• 0 replies
• 184 views

Userlevel 5
• Looker Staff
• 0 replies

Last tested: Sep 14, 2017

You can check for those coordinates that are identical, then add a small value to one of them, like so:

`dimension: location { type: location sql_latitude: case when \${TABLE}.a_lat = \${TABLE}.b_lat then \${TABLE}.a_lat + .00005 else \${TABLE}.a_lat end;; sql_longitude: case when \${TABLE}.a_lon = \${TABLE}.b_lon then \${TABLE}.a_lon + .00005 else \${TABLE}.a_lon end;; } `

This avoids calculation between identical locations by making them un-identical.

Another method is calculating the distance manually using this formula and incorporating `CASE WHEN` logic to convert ACOS values > 1 or < -1 to null, like so:

` `

`dimension: distance { type: number value_format: "0.00\ \k\m" sql: 6371 *acos( case when abs(cos(radians(\${latitude}))* cos(radians(\${transaction_fact.latitude}))* cos(radians(\${longitude}) - radians(\${transaction_fact.longitude})) + sin(radians(\${latitude})) * sin(radians(\${transaction_fact.latitude}))) <= 1 then cos(radians(\${latitude})) * cos(radians(\${transaction_fact.latitude})) * cos(radians(\${longitude}) - radians(\${transaction_fact.longitude})) + sin(radians(\${latitude})) * sin(radians(\${transaction_fact.latitude}))end) ;;`

The above example only checks for those ACOS values > 1, but it could be modified to check for those < -1 as well.

This content is subject to limited support.