Russ, if you're referring to this one:
*
http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268742
*<http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268742>
works fine if you want to pass in the lat/long directly, but I'm trying to
come up with a way to do it off zip code. your function got really slow
when I added functionality inside to do lat/long lookups, because the
function executes for each zip code you're comparing - up to 42000.
However, in attemping to explain all this, I've actually worked out a
solution:
The following query worked pretty well, given that "TB907" (the address
table) contains nearly 900,000 records. (Don't blame me for the table name,
blame BSR, it's their product)
DECLARE @long1 decimal(5,2)
DECLARE @lat1 decimal(5,2);
SELECT @long1 = dbo.getlongitude('27502');
SELECT @lat1 = dbo.getLatitude('27502');
SELECT A.*
FROM WEBREPORTS.dbo.TB907 A
WHERE
zipcode in
(
SELECT B.zipcode
FROM zipcodes B
WHERE
dbo.getDistanceBetween('',@lat1,@long1,'',B.latitude,B.longitude) < 25
)
--
I'm not certified, but I have been told that I'm certifiable...
Visit http://www.opensourcecf.com today!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268853
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
