# Finding The Distance Between Latitude / Longitude Locations In ColdFusion

Posted February 9, 2009 at 10:08 AM by Ben Nadel

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.
• --->
• 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
• 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
• access="public"
• returntype="numeric"
• output="false"
• hint="I convert radians to degrees.">
•
• <!--- Define arguments. --->
• <cfargument
• 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.

### Looking For a New Job?

25% of job board revenue is donated to Kiva. Loans that change lives - Find out more »

Feb 9, 2009 at 10:23 AM // reply »

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))
)

Feb 9, 2009 at 10:28 AM // reply »

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. :)

Feb 9, 2009 at 10:31 AM // reply »

@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.

Feb 9, 2009 at 10:37 AM // reply »

@Matt,

Nothing wrong with an API - offload the burden of the search!

Feb 9, 2009 at 10:45 AM // reply »

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.

Feb 9, 2009 at 10:50 AM // reply »

@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!

Feb 9, 2009 at 11:15 AM // reply »

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

Feb 9, 2009 at 11:36 AM // reply »

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

Feb 9, 2009 at 11:45 AM // reply »

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

Feb 9, 2009 at 11:48 AM // reply »

@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.

Feb 9, 2009 at 12:03 PM // reply »

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.

Feb 9, 2009 at 12:05 PM // reply »

@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.

Feb 9, 2009 at 12:14 PM // reply »

"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?

Feb 9, 2009 at 12:17 PM // reply »

@George,

Ha ha, good point :) I figured it was an interesting topic and one that I've never done before, so... what the heck.

Feb 9, 2009 at 12:22 PM // reply »

...and in general this is a good google recommended reference for calculations w/geographic coords:

Feb 9, 2009 at 12:42 PM // reply »

@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 ;-)

Feb 9, 2009 at 3:05 PM // reply »

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.

Feb 10, 2009 at 9:42 AM // reply »

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.

Feb 10, 2009 at 10:08 AM // reply »

@Brian,

Always glad to help. Check out my latest post:

This demonstrates that using a bounding box model is practically just as accurate and much easier to use.

Mar 15, 2010 at 12:52 PM // reply »

@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?

Mar 15, 2010 at 1:26 PM // reply »

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.

Mar 25, 2011 at 7:09 AM // reply »

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

May 5, 2011 at 11:34 AM // reply »

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:
1) 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:

• <CFPARAM name="errormessage" type="string" default="">
• <CFPARAM name="passedzipcode" type="string" default="78626">
• <cfquery name="passedzip" datasource="#ds#">
• SELECT * FROM zip_codes
• WHERE zipcode = '#form.passedzipcode#'
• </cfquery>
•
•
• <cfquery datasource="servicio" name="getlocs">
• SELECT ID, company_name, zip_code, latitude, longitude, state_in, city,
• 3963 * (ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
• (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
• COS(longitude/57.2958 - #passedzip.longitude#/57.2958)))) AS distance
• FROM service_us
• WHERE 3963 * (ACOS((SIN(#passedzip.latitude#/57.2958) * SIN(latitude/57.2958)) +
• (COS(#passedzip.latitude#/57.2958) * COS(latitude/57.2958) *
• COS(longitude/57.2958 - #passedzip.longitude#/57.2958)))) <= 100
•
• ORDER BY distance
•
• </cfquery>

I hope it helps.

May 5, 2011 at 11:41 AM // reply »

@Ben Nadel, do you know of any web service that gives me the latitude and longitude after entering the zip/posta code?

May 5, 2011 at 12:06 PM // reply »

@Dani,

I have answer my own question with this great component from the Jedi:

Jan 27, 2012 at 9:53 PM // reply »

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?

Jan 28, 2012 at 4:00 AM // reply »

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

Jan 28, 2012 at 12:29 PM // reply »

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

Jan 28, 2012 at 4:27 PM // reply »

@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.

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

 Author Name: Author Email: Author Website: Comment: Supported HTML tags for formatting: bold   italic   code Remember my information Subscribe to comments Send me a copy of this comment
InVision App - Prototyping Made Beautiful With Prototyping Tools Recent Blog Comments
Mar 11, 2014 at 6:20 PM
Reading In File Data One Line At A Time Using ColdFusion's CFLoop Tag Or Java's LineNumberReader
Thanks Ben for you very interesting articles on handling large files. As I am not a programmer, I am quite insecure whether my issue is directly related to your description or not. My problem is, tha ... read »
Mar 11, 2014 at 12:21 PM
Compound Transclusion Prevented In AngularJS 1.2
Yeah this hit our team too and locked us as 1.1.5. It seemed like an irresponsible unilateral decision, but our graver concern is the future decisions the Angular team will make with other fundamenta ... read »
Mar 11, 2014 at 2:00 AM
ColdFusion, jQuery, And "AJAX" File Upload Demo
Mar 10, 2014 at 8:24 PM
Nested Views, Routing, And Deep Linking With AngularJS
@Steven, the action property is not something from AngularJS but rather part of the concept that Ben is describing in his blogpost here. it is a custom added property, which is read by the requestCo ... read »
Mar 10, 2014 at 2:03 PM
Nested Views, Routing, And Deep Linking With AngularJS
Where is the angular documentation for 'action:' it is not here: http://docs.angularjs.org/api/ngRoute/provider/ \$routeProvider Thanks. ... read »
Mar 10, 2014 at 12:06 PM
Using Track-By With ngRepeat In AngularJS 1.2
I was hoping that this will work with pagination using ng-repeat. My use case scenario is that I have an images object. images[0] = [im1,im2,im3] // First Page images[1] = [im4, im5, im6] // Second ... read »
Mar 9, 2014 at 6:11 PM
For Better Security Use HtmlEditFormat() In Conjunction With JSStringFormat() In ColdFusion
It looks like htmleditformat() will be deprecated in CF 11 https://wikidocs.adobe.com/wiki/display/coldfusionen/New +in+ColdFusion ... read »
Mar 9, 2014 at 10:55 AM
\$.stop() vs. \$.finish() In jQuery Animations
Nice feature! Thanks for sharing. :) Good for when you are making a 100% AJAX controlled site. ... read »