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?

[LookerExperts]

Knowledge Drop

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.