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