April 29, 2008 at 10:07 am
it is supposed to give me that back zips within 50 miles but it does not give me any rows back
April 29, 2008 at 10:10 am
1. are there any rows in ZIPUSA for zip code '45053'? if not - that will never return anything.
2. if 1. does have data, then the function is messed up, and needs to be fixed somehow.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 29, 2008 at 11:42 am
Should have Okeana, OH for that Zip code. There are approx 300 Zip codes within approx 50 miles of that Zip.
(Edit: Took less than 1 ms of CPU time, total of 15 ms, to get that, using my query on a table of all US Zip codes that I have.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 11:45 am
i swear my function was working fine before now it is not returning the results i would expect. I haven't changed anything
Gsquared solution works like you suggested
I get several results
select zp.zip_code,zp.citystname,zip2.lat,zip2.lng
from
(select distinct zip_code, citystname, lat,lng
from zipusa) As ZP
inner join
(select lat,lng,Zip_code from zipusa where
zip_code='45053') as Zip2 on
zp.lat between (zip2.lat -1.45) and (zip2.lat + 1.45)
and zp.lng between (zip2.lng -1.45) and (zip2.lng + 1.45)
however when i tried to add variables to the above solution and create a procedure
ALTER PROCEDURE [dbo].[zipFind]
@Radius int = 0,
@Zip nvarchar (50)
AS
BEGIN
SET NOCOUNT ON;
declare @LatCalc int
declare @LngCalc int
set @LatCalc=(@Radius/69)
set @LngCalc=(@Radius/69)
select zp.zip_code,zp.citystname,zip2.lat,zip2.lng
from
(select distinct zip_code, citystname, lat,lng
from zipusa) As ZP
inner join
(select lat,lng,Zip_code from zipusa where
zip_code=@Zip) as Zip2 on
zp.lat between (zip2.lat -@LatCalc) and (zip2.lat + @LatCalc)
and zp.lng between (zip2.lng - @LngCalc) and (zip2.lng + @LngCalc)
END
I only get back one result that is equal to my zip variable
April 29, 2008 at 11:46 am
GSquared (4/29/2008)
Should have Okeana, OH for that Zip code. There are approx 300 Zip codes within approx 50 miles of that Zip.(Edit: Took less than 1 ms of CPU time, total of 15 ms, to get that, using my query on a table of all US Zip codes that I have.)
yeah i can't get it to work in a stored proc
April 29, 2008 at 11:47 am
Change:
declare @LatCalc int
declare @LngCalc int
to:
declare @LatCalc float
declare @LngCalc float
See if that fixes it for you.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 11:49 am
You might also want to change:
set @LatCalc=(@Radius/69)
set @LngCalc=(@Radius/69)
to:
set @LatCalc=abs(@Radius/69)
set @LngCalc=abs(@Radius/69)
That way, if someone accidentally enters a negative value for the radius, it will still work. Kind of paranoid, but I believe in being careful about that kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 12:10 pm
made both changes you suggested still am only getting one resuls
April 29, 2008 at 12:41 pm
I missed that @Radius is declared as Int. Change that to Float too, please.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2008 at 1:02 pm
changing the int variable to float did the trick.
thanks
2 last questions
How can i calculate the distance in miles for this query
i would like each row in my results to have a column like distance and that columns would tell you the distance from that zip to the zip variable
why did changing the to float as opposed to integer make the difference?
was it because it was more of a real number?
Thanks again to you and Matt for your help
April 29, 2008 at 1:29 pm
The reason changing to float worked is because 50/69, in Integer, is 0. In Float/Real, it's .72464. Makes a big difference in the calculation.
Here's a formula for distance:
declare @DegToRad as float
set @DegToRad = 57.29577951
abs(3959 * ATAN(SQRT(1 -
SQUARE(
SIN(lat1 / @DegToRad) *
SIN(lat2 / @DegToRad) +
COS(lat1 / @DegToRad) *
COS(lat2 / @DegToRad) *
COS(ABS(long2 - long1)/@DegToRad)))
/
SIN(lat1 / @DegToRad) *
SIN(lat2 / @DegToRad) +
COS(lat1 / @DegToRad) *
COS(lat2 / @DegToRad) *
COS(ABS(long2 - long1)/@DegToRad)))
It has occassional problems because of floating-point rounding, but otherwise works quite well. The problem only comes up when locations are extremely close together.
You'll have to modify it to have your latitude and longitude columns in it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 30, 2008 at 6:38 am
GSquared (4/29/2008)
The reason changing to float worked is because 50/69, in Integer, is 0. In Float/Real, it's .72464. Makes a big difference in the calculation.Here's a formula for distance:
...
It has occassional problems because of floating-point rounding, but otherwise works quite well. The problem only comes up when locations are extremely close together.
You'll have to modify it to have your latitude and longitude columns in it.
I am getting some crazy numbers with this formula I am getting 2132 as distance for places i know are only 10 miles away just about everything is coming up as 2100 miles or more even though my radius is only 50.
here is my formula I was not sure which longitude to subtract from but i tried both ways and still got similar results
abs(3959 * ATAN(SQRT(1 -
SQUARE(
SIN(zp.lat / @DegToRad) *
SIN(zip2.lat / @DegToRad) +
COS(zp.lat / @DegToRad) *
COS(zip2.lat / @DegToRad) *
COS(ABS(zp.lng-zip2.lng)/@DegToRad)))
/
SIN(zp.lat / @DegToRad) *
SIN(zip2.lat / @DegToRad) +
COS(zp.lat / @DegToRad) *
COS(zip2.lat / @DegToRad) *
COS(ABS(zp.lng-zip2.lng)/@DegToRad))) as distance
April 30, 2008 at 12:31 pm
My fault. I forgot a pair of parentheses when I was converting this from the function I use.
Here you go:
abs(3959 * ATAN(SQRT(1 -
SQUARE(
SIN(zp.lat / @DegToRad) *
SIN(zip2.lat / @DegToRad) +
COS(zp.lat / @DegToRad) *
COS(zip2.lat / @DegToRad) *
COS(ABS(zp.lng-zip2.lng)/@DegToRad)))
/
(SIN(zp.lat / @DegToRad) *
SIN(zip2.lat / @DegToRad) +
COS(zp.lat / @DegToRad) *
COS(zip2.lat / @DegToRad) *
COS(ABS(zp.lng-zip2.lng)/@DegToRad)))) as distance
Yeah, without the parentheses, it gives truly strange results.
(Edit: Note, with the ABS on it, it really doesn't matter which long is subtracted from which. abs(10-5) = abs(5-10).)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply