Improving the "Calculating the Distance Between Cities" AlgorithmBy Mike Shaffer
This article serves as an addendum to my previous article entitled "Calculating the Distance Between Cities". That article talked about finding the distance between cities as being a useful way to determine, for example, which of a company's many locations might be closest to a particular customer. In that example, your store database may consist of several dozen to several hundred records (one for each store), and for each record you would have to perform the distance calculation. This is not so bad. But what if you had over 1,000 locations? Or what if you wanted to find all cities located within a particular radius from a customer, regardless of whether or not you have a store there?
In this case, you would certainly NOT want to loop through all cities within your database making the distance calculation. That would take a VERY long time. You might, in a flash of inspiration, attempt to perform the distance calculation only for cities within that customer's state... but that would not work very well for people who live near a state's border, or for people who live, say, in Rhode Island.
Michael Swanson's article, entitled "A Followup to Calculating the Distance Between Cities" correctly implied that there was a way to reduce the number of cities for which you would have to make the distance calculation. This article details that method.
By first performing a rough trim of the cities list based on latitude and longitude, we limit the number of calculations we must make. By using the algorithm below, we can accomplish that, as well as ensure that we are grabbing all cities within a particular area, regardless of state boundaries, etc.
Given a customer's latitude (
iStartLat) and longitude (
iStartLong), and the
radius we wish to search (
iRadius, in miles), the following algorithm will
return the latitude and longitude range for cites we will evaluate (using
the formula provided in the last article). NOTE: Be aware that -- as in the
last article -- this algorithm is accurate enough for most e-commerce
applications, but NOT for navigational applications:
Now you can create a SQL statement which limits the recordset of cities in this manner:
(Note: This algorithm works for US and Canadian cities. Adapting the algorithm to other regions may require changing the comparison order due to negative latitudes, etc.)
Performing the distance calculation (covered in the previous article) on the resulting recordset ensure that you are not wasting too many CPU cycles on cities that are obviously outside the radius you have specified.
A SIDE NOTE
Many of you have written asking for other sources of zip code/latitude/longitude data. I think what many of you were REALLY asking was if I knew of any FREE source of data. Kelly Sumrall recently sent me an email with this link:
Although the data may be a bit outdated, it is FREE! Thanks for the tip, Kelly!