Wednesday, February 16, 2011

The Shortest Distance between Two Points is MATH

They need me to put together a simple little system at work that finds known locations within a given radius of a given location. This sounded familiar, so I dug up an old SQL query of mine from a LONG time ago...

SELECT
  Name,
  Address,
  City,
  State,
  Latitude,
  Longitude,
  (
    ACOS(
      COS(@center_latitude * (PI()/180)) *
      COS(@center_longitude * (PI()/180)) *
      COS(Latitude * (PI()/180)) *
      COS(Longitude * (PI()/180)) +
      COS(@center_latitude * (PI()/180)) *
      SIN(@center_longitude * (PI()/180)) *
      COS(Latitude * (PI()/180)) *
      SIN(Longitude * (PI()/180)) +
      SIN(@center_latitude * (PI()/180)) *
      SIN(Latitude * (PI()/180))
    ) *
    (
      (@equatorial_radius * @polar_radius) /
      (
        SQRT(
          (@equatorial_radius * @equatorial_radius) -
          (
            (
              (@equatorial_radius * @equatorial_radius) -
              (@polar_radius * @polar_radius)
            ) *
            (
              COS(@center_latitude) *
              COS(@center_latitude)
            )
          )
        )
      )
    )
  ) AS Miles
FROM
  Places
WHERE
  Miles <= @search_radius

This query accepts a center lat/long (the customer location) and a radius (the distance from the location to search) and finds any known locations within that distance from the center and notes that distance in miles (so we can sort and find the closest).

It also accepts parameters for the radius of the Earth. The radius of the Earth can be expressed in many various ways. For simplification, we'll use these:

@equatorial_radius = 3963.190 miles
@polar_radius = 3949.902 miles

(Note that this equatorial radius may not account for the crust displacement during the 2004 Indian Ocean earthquake.)

This distance calculation not only plots a circle around the area, but in the projection of that circle onto the plane of the Earth it accounts for the curvature of the Earth’s surface and the bulging at the equator, derived from the WGS-84 ellipsoid model.

We never did get around to using this at a previous job. (They plotted searches as rectangles around the center... ew. Just goes to show how a fundamentally broken business idea can churn out sub-par results from an otherwise intelligent and talented team of developers.) But maybe I'll finally get a chance to use it here.

No comments:

Post a Comment