Spatial SQL -> MS-SQL. Measure sharpness of angle with given triangle vertices

In this example we have triangle with edges A, B, C. We know coordinates of triangle vertices and we will try to understand if angle opposite to side "C" is sharp. The idea is to use proportion between length of edges.

 -- ============================================================================ -- function: sfn_is_sharp_angle_triangle_LL -- measure sharpness of angle - between given points -- returns 1 if angle opposite to edge C is sharp (less then 90 degrees) -- input: three points (of geography type) -- output: (0 or 1) tinyint -- ============================================================================ -- drop prev version IF EXISTS (   SELECT * FROM sys.objects   WHERE object_id = OBJECT_ID(N'dbo.sfn_is_sharp_angle_triangle_LL')   AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION dbo.sfn_is_sharp_angle_triangle_LL GO CREATE FUNCTION sfn_is_sharp_angle_triangle_LL (@FPoint GEOGRAPHY, @CPoint GEOGRAPHY, @TPoint GEOGRAPHY) RETURNS TINYINT AS BEGIN DECLARE @sharp TINYINT, @A FLOAT, @B FLOAT, @C FLOAT, @sides_ratio FLOAT; DECLARE @short_side_ratio FLOAT, @long_side_ratio FLOAT; SET @sharp = 0; SET @short_side_ratio = 0.29; SET @long_side_ratio = 0.8; -- calculate length of triangle sides -- @C is length of side opposite to point @CPoint SET @A = ROUND(@CPoint.STDistance(@FPoint),6); SET @B = ROUND(@CPoint.STDistance(@TPoint),6); SET @C = ROUND(@FPoint.STDistance(@TPoint),6); -- identify sharpness of the angle (@CPoint) -- [1] (C is shortest side in triangle) IF (@C < @A) AND (@C < @B) BEGIN   IF (@A >= @B)    SET @sides_ratio = (@B / @A);   ELSE    SET @sides_ratio = (@A / @B);   IF (@sides_ratio > 0.7) AND ((@C/(@A+@B)) < @short_side_ratio)   BEGIN    SET @sharp = 1;   END   -- A is longest side in triangle   IF (@sharp = 0) AND (@A >= @B) AND ((@A/(@B + @C)) > @long_side_ratio)   BEGIN    SET @sharp = 1;   END   -- B is longest side in triangle   IF (@sharp = 0) AND (@B > @A) AND ((@B/(@A + @C)) > @long_side_ratio)   BEGIN    SET @sharp = 1;   END END -- [2] (A is longest side in triangle) IF (@sharp = 0) AND (@A > @C) AND (@A > @B) AND (@B <= @C) BEGIN   IF ((@A/(@B + @C)) > 0.85) AND ((@B/@C) BETWEEN 0.5 AND 1)   BEGIN    SET @sharp = 1;v   END   IF (@sharp = 0) AND ((@A/(@B + @C)) > 0.94) AND ((@B/@C) BETWEEN 0.25 AND 0.5)   BEGIN    SET @sharp = 1;   END   IF (@sharp = 0) AND ((@A/(@B + @C)) > 0.99) AND ((@B/@C) BETWEEN 0.001 AND 0.25)   BEGIN    SET @sharp = 1;   END END -- [3] (B is longest side in triangle) IF (@sharp = 0) AND (@B > @C) AND (@B > @C) AND (@A <= @C) BEGIN   IF ((@B/(@A + @C)) > 0.85) AND ((@A/@C) BETWEEN 0.5 AND 1)   BEGIN    SET @sharp = 1;   END   IF (@sharp = 0) AND ((@B/(@A + @C)) > 0.94) AND ((@A/@C) BETWEEN 0.25 AND 0.5)   BEGIN    SET @sharp = 1;   END   IF (@sharp = 0) AND ((@B/(@A + @C)) > 0.99) AND ((@A/@C) BETWEEN 0.001 AND 0.25)   BEGIN    SET @sharp = 1;   END END -- [4] (C is opposite to sharp angle) IF (@sharp = 0) AND (@C < @A) AND (@C > @B) BEGIN   -- (A is longest side in triangle)   IF (@A > @C) AND (@A > @B) AND ((@A/(@B + @C)) > @long_side_ratio)   BEGIN   IF ((@B/@C) BETWEEN 0.65 AND 1) AND ((@A/(@B + @C)) > (@long_side_ratio + 0.6))   BEGIN    SET @sharp = 1;   END   IF ((@B/@C) BETWEEN 0.25 AND 0.65) AND ((@A/(@B + @C)) > 0.96)   BEGIN    SET @sharp = 1;   END   IF ((@B/@C) BETWEEN 0.001 AND 0.25) AND ((@A/(@B + @C)) > 0.99)   BEGIN    SET @sharp = 1;   END   END END IF (@sharp = 0) AND (@C < @B) AND (@C > @A) BEGIN   -- (B is longest side in triangle)   IF (@B > @C) AND (@B > @A) AND ((@B/(@A + @C)) > @long_side_ratio)   BEGIN   IF ((@A/@C) BETWEEN 0.65 AND 1) AND ((@B/(@A + @C)) > (@long_side_ratio + 0.6))   BEGIN    SET @sharp = 1;   END   IF ((@A/@C) BETWEEN 0.25 AND 0.65) AND ((@B/(@A + @C)) > 0.96)   BEGIN    SET @sharp = 1;   END   IF ((@A/@C) BETWEEN 0.001 AND 0.25) AND ((@B/(@A + @C)) > 0.99)   BEGIN    SET @sharp = 1;   END   END END -- returns 1 if angle is sharp RETURN @sharp; END GO

sqlexamples.info