DZone Snippets: distance code

GPS distance and initial bearing between points (MySQL)

mrclay (Steve Clay) — Wed, 16 Jan 2008 21:14:20 GMT

Assume you have a table of locations with Latitude and Longitude for each one. In my case the table is "station" and the primary key is "LocID".

First we create a view to help with the 3D geometry (6378 = Earth's radius in km):



CREATE VIEW gpsGlb AS

    SELECT 

        LocID

        ,6378 * COS(RADIANS(Latitude)) * COS(RADIANS(Longitude)) AS x

        ,6378 * COS(RADIANS(Latitude)) * SIN(RADIANS(Longitude)) AS y

        ,6378 * SIN(RADIANS(Latitude)) AS z

    FROM station;

Here I query for distances to all my locations that are NOT LocID = 405 (rounded miles in my case):



SELECT 

    LocID

    ,ROUND((2 * 6378 * ASIN(d / 2 / 6378)) * 0.621371192) AS dist_mi

FROM

    (SELECT

        SQRT(dx * dx + dy * dy + dz * dz) AS d

        ,LocID

     FROM

        (SELECT

            p1.x - p2.x AS dx

            ,p1.y - p2.y AS dy

            ,p1.z - p2.z AS dz

            ,p2.LocID

        FROM gpsGlb p1

        JOIN gpsGlb p2 ON (p1.LocID = 405 AND p2.LocID != 405)

       ) t1

    ) t2

ORDER BY dist_mi

Here I get the initial bearing to the locations. The "boxed" calculation will come in handy later.



SELECT

    LocID

    ,(360 + DEGREES(ATAN2(y, x))) % 360 AS initBearing_deg

    ,ROUND(((360 + DEGREES(ATAN2(y, x))) % 360) / 22.5) * 22.5 

     AS initBearingBoxed_deg

FROM

    (SELECT

        SIN(RADIANS(s2.Longitude - s1.Longitude)) * COS(RADIANS(s2.Latitude)) 

        AS y

        ,COS(RADIANS(s1.Latitude)) * SIN(RADIANS(s2.Latitude))

            - SIN(RADIANS(s1.Latitude)) * COS(RADIANS(s2.Latitude))

               * COS(RADIANS(s2.Longitude - s1.Longitude)) 

        AS x

        ,s2.LocID

    FROM station s1

    JOIN station s2 ON (s1.LocID = 405 AND s2.LocID != 405)

    ) q1

Here's the combined query plus boxed degrees converted to 'NNE', etc. I've also added a limit for the distance in the qq1 subquery.



SELECT

    qq2.LocID

    ,dist_mi

    ,CASE initBearingBoxed_deg

        WHEN 22.5 THEN 'NNE'   WHEN 45 THEN 'NE'

        WHEN 67.5 THEN 'ENE'   WHEN 90 THEN 'E'

        WHEN 112.5 THEN 'ESE'  WHEN 135 THEN 'SE'

        WHEN 157.5 THEN 'SSE'  WHEN 180 THEN 'S'

        WHEN 202.5 THEN 'SSW'  WHEN 225 THEN 'SW'

        WHEN 247.5 THEN 'WSW'  WHEN 270 THEN 'W'

        WHEN 292.5 THEN 'WNW'  WHEN 315 THEN 'NW'

        WHEN 337.5 THEN 'NNW'  ELSE 'N'

     END AS bearing

FROM (

    SELECT 

        LocID

        ,ROUND((2 * 6378 * ASIN(d / 2 / 6378)) * 0.621371192) AS dist_mi

    FROM

        (SELECT

            SQRT(dx * dx + dy * dy + dz * dz) AS d

            ,LocID

         FROM

            (SELECT

                p1.x - p2.x AS dx

                ,p1.y - p2.y AS dy

                ,p1.z - p2.z AS dz

                ,p2.LocID

            FROM gpsGlb p1

            JOIN gpsGlb p2 ON (p1.LocID = 405 AND p2.LocID != 405)

           ) t1

        ) t2

    ) qq1

JOIN (

    SELECT

        LocID

        ,(360 + DEGREES(ATAN2(y, x))) % 360 AS initBearing_deg

        ,(360 + ROUND((DEGREES(ATAN2(y, x))) / 22.5) * 22.5) % 360 

         AS initBearingBoxed_deg

    FROM

        (SELECT

            SIN(RADIANS(s2.Longitude - s1.Longitude)) * COS(RADIANS(s2.Latitude)) 

             AS y

            ,COS(RADIANS(s1.Latitude)) * SIN(RADIANS(s2.Latitude))

                - SIN(RADIANS(s1.Latitude)) * COS(RADIANS(s2.Latitude))

                   * COS(RADIANS(s2.Longitude - s1.Longitude)) 

             AS x

            ,s2.LocID

        FROM station s1

        JOIN station s2 ON (s1.LocID = 405 AND s2.LocID != 405)

        ) q1

    ) qq2 ON (qq1.LocID = qq2.LocID

              AND qq1.dist_mi <= 60)

ORDER BY dist_mi

Variance, Mean, Normalizing Functions, Euclidean and other Distances

dustinsmith (dustin smith) — Sat, 10 Feb 2007 02:14:52 GMT

Here sum, mean and variance were inspired by the Peter's inline sum code:



class Array; def sum; inject( nil ) { |sum,x| sum ? sum+x : x }; end; end

class Array; def mean; self.sum/self.size.to_f; end; end

class Array; def variance; mean = self.mean; Math.sqrt(inject( nil ) { |var,x| var ? var+((x-mean)**2) : ((x-mean)**2)}/self.size.to_f); end; end

If you want to normalize a random variable (array) so that mean = 0 and variance = 1, you can transform your array x by calling:



# inputs a random variable, sets mean = 0 and variance = 1

def standardize_random_variable(x)

  mean = x.mean

  variance = x.variance

  x.map!{|a| (a-mean)/variance }

end

If you want to compute distance, call these functions between two arrays of data, a and b.



## Distance Functions



# Sum of (x-y)^2

def euclidean_squared_distance(a,b)

  b = b.to_a

  a = a.to_a

  sum_of_diff_sq = 0

  (0...a.size).each { |i| sum_of_diff_sq+=((a[i].to_f-b[i].to_f)**2)}

  sum_of_diff_sq 

end



# Square root of sum of (x-y)^2

def euclidean_distance(neighbor,xq)

  Math.sqrt(euclidean_squared_distance(neighbor,xq))

end



# Sum of abs(x,y)

def cityblock_distance(neighbor,xq)

  xq = xq.to_a

  abs_diff = 0

  (0...xq.size).each { |i| abs_diff+=(Math.abs(xq[i].to_f-neighbor[i].to_f)}

  abs_diff

end

Date.distance_to method for Ruby

timmorgan (Tim Morgan) — Sat, 27 May 2006 00:58:55 GMT

Gives years, months, and days between two dates (like a human would expect)



class Date

  def distance_to(end_date)

    years = end_date.year - year

    months = end_date.month - month

    days = end_date.day - day

    if days < 0

      days += 30

      months -= 1

    end

    if months < 0

      months += 12

      years -= 1

    end

    {:years => years, :months => months, :days => days}

  end

end