Here's the sp I use.
Hope this helps,
---nimer
==============================
CREATE PROCEDURE Zip_Nearest
@ZipCode int,
@Miles int
AS
declare @cntZips int,
@iLat float,
@iLng float,
@vZip varchar(10)
select Distinct @vZip=ZipCode, @iLat=Lat, @iLng=Lng, @cntZips = count(*)
from ZipUsa
Where Convert(int, ZipCode) = @ZipCode
group by ZipCode,lat,lng
IF (@cntZips > 0 and isNull(@iLat, 0) > 0 AND isNull(@iLNG, 0) > 0)
BEGIN
SELECT distinct zipcode
FROM ZIPUSA
Where (3963.192 * ACOS((SIN(Lat * 0.0174603) *
SIN(@iLAT * 0.0174603)) +
(COS(LAT * 0.0174603) *
COS(@iLAT * 0.0174603)*
COS((LNG * 0.0174603)-(@iLNG * 0.0174603)))))
<= @miles
/*
SELECT distinct ZIPCODE, Lat, LNG
FROM ZIPUSA
WHERE (3963.192 * ACOS((SIN(@iLat * 0.0174603) *
SIN(LAT * 0.0174603)) +
(COS(@iLAT * 0.0174603) *
COS(LAT * 0.0174603)*
COS((@iLNG * 0.0174603)-(LNG * 0.0174603)))))
<= @miles
*/
END
ELSE
BEGIN
select * from ZipUsa
Where ZipCode = @ZipCode
END
GO
==============================
-----Original Message-----
From: Robert Everland [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 09, 2001 8:42 AM
To: CF-Talk
Subject: Zipcode stored procedure
I remember someone talking before about designing a stored procedure
to calculate zipcode radius, before I start coding it myself I was curious
if this person is still on the list and willing to share the code.
Robert Everland III
Dixon Ticonderoga
Web Developer Extraordinaire
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
