Filter by zip code, or other location based data retrieval strategies

Question

My little site should be pooling list of items from a table using the active user's location as a filter. Think Craigslits, where you search for "dvd' but the results are not from all the DB, they are filtered by a location you select. My question has 2 levels:

1. should I go a-la-craigslit, and ask users to use a city level location? My problem with this is that you need to generate what seems to me a hard coded, hand made list of locations.
2. should I go a-la-zipCode. The idea of just asking the user to type his zipcode, and then pool all items that are in the same or in a certain distance from his zip code.

I seem to prefer the zip code way as it seems more elegant solution, but how on earth do one goes about creating a DB of all zip codes and implement the function that given zip code 12345, gets all zipcodes in 1 mile distance?

this should be fairly common "task" as many sites have a need similar to mine, so I am hoping not to re-invent the wheel here.

thanks

Answer 1

Getting a Zip Code database is no problem. You can try this free one: http://zips.sourceforge.net/

Although I don't know how current it is, or you can use one of many providers. We have an annual subscription to ZipCodeDownload.com, and for maybe $100 we get monthly updates with the latest Zip Code data complete with Lat/Longs of the centroid of the zip code.

As for querying for all zips within a certain radius, you are going to need a spatial library of some sort. If you just have a table of zips with lats/longs, you will need a database-oriented mechanism. SQL Server 2008 has the capability built in, and there are open source libraries and commercial libraries that will add such capabilities to SQL Server 2005. The open source database PostgreSQL has a project, PostGIS that adds this capability to that database. It is here: http://postgis.refractions.net/

Other database platforms probably have similar projects, but those are the ones I am aware of. With one of these DB based libraries you should be able to directly query for any zip codes (or any rows of any kind that have lat/long columns) within a given radius.

If you want to go a different route you can use spatial tools with a mapping library. There are open source options here as well, such as SharpMap and many others (Google can help out) that can use the free Tiger maps for the united states as the data source. However, this route is somewhat more complicated and possibly less performant if all you need is a radius search.

Finally, you may want to look into a web service. This, as you say, is a common need, and I imagine there are any number ob web services that you can subscribe to that can provide all zip codes in a given radius from a provided zip code. A quick Google search turned up this: http://www.zip-codes.com/free-zip-code-tools.asp#radius But there are MANY resources to be had for the searching on this subject.

Answer 2

how on earth do one [...] implement the function that given zip code 12345, gets all zipcodes in 1 mile distance?

Here is a sample on how to do that:

http://www.codeproject.com/KB/cs/zipcodeutil.aspx

Answer 3

Just to be technical... PostGIS isn't a project of the Postgres community... it's a stand-alone project that is built on top of Postgres. If you want help or support with PostGIS, you'll want to go to it's community instead of Postgres.

Answer 4

You can use PostGIS. Additionally, I've used deCarta's mapping libraries. They have technology which allows you to geokey any arbitrary data type. Then you can query these spatially.

disclaimer: I work for deCarta

Answer 5

Wouldn't it be more efficient to just figure out which cities are within a 1 mile radius and store that information in a table? Then you don't have to do calculations in the database all the time.