by James Turner

I'll Buy It! Tell Me Where To Go.

Anyone who runs a business with more that one point of sales or branch knows that it is critical to direct consumers to the closest venue. Most large corporate Web sites have a "dealer locator" feature that lets a user enter a zip code and get a list of the closest locations to them. If you're interested in adding a similar feature to your site, you're in luck, because it turns out to be both cheap and easy to do.
February 29, 2000

The first step is to obtain a US zip code database that includes latitude and longitude data. As a high-school geography refresher, latitude is the amount north or south of the equator, while longitude is the amount east or west of Greenwich, England. Many vendors offer this data for varying prices; the data used in this example came from TPS Products and Services, Inc of Newcastle, CA www.zipcodedatabases.com.

TPS offers the full US zip code database with a wealth of data (including population, elevation and demographic data) for $150 a year, including 12 monthly updates. Getting the updates is important because the USPS is adding and reassigning zip codes.

Each vendor supplies the data in a slightly different format. The example presented here uses TPS's comma-seperated format, which looks like:

"D","00501","V13916","U","HOLTSVILLE","","P","Y","V13916","HOLTSVILLE",
"N","N","","353910","NY","103","SUFFOLK",40.8153,73.0456,"516","5","25",
3.11,1321864,911,424623,1192236,82473,84238,49128,165200

Most of the data here is not useful for our purposes, the only 5 fields we will be using are "00501" (the zip code,) "HOLTSVILLE" (specifically the second mention, which is the city as opposed to the first one which is sometimes the name of a company or organization that owns the entire zip code,) "NY" (the state,) 40.8153 (the latitude in degrees) and 73.0456 (the longitude, also in degrees.)

Note that because all US zip codes are east of Greenwich, TPS has cheated a bit and dropped the minus sign that would normally precede the longitude. We will correct this during importation.

In this example, we will be using the MySQL database server with the DBI perl interface, but the techniques outlined should work as well with Visual Basic and ASP and SQL Server 7, or any other combination of a SQL database and scripting language.

We will assume that either we or an administrator has already set up a database in MySQL for us, and we're ready to start populating it. We begin by creating a table for the zip code data.

% mysql -u us7716b -p db7716b
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 316640 to server version: 3.22.19

Type 'help' for help.

mysql> create table zipcodes (
-> zipcode char(5) not null,
-> city varchar(128) not null,
-> state char(2) not null,
-> latitude double not null,
-> longitude double not null,
-> unique (zipcode));
Query OK, 0 rows affected (0.12 sec)

mysql> quit
Bye

This table is fairly straightforward; the only interesting point is that we are going to enforce uniqueness on the zip code field. Now we're ready to import our data. (Note: for presentation here on the HTML page, there is one artificial line break in this code. The line that begins \" is a continuation of the line above.)

parse-zipcodes.pl
#!/usr/local/bin/perl

use DBI;
$PI = 3.1415926;
$dbh = DBI->connect("DBI:mysql:db7716b", "us7716b", "demo") ||
    die "Can't connect: $_";
$dbh->do("delete from zipcodes") ||
    die "Can't clear zipcode database: $_";
$lastzip = "";
while (<>) {
    chomp;
    tr/\r//d;
    if (($zipcode, $city, $state, $latitude, $longitude)
	= /^\"D\",\"([0-9]+)\"(?:,\"[^\"]*\"){7},\"([^\"]+)\"(?:,\"[^\"]*\"){4},
\"([^\"]+)\"(?:,\"[^\"]*\"){2},([0-9\.\-]+),([0-9\.\-]+),/) {
	$city = makequote($city);
	$latitude_radians = ($PI / 180.0) * $latitude;
        $longitude_radians = ($PI / -180.0) * $longitude;
        ($dbh->do("insert into zipcodes (zipcode, city, state, latitude,longitude) " .
                 "values ('$zipcode','$city','$state'," .
		 "        $latitude_radians, $longitude_radians)")
         || die "Can't insert $zipcode in database: $_") unless ($zipcode eq $lastzip);
        $lastzip = $zipcode;
    } else {
# If you uncomment this, you will get all sorts of bad lines for APO and
# FPO sites, as well as some sites without latitude and longitude data
#	print "BAD LINE\n$_\n;";
    }
}
sub makequote {
    my ($str) = @_;
    $str =~ s/\'/\'\'/g;
    return($str);
}
% ./parse_zipcodes.pl < ZIPUSA.ASC

Let's take a walk through this bad boy. It begins by defining loading the perl DBI database interface and making a connection to the database. Next, it clears the whole zip code table out so we can start fresh. After reading a line from the standard input, it strips off any pesky ^M characters that might have been introduced in file transfer, and then feeds it to the monster regexp that's the heart of the program. In summary, it skips the first field, reads the zipcode, skips 7 fields, reads the city, skips 4 fields, reads the state, skips 2 fields, and reads the latitude and longitude. Because some zip codes have multiple entries, we skip if this zip code is the same as the last (remembering that the database will only allow one entry per zip code.)

