FYI, Ben Forta described how he did exactly this (anecdotaly) the
ColdFusion conference last fall. He was giving an example of how he
actually used his high school trigonometry in the real world!
No, really he was giving an example of code that started out in CF (where
the logic was good, but it ran like a dog), an he ended up in stored
procedures, using temporary tables, and ran quite nicely.
Sorry, he did not give actual code :-( He was just talking anecdotally.
A good example of using the power of the database to the fullest, where CF
just does not cut it.
At 10:30 PM 5/2/01, John Quarto-vonTivadar wrote:
> > 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
