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]
