|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
User Defined Functionstored as a varchar I want to supply the user defined function with a point of the same varchar format: '51.6899, -0.5547' and I am trying to get it to return the distance to that point. Using simple trig. cast(ltrim(left(@Point, charindex(',', @Point) - 1)) as decimal(20, 16)) is used to split the varchar and cast the relevant parts into decimals so i can perform arithmetic operations on them. The function works, but it gives me the same value for each item in the table, despite having many different points. The end goal is I need a query which i will supply a point to, and i need it to return the closest point. I've asked a few questions on here before, but I'd really appreciate it if you could help. Not mastered SQL server yet. CREATE FUNCTION findNearestPoint ( @Point VARCHAR(60)) RETURNS DECIMAL( 20, 16 ) AS BEGIN DECLARE @x1 DECIMAL( 20, 16 ), @x2 DECIMAL( 20, 16 ), @y1 DECIMAL( 20, 16 ), @y2 DECIMAL( 20, 16 ) SELECT @x1 = cast(ltrim(left(@Point, charindex(',', @Point) - 1)) as decimal(20, 16)), @y1 = cast(ltrim(right(@Point, charindex(',', reverse(@Point)) - 1)) as decimal(12, 2)), @x2 = cast(ltrim(left(position, charindex(',', position) - 1)) as decimal(20, 16)), @y2 = cast(ltrim(right(position, charindex(',', reverse(position)) - 1)) as decimal(12, 2)) FROM dbo.positions RETURN ( SQRT( POWER( @x2 - @x1, 2 ) + POWER( @y2 - @y1, 2 ) ) ) END Hi
You are selecting data from dbo.Positions table. Remove the reference to the table. CREATE FUNCTION findNearestPoint ( @Point VARCHAR(60)) RETURNS DECIMAL( 20, 16 ) AS BEGIN DECLARE @x1 DECIMAL( 20, 16 ), @x2 DECIMAL( 20, 16 ), @y1 DECIMAL( 20, 16 ), @y2 DECIMAL( 20, 16 ) SELECT @x1 = cast(ltrim(left(@Point, charindex(',', @Point) - 1))) SELECT @y1 = cast(ltrim(right(@Point, charindex(',', reverse(@Point)) - 1))) SELECT @x2 = cast(ltrim(left(position, charindex(',', position) - 1))) SELECT @y2 = cast(ltrim(right(position, charindex(',', reverse(position)) - 1))) RETURN ( SQRT( POWER( @x2 - @x1, 2 ) + POWER( @y2 - @y1, 2 ) ) ) END Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "Douglas Adams" wrote: > I have a database table that contains simply a point: '51.6899, -0.5547' > stored as a varchar > > I want to supply the user defined function with a point of the same varchar > format: '51.6899, -0.5547' > > and I am trying to get it to return the distance to that point. Using > simple trig. > > cast(ltrim(left(@Point, charindex(',', @Point) - 1)) as decimal(20, 16)) > is used to split the varchar and cast the relevant parts into decimals so i > can perform arithmetic operations on them. > > The function works, but it gives me the same value for each item in the > table, despite having many different points. The end goal is I need a query > which i will supply a point to, and i need it to return the closest point. > > I've asked a few questions on here before, but I'd really appreciate it if > you could help. Not mastered SQL server yet. > > > CREATE FUNCTION findNearestPoint ( @Point VARCHAR(60)) > RETURNS DECIMAL( 20, 16 ) > AS > > BEGIN > DECLARE @x1 DECIMAL( 20, 16 ), > @x2 DECIMAL( 20, 16 ), > @y1 DECIMAL( 20, 16 ), > @y2 DECIMAL( 20, 16 ) > SELECT @x1 = cast(ltrim(left(@Point, charindex(',', @Point) - 1)) as > decimal(20, 16)), > @y1 = cast(ltrim(right(@Point, charindex(',', reverse(@Point)) - 1)) as > decimal(12, 2)), > @x2 = cast(ltrim(left(position, charindex(',', position) - 1)) as > decimal(20, 16)), > @y2 = cast(ltrim(right(position, charindex(',', reverse(position)) - 1)) as > decimal(12, 2)) > > FROM dbo.positions > > RETURN ( SQRT( POWER( @x2 - @x1, 2 ) + POWER( @y2 - @y1, 2 ) ) ) > END > > > I'm sorry, I don't quite understand what you're doing. When I try to run
this, I get errors: Server: Msg 1035, Level 15, State 10, Procedure findNearestPoint, Line 10 Incorrect syntax near 'cast', expected 'AS'. Server: Msg 1035, Level 15, State 1, Procedure findNearestPoint, Line 11 Incorrect syntax near 'cast', expected 'AS'. Server: Msg 1035, Level 15, State 1, Procedure findNearestPoint, Line 12 Incorrect syntax near 'cast', expected 'AS'. Server: Msg 1035, Level 15, State 1, Procedure findNearestPoint, Line 14 Incorrect syntax near 'cast', expected 'AS'. Show quote "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message news:A15A9121-7718-45D1-9224-54E7C987FB58@microsoft.com... > Hi > > You are selecting data from dbo.Positions table. Remove the reference to > the > table. > > CREATE FUNCTION findNearestPoint ( @Point VARCHAR(60)) > RETURNS DECIMAL( 20, 16 ) > AS > > BEGIN > DECLARE @x1 DECIMAL( 20, 16 ), > @x2 DECIMAL( 20, 16 ), > @y1 DECIMAL( 20, 16 ), > @y2 DECIMAL( 20, 16 ) > SELECT @x1 = cast(ltrim(left(@Point, charindex(',', @Point) - 1))) > SELECT @y1 = cast(ltrim(right(@Point, charindex(',', reverse(@Point)) - > 1))) > SELECT @x2 = cast(ltrim(left(position, charindex(',', position) - 1))) > SELECT @y2 = cast(ltrim(right(position, charindex(',', > reverse(position)) - > 1))) > > RETURN ( SQRT( POWER( @x2 - @x1, 2 ) + POWER( @y2 - @y1, 2 ) ) ) > END > > > > Regards > -------------------------------- > Mike Epprecht, Microsoft SQL Server MVP > Zurich, Switzerland > > MVP Program: http://www.microsoft.com/mvp > > Blog: http://www.msmvps.com/epprecht/ > > > > "Douglas Adams" wrote: > >> I have a database table that contains simply a point: '51.6899, -0.5547' >> stored as a varchar >> >> I want to supply the user defined function with a point of the same >> varchar >> format: '51.6899, -0.5547' >> >> and I am trying to get it to return the distance to that point. Using >> simple trig. >> >> cast(ltrim(left(@Point, charindex(',', @Point) - 1)) as decimal(20, 16)) >> is used to split the varchar and cast the relevant parts into decimals so >> i >> can perform arithmetic operations on them. >> >> The function works, but it gives me the same value for each item in the >> table, despite having many different points. The end goal is I need a >> query >> which i will supply a point to, and i need it to return the closest >> point. >> >> I've asked a few questions on here before, but I'd really appreciate it >> if >> you could help. Not mastered SQL server yet. >> >> >> CREATE FUNCTION findNearestPoint ( @Point VARCHAR(60)) >> RETURNS DECIMAL( 20, 16 ) >> AS >> >> BEGIN >> DECLARE @x1 DECIMAL( 20, 16 ), >> @x2 DECIMAL( 20, 16 ), >> @y1 DECIMAL( 20, 16 ), >> @y2 DECIMAL( 20, 16 ) >> SELECT @x1 = cast(ltrim(left(@Point, charindex(',', @Point) - 1)) as >> decimal(20, 16)), >> @y1 = cast(ltrim(right(@Point, charindex(',', reverse(@Point)) - 1)) as >> decimal(12, 2)), >> @x2 = cast(ltrim(left(position, charindex(',', position) - 1)) as >> decimal(20, 16)), >> @y2 = cast(ltrim(right(position, charindex(',', reverse(position)) - 1)) >> as >> decimal(12, 2)) >> >> FROM dbo.positions >> >> RETURN ( SQRT( POWER( @x2 - @x1, 2 ) + POWER( @y2 - @y1, 2 ) ) ) >> END >> >> >> SELECT @x2 = cast(ltrim(left(position, charindex(',', position) - 1)))
Also how would it know where position comes from. Since position is a column in the dbo.positions table FROM dbo.positions Show quote "Douglas Adams" <doug4345***@hotmail.co.uk> wrote in message news:%23QB2jKrjFHA.2484@TK2MSFTNGP15.phx.gbl... > I'm sorry, I don't quite understand what you're doing. When I try to run > this, I get errors: > > Server: Msg 1035, Level 15, State 10, Procedure findNearestPoint, Line 10 > Incorrect syntax near 'cast', expected 'AS'. > Server: Msg 1035, Level 15, State 1, Procedure findNearestPoint, Line 11 > Incorrect syntax near 'cast', expected 'AS'. > Server: Msg 1035, Level 15, State 1, Procedure findNearestPoint, Line 12 > Incorrect syntax near 'cast', expected 'AS'. > Server: Msg 1035, Level 15, State 1, Procedure findNearestPoint, Line 14 > Incorrect syntax near 'cast', expected 'AS'. > > > > > "Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message > news:A15A9121-7718-45D1-9224-54E7C987FB58@microsoft.com... >> Hi >> >> You are selecting data from dbo.Positions table. Remove the reference to >> the >> table. >> >> CREATE FUNCTION findNearestPoint ( @Point VARCHAR(60)) >> RETURNS DECIMAL( 20, 16 ) >> AS >> >> BEGIN >> DECLARE @x1 DECIMAL( 20, 16 ), >> @x2 DECIMAL( 20, 16 ), >> @y1 DECIMAL( 20, 16 ), >> @y2 DECIMAL( 20, 16 ) >> SELECT @x1 = cast(ltrim(left(@Point, charindex(',', @Point) - 1))) >> SELECT @y1 = cast(ltrim(right(@Point, charindex(',', reverse(@Point)) - >> 1))) >> SELECT @x2 = cast(ltrim(left(position, charindex(',', position) - 1))) >> SELECT @y2 = cast(ltrim(right(position, charindex(',', >> reverse(position)) - >> 1))) >> >> RETURN ( SQRT( POWER( @x2 - @x1, 2 ) + POWER( @y2 - @y1, 2 ) ) ) >> END >> >> >> >> Regards >> -------------------------------- >> Mike Epprecht, Microsoft SQL Server MVP >> Zurich, Switzerland >> >> MVP Program: http://www.microsoft.com/mvp >> >> Blog: http://www.msmvps.com/epprecht/ >> >> >> >> "Douglas Adams" wrote: >> >>> I have a database table that contains simply a point: >>> '51.6899, -0.5547' >>> stored as a varchar >>> >>> I want to supply the user defined function with a point of the same >>> varchar >>> format: '51.6899, -0.5547' >>> >>> and I am trying to get it to return the distance to that point. Using >>> simple trig. >>> >>> cast(ltrim(left(@Point, charindex(',', @Point) - 1)) as decimal(20, 16)) >>> is used to split the varchar and cast the relevant parts into decimals >>> so i >>> can perform arithmetic operations on them. >>> >>> The function works, but it gives me the same value for each item in the >>> table, despite having many different points. The end goal is I need a >>> query >>> which i will supply a point to, and i need it to return the closest >>> point. >>> >>> I've asked a few questions on here before, but I'd really appreciate it >>> if >>> you could help. Not mastered SQL server yet. >>> >>> >>> CREATE FUNCTION findNearestPoint ( @Point VARCHAR(60)) >>> RETURNS DECIMAL( 20, 16 ) >>> AS >>> >>> BEGIN >>> DECLARE @x1 DECIMAL( 20, 16 ), >>> @x2 DECIMAL( 20, 16 ), >>> @y1 DECIMAL( 20, 16 ), >>> @y2 DECIMAL( 20, 16 ) >>> SELECT @x1 = cast(ltrim(left(@Point, charindex(',', @Point) - 1)) as >>> decimal(20, 16)), >>> @y1 = cast(ltrim(right(@Point, charindex(',', reverse(@Point)) - 1)) as >>> decimal(12, 2)), >>> @x2 = cast(ltrim(left(position, charindex(',', position) - 1)) >>> as >>> decimal(20, 16)), >>> @y2 = cast(ltrim(right(position, charindex(',', reverse(position)) - >>> 1)) as >>> decimal(12, 2)) >>> >>> FROM dbo.positions >>> >>> RETURN ( SQRT( POWER( @x2 - @x1, 2 ) + POWER( @y2 - @y1, 2 ) ) ) >>> END >>> >>> >>> > > Douglas,
Nothing in your function implements the idea "closest", but you can add that in with TOP 1 .. ORDER BY or MIN() Here's a suggestion. Be sure you intend for your Y coordinates to be less precise than your X coordinates. I've left it that way. CREATE FUNCTION findNearestPoint ( @Point VARCHAR(60)) RETURNS DECIMAL( 20, 16 ) AS BEGIN RETURN ( SELECT TOP 1 Distance FROM ( SELECT SQRT( SQUARE( X2 - X1 ) + SQUARE( Y2 - Y1 ) ) AS Distance FROM ( SELECT cast(ltrim(left(@Point, charindex(',', @Point) - 1)) as decimal(20, 16)) AS X1, cast(ltrim(right(@Point, charindex(',', reverse(@Point)) - 1)) as decimal(12, 2)) AS Y1, cast(ltrim(left(position, charindex(',', position) - 1)) as decimal(20, 16)) AS X2, cast(ltrim(right(position, charindex(',', reverse(position)) - 1)) as decimal(12, 2)) AS Y2 FROM dbo.positions ) Legs ) Distance ORDER BY Distance ) END You can also do this a bit more cleanly with SUBSTRING and no REVERSE, but it's a little longer. .... RETURN ( SELECT TOP 1 Distance FROM ( SELECT SQRT( SQUARE( X2 - X1 ) + SQUARE( Y2 - Y1 ) ) AS Distance FROM ( SELECT cast(ltrim(rtrim(substring(position,1,positionComma-1))) as decimal(20,16)) as X1, cast(ltrim(rtrim(substring(position,positionComma+1,60))) as decimal(12,2)) as Y1, cast(ltrim(rtrim(substring(@Point,1,PointComma-1))) as decimal(20,16)) as X2, cast(ltrim(rtrim(substring(@Point,PointComma+1,60))) as decimal(12,2)) as Y2 FROM ( SELECT position, CHARINDEX(',',position) as positionComma, CHARINDEX(',',@Point) as PointComma FROM dbo.positions ) Commas ) Legs ) Distance ORDER BY Distance ) .... Steve Kass Drew University Douglas Adams wrote: Show quote >I have a database table that contains simply a point: '51.6899, -0.5547' >stored as a varchar > >I want to supply the user defined function with a point of the same varchar >format: '51.6899, -0.5547' > >and I am trying to get it to return the distance to that point. Using >simple trig. > >cast(ltrim(left(@Point, charindex(',', @Point) - 1)) as decimal(20, 16)) >is used to split the varchar and cast the relevant parts into decimals so i >can perform arithmetic operations on them. > >The function works, but it gives me the same value for each item in the >table, despite having many different points. The end goal is I need a query >which i will supply a point to, and i need it to return the closest point. > >I've asked a few questions on here before, but I'd really appreciate it if >you could help. Not mastered SQL server yet. > > >CREATE FUNCTION findNearestPoint ( @Point VARCHAR(60)) >RETURNS DECIMAL( 20, 16 ) >AS > >BEGIN > DECLARE @x1 DECIMAL( 20, 16 ), > @x2 DECIMAL( 20, 16 ), > @y1 DECIMAL( 20, 16 ), > @y2 DECIMAL( 20, 16 ) > SELECT @x1 = cast(ltrim(left(@Point, charindex(',', @Point) - 1)) as >decimal(20, 16)), > @y1 = cast(ltrim(right(@Point, charindex(',', reverse(@Point)) - 1)) as >decimal(12, 2)), > @x2 = cast(ltrim(left(position, charindex(',', position) - 1)) as >decimal(20, 16)), > @y2 = cast(ltrim(right(position, charindex(',', reverse(position)) - 1)) as >decimal(12, 2)) > >FROM dbo.positions > > RETURN ( SQRT( POWER( @x2 - @x1, 2 ) + POWER( @y2 - @y1, 2 ) ) ) >END > > > >  |
|||||||||||||||||||||||
Other interesting topics