Assuming the data was read correctly (and there are some entries that are not complete and won't, such as APO and FPO zip codes,) the program next checks for apostrophes in the city name and doubles them so SQL won't choke on them. The formulas that are used to compute distances use radians instead of degrees, so the program makes the conversion before writing to the database, as well as clearing up the sign issue with the longitude.

Once run, you'll find yourself with a database full of zipcodes.

mysql> select count(*) from zipcodes;
+----------+
| count(*) |
+----------+
| 42154 |
+----------+
1 row in set (0.02 sec)

Now you're ready to import your dealer data.

mysql> create table dealers (
-> dealer_name varchar(128) not null,
-> dealer_zipcode char(5) not null references zipcodes(zipcode),
-> dealer_phone char(20));
Query OK, 0 rows affected (0.11 sec)

You should note that because we are placing an explicit foreign key reference between the dealer_zipcode field and the zipcode field in zipcodes, you will not be able to input a dealer unless the zip code for the dealer is legitimate. You will also need to delete the contents of the dealers table before you will be allowed to delete from zipcodes. Let's populate the table with a few dealers for example purposes.

mysql> insert into dealers (dealer_name, dealer_zipcode, dealer_phone)
-> values ('Newton House of Newts', '02159', '617-555-1212');
Query OK, 1 row affected (0.02 sec)

mysql> insert into dealers (dealer_name, dealer_zipcode, dealer_phone)
-> values ('Derry Lizard Mart', '03038', '603-555-1212');
Query OK, 1 row affected (0.00 sec)

mysql> insert into dealers (dealer_name, dealer_zipcode, dealer_phone)
-> values ('Beverly Hills Reptiles', '90210', '415-555-1212');
Query OK, 1 row affected (0.00 sec)

mysql>

With all the data in place, all that's left is to write the actual CGI program to look things up.

#!/usr/local/bin/perl

use DBI;

require './cgi-lib.pl';
&ReadParse;

print "Content-type: text/plain\n\n";

$zipcode = $in{"zipcode"};

$dbh = DBI->connect("DBI:mysql:db7716b", "us7716b", "demo") ||
die "Can't connect: $_";

$PI = 3.1415926;

$sth = $dbh->prepare("select city, state, latitude, longitude " .
"from zipcodes where zipcode = '$zipcode'")
or die "Can't prepare zipcode: $dbh->errstr\n";

$rv = $sth->execute
or die "can't execute the query: $sth->errstr\n";

($city, $state, $latitude, $longitude) = $sth->fetchrow_array;

print "Looking for close matches to $city, $state\n";

$sth = $dbh->prepare("select dealer_name, dealer_phone, city, state, " .
" latitude, longitude from dealers, zipcodes ".
"where dealers.dealer_zipcode = zipcodes.zipcode");

$rv = $sth->execute
or die "can't execute the query: $sth->errstr\n";

undef %dealer_distances;

while (($name, $d_phone, $d_city, $d_state, $d_lat, $d_long) =
$sth->fetchrow_array) {

$prodsin=sin($latitude)*sin($d_lat);
$prodcos=cos($latitude)*cos($d_lat);
$deltalong=cos(abs($d_long-$longitude));
$dist=&acos($prodsin+($prodcos*$deltalong));

$dist=$dist*(180.0/$PI)*69.0;

$dealer_distances{"$name ($d_city, $d_state) [$d_phone]"} = $dist;

}

@ordered = sort {$dealer_distances{$b} >
$dealer_distances{$a}} keys(%dealer_distances);

foreach $item (@ordered) {
print "$item is " . sprintf("%d", $dealer_distances{$item}) .
" miles away\n";
}

sub acos {
($val) = @_;

return(atan2(sqrt(1-($val * $val)),$val));
}

sub asin {
($val) = @_;

return(atan2($val, sqrt(1-($val * $val))));
}

Once again, we begin by loading the DBI package. We also load the standard cgi-lib package to parse the arguments handed to us from the user. Once we have a zip code to check, we look it up to find latitude and longitude (and the name of the place as well, while we're at it.) Next we get a list of all the dealers and their position by joining the dealers and zipcodes tables. Looping through the dealers, we apply the magic equation:

prodsin = sin lat1 x sin lat2
prodcos = cos lat1 x cos lat2
(long = cos | $lat2 - $lat1 |
dist = acos (prodsin + ( prodcos x (long))
distmiles = dist x (180.0 / () x 69.0

Which (given latitudes and longitudes in radians) gives us distance in miles. The important thing to remember is that you don't need to understand the math to use the formula. Because perl (or at least the version that the example was run using) doesn't have acos, it is defined later in the file.

After determining all the distances and storing them in a hash, the program sorts the hash keys by distance and then displays the dealers in nearest to farthest order.

http://www.yourhost.com/cgi-bin/find-closest-dealer.pl?zipcode=23432

Looking for close matches to SUFFOLK, VA
Newton House of Newts (NEWTON, MA) [617-555-1212] is 472 miles away
Derry Lizard Mart (DERRY, NH) [603-555-1212] is 500 miles away
Beverly Hills Reptiles (BEVERLY HILLS, CA) [415-555-1212] is 2339 miles away

A few final notes. Similar postal code databases are available for other countries (notably the UK and Canada,) so it is possible to extend the functionality of the locator to handle multiple geographic areas. You should also remember to update your zip code database regularly to avoid leaving customers with new codes out in the cold.

More articles like this

James Turner is currently employed as a Senior Software Engineer at Viridien Technologies in Boxborough, MA, working on e-Commerce projects. He is also an adjunct professor of Internet Studies at the Massachusetts Communications College and a regular columnist on the Internet for the Christian Science Monitor.