John, I think that is the answer to your question.
There is a way to apply the filter through a join.
SELECT TOP #n# M.MatchID, Distance(P1.lat, P1.lon, P2.lat, P2.lon)
FROM Matches M INNER JOIN PostalCodes P1 ON P1.id = M.postalcode
INNER JOIN PostalCodes P2 ON P2.id = #myPostalCod#
WHERE Distance(P1.lat, P1.lon, P2.lat, P2.lon) < #searchDistance#
AND M.OtherCriteria = #myOtherCriteria#
ORDER BY Distance(P1.lat, P1.lon, P2.lat, P2.lon)
And if that's not what you're looking for, you can do it the
way you're doing it now except use a nested query.
SELECT
MatchID FROM
Matches WHERE ZipPostalCode IN (Select ZipPostalCode FROM ZipPostalCodes
WHERE ...)
Patrick
> -----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
