How to model coarse grained geolocation to correct market

Question

Use case example: Client A comes to request sales information, enters their zip code and are directed to Representative X.

Since there is an effectively infinite number of zip codes there will not be an agent assigned to every single zip code which would then move out to the county level, and then so on into a region of counties and finally end at the state level.

What type of relationships would be best at modeling this scenario?

Is there any way to remove the need to specifically define regions that a zip code could be approximated to a nearest county assigned a rep, if so would it only be possible with a full geolocation look up to do distance comparisons to find the nearest representative or perhaps could the zip code itself be used to closely approximate the distance?

Clarification: The real intent of this question is how to solve the region approximation without needing to maintain a perfect association table of counties to regions or is the solution to this problem so complex that the cost of maintaining the relationships manually is less prohibitive?

Answer 1

You can get cheap or free (Census.gov) Zip code information containing at least the State in which the Zip code lies. You might even be able to get Zip to County information if you require more granularity. At this point you could simply grab the representative that matches the state or county in which the zip code lies.

Using the TIGER data available from the Census Bureau, you could populate a spatial database with the zip code boundaries and your representative's known areas. A simple query could then retrieve the representative which is closest to the given zip code. I've had a favorable experience with PostGIS and I am aware of both Oracle and SqlServer spatial extensions.

Answer 2

Well you can easily get the Lat/Long from the Google or Yahoo API (in my last project we had an issue with Google not finding a certain zipcode in NJ that Yahoo found, fyi).

So if you cache the Lat/Long for each zipcode with a representative (and I would probably recommend caching the Lat/Long for each zipcode entered by a customer too, so you don't have to hit a web service each time), then you can just get the Lat/Long of the user's zipcode and run through a distance calculation for each rep. This does have an order of n-time complexity (O(n) for n representatives)