-----Original Message-----
From: Christian Watt [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 5:38 PM
To: CF-Talk
Subject: RE: OT- sql format question
Eric,
I am going to guess that there is a flaw with the other formula you
have. I did a lot of searching on the best formula to use, and that is
what I came up with. But if you think about it, 324 zip codes in a 50
mile radius, that means that every 2.7 miles you are in a new zipcode.
The only thing that you have to watch out for though is around Lake
Michigan and the Florida panhandle. form places in Wisconsin to
Illinois or NY, or Michigan my come back within 50 miles, of course they
are, if you have a boat, otherwise it is a couple of hundred miles.
-----Original Message-----
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 5:14 PM
To: CF-Talk
Subject: RE: OT- sql format question
Thanks again for the help
Question though. When i run your sp I get 26 zipcodes for the
cincinnatti surrounding area of 50 miles. When I run the other code in
access i get 324 zipcodes?
Here is that ACCESS code again
SELECT zipCodes.*,
Sqr(((69.1*(CDbl(zipCodes.Latitude)-39.166759))^2)+((69.1*(CDbl(zipCodes
.Longitude)-84.53822)*Cos(39.166759/57.3))^2)) AS DistanceInMiles
FROM zipCodes
WHERE
(((Sqr(((69.1*(CDbl(zipCodes.Latitude)-39.166759))^2)+((69.1*(CDbl(zipCo
des.Longitude)-84.53822)*Cos(39.166759/57.3))^2)))<50));
-----Original Message-----
From: Christian Watt [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 2:14 PM
To: CF-Talk
Subject: RE: OT- sql format question
I don't know if you are dealing with degrees or radians, but
here is my
SQL code using radians.
Of course this is a stored procedure where I pass the Lat and
Lon.
SELECT Zip
FROM Zip
WHERE((lat >= (@TLat - (200 * 0.0005))) AND
(lat <= (@TLat + (200 * 0.0005)))) AND
((lon >= (@TLon - (200 * 0.0005))) AND
(lon <= (@TLon + (200 * 0.0005)))) AND
((3963 * (acos(sin(@TLat) * sin(ZIP.lat + 0.000001)
+
cos(@TLat) * cos(ZIP.lat + 0.000001) * cos((ZIP.lon +
0.000001) -
@TLon)))) <= 201))
Christian
-----Original Message-----
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 2:02 PM
To: CF-Talk
Subject: RE: OT- sql format question
OOOPs it looks like I forgot to paste the rest:
WHERE
(((Square(((69.1*(CDbl(zipCodes.Latitude)-39.166759))^2)+((69.1*(CDbl(zi
pCodes.Longitude)-84.53822)*Cos(39.166759/57.3))^2)))<50));
-----Original Message-----
From: Phillip Beazley [mailto:[EMAIL PROTECTED]
Sent: Friday, August 13, 2004 1:58 PM
To: CF-Talk
Subject: Re: OT- sql format question
At 02:38 PM 8/13/2004, you wrote:
>This was written in access, can anyone convert to T-SQL
>I keep doing it and keep missing something.
>
>SELECT zipCodes.*,
>Sqr(((69.1*(CDbl(zipCodes.Latitude)-39.166759))^2)+((69.1*(CDbl(zipCode
s.Longitude)-84.53822)*Cos(39.166759/57.3))^2))
>AS DistanceInMiles
Untested but looks good:
SQRT(POWER((69.1*(zipCodes.latitude-39.166759)),2)+POWER((69.1*(zipCodes
.longitude-84.53822)*COS(39.166759/57.3)),2)
AS DistanceInMiles
--
Phillip Beazley
Onvix -- Website Hosting, Development & E-commerce
Visit http://www.onvix.com/ or call 727-578-9600.
_____
________________________________
_____
________________________________
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
