TalkingQuickly's Today I Learned

Calculating distance in Redshift

Execute the below in Redshift as an admin to create a function for calculating the distance in miles between two lat lngs, saves having lots of queries full of trig. Can get to km if needed by multiplying by 1.609344.

------- DISTANCE_IN_MILES FUNCTION ---------
CREATE FUNCTION DISTANCE_IN_MILES (orig_lat float, orig_long float, dest_lat float, dest_long float)
  RETURNS float
STABLE
AS $$
  import math
  r = 3963.1676
  phi_orig = math.radians(orig_lat)
  phi_dest = math.radians(dest_lat)
  delta_lat = math.radians(dest_lat - orig_lat)
  delta_long = math.radians(dest_long - orig_long)
  a = math.sin(delta_lat/2) * math.sin(delta_lat/2) + math.cos(phi_orig) \
      * math.cos(phi_dest) * math.sin(delta_long/2) * math.sin(delta_long/2)
  c = 2 * math.atan2(math.sqrt(a), math.sqrt(1 - a))
  d = r * c
  return d
$$ LANGUAGE plpythonu;

From: https://stackoverflow.com/questions/25316193/postgres-sql-amazon-redshift-how-to-calculate-distance-between-two-latitude