Finding The Distance Between Latitude / Longitude Locations In ColdFusion

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

Tags: ColdFusion, Ask Ben

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:

http://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:
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:

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.

Post A Comment

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.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment