April 25, 2008 at 8:06 am
Cross Apply?
SELECTtd.FirstName,
td.LastName,
td.City,
td.State,
td.ZIPCode,
Result.CityFirstName,
Result.State,
Result.ZipCode,
Result.Distance,
zu.LAT,
zu.LNG,
zu.LAT_RADIANS,
zu.LNG_RADIANS
FROMMyTable1.dbo.App_TelephoneDetails AS td
INNER JOINZipUSA AS zu ON zu.ZIP_CODE = td.ZipCode
CROSS APPLYUfn_GetLocationsInRadius(zu.LAT, zu.LNG, 25) AS Result
WHEREtd.ZipCode = '12345'
N 56°04'39.16"
E 12°55'05.25"
April 25, 2008 at 8:20 am
Gail (4/25/2008)
hey matt 2 thingsone when executing the original query you posted i get
The multi-part identifier "Ufn_GetLocationsInRadius.distance" could not be bound.
for the line in the select statement not the cross apply i tried changing it to dbo.ufn... same error.
For the moment I commented out that line and now
your query executed but did not return any results
I happen to know there are 7 entries for the zip code 70737.
I wondered if because the zipcodes in the telephone table are nchar and the ones in the zipusa are nvar char if that may cause an issue with the join. I tried converting the telephone zip in the join but i still got no results.
SELECT
mytable1.dbo.app_TelephoneDetails.FirstName,
mytable1.dbo.app_TelephoneDetails.LastName,
mytable1.dbo.app_TelephoneDetails.City,
mytable1.dbo.app_TelephoneDetails.State,
mytable1.dbo.app_TelephoneDetails.ZIPCode,
--dbo.Ufn_GetLocationsInRadius.distance,
ZIPUSA.LAT,
ZIPUSA.LNG,
ZIPUSA.LAT_RADIANS,
ZIPUSA.LNG_RADIANS
FROM
mytable1.dbo.app_TelephoneDetails
INNER JOIN ZIPUSA
ON mytable1.dbo.app_TelephoneDetails.ZIPCode = ZIPUSA.ZIP_CODE
CROSS APPLY Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,25) T2
where mytable1.dbo.app_TelephoneDetails.ZIPCode='70737'
that's because the Ufn_GetLocationsInRadius is aliased as T2, so put T2.distance in your SELECT, and you should be okay....
As to not getting results - try changing the CROSS APPLY to OUTER APPLY (roughly the same as switching from INNER JOIN to OUTER JOIN). If you still get no results - then something else is happening.... I'd tend to agree that your join criterion is problematic if the two fields aren't compatible....
----------------------------------------------------------------------------------
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 25, 2008 at 8:29 am
Gail (4/25/2008)
GSquared (4/24/2008)
How accurate do you need this to be?Let's say you're starting with latitude 47.61, longitude -122.34 (which happens to be in Seattle, WA), and you want to find all Zip codes within 100 miles.
100/69 = 1.45
So any Zip code that lists as latitude between 46.16 and 49.06 and also between longitude -120.89 and -123.79, is within about 100 miles of that original location.
I am with you on everything you said except for this 100/69=1.45 ok
Then the next line you say any zip that show lat between 46.16 and 49.06...
How did you arrive at that conclusion by getting the 1.45?
Thanks
47.61 (latitude of location) + 1.45 = 49.06
47.61 - 1.45 = 46.16
I'm adding and subtracting the number of degrees from the latitude and longitude of the central location.
Then, once you have that, select from the Zip codes table where latitude and longitude are between those value ranges.
For example:
select distinct zipcode, city
from dbo.zips
where latitude between 46.16 and 49.06
and longitude between -123.79 and -120.89
(My Zips table is populated by data from http://www.zip-codes.com.)
This select gives me a list of Zips and cities in the Seattle area, including Auburn, Belleview, Tacoma, Olympia, etc. Just knowing that area, I can say this is pretty much a good list for the center I picked and the radius I used.
- 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 25, 2008 at 9:56 am
Matt Miller (4/25/2008)
that's because the Ufn_GetLocationsInRadius is aliased as T2, so put T2.distance in your SELECT, and you should be okay....
As to not getting results - try changing the CROSS APPLY to OUTER APPLY (roughly the same as switching from INNER JOIN to OUTER JOIN). If you still get no results - then something else is happening.... I'd tend to agree that your join criterion is problematic if the two fields aren't compatible....
the outer apply worked however i have 2 issues
so my query is not returning zip codes within the range only the zipcode from my where clause.
2. the distance column
When i add the t2.distance i get
Invalid column name 'Ufn_GetLocationsInRadius'.
It doesn't even say that distance is the column it just says t2.'Ufn_GetLocationsInRadius'.
is invalid column name this may or may not relate to my problem above since no distance is returned it
may not see the other zips in the range. I am really just guessing on that though
Thanks for your help we are so close to accomplishing this...
SELECT
mytable1.dbo.app_TelephoneDetails.FirstName,
mytable1.dbo.app_TelephoneDetails.LastName,
mytable1.dbo.app_TelephoneDetails.City,
mytable1.dbo.app_TelephoneDetails.State,
mytable1.dbo.app_TelephoneDetails.ZIPCode,
t2.Ufn_GetLocationsInRadius.distance,
ZIPUSA.LAT,
ZIPUSA.LNG,
ZIPUSA.LAT_RADIANS,
ZIPUSA.LNG_RADIANS
FROM
mytable1.dbo.app_TelephoneDetails
INNER JOIN ZIPUSA
ON mytable1.dbo.app_TelephoneDetails.ZIPCode = ZIPUSA.ZIP_CODE
outer APPLY Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,50) T2
where mytable1.dbo.app_TelephoneDetails.ZIPCode='70737'
April 25, 2008 at 10:11 am
1.You're still not referring to that field the way I told you.
Replace
t2.Ufn_GetLocationsInRadius.distance,
with
t2.distance,
2. You haven't asked it to return things within the range - you've asked it to return only rows from your telephone table within that specific zipcode. You're not filtering on distance at all....
SELECT
mytable1.dbo.app_TelephoneDetails.FirstName,
mytable1.dbo.app_TelephoneDetails.LastName,
mytable1.dbo.app_TelephoneDetails.City,
mytable1.dbo.app_TelephoneDetails.State,
mytable1.dbo.app_TelephoneDetails.ZIPCode,
t2.distance,
ZIPUSA.LAT,
ZIPUSA.LNG,
ZIPUSA.LAT_RADIANS,
ZIPUSA.LNG_RADIANS
FROM
mytable1.dbo.app_TelephoneDetails
INNER JOIN ZIPUSA
ON mytable1.dbo.app_TelephoneDetails.ZIPCode = ZIPUSA.ZIP_CODE
CROSS APPLY Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,50) T2
where T2.ZIPCode='70737'
----------------------------------------------------------------------------------
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 25, 2008 at 1:14 pm
you are right on the t2.distance i misread what you had wrote before.
as far as not filtering on distance my function does that
Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,25) that is the last parameter my function calls for the radius. the distance field is a calculated field
April 25, 2008 at 4:39 pm
but you're not filtering on the distance at all. The OUTER APPLY made sure of that - besides you're trying to filter the wrong zipcode field.
Did you try the query I included at the bottom of my previous post? THAT is an attempt to give you all zipcodes within 50 (since that was the parameter being passed) of that particular zip code.
----------------------------------------------------------------------------------
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 25, 2008 at 8:16 pm
you mean this one
SELECT
mytable1.dbo.app_TelephoneDetails.FirstName,
mytable1.dbo.app_TelephoneDetails.LastName,
mytable1.dbo.app_TelephoneDetails.City,
mytable1.dbo.app_TelephoneDetails.State,
mytable1.dbo.app_TelephoneDetails.ZIPCode,
t2.distance,
ZIPUSA.LAT,
ZIPUSA.LNG,
ZIPUSA.LAT_RADIANS,
ZIPUSA.LNG_RADIANS
FROM
mytable1.dbo.app_TelephoneDetails
INNER JOIN ZIPUSA
ON mytable1.dbo.app_TelephoneDetails.ZIPCode = ZIPUSA.ZIP_CODE
CROSS APPLY Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,50) T2
where T2.ZIPCode='70737'
I ran it it took 5 minutes and 40 seconds to run and returned no results i know that zip is in there though
April 28, 2008 at 7:14 am
Have you tried using my approximate solution? Should give you results in under a second.
- 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 7:53 am
GSquared (4/28/2008)
Have you tried using my approximate solution? Should give you results in under a second.
I have and I still get same type of results that i got with matt's suggestions I am only getting
data from the zip code i query and not a range of zipcodes
I tried your solution doing something like this for zips in 100 mile radius
select zp.zip_code,zp.citystname,zip2.lat,zip2.lng
from
(select distinct zip_code, citystname
from zipusa) As ZP
inner join
(select lat,lng,Zip_code from zipusa where
zip_code='45053') as Zip2 on Zip2.Zip_Code = ZP.Zip_Code
where
zip2.lat between (zip2.lat -1.45) and (zip2.lat + 1.45)
and zip2.lng between (zip2.lng -1.45) and (zip2.lng + 1.45)
April 29, 2008 at 8:05 am
There's a basic flaw in your joining characteristics. You can't limit by zipcode, then JOIN by zipcode, and expect to get zipcodes other than the ones you join on. You need to pick a specific zip, find the coords, and correlate based on coords and distance from what you found - no joining on zip.
Here are two possible suggestions.
First one - taking GSquared's approximation
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)
Flavor2, using the function
select zp.zip_code,zp.citystname,zip2.lat,zip2.lng
from
(select distinct zip_code, citystname, from zipusa) As ZP
inner join
(select distinct t2.zipcode
from zipusa
CROSS APPLY Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,50) T2
where
zipusa.zip_code='45053') as Zip2 on zp.zip_code=zip2.zip_code
----------------------------------------------------------------------------------
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 9:27 am
Gail, the query you had joins on the Zip code columns. That's what's causing the problem. That, all by itself, eliminates all other Zip codes.
- 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 9:33 am
matt the appoximation query from gsquared worked, the other one you gave me that calls the function still gives me no results with cross apply and only one zip code with outer apply.
I would prefer to use the function as opposed to the other option.
April 29, 2008 at 9:33 am
GSquared (4/29/2008)
Gail, the query you had joins on the Zip code columns. That's what's causing the problem. That, all by itself, eliminates all other Zip codes.
right i thought that may be the problem, i was not sure what to do when using it with my function.
is there a way to calculate distance for your solution???
April 29, 2008 at 9:47 am
Gail (4/29/2008)
matt the appoximation query from gsquared worked, the other one you gave me that calls the function still gives me no results with cross apply and only one zip code with outer apply.I would prefer to use the function as opposed to the other option.
I keep making assumptions about what this does.
what does this return (I don't necessarily need a LOT, just give me a few examples/ first few lines of the actual data)
select distinct t2.*
from zipusa
CROSS APPLY Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,50) T2
where
zipusa.zip_code='45053'
I've been assuming this returns all zip codes within a 50 mile radius of a particular zipcode. Is that really what it's doing? How many results do you get back?
----------------------------------------------------------------------------------
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?
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply