To read the article online, visit http://www.4GuysFromRolla.com/webtech/010500-2.shtml

A Followup to Calculating the Distance Between Cities
By Mike Swanson

Thanks for the article on calculating distances with zip codes. I have used a custom routine for this for a few years now, and I have a couple of additional recommendations that I'm sure your readers would appreciate.

First, realize that no zip code has a true latitude and longitude since a zip code represents an area, and the two latitude and longitude coordinates represent a point. When you select a zip code database, check to see if they have weighted their latitude and longitude calculations based on the population of that zip code. Using weighted data, your coordinates should theoretically land smack in the middle of the most populated area and as a result, be a little more accurate for more people. If most of your locations are relatively far apart, then this additional accuracy won't concern you.

Second, if you have a large database of locations to search, performing a distance calculation for all of them is very expensive. For example, I ran a public web site that had to look through over 12,000 locations routinely. To calculate a distance for each of them would be unreasonably slow (Mike Shaffer was correct when he implied that this could take some time). As with most things, there is almost always a better method.

When a visitor entered their zip code, my web site would first perform four "reverse" computations. If the visitor wanted to see all locations within 60 miles of their home zip code, the following general steps would happen:

1. The site would look up the latitude and longitude of the visitor's home zip code.

2. Four "reverse" computations would be made. Essentially the algorithm would figure out the latitude 60 miles North of the visitor's home zip code, then figure out the latitude 60 miles South. After doing that, it would perform the same steps for finding longitude 60 miles East and West of the location.

3. These four values were then used to query a "patch" of information from the 12,000+ location database. Although this isn't the exact query I used, it is close enough to understand:

 ``` SELECT * FROM Locations WHERE Latitude <= [NorthLatitude] AND Latitude >= [SouthLatitude] AND Longitude >= [WestLongitude] AND Longitude <= [EastLongitude] ```

This query is very easy to process and returns results very efficiently. Using this method quickly eliminates the vast majority of locations we need to consider.

4. Now, using Mike Shaffer's handy routine, you can step through each record and "trim" all of the locations that are near the four corners of our SELECTed square. This leaves only locations that truly fall within the requested distance of the visitor's home zip code.

The benefits of using this method are speed and efficiency. Results are returned and calculated quickly with little overhead.

I hope this addition to Mike Shaffer's material is helpful. Perhaps if Mike is so inclined, he will provide us with the functions to perform the "reverse" calculations used in my method. Unfortunately, I can no longer locate the code I generated many years back.

Happy Programming!

Michael Swanson, MCSE, MCP+Internet
Webmaster
Donnelly Corporation
mike.swanson@donnelly.com

•  Article Information Article Title: A Followup to Calculating the Distance Between Cities Article Author: Mike Swanson Published Date: Wednesday, January 05, 2000 Article URL: http://www.4GuysFromRolla.com/webtech/010500-2.shtml