SELECT statement that has a formula in it

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SELECT statement that has a formula in it

Author

Topic

dmj07
Starting Member

8 Posts

Posted - 2010-05-27 : 09:58:14

Maybe I'm doing something wrong or going mad anyways when I run this SELECT statement:


DECLARE @Lat1      decimal(8,4)
DECLARE @Long1     decimal(8,4)
DECLARE @Lat2      decimal(8,4)
DECLARE @Long2     decimal(8,4)

SET @Lat1 = 53.368770
SET @Long1 = -2.737818
SET @Lat2 = 53.366103
SET @Long2 = -2.734406

DECLARE @Show   decimal(28, 10)

SET @Show = (SELECT     3958.75 * ATAN(SQRT(1 - POWER(SIN(@Lat1 / 57.2958) * SIN(@Latitude2 / 57.2958) + COS(@Lat1 / 57.2958) * COS(@Lat2 / 57.2958) 
	                                   * COS(@Long2 / 57.2958 - @Long1 / 57.2958), 2)) / (SIN(@Lat1 / 57.2958) * SIN(@Lat2 / 57.2958) + COS(@Lat1 / 57.2958) 
	                                   * COS(@Lat1 / 57.2958) * COS(@Long1 / 57.2958 - @Long1 / 57.2958))) AS Distance
	             FROM         Places)

PRINT @Show

I get a number like 27.3 but when I step into it and debug it I get the correct number which is 0.23.

Any ideas where I am going wrong? Is it a data type issue?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 10:09:50

When I try to run it then I have to
- comment out "FROM Places"
- change @Latitude2 to @Lat2
and after that it gives me:
0.2333489554

No, you're never too old to Yak'n'Roll if you're too young to die.

dmj07
Starting Member

8 Posts

Posted - 2010-05-27 : 10:35:19

Yes but what about when you select it through a function? Basically when I call this as a function from a stored procedure it returns the 27.3 or sometimes 0.000000:


--Stored procedure
SET @Latitude = 53.366103
SET @Lomgitude = -2.734406

SELECT dbo.fnGetDistance(@Latitude, @Longitude, 53.368770, -2.737818) AS Distance
--Stored procedure


ALTER FUNCTION dbo.fnGetDistance
(   
      @lat1 decimal(8,4),   
      @long1 decimal(8,4),   
      @lat2 decimal(8,4),   
      @long2 decimal(8,4)
)   

RETURNS decimal(28,10)
AS
	BEGIN
	
	DECLARE @d decimal(28,10)

	SET @d = 3958.75 * ( Atan(Sqrt(1 - power(((Sin(@lat1/57.2958) * Sin(@lat2/57.2958)) + (Cos(@lat1/57.2958) * Cos(@lat2/57.2958) * Cos((@long2/57.2958) - (@long1/57.2958)))), 2)) / ((Sin(@lat1/57.2958) * Sin(@lat2/57.2958)) + (Cos(@lat1/57.2958) * Cos(@lat2/57.2958) * Cos((@long2/57.2958) - (@long1/57.2958))))))

	RETURN @d
	
	END

I'm using Visual Studio to do this so don't know if this is also an issue :o\

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-28 : 03:47:54

You seem to be passing them the variables into the function in the wrong order!
The call the "Function" version doesn't line up with the input values the same as the "raw code"

and you've a spelling mistake in the declare of your variables @Lomgitude

dmj07
Starting Member

8 Posts

Posted - 2010-05-28 : 05:56:37

Ok minus the spelling mistake, how are they being passed in wrong? Like I said if I debug it and assign it to a variable I get the correct answer but when selected from a function it returns the wrong answer.

Is there any other way to implement this type of getting the distance between two latitude and longitude points?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 06:16:43

quote:
how are they being passed in wrong?

For example:
In your first post: SET @Lat2 = 53.366103
In your function call it becomes 53.368770

No, you're never too old to Yak'n'Roll if you're too young to die.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-05-28 : 07:36:55

print your variables inside the function after they are filled by the calling procedure

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources