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