Find the locations closest to a point (Calculate distance between two points) using SQL
Problem
We often have to list the stores closest to a zip code and show the distance between the two points as well.
Solution
The solution is performed within SQL itself (MSSQL in this case, but can probably be easily modified for other databases).
Detailed explanation
To calculate the distance between two points (Latitude/Longitude pairs) on the earth's surface, we have to use Spherical Law of Cosines (http://en.wikipedia.org/wiki/Spherical_law_of_cosines) shown below:
d = acos( sin(φ1).sin(φ2) + cos(φ1).cos(φ2).cos(Δλ) ).R
where: φ = latitude, λ = longitude, R = radius of earth, d = distance between the points
and the formula to calculate the distance using a SQL Query in Coldfusion is shown below:
This requires you to pass in the latitude/longitude for the location you are searching for and assumes you have the latitude/longitude values for a store in the database. (Or you can join it with a zips table to get the latitude/longitude pairs).
The value of 69.17073 is used to convert the distance into miles and a value of 111.3195 is used to convert the distance into kilometers. This value is most accurate for points near the equator and varies as it deviates from the equatorial line.
<cfset radius = 111.3195> <!--- km --->
<cfset radius = 69.17073> <!--- mi --->
<cfset distance = 100> <!--- distance from the point you are searching for --->
<!---
degrees - tweak according to your needs / it should be related to the distance you set above
this will help limit the records searched instead of having to perform the calculation for every record
--->
<cfset searchDegree = 4>
<cfset latitude = 0> <!--- pass in this value --->
<cfset longitude = 0> <!--- pass in this value --->
<cfquery name="closestStore" datasource="#application.dsn#">
Select #radius# * Degrees(
acos(
sin(radians(#latitude#))*sin(radians(store.latitude))
+
cos(radians(#latitude#)) * cos(radians(store.latitude)) * cos(radians(store.longitude)-radians(#longitude#))
)
) As distance,
store.name
From store
Where
(
store.latitude between
#latitude# - #searchDegree#
and
#latitude# + #searchDegree#
)
And
(
store.longitude between
#longitude# - #searchDegree#
and
#longitude# + #searchDegree#
)
And
(
#radius# * Degrees(
acos(
sin(radians(#latitude#))*sin(radians(store.latitude))
+
cos(radians(#latitude#)) * cos(radians(store.latitude)) * cos(radians(store.longitude)-radians(#longitude#))
)
)
) < #distance#
Order By distance, name
</cfquery>
+
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License. Permissions beyond the scope of this license, pertaining to the examples of code included within this work are available at Adobe.
Report abuse
