Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

My zipcode query is not returning the correct results

Status
Not open for further replies.
Reessee

Reessee

Programmer
May 10, 2006
6
US
The following query:
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.
 
Sort by date Sort by votes
What about replacing this:
<= '10'
with this ,
<= 10

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Thank you so much for the suggestion.
Unfortunatley I still received the same answer.
 
Upvote 0 Downvote
Just to rule out the obvious ...

Do you know by some independent means that there are in fact other ZIP codes within 10 miles of "80222"? I don't know where "80222" is but some US ZIP codes cover a pretty big area.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]
 
Upvote 0 Downvote
Well, that is just like something that I would do, however, I did check and found at least one other zipcode within 2.07 miles. which my code says is 23.5 miles away. (80250)
 
Upvote 0 Downvote
Are the LATITUDE and LONGITUDE in radians ?
Are you absolutely sure for the 3963 value ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Actually I have no idea if my latitude and longitude are in radians. You wouldn't have any idea how I might find out?
 
Upvote 0 Downvote
You wouldn't have any idea how I might find out?
What is the result of the following query ?
SELECT MIN(Latitude), MAX(Latitude), MIN(Longitude), MAX(Longitude)
FROM ZipCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
-14.270200000000001 70.603759999999994 -176.64435 166.410291
 
Upvote 0 Downvote
Well, an angle's measure in radian should be between -PI and +PI ...
You may try to replace all occurrences of ?.*ITUDE with 3.1416 * ?.*ITUDE / 180

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Tetol
  • Locked
  • Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
135
carp
carp
szeiss
  • Locked
  • Question
Need to select the one value if it exists otherwise another value
Replies
4
Views
162
szeiss
szeiss
manbob
  • Locked
  • Question
need a help with sub query getting group ereor, please correct
Replies
2
Views
140
carp
carp
jchewsmith
  • Locked
  • Question
Update Query with a MAX(Date) subquery 1
Replies
1
Views
151
JarlH
JarlH
Stewman
  • Locked
  • Question
Calculations using a Union query 1
Replies
2
Views
121
Stewman
Stewman

Part and Inventory Search

Sponsor

Back
Top