Well, first of all, the 8000 mile maximum was a mistake on the form, and has
been corrected to 2000.
At the present time, the zip code radius search is for members that have a
US zip code only, because we ONLY have a database of US zip codes, and their
corresponding Latitudes and Longitudes.
For now, we execute a search on ALL OTHER match criteria first, and then if
the user has selected to limit by zip code radius, we feed the results from
the first query into the second, loop over the zip codes to determine their
distance, and if they are within the radius, they are part of the output.
We plan on optimizing this process over the next 6 months, (perhaps a
re-write, considering some of the ideas we have!) but for now this query set
works fast and reliably. We'll also be implementing an "adjacent state"
table in the future, to narrow the criteria further as well. This entire
process will become one big optimized stored proc once we have it
fine-tuned.
I know that's not much help, but that's how we've made it work so far, and
the response times are very good.
Marc
-----Original Message-----
From: John Quarto-vonTivadar [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 10:31 PM
To: Fusebox
Subject: Mileage Search (spelling corrected)
> 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
