View the original community article here

*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__.