Question

Distance between latitude/longitude coordinate pairs

  • 27 August 2015
  • 1 reply
  • 1260 views



##Howdy Good Lookers!


###Lucky for you Looker now has a built-in distance dimension that supports this use case!


###So, if you LOVE Trigonometry like me, please read on and enjoy! If not, you can use the built in Looker functionality in the link above!





##Latitude/Longitude Pairs and Great Circle Distance in SQL



There are ways of using basic trigonometric functions to calculate the great circle distance between two pairs of latitude-longitude coordinates on a sphere. Many SQL dialects (including but not limited to: Redshift, Postgres, MySQL) support these trigonometric functions.



The Spherical Law of Cosines accurately calculates the distance between pairs of lat/lon coordinate as long as the two points are greater than ~1.5 meters apart. If you need better accuracy than that, consider implementing the Vincenty’s formulae.



##The Spherical Law of Cosines





ACOS( SIN(lat_a) * SIN(lat_b) + COS(lat_a) * COS(lat_b) * COS(lon_b - lon_a) ) * e_radius = dist_ab



Where:


       lat_a, lon_a = latitude and longitude of point ‘a’ (in radians)


       e_radius = approximate radius of earth (~6371 km)


       dist_ab = distance between point ‘a’ and point ‘b’





Conventions:





Signs:


       NORTH latitudes are POSITIVE


       SOUTH latitudes are NEGATIVE


       EAST longitudes are POSITIVE


       WEST longitudes are NEGATIVE



Units of Measure:


       Most SQL dialects take arguments for trigonometric functions in radians.


       If your raw lat/lon values are in degrees, convert them via:   ( degree_value * π/180 = radian_value )


       Define the lat and lon values as double precision elements for the most accurate results





##Potential Use Cases



There are many valuable use cases for this formula. A simple example is calculating the distance of customers from a central distribution center (cdc):



 - dimension: distance_from_cdc_in_kilometers

type: number

sql: |

ACOS( SIN(${cdc_location.lat}) * SIN(${customer_facts.lat}) + COS(${cdc_location.lat}) * COS(${customer_facts.lat}) * COS(${customer_facts.lon}-${cdc_location.lon}) ) * 6371



Another use case is joining tables based on proximity. Maybe you want to join rows in your ‘customers’ table to your ‘storefronts’ table whenever a customer is within 5 kilometers of a storefront. That might look something like this



- explore: storefronts

joins:

- join: customers

sql_on : |

ACOS( SIN(${storefronts.lat}) * SIN(${customers.lat}) + COS(${storefronts.lat}) * COS(${customers.lat}) * COS(${customers.lon}-${storefronts.lon}) ) * 6371 <= 5

type: inner

relationship: many_to_many



##Reminders







  1. SQL trig functions normally expect arguments in radians so convert degree lat/lon values to radians if necessary. ( degree_value * π/180 = radian_value )







  2. Make sure the values you use for earth’s radius and the distance between the lat/lon points are measured in the same units (e.g. kilometers, miles, etc.).






1 reply

Userlevel 3

As of 3.44, Looker released a type: distance dimension which makes this a whole bunch easier!

Reply