> I did this once. It's pretty easy. The trick was to find a
> table that related zip codes to coordinates (longitude &
> latitude). Then I found a formula on the net that calculates
> distances between two points on the globe. My query then
> found the distance between the base zip code an all other
> zip codes, and filtered out the ones that were too far.
>
fortunately this wasn't my question :)
my question was, suppose you make the mileage between you and the potential
match (or Nearest Store or closest McDonald's etc.) really large, like Marc
suggested 8000 miles. Now if you've got a full ZIP/Postal DB the resultant
codes list is going to be quite large, in the tens of thousands. This is
then the filter for finding matches along the lines of "select MatchID FROM
Matches WHERE ZipPostalCode IN (#big-ass-list-here#). The question is is
there an easier way to apply the filter, through some sort of join? My zip
database also includes Canada so it's even worse since a good portion of the
results are alphanumeric, so even the advantage of an integer filter a la US
zip codes is lost.
On my own timing tests, the bottleneck is not in retrieving the list of
possible ZIPs with X miles, it is in using that result set as a filter on
the second query. I'd love to somehow let the RDBMS handle the join instead
of bring the first result set back thru CF and then re-applying it for the
second result set.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
