April 24, 2008 at 10:29 am
ok i am sure this is simple but i may be over thinking this i am a noob to functions.
I have a function that calculates distance etc based on latitude and longitude from a table.
I am trying to create a stored proc that will give me all the zip codes within X number of miles. I have all this set up in the function but am not sure how to pass the parameters to the function
my functions work just fine if i execute them and manually put in the parameters values, i am not sure how to get it to work via a joined query. right
I basically want to pass the value of the lat and lon from my zipusa table to my function like this Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,25)
now i get
Cannot find either column "zipcodes" or the user-defined function or aggregate "dbo.Ufn_GetLocationsInRadius", or the name is ambiguous
I tried something like this I may be way off
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,
Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,25)as result,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
where mytable1.dbo.app_TelephoneDetails.ZIPCode='12345'
[/code]
FUNCTION [dbo].[Ufn_GetLocationsInRadius](@latitude [float], @longitude [float], @radius [int])
RETURNS TABLE (
[citystname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[zipCode] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[distance] [float] NULL
) WITH EXECUTE AS CALLER
AS EXTERNAL NAME [ZipCodeRadius].[ZipCodeRadius.LocationsInRadiusFunction].[InitMethod]
[/code]
April 24, 2008 at 11:41 am
April 24, 2008 at 12:04 pm
You missed the fact that the function returns all zips within a specified radius. It needs to be TBV'd.
Try 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,
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
INNER JOIN Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,25)T2
on T2.ZIPCODE = ZIPUSA.ZIPCODE,
where mytable1.dbo.app_TelephoneDetails.ZIPCode='12345'
Doug
April 24, 2008 at 12:29 pm
i tried dougs solution but i get
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ZIPUSA.LAT" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "ZIPUSA.LNG" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Ufn_GetLocationsInRadius.distance" could not be bound.
April 24, 2008 at 12:36 pm
April 24, 2008 at 12:37 pm
April 24, 2008 at 1:06 pm
ok i got rid of the error for the ufn by using the t2 i still get the other ones relating to zipusa
here is schema
CREATE TABLE [dbo].[ZIPUSA](
[ZIPID] [int] IDENTITY(1,1) NOT NULL,
[ZIP_CODE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CITYSTNAME] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATE] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LAT] [real] NULL,
[LNG] [real] NULL,
[LAT_RADIANS] AS (radians([LAT])) PERSISTED,
[LNG_RADIANS] AS (radians([LNG])) PERSISTED,
[SOUNDEX_CITYNAME] AS (soundex([CITYSTNAME])) PERSISTED,
[County] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ZipClass] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ZIPCODESUSA] PRIMARY KEY CLUSTERED
(
[ZIPID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
thanks
April 24, 2008 at 1:18 pm
April 24, 2008 at 1:38 pm
How accurate do you need this to be?
If a few miles off one way or the other is okay, the way I would do this is a reverse distance lookup. This won't be exact, but it's probably good enough for most uses.
Distance north-south is approximately 69 miles per degree of latitude. Distance east-west varies the further you are from the equator, but if you estimate it the same way, you won't be too far off. (Distances in the continental US, for example, vary from nearly 60 miles per degree in southern Texas to just about 45 miles per degree at the Canadian border.)
So, if you want to quickly find what Zip codes are within X miles of each other, divide by 69, and find out which ones are within that latitude-longitude range.
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.
It's not precise, but it's not off by much either. And since Zip code lat/long data is based (in most cases) on the post office that services the Zip code, which isn't very precise, it's probably close enough for most work.
If you need exact, then you'll need data on the borders of all Zip codes (which can be very irregular shapes), and you'll need to pre-calculcate as much of it as possible.
If you need slightly more precise than this, but not exact, then use the usual lat-long of the post office, and pre-calculate all distances. It's approximately 1.7 billion combinations (based on 41,000 Zip codes). You'll need good indexes on that if you want any speed on 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 24, 2008 at 1:39 pm
If you're happy with your current function, look up Apply in Books Online. It tells you how to "join" to a UDF with input parameters based on the columns of the table it's being joined to.
- 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 24, 2008 at 1:56 pm
Hey GS, I like those approximations. I don't think we're talking interplanetery landings here so it should be close enough. btw, when I've used a "locate store" function on a website it does usually put me at the main Post Office in that zip.
DAB
April 25, 2008 at 7:10 am
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
April 25, 2008 at 7:14 am
bledu (4/24/2008)
forgot to say and can you also post the schema formytable1.dbo.app_TelephoneDetails
I did not create this table as i would had done it a little differently...
CREATE TABLE [dbo].[app_TelephoneDetails](
[TelephoneID] [int] IDENTITY(1,1) NOT NULL,
[Telephone] [nchar](10) NOT NULL,
[FirstName] [nchar](100) NOT NULL,
[LastName] [nvarchar](255) NULL CONSTRAINT [DF_app_TelephoneDetails_LastName] DEFAULT (N'None'),
[Address] [nchar](256) NULL,
[LastModified] [datetime] NULL,
[StatusCode] [nchar](10) NULL,
[LastUser] [nvarchar](256) NULL,
[Active] [bit] NULL,
[Title] [nvarchar](255) NULL,
[Gender] [nvarchar](255) NULL,
[OfficeName] [nvarchar](255) NULL,
[City] [nvarchar](255) NULL,
[State] [nvarchar](255) NULL,
[ZIPCode] [nchar](10) NULL,
[CountyName] [nvarchar](255) NULL,
[MSAName] [nvarchar](255) NULL,
[PrimarySpecialty] [nvarchar](255) NULL,
[SecondarySpecialty] [nchar](255) NULL,
[HomeAddress] [nvarchar](max) NULL,
[HomeCity] [nvarchar](255) NULL,
[HomeState] [nvarchar](255) NULL,
[HomeZIP] [nchar](10) NULL,
[HomePhone] [nchar](10) NULL,
[Disp] [nvarchar](50) NULL,
[RoleName] [varchar](256) NULL,
[Email] [nvarchar](255) NULL,
[CellPhone] [nvarchar](10) NULL,
[DateAvailable] [datetime] NULL,
CONSTRAINT [PK_app_TelephoneDetails] PRIMARY KEY CLUSTERED
(
[TelephoneID] ASC
April 25, 2008 at 7:17 am
SQLServerLifer (4/24/2008)
You missed the fact that the function returns all zips within a specified radius. It needs to be TBV'd.Try 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,
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
INNER JOIN Ufn_GetLocationsInRadius(ZIPUSA.LAT,ZIPUSA.LNG,25)T2
on T2.ZIPCODE = ZIPUSA.ZIPCODE,
where mytable1.dbo.app_TelephoneDetails.ZIPCode='12345'
Doug
I don't think that you can use a Table-valued function using row values from a table in an INNER join, you have to call it using the CROSS APPLY functionality.
Meaning:
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,
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='12345'
----------------------------------------------------------------------------------
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 7:54 am
hey matt 2 things
one 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'
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply