or a subselect. Then you'd just have a single query coming back from the
database, rather than the main query, followed by an additional query for
every row in the main query.
Cheers,
barneyb
> -----Original Message-----
> From: Butch Zaccheo [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, June 16, 2004 5:01 PM
> To: CF-Talk
> Subject: Queries Queries Queies
>
> Ok... this is my end goal..... to bring back a list of
> zipcodes within a
> given radius and compare that list against a list if dealers
> addresses.
>
> Right now I query the zipcodes table and bring back a list of zipcodes
> within a given radius. I create an array and then loop
> through all zipcodes
> in the array checking each against the list of dealers...
> this is where the
> problem comes in.
>
> My US zipcode table had about 70,000 records in it. Processing time is
> fairly quick using the method above. However, when I added
> the Canadian
> postal codes to the US postal codes my total records
> increased to 800,000.
> With this amount of data I get far to many return zip/postal
> codes to use a
> loop function.
>
> Can anyone help me with a single query that will perform this function
> rather than using two steps (basically a query of query)?
>
> My current code:
>
> (query to return zipcodes within a given radius)
>
> <cfquery name="getCities" datasource="some_datasource">
> Select DISTINCT zip as returnZip, city, state,
> SQR(((#miles_per_lat#
> * (latitude - #Val(getZip.latitude)#))*(#miles_per_lat# * (latitude -
> #Val(getZip.latitude)#))) + ((#miles_per_lon# * (longitude -
> #Val(getZip.longitude)#))*(#miles_per_lon# * (longitude -
> #Val(getZip.longitude)#)))) AS Distance
> FROM zipcode_data
> WHERE (latitude BETWEEN #Val(Val(getZip.latitude) -
> deg_latitude)#
> AND #Val(Val(getZip.latitude) + deg_latitude)#)
> AND (longitude BETWEEN
> #Val(Val(getZip.longitude)-deg_longitude)#
> AND #Val(Val(getZip.longitude)+deg_longitude)#)
> AND SQR(((#miles_per_lat# * (latitude -
> #Val(getZip.latitude)#))*(#miles_per_lat# * (latitude -
> #Val(getZip.latitude)#))) +
> ((#miles_per_lon# * (longitude -
> #Val(getZip.longitude)#))*(#miles_per_lon# * (longitude -
> #Val(getZip.longitude)#)))) <= #Replace(radius,",","","all")#
> </cfquery>
>
>
> (loop through all return zipcodes to find dealers with this zipcode)
>
> <cfloop Query="GetCities">
> <cfquery name="getDealerInfo"
> datasource="some_datasource">
> SELECT * FROM dealers
> WHERE Zip='#getCities.returnZip#' AND
> AudLoc='str' OR
> left(dealers.Zip, LeftVar)='#getCities.retrunZip#' AND AudLoc
> = 'bsw' ORDER
> BY company
> </cfquery>
> <cfif getDealerInfo.RecordCount GT 0>
> <cfloop Query="GetDealerInfo">
> <cfset MaxPos =
> ArrayLen(session.DealersList) +
> 1>
> <cfset
> session.DealersList[#MaxPos#][1]="#GetDealerInfo.Company#">
> <cfset
> session.DealersList[#MaxPos#][2]="#GetDealerInfo.Address#">
> <cfset
> session.DealersList[#MaxPos#][3]="#GetDealerInfo.City#">
> <cfset
> session.DealersList[#MaxPos#][4]="#GetDealerInfo.State#">
> <cfset
> session.DealersList[#MaxPos#][5]="#GetDealerInfo.Zip#">
> <cfset
> session.DealersList[#MaxPos#][6]="#GetDealerInfo.Phone#">
> <cfset
> session.DealersList[#MaxPos#][7]="#getCities.Distance#">
> <cfif GetDealerInfo.Website IS NOT "">
> <cfset
> session.DealersList[#MaxPos#][8]="#GetDealerInfo.Website#">
> <cfelse>
> <cfset
> session.DealersList[#MaxPos#][8]="none">
> </cfif>
> </cfloop>
> </cfif>
>
> Any help would be much appreciated....
>
> Butch Zaccheo
>
>
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
