Mapping Hacks

416

|

Chapter 7, Names and Places

#82 Find Nearby Things Using U.S. ZIP Codes

HACK

Who Is Nearby?

The president of a former employer of mine gave presentations for our cus-

tomers a few times a year. Each time, I was asked to produce a list of our

customers whom we should invite to the event, based on their proximity to

the speaking location. I did this in the old days using FoxPro for DOS, but

we can bring this method up to date with MySQL.

First download the MySQL dump version of the Civic Space ZIP Code data-

base (http://civicspacelabs.org/zipcodedb) and uncompress it. Then, create a

MySQL database (or you can simply add the ZIP Codes table to an existing

database):

mysqladmin create zipcodes

Load it up:

mysql zipcodes < zipcodes-mysql-10-Aug-2004/zipcodes.mysql

You can now start MySQL:

mysql zipcodes

And start looking at ZIP Codes:

select * from zipcodes where zip="95472"

+-------+------------+-------+-----------+-------------+----------+-----+

| zip | city | state | latitude | longitude | time zone | dst |

+-------+------------+-------+-----------+-------------+----------+-----+

| 95472 | Sebastopol | CA | 38.393314 | -122.836660 | -8 | 1|

+-------+------------+-------+-----------+-------------+----------+-----+

1 row in set (0.00 sec)

The simplest way to select the nearby items is to define a bounding box:a

rectangular area specified by the corners of a rectangle that you know is

larger than your area ...