469,636 Members | 1,781 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,636 developers. It's quick & easy.

Nearby zipcodes from a point

Hi,
I have a database of zipcodes,longitude and latitude. I want to
calculate the nearby zipcodes from a specific zipcode. I have the
function that gives the distance between two zipcodes but I need more
robust way to find the nearby zipcodes without looping and calling my
function for 33,000 records of zipcodes.

here is the function I used to get the distance between two zips:
(sql2000 server user defined function)

CREATE FUNCTION dbo.fn_CalcMiles( @LatA FLOAT , @LongA FLOAT , @LatB
FLOAT ,@LongB FLOAT )

RETURNS FLOAT

AS

BEGIN

DECLARE @localRConst FLOAT
DECLARE @localAns FLOAT
DECLARE @localMiles FLOAT

SET @localRConst = 57.3
SET @localAns = 0
SET @localMiles = 0

IF @LatA IS NULL OR @LatA = 0 OR @LongA IS NULL OR @LongA = 0 OR @LatB
IS
NULL OR @LatB = 0 OR @LongB IS NULL OR @LongB = 0
BEGIN
RETURN ( @localMiles )
END

SET @localAns = SIN(@LatA / @localRConst) * SIN(@LatB / @localRConst)
+
COS(@LatA / @localRConst ) * COS( @LatB / @localRConst ) *
COS(ABS(@LongB -
@LongA )/@localRConst)
SET @localMiles = 3959 * ATAN(SQRT(1 - SQUARE(@localAns)) / @localAns)

SET @localMiles = CEILING(@localMiles)

RETURN ( @localMiles )

END
Nov 22 '05 #1
0 1118

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Roger Leigh | last post: by
2 posts views Thread by Philip Herlihy | last post: by
1 post views Thread by muhammet | last post: by
39 posts views Thread by DanielJohnson | last post: by
1 post views Thread by Robert | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.