goes a long way to clean up your code:
Distance(fromLat, fromLon, toLat, toLon) UDF:
SQR(((69 * ($3 - $1))*(69 * ($3 - $1))) + ((69 * ($4 -
$2))*(69 * ($4 - $2))))
Obviously you need to tailor this to whatever SQL dialect your
database uses. Then just join everything together:
Select DISTINCT
z.zip as returnZip,
z.city,
z.state,
Distance(#getZip.laitude#, #getZip.longitude#, z.longitude,
z.latitude) AS Distance,
d.*
FROM
zipcode_data z INNER JOIN dealers d ON (d.Zip=z.zip AND
d.AudLoc='str' OR
left(dealers.Zip, LeftVar)=z.retrunZip AND d.AudLoc = 'bsw')
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 Distance(#getZip.laitude#, #getZip.longitude#, longitude,
latitude) <= #Replace(radius,",","","all")#
You just need to fix the Left() in the join condition to whatever
your database uses and add the appropriate cfqueryparam calls.
The size of everything that is in the WHERE and SELECT might make
this intimidating at first, but if you start out with building
the FROM, then the WHERE and then the SELECT it really isn't that
bad.
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
