float error in t-sql scalar function
-
Hello Everyone,
The below code is giving me error:
Msg 3623, Level 16, State 1, Line 27
An invalid floating point operation occurred.
declare @lat1 as float
declare @long1 as float
declare @lat2 as float
declare @long2 as float
declare @DegToRad as float
declare @Ans as float
declare @klm as floatset @lat1 = 37.6053577010
set @long1 = 26.2733349949
set @lat2 = 37.6053577010
set @long2 = 26.2733349950
set @DegToRad = 57.29577951
set @Ans = 0.00
set @klm = 0.00if @lat1 is null or @lat1 = 0.00 or @long1 is null or @long1 = 0.00 or @lat2 is
null or @lat2 = 0.00 or @long2 is null or @long2 = 0.00
begin
return
--( @klm )
endif @lat1 = @lat2 and @long1 = @long2 return --0.1
begin
set @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 - @long1 )/@DegToRad)
set @klm = 6371 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans) -- The problem is on this line
set @klm = convert(numeric(10,2), @klm * 1000)
if @klm = 0.00 SET @klm=0.01
return --( @klm )
endCan anyone, please provide me a solution?
Thanks.
-
Hi,
When I tried to execute the expression at my end the expression inside the SQRT function returned negative values for me. Just check the value of
SELECT 1 - SQUARE(@Ans)and see. May be you need to convert the value in @Ans variable to INT?
-
Don't use float, it's an inaccurate data type, that suffers rounding problems. If you use an accurate data type, such as decimal(12,10), the error doesn't occur, and it's more accurate. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - akapellas - Wednesday, July 18, 2018 4:48 AM
Even if you use a better data type, you're going to have a problem with @Ans = 0.00. You can't divide by zero, and the line you have indicated is the problem does divide by @Ans, so that's where at least 1 problem is. Using float is also a problem as it's an inexact data type, and will produce incorrect results at times, and it will not be predictable. You'll need to change your logic to handle the possibility of a 0 value for @Ans, or you'll continue to get an error.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Anyone recommending that you should avoid the FLOAT datatype should be reminded that all the functions in this calculation (SIN, COS, ATAN, SQRT) will convert their parameters to FLOAT and will return FLOAT results. Changing the variables to DECIMAL will not improve the accuracy of any of these calculations, and could make it much less accurate. Any multiplication or division operation on a DECIMAL value will affect the scale and precision of the result, and it can be difficult to trace through a complicated expression to figure out how many decimal places you can count on. The trigonometry functions can return results very close to zero, such as 0.0000000123456789012345. As DECIMAL(12,10) that will be 0.0000000123, while as FLOAT it will have 15 significant digits of precision.
One thing you should never do with FLOAT however is test for strict equality. The APL language was designed for math and would automatically change "IF @lat = @lon" to "IF ABS(@lat - @lon) < FUZZ", where FUZZ is a system variable that defaults to 10^-15 IFRC. So if the difference between two FLOAT values is less than FUZZ, they are essentially equal. The designer of the language expected users to know their problem domain well enough to know when FUZZ should be higher or lower, it would not adjust automatically. You're unlikely to find an APL system to do your math the right way these days, so it is up to you to write the code correctly yourself.
(The main thing you should never do with FLOAT is use it for currency. It's great for trigonometry, lousy for accounting.)
No matter what datatype you use, you cannot divide by zero.
-
Thanks Scott for the reference.
Also, please be mindful about the usage of SQRT function (so that the parameter doesn't become negative) apart from division by zero. In this specific case, as per my understanding, that was the root cause.
-
Thank you guys, for your replies.
So the best idea is to rewrite the same query in a different apporach?
-
In my opinion, if you do proper checking (e.g. checking for '0' for the denominator in a division and negativity of a SQRT function), that will do.
-
Dear Debasis,
Can you please post a code-example of what you propose please?
Thanks a lot.
-
Hi,
You can try like below (note the inline comments):
declare @lat1 as float
declare @long1 as float
declare @lat2 as float
declare @long2 as float
declare @DegToRad as float
declare @Ans as float
declare @klm as float
declare @SqrtRequest floatset @lat1 = 37.6053577010
set @long1 = 26.2733349949
set @lat2 = 37.6053577010
set @long2 = 26.2733349950
set @DegToRad = 57.29577951
set @Ans = 0.00
set @klm = 0.00
set @SqrtRequest = 0.00if @lat1 is null or @lat1 = 0.00 or @long1 is null or @long1 = 0.00 or @lat2 is
null or @lat2 = 0.00 or @long2 is null or @long2 = 0.00
begin
return
--( @klm )
endif @lat1 = @lat2 and @long1 = @long2 return --0.1
begin
set @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 - @long1 )/@DegToRad)
IF(SQUARE(@Ans)<=1) Set @SqrtRequest = SQRT(1 - SQUARE(@Ans)) -- Let @SqrtRequest be 0.00 in case the param for SQRT is -ve
IF(@Ans !=0) set @klm = 6371 * ATAN(@SqrtRequest / @Ans) -- Check whether @Ans is 0 or not before dividing by it
set @klm = convert(numeric(10,2), @klm * 1000)
if @klm = 0.00 SET @klm=0.01
return --( @klm )
end -
Quick question, are you trying to calculate geographical distance?
😎Quick example using the geography spatial data type
DECLARE @LAT1 FLOAT = 37.6053577010;
DECLARE @LONG1 FLOAT = 26.2733349949;
DECLARE @LAT2 FLOAT = 37.6053577010;
DECLARE @LONG2 FLOAT = 26.2733349950;DECLARE @GEOG01 GEOGRAPHY = GEOGRAPHY::Point(@LAT1, @LONG1, 4326);
DECLARE @GEOG02 GEOGRAPHY = GEOGRAPHY::Point(@LAT2, @LONG2, 4326);SELECT @GEOG01.STDistance(@GEOG02) AS GEO_DISTANCE;
-
Yes @Eirikur i am trying to calculate the geographical distance.
DECLARE @LAT1 FLOAT = 37.6053577010;
DECLARE @LONG1 FLOAT = 26.2733349949;
DECLARE @LAT2 FLOAT = 37.6053577010;
DECLARE @LONG2 FLOAT = 26.2733349950;DECLARE @GEOG01 GEOGRAPHY = GEOGRAPHY:
oint(@LAT1, @LONG1, 4326); -- What is the missing letter?
DECLARE @GEOG02 GEOGRAPHY = GEOGRAPHY:oint(@LAT2, @LONG2, 4326); -- What is the missing letter?SELECT @GEOG01.STDistance(@GEOG02) AS GEO_DISTANCE;
where do i have to embed the above code into my code?
Thanks
- akapellas - Thursday, July 19, 2018 2:37 AMYes @Eirikur i am trying to calculate the geographical distance.
DECLARE @LAT1 FLOAT = 37.6053577010;
DECLARE @LONG1 FLOAT = 26.2733349949;
DECLARE @LAT2 FLOAT = 37.6053577010;
DECLARE @LONG2 FLOAT = 26.2733349950;DECLARE @GEOG01 GEOGRAPHY = GEOGRAPHY:
oint(@LAT1, @LONG1, 4326); -- What is the missing letter?
DECLARE @GEOG02 GEOGRAPHY = GEOGRAPHY:oint(@LAT2, @LONG2, 4326); -- What is the missing letter?SELECT @GEOG01.STDistance(@GEOG02) AS GEO_DISTANCE;
where do i have to embed the above code into my code?
Thanks
The missing letters are : and P respectfully, the forum software translates this into 😛
😎Can you post the full create statement for the function, should be easy to replace the code and even better, convert the scalar function into a much faster inline table valued function.
-
The initial Function No1 is:
Declare @ProximityMetersLimit integer
Declare @TaskStatus nvarchar(100)
Declare @TaskGID nvarchar(100)
Declare @Count as int
Declare @Latitude as decimal(25,10)
Declare @longitude as decimal(25,10)
Declare @proximityDistanceInMeters as decimal(10,2)
Declare @RetValue as nvarchar(100)--if @TaskStatus = 'OPEN' or @TaskStatus = 'CANCELLED' Return ' '
Set @proximityDistanceInMeters = 50
Set @TaskGID = '43D010F6-8600-4315-B2AF-8D51612D4D40'
Set @TaskStatus = 'COMPLETED'Select --@proximityDistanceInMeters=
min(isnull(dbo.CF_CalculateGPSDistance(gps.Latitude, gps.Longitude, sit.Latitude,sit.Longitude),0)) -- This line here is calling the other function No.2
gps.Latitude, gps.Longitude, sit.Latitude,sit.Longitude
from ES00GPSLog gps
inner join Task AS tsk on tsk.GID = gps.FGID
inner join Person AS p on p.GID = tsk.fPersonGID
inner join Sites sit on sit.GID = tsk.fAddressGID and (sit.Latitude <> 0 or sit.longitude <> 0)
where gps.FGID = @TaskGID
and (gps.recordType = 6 OR gps.recordType = 61)Function No.2 is the Function already posted at the beginning of this thread
-
I did ask you for the full create statement, your posts do not list any input parameters! On top of it, you have a data set that nobody can see or have any knowledge of.
😎Here is a sample inline table valued function for calculating the distance between two points by the points' coordinates
USE TEEST;
GO
SET NOCOUNT ON;
GO
CREATE FUNCTION dbo.CF_GEOGRAPHY_DISTANCE_BY_COORDINATES
(
@LAT1 FLOAT
,@LONG1 FLOAT
,@LAT2 FLOAT
,@LONG2 FLOAT
)
RETURNS TABLE
AS
RETURN
WITH GEOPOINTS AS
(
SELECT
GEOGRAPHY::Point(@LAT1, @LONG1, 4326) AS GEOG01
,GEOGRAPHY::Point(@LAT2, @LONG2, 4326) AS GEOG02
)
SELECT
GP.GEOG01.STDistance(GP.GEOG02) AS GEO_DISTANCE
FROM GEOPOINTS GP;
GOThis is how you should use it in the second code snippet
Declare @ProximityMetersLimit integer
Declare @TaskStatus nvarchar(100)
Declare @TaskGID nvarchar(100)
Declare @Count as int
Declare @Latitude as decimal(25,10)
Declare @longitude as decimal(25,10)
Declare @proximityDistanceInMeters as decimal(10,2)
Declare @RetValue as nvarchar(100)--if @TaskStatus = 'OPEN' or @TaskStatus = 'CANCELLED' Return ' '
Set @proximityDistanceInMeters = 50
Set @TaskGID = '43D010F6-8600-4315-B2AF-8D51612D4D40'
Set @TaskStatus = 'COMPLETED'Select --@proximityDistanceInMeters=
min(isnull(GEOD.GEO_DISTANCE,0)) -- This line here is calling the other function No.2
gps.Latitude, gps.Longitude, sit.Latitude,sit.Longitude
from ES00GPSLog gps
CROSS APPLY dbo.CF_GEOGRAPHY_DISTANCE_BY_COORDINATES(gps.Latitude, gps.Longitude, sit.Latitude,sit.Longitude) GEOD
inner join Task AS tsk on tsk.GID = gps.FGID
inner join Person AS p on p.GID = tsk.fPersonGID
inner join Sites sit on sit.GID = tsk.fAddressGID and (sit.Latitude <> 0 or sit.longitude <> 0)
where gps.FGID = @TaskGID
and (gps.recordType = 6 OR gps.recordType = 61)
oint(@LAT1, @LONG1, 4326); -- What is the missing letter?Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply