Finding The Distance Between Latitude / Longitude Locations In ColdFusion
Someone asked me to write up a demo on finding all of the zip codes in the local proximity to a given zip code (such as you would do with a Store Locator). I have never done this before, so I figured I better start with the basics. Seeing as most zip code databases work off of latitude and longitude, I figured I would first start out trying to find the earthly distance between two sets of latitude and longitude readings. As I have no working knowledge of how to do this, I did what everyone else does - I Googled for an answer.
After combing through a number of results, I finally settled on this one: http://zips.sourceforge.net. The formula look pretty simple so I figured converting it to ColdFusion would be straightforward. One thing that you will notice in the GetLatitudeLongitudeProximity() method is that we are multiplying the difference in degrees by an approximate number of miles-per-degree. This is an approximation and will change (ie. become less accurate) the closer you get to the North and South Pole where the longitude lines get closer together. However, for our purposes, I am just going to run with it.
<cffunction
name="GetLatitudeLongitudeProximity"
access="public"
returntype="numeric"
output="false"
hint="I find the approximate distance in miles between the given pair of latititude and longitude values. The closer you get to the North or South Pole, the less accurate this becomes due to changes in distance between degrees. Math borrowed from http://zips.sourceforge.net.">
<!--- Define arguments. --->
<cfargument
name="FromLatitude"
type="numeric"
required="true"
hint="I am the starting latitude value."
/>
<cfargument
name="FromLongitude"
type="numeric"
required="true"
hint="I am the starting longitude value."
/>
<cfargument
name="ToLatitude"
type="numeric"
required="true"
hint="I am the target latitude value."
/>
<cfargument
name="ToLongitude"
type="numeric"
required="true"
hint="I am the target longitude value."
/>
<!--- Define the local scope. --->
<cfset var LOCAL = {} />
<!---
The approximate number of miles per degree of latitude.
Once we have the difference in degrees, we will use this
to find an approximate horizontal distance.
--->
<cfset LOCAL.MilesPerLatitude = 69.09 />
<!---
Calculate the distance in degrees between the two
different latitude / longitude locations.
--->
<cfset LOCAL.DegreeDistance = RadiansToDegrees(
ACos(
(
Sin( DegreesToRadians( ARGUMENTS.FromLatitude ) ) *
Sin( DegreesToRadians( ARGUMENTS.ToLatitude ) )
)
+
(
Cos( DegreesToRadians( ARGUMENTS.FromLatitude ) ) *
Cos( DegreesToRadians( ARGUMENTS.ToLatitude ) ) *
Cos( DegreesToRadians( ARGUMENTS.ToLongitude - ARGUMENTS.FromLongitude ) )
)
)
) />
<!---
Given the difference in degrees, return the approximate
distance in miles.
--->
<cfreturn Round( LOCAL.DegreeDistance * LOCAL.MilesPerLatitude ) />
</cffunction>
<cffunction
name="DegreesToRadians"
access="public"
returntype="numeric"
output="false"
hint="I convert degrees to radians.">
<!--- Define arguments. --->
<cfargument
name="Degrees"
type="numeric"
required="true"
hint="I am the degree value to be converted to radians."
/>
<!--- Return converted value. --->
<cfreturn (ARGUMENTS.Degrees * Pi() / 180) />
</cffunction>
<cffunction
name="RadiansToDegrees"
access="public"
returntype="numeric"
output="false"
hint="I convert radians to degrees.">
<!--- Define arguments. --->
<cfargument
name="Radians"
type="numeric"
required="true"
hint="I am the radian value to be converted to degrees."
/>
<!--- Return converted value. --->
<cfreturn (ARGUMENTS.Radians * 180 / Pi()) />
</cffunction>
Once I had the primary ColdFusion user defined function and its two helper methods in place (for radian-degree conversion), I just needed to test them. I calculated the distance between my office zip code (10016) and several other locations in New York and Boston:
<!--- Get the starting location. NYC. --->
<cfset Location10016 = {
Latitude = 40.7445,
Longitude = -73.9782
} />
<!--- Get the target location. NYC. --->
<cfset Location10011 = {
Latitude = 40.7409,
Longitude = -73.9997
} />
<!--- Get the target location. Croton on the Hudson. --->
<cfset Location10520 = {
Latitude = 41.2219,
Longitude = -73.8870
} />
<!--- Get the target location. Boston. --->
<cfset Location02155 = {
Latitude = 42.4255,
Longitude = -71.1081
} />
<!--- Output the distance. --->
<cfoutput>
<p>
Distance (10016 - 10011):
#GetLatitudeLongitudeProximity(
Location10016.Latitude,
Location10016.Longitude,
Location10011.Latitude,
Location10011.Longitude
)#
miles
</p>
<p>
Distance (10016 - 10520):
#GetLatitudeLongitudeProximity(
Location10016.Latitude,
Location10016.Longitude,
Location10520.Latitude,
Location10520.Longitude
)#
miles
</p>
<p>
Distance (10016 - 02155):
#GetLatitudeLongitudeProximity(
Location10016.Latitude,
Location10016.Longitude,
Location02155.Latitude,
Location02155.Longitude
)#
miles
</p>
</cfoutput>
When we run this code, I get the following output:
Distance (10016 - 10011): 1 miles
Distance (10016 - 10520): 33 miles
Distance (10016 - 02155): 188 miles
That looks accurate enough for me. Next step will be to move this calculation into a SQL query.
Want to use code from this post? Check out the license.
Reader Comments
Hey Awesome Ben. My only concern is that I think the majority of the lat / long calculations that I would be doing would have to be done directly at the database level instead of pulling all items and then calculating in code. Here is a copy paste for the MySQL implementation of such a calculation directly in mysql. Note that I have not personally tested this.
Very cool post though. Thanks!
select asciiname,latitude,longitude, acos(SIN( PI()* 40.7383040 /180 )*SIN( PI()*latitude/180 )
)+(cos(PI()* 40.7383040 /180)*COS( PI()*latitude/180) *COS(PI()*longitude/180-PI()* -73.99319 /180)
)* 3963.191 AS distance
FROM allcountries
WHERE 1=1
AND 3963.191 * ACOS( (SIN(PI()* 40.7383040 /180)*SIN(PI() * latitude/180)) +
(COS(PI()* 40.7383040 /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* -73.99319 /180))
) < = 1.5
ORDER BY 3963.191 * ACOS(
(SIN(PI()* 40.7383040 /180)*SIN(PI()*latitude/180)) +
(COS(PI()* 40.7383040 /180)*cos(PI()*latitude/180)*COS(PI() * longitude/180-PI()* -73.99319 /180))
)
Hey Ben
excellent component.
I've been playing around with latitude and longitude for a few years on and off, but being in the UK have been restricted with 'free' data to work with (the Royal Mail charge a fortune for their database of UK postcodes per licence), and the UK geocoding using the Google Map API is still minimal.
I ended up using a Multimap API to do the search, (for which I need to tidy up and release my component to the community), and your function will certainly be a welcome addition to the code.
Yes, I am geekily in love with structures, xml and playing remote services. :)
@Brian,
I decided that I needed to start out just figuring the distance to get comfortable with the calculation. I know formatting on my comments is a hard thing to do since tabbing is not kept, but as you can see, the SQL statement has almost zero readability. That's why I wan't to really look at the formula in a highly formatted way in ColdFusion.
Once I have that done and tested, I decided (as per the last line in my blog post) that the next step would be to move it into the database SQL statements.
In my research, I did find someone that said he actually had much better performance pulling out approximate matches (based on degree differences only) and then using PHP to run the actual calculations. I think this makes a lot of sense considering the number of zip codes in the database (over 42,000). This might actually be my next step of experimentation.
@Matt,
Nothing wrong with an API - offload the burden of the search!
Nice one Ben. I've had to do something similar a while ago, but as Matt mentioned above, the 'free' databases for the UK are dodgy or incomplete. I ended up using the Royal Mail's database, which as mentioned costs a fortune but is very complete. It'd be great to have a service like this one you found on sourceforge for the UK.
@Marcos,
I didn't even know there were free versions of zip code database here. I have one we needed for a project. It was really cheap though, like $30. That's annoying that they are so expensive over in the UK!
I use a user defined function in SQL Server to calculate the distance.
CREATE FUNCTION [dbo].[getDistanceBetweenLatLon]
(
@lat1 decimal(5,2),
@long1 decimal(5,2),
@lat2 decimal(5,2),
@long2 decimal(5,2)
)
RETURNS NUMERIC( 10, 5 )
AS
BEGIN
DECLARE @x decimal(20,10)
DECLARE @pi decimal(21,20)
DECLARE @distance float
SET @pi = 3.14159265358979323846
SET @x = sin( @lat1 * @pi/180 ) * sin( @lat2 * @pi/180 ) + cos(@lat1 *@pi/180 ) * cos( @lat2 * @pi/180 ) * cos( abs( (@long2 * @pi/180) - (@long1 *@pi/180) ) )
SET @x = atan( ( sqrt( 1- power( @x, 2 ) ) ) / @x )
SET @distance = abs(( 1.852 * 60.0 * ((@x/@pi)*180) ) / 1.609344)
RETURN @distance
END
Ben,
There's also a UDF on cflib that does the distance calculation and returns the results in km (kilometers), sm (statute miles), nm (nautical miles), or radians:
http://www.cflib.org/udf/LatLonDist
I've been using this code for a while now and it works really well (it came out 4 or 5 years ago and runs awesome).
http://www.zipcodeworld.com/samples/distance.cfm.txt
@Jeff, Rob,
Thanks guys. Looks like everyone has a different solution to this. I wish I knew enough about the mathematics behind it to see how each formula differs.
if you're just trying to isolate points within a certain distance of a given point, a simple bounding box search will save you tons of work. take your search point add/subtract the distance you're looking for to build up 2 pairs of points that will make up your bounding box. use the bounding box in your WHERE clause & bob's your uncle. if you need precise distance per point *then* run those calculations.
@PaulH,
I think that is actually going to be my next plan of attack. I saw a thread where a guy talked about doing that to get a rough list, then once the data was out of the database, he performed the more precise calculations on the subset of zip codes.
"As I have no working knowledge of how to do this, I did what everyone else does - I Googled for an answer."
Everyone, that is, except the person who asked you "to write up a demo on finding all of the zip codes in the local proximity to a given zip code" in the first place. I appreciate it's not a particularly straight-forward problem and certainly not something a programmer will do very often in his/her career, but seriously... that's a big ask. Did they even have a go before asking you to do it for them?
@George,
Ha ha, good point :) I figured it was an interesting topic and one that I've never done before, so... what the heck.
...and in general this is a good google recommended reference for calculations w/geographic coords:
http://econym.googlepages.com/epoly.htm
@ben depending on the end use, 90% of the time a bounding box is "good enough". you're looking at a point representing a polygon anyway (if your use case is zipcodes) which makes all the distance calculations more or less a sham. to be done "right", for some kind of consumer application, you're probably looking at routing (unless the users are crows that is) or real GIS operations (like doing an intersection) if another end use. otherwise i'd just go w/google maps, esri or whatever.
btw if you've looked at the mysql "spatial" bits, a bounding box attack is used for most all their "spatial" functions (intersection, union, point-in-polygon searches, etc.). very sham-ful from and old school GIS point of view ;-)
You inspired me to write my php answer to this very problem over on my blog. I had to do something similar except I didn't have the lat/long for each zip code so I had to look it up with the Yahoo maps api. Not perfect (in terms of speed perhaps) but free and it worked well.
The Yahoo API for this task is:
http://api.local.yahoo.com/MapsService/V1/geocode' . '?appid=' . $yahooAPIAppID . '&output=php&zip='.$zip;
Where $yahooAPIAppID is a key registered with yahoo maps api
and the output value can undoubtedly be changed to fit various formats.
Thanks for writing this post Ben! Invaluable resource. I knew you would be the best person to ask about building a zip code proximity search for ColdFusion. I'm sure this post is only going to keep growing. Can't wait to see how you decide to take on the next step.. Thanks as well to the others contributing.
@Brian,
Always glad to help. Check out my latest post:
www.bennadel.com/blog/1490-Using-A-Rough-Box-Model-To-Gather-Near-By-Zip-Codes.htm
This demonstrates that using a bounding box model is practically just as accurate and much easier to use.
@PaulH,
These calcs are fine for flying city-to-city type long distance but does not help too much for driving distances. Any thoughts on driving distance between two geopoints?
as i said, "routing". googlemaps work well enough, even here in thailand.
if that's not good enough & you have better transport infrastructure data (which is probably the only reason it's not "good enough") have a look at postGIS/pgRouting.
if you're lighting cigars w/ben franklins, look at ESRI.
It is the online postcode to postcode distance calculator allows you to enter two postcodes and after clicking the 'Calculate Distance' button the Online Postcode to PostCode Travel Distance Calculator will calaculate.
http://www.postcode.org.uk
Hello Ben, community, I was using this code for about 6 years now and according to my client, it seems to be quite accurate. It basically does the following: If a client call asking for a service tech in his/her area, we ask for the zip code and enter it on a form.
Here is the code:
I hope it helps.
@Ben Nadel, do you know of any web service that gives me the latitude and longitude after entering the zip/posta code?
@Dani,
I have answer my own question with this great component from the Jedi:
http://googlegeocoder3.riaforge.org/
So when feeding these functions set through a larger scale system we came across a bit of a untraceable error that makes me think there might need to be better handling.
1.0000000000000002 must be within range: ( -1 : 1 )
got and wise ones?
A^2+B^2=C^2
You can find the length of A, and B easily using elementary math, just think about plot charts; once you have that you can do that math to find the length of C and remember 1 degree in long, and lat = 69miles if you are looking for a more precise measurement
Jody, yes that would work if the lang-long system was an evenly distributed Cartesian space, but as far as I understand it, that is not the case. So the farther north you go, for instance, 1km in actual distance (two points on a sphere) would actually show larger and larger values using your method.
So unless I am misunderstanding it, your method would work at the equator but would break down completely closer to the poles.
-Brian
@Brian -
Each degree of latitude is approximately 69 miles (111 kilometers) apart. The range varies (due to the earth's slightly ellipsoid shape) from 68.703 miles (110.567 km) at the equator to 69.407 (111.699 km) at the poles.
If you where doing a "within" distance search it would be 96% accurate.
Calculate the max long, max lat, and the min long, and the min lat and do a search BETWEEN minlong maxlong AND BETWEEN minlat and maxlat using the fields where appropriate - although I did this in PHP for distance calculation I found out it was pretty accurate except it was finding areas within a square and some areas of the square where a little off - not by much 1 - 5 miles but still pretty close.