I'm just guessing, but I would bet that my solution is a lot faster... > -----Original Message----- > From: Rick Root [mailto:[EMAIL PROTECTED] > Sent: Tuesday, February 06, 2007 9:42 AM > To: CF-Talk > Subject: Re: Postal Code database and proximity > > This seems to do the trick: > > > CREATE FUNCTION [dbo].[getDistanceBetween] > ( > @zip1 char(5), > @zip2 char(5) > ) > RETURNS NUMERIC( 10, 5 ) > AS > BEGIN > DECLARE @x decimal(20,10) > DECLARE @pi decimal(21,20) > DECLARE @lat1 decimal(5,2) > DECLARE @long1 decimal(5,2) > DECLARE @lat2 decimal(5,2) > DECLARE @long2 decimal(5,2) > > SET @long1 = (select LONGITUDE FROM dbo.ZIPCODES where ZIPCODE = > @zip1) > SET @lat1 = (select LATITUDE FROM dbo.ZIPCODES where ZIPCODE = > @zip1) > SET @long2 = (select LONGITUDE FROM dbo.ZIPCODES where ZIPCODE = > @zip2) > SET @lat2 = (select LATITUDE FROM dbo.ZIPCODES where ZIPCODE = > @zip2) > SET @pi = 3.14159265358979323846 > SET @x = sin( @lat1 * @pi/180 ) * sin( @lat2 * @pi/180 ) + cos( > @lat1 [EMAIL PROTECTED]/180 ) * cos( @lat2 * @pi/180 ) * cos( abs( (@long2 * > @pi/180) - > (@long1 [EMAIL PROTECTED]/180) ) ) > SET @x = atan( ( sqrt( 1- power( @x, 2 ) ) ) / @x ) > RETURN abs(( 1.852 * 60.0 * ((@x/@pi)*180) ) / 1.609344) > END > > select A.*, dbo.getDistanceBetween('27502',A.zipcode) AS DIST > from dbo.ZIPCODES A > where > a.zipcode <= '27552' and A.zipcode >= '27452' and > dbo.getDistanceBetween('27502',A.zipcode) < 25 > > That lists all of the zip codes within 25 miles of 27502. > > Rick > > > -- > 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:268819 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
