Integrating mile radius into location search

B

bluesdoggy

Limp Gawd
Joined
Jul 14, 2000
Messages
442
I'm in the process of building a database application for use by our sales department. One of the features that has been requested is the abilty to search for all customers within a certain mile radius of a zipcode/city. This is a fairly common feature on most web searches i've seen, so it can't be terribly complex..however i'm not sure how to get the data to make it happen.

I have found the following:

http://www.census.gov/geo/ZCTA/zcta.html

which seems to offer rough approximations in terms of Lat and Lon for zip code areas... however i'm not sure if there's something better i could use in order to also be able to include city searching. Does anyone have any advice / tips on getting this done at a minimal cost? i'd rather have to do some work building interface / populating db from raw info than a "in box" solution that costs any appreciable amount.
 
Why doesn't the ZIP Code table let you include city searching?

I'd get a ZIP Code database that included latitutde and longitude data and get it into a database. Then, whenever someoen wanted to know what customers were within n miles of a ZIP Code, I'd join between the ZIP Code table and the customers table with an expression using the great circle distance to find the distance between the customers' locations and the locations in the ZIP Code or city.

Great circle distance isn't hard to compute, and if you're using a half-decent database system, you'll find that the query isn't too expensive.
 
that particular thing i pointed to just doesnt include cities in its information.

If anyone could point me to a zip code table taht included cities, i'd be greatful
 
There are lots of different companies that sell ZIP Code data. You can get it directly from the post office, if you'd like.

The first database I bought was from Melissa Data in 1991. They're still in business, have lots of other interesting products, and are very reputable.

ZIPCodeDownload.com also sells 'em, and has some very inexpensive products. I've used one of their databases, but only for a research project; I was the only user, and I didn't actually mail anything against the addresses invovled.
 
Or you can do some math :)

Since the census data gives you lat and long of each zipcode you can approximate the distance between those two zip codes.

R = 3956 miles
dlon = lon2 - lon1
dlat = lat2 - lat1

a = (sin(dlat/2))^2 + cos(lat1) * cos(lat2) * (sin(dlon/2))^2
c = 2 * atan2(sqrt(a), sqrt(1-a))
d = R * c


Also here are some other links that may help you.
http://www.census.gov/geo/www/gazetteer/places2k.html
http://www.census.gov/geo/www/tiger
 
You must log in or register to reply here.
Back
Top