By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,716 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

Nearby zipcodes from a point

P: n/a
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
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.