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:268811
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
