Joshua Olson joshua at waetech.com
Thu Dec 19 13:55:03 CST 2002

```----- Original Message -----
From: "Rob Smith" <rob.smith at THERMON.com>
Sent: Thursday, December 19, 2002 2:34 PM

> Howdy,
>
> This is something I've always been curious about. How do Zip/Postal Code
> finders work where you enter your zip and it brings you the closest
> something sorted by distance? Do you have to have a listing of all known
zip
> codes, distances to and fro or is there a website/formula that will
> calculate this for you?

Hi Rob,

I find myself doing this quite a bit.  Most zip code databases include the
latitude and longitude of the Post Office.  If you know the latitude and
longitude of the two points, then finding the distance can be done a couple
of ways.  The hardest (mathematically) is to find the Great Circle distance.
While it is a more complex equation that the other method (I'll cover that
next) it provides the best results.  The technique I use is MUCH simpler,
though.

In this instance, I'll assume I want to find all businesses within 10 miles
of a certain zip code.  And, assume that each business already has a zip
code field, a attitude field, and a longitude field.  All of which are
prefilled.

Here is some code I use, in CF with SQL Server 2k, to do the lookup:

<cfquery name="get_zip">
SELECT latitude, longitude
FROM zipcode
WHERE zip = '12345'
</cfquery>

<cfset miles_per_lat = 69.09>
<cfset miles_per_lon = Abs(69.09 * Cos(Val(get_zip.latitude) * Pi() / 180))>

<cfset deg_latitude = radius / miles_per_lat>
<cfset deg_longitude = radius / miles_per_lon>

SELECT *,
ROUND(SQRT(SQUARE(#miles_per_lat# * (latitude - #Val(get_zip.latitude)#)) +
square(#miles_per_lon# * (longitude - #Val(get_zip.longitude)#))), 1) AS
distance
WHERE
latitude BETWEEN #Val(Val(get_zip.latitude) - deg_latitude)# AND
#Val(Val(get_zip.latitude) + deg_latitude)#
AND longitude BETWEEN #Val(Val(get_zip.longitude) - deg_longitude)# AND
#Val(Val(get_zip.longitude) + deg_longitude)#
</cfquery>

~~~~~~~~~~~~~~~~~~~~~~~~

For small radiuses this should work great.  Far larger distances, the system
breaks down.  Here's a basic idea of what it's doing.

1.  It does a lookup of the zip codes lat and lon from the database.
2.  It records how many miles there are per degree latitude (69.09 globally)
3.  It determines how many miles there are per degree longitude.  This
depends on the latitude.  The number of miles shrinks as we approach the
north and south pole.
4.  It uses 2 and 3 to determine how many degrees to look north-south and
east-west.  This designates a rectangle on the ground to search.
5.  Use a simple lookup on the businesses table to find businesses within
the rectangle.  This may return businesses that are outside of the