MS SQL

MS SQL RSS

My zipcode query is not returning the correct results

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: May 2006

Posts: 3

Reputation: Reessee is an unknown quantity at this point

Solved Threads: 0

Reessee

Offline

Newbie Poster

My zipcode query is not returning the correct results

May 10th, 2006

The following query:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE PROCEDURE GetDistance AS
 
SELECT a.ZIPCODE, a.CITY, a.STATE,
ROUND(
(ACOS(
(SIN(c.LATITUDE) * SIN(a.LATITUDE)) +
(COS(c.LATITUDE) * COS(a.LATITUDE) *
COS(a.LONGITUDE - c.LONGITUDE))
)
)
* 3963,1
) AS distance
FROM Zipcodes a, Zipcodes c
WHERE (
ACOS(
(SIN(c.LATITUDE) * SIN(a.LATITUDE)) +
(COS(c.LATITUDE) * COS(a.LATITUDE) * COS(a.LONGITUDE - c.LONGITUDE))
)
) * 3963 <= '10'
AND c.ZIPCODE='80222'
ORDER BY distance
GO
CREATE PROCEDURE GetDistance AS
 
SELECT a.ZIPCODE, a.CITY, a.STATE,
ROUND(
(ACOS(
(SIN(c.LATITUDE) * SIN(a.LATITUDE)) +
(COS(c.LATITUDE) * COS(a.LATITUDE) *
COS(a.LONGITUDE - c.LONGITUDE))
)
)
* 3963,1
) AS distance
FROM Zipcodes a, Zipcodes c
WHERE (
ACOS(
(SIN(c.LATITUDE) * SIN(a.LATITUDE)) +
(COS(c.LATITUDE) * COS(a.LATITUDE) * COS(a.LONGITUDE - c.LONGITUDE))
)
) * 3963 <= '10'
AND c.ZIPCODE='80222'
ORDER BY distance
GO

tells me that the only zipcode within 10 miles of 80222 is 80222 and if I replace 10 with 25 it offers only one additional zip code? Does anyone have any ideas why this might be happening?
Thanks so much for any suggestions anyone has to offer.:rolleyes:

Last edited by cscgal; May 11th, 2006 at 4:09 pm. Reason: Added code tags

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: My zipcode query is not returning the correct results

May 10th, 2006

give us table setup and what data you have in those tables

•

Join Date: May 2006

Posts: 3

Reputation: Reessee is an unknown quantity at this point

Solved Threads: 0

Reessee

Offline

Newbie Poster

Re: My zipcode query is not returning the correct results

May 10th, 2006

Column Name Data Type
ZIPCODE nvarchar
LATITUDE float
LONGITUDE float
CITY nvarchar
STATE nvarchar
ABBR nvarchar

The first five entries look like this:
00501 40.81718 -73.04536 HOLTSVILLE NEW YORK NY
00544 40.81718 -73.04536 HOLTSVILLE NEW YORK NY
00601 18.165273 -66.722583 ADJUNTAS PUERTO RICO PR
00602 18.393103 -67.180953 AGUADA PUERTO RICO PR
00603 18.455913 -67.14578 AGUADILLA PUERTO RICO PR

I think that my problem may be that I am assuming that the latitude and longitude are in radins. But they may be in degrees and I do not know how to tell the difference.

Have I provided enough information?

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: My zipcode query is not returning the correct results

May 11th, 2006

yes, those locations are in degrees

•

Join Date: May 2006

Posts: 3

Reputation: Reessee is an unknown quantity at this point

Solved Threads: 0

Reessee

Offline

Newbie Poster

Re: My zipcode query is not returning the correct results

May 11th, 2006

Thank you for your help.

•

Join Date: Nov 2008

Posts: 1

Reputation: ashley1213 is an unknown quantity at this point

Solved Threads: 0

ashley1213 ashley1213 is offline

Offline

Newbie Poster

Re: My zipcode query is not returning the correct results

Nov 19th, 2008

May be something is wrong with your formula or data. The both you can find on Zip Postal Codes web site for example.

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: My zipcode query is not returning the correct results

Nov 19th, 2008

•

Originally Posted by ashley1213

May be something is wrong with your formula or data. The both you can find on Zip Postal Codes web site for example.

While I don't want to discourage new members, you might want to look at the dates of previous messages. This thread has been dead for 2 and a half years.