SQL Server Techniques

Wednesday, September 4, 2019

Drawing Slice of Pizza by drawing a Sector

Drawing Pie Chart in SSMS using spatial data =>

I was needed to draw a lot of Sectors for my next blog post, but I've never done it before.
So, will draw a slice of Pizza and learn how to draw a Sector.

To draw a sector we need to choose following parameters:

Radius of our circle (@Radius)
X and Y coordinates of our circle's center (@CenterX & @CenterY)
Starting Angle of our sector (@Angle1_Degrees)
Angle of a Sector itself (@Angle_Degrees)

The next step is to transfer angle's values in radians, because SQL Server "Sin" and "Cos" functions does not accept angles in degrees:

Starting Angle of a Sector in Radians (@Angle1_Radians)
Ending Angle of a Sector in Radians (@Angle2_Radians)
Midpoint Angle, which lays right between the first and the second angles (@AngleM_Radians)

We will build a sector by layers.
The first layer is a semicircle built by "CIRCULARSTRING":

DECLARE @Radius DECIMAL = 10;

DECLARE @CenterX DECIMAL = 1;

DECLARE @CenterY DECIMAL = 2;

DECLARE @Angle1_Degrees DECIMAL = 10;

DECLARE @Angle_Degrees DECIMAL = 60;

DECLARE @Angle1_Radians DECIMAL(38,17) = @Angle1_Degrees * 2 * Pi() / 360;

DECLARE @Angle2_Radians DECIMAL(38,17) = (@Angle1_Degrees + @Angle_Degrees) * 2 * Pi() / 360;

DECLARE @AngleM_Radians DECIMAL(38,17) = (@Angle1_Radians + @Angle2_Radians) / 2;

SELECT CAST('CIRCULARSTRING

(' + CAST(@Radius * SIN(@Angle1_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@Angle1_Radians) + @CenterY as VARCHAR) +

', ' + CAST(@Radius * SIN(@AngleM_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@AngleM_Radians) + @CenterY as VARCHAR) +

', ' + CAST(@Radius * SIN(@Angle2_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@Angle2_Radians) + @CenterY as VARCHAR) +

')' as GEOMETRY);

At the second layer we need to add to lines to our semicircle using "COMPOUNDCURVE"

DECLARE @Radius DECIMAL = 10;

DECLARE @CenterX DECIMAL = 1;

DECLARE @CenterY DECIMAL = 2;

DECLARE @Angle1_Degrees DECIMAL = 10;

DECLARE @Angle_Degrees DECIMAL = 60;

DECLARE @Angle1_Radians DECIMAL(38,17) = @Angle1_Degrees * 2 * Pi() / 360;

DECLARE @Angle2_Radians DECIMAL(38,17) = (@Angle1_Degrees + @Angle_Degrees) * 2 * Pi() / 360;

DECLARE @AngleM_Radians DECIMAL(38,17) = (@Angle1_Radians + @Angle2_Radians) / 2;

SELECT CAST('COMPOUNDCURVE(CIRCULARSTRING

(' + CAST(@Radius * SIN(@Angle1_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@Angle1_Radians) + @CenterY as VARCHAR) +

', ' + CAST(@Radius * SIN(@AngleM_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@AngleM_Radians) + @CenterY as VARCHAR) +

', ' + CAST(@Radius * SIN(@Angle2_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@Angle2_Radians) + @CenterY as VARCHAR) +

'),(' + CAST(@Radius * SIN(@Angle2_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@Angle2_Radians) + @CenterY as VARCHAR) +

', ' + CAST(@CenterX as VARCHAR) + ' ' + CAST( @CenterY as VARCHAR) +

', ' + CAST(@Radius * SIN(@Angle1_Radians) + @CenterX as VARCHAR) +

' ' + CAST(@Radius * COS(@Angle1_Radians) + @CenterY as VARCHAR) + '))' as GEOMETRY);

And the last third layer is "CURVEPOLYGON", needed to convert our compounded curve into a polygon:

DECLARE @Radius DECIMAL = 10;

DECLARE @CenterX DECIMAL = 1;

DECLARE @CenterY DECIMAL = 2;

DECLARE @Angle1_Degrees DECIMAL = 10;

DECLARE @Angle_Degrees DECIMAL = 60;

DECLARE @Angle1_Radians DECIMAL(38,17) = @Angle1_Degrees * 2 * Pi() / 360;

DECLARE @Angle2_Radians DECIMAL(38,17) = (@Angle1_Degrees + @Angle_Degrees) * 2 * Pi() / 360;

DECLARE @AngleM_Radians DECIMAL(38,17) = (@Angle1_Radians + @Angle2_Radians) / 2;

SELECT CAST('CURVEPOLYGON(COMPOUNDCURVE(CIRCULARSTRING