Okay, below is the whole function. Here are some values and their
results compared to zipfind.net. Note the strange behavior for 32610 and
32611, and others had this same value too.
Search for 10 mile radius of 32601 and get distance between zip codes
(This post is only concerned with the distance function part)
my results (sample):
zip code miles latitude longitude
32601 0.0 29.68040999998 -82.345738999999995
32602 4.6333652 29.629887 -82.396567000000005
32604 8.0310783 29.573293 -82.397903999999997
32610 0.49070904 29.68131199998 -82.353862000000007
32611 0.49070904 29.68131199998 -82.353862000000007
...
zipfind.com results:
zip code miles
32601 0.0
32602 5.5
32604 1.5
32610 1.3
32611 0.2
...
CREATE FUNCTION dbo.GetDistance(
@lat1 Float(8),
@log1 Float(8),
@lat2 Float(8),
@log2 Float(8)
)
RETURNS Float(8)
AS
BEGIN
DECLARE @distance Float(8)
DECLARE @R int
DECLARE @Dlog Float(8)
DECLARE @Dlat Float(8)
DECLARE @A Float(8)
DECLARE @C FLoat(8)
SET @lat1 = RADIANS(@lat1)
SET @lat2 = RADIANS(@lat2)
SET @log1 = RADIANS(@log1)
SET @log2 = RADIANS(@log2)
SET @Dlat = ABS(@lat2 - @lat1)
SET @Dlog = ABS(@log2 - @log1)
SET @R = 3956 /*Approximate radius of earth in miles*/
SET @A = SQUARE(SIN(@Dlat/2)) + COS(@lat1) * COS(@lat2) *
SQUARE(SIN(@Dlog/2))
SET @C = 2 * ATN2(SQRT(@A), SQRT(1 - @A))
/*SET @C = 2 * ASIN(min(SQRT(@A))) Alternative calculation*/
SET @distance = @R * @C
RETURN @distance
END
GO
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
