My zipcode query is not returning the correct results

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 427,760 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,708 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 1475 | Replies: 4

•

Join Date: May 2006

Posts: 3

Reputation: Reessee is an unknown quantity at this point

Rep Power: 0

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

(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

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 3:09 pm. Reason: Added code tags

•

Join Date: Jul 2005

Location: Dallas, TX

Posts: 481

Reputation: campkev is an unknown quantity at this point

Rep Power: 4

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

Rep Power: 0

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?