## Tuesday, August 8, 2017

### <= Drawing 3D Cube

Couple of years ago I came up with an algorithm of drawing an ellipse using SQL Server spatial geometry: http://slavasql.blogspot.com/2015/02/drawing-ellipse-in-ssms.html

I've used that algorithm to make a sphere and as in my previous blog of drawing 3D Cube I use external procedure to simplify the process.
This time instead of temporary stored procedure I'm using a function to generate Geometrical content.
Here is the function's code:
```SET NOCOUNT ON
GO
USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.Elipse') IS NOT NULL
DROP FUNCTION dbo.Elipse
GO
CREATE FUNCTION dbo.Elipse(
@x FLOAT = 0,   -- x coordinate of center
@y FLOAT = 0,   -- y coordinate of center
@rh FLOAT = 1, -- Horizontal radius
@rv FLOAT = 0, -- Vertical Radius
@as FLOAT = 0, -- Starting angle
@af FLOAT = 0 -- Finishing angle
) RETURNS GEOMETRY AS
BEGIN
SELECT @af = CASE @af WHEN 0 THEN 2*Pi() ELSE @af END
, @rv = CASE @rv WHEN 0 THEN @rh ELSE @rv END

DECLARE @MP VARCHAR(MAX)='';
-- angle that will be increased each loop
DECLARE @theta FLOAT = @as;
-- amount to add to theta each time (degrees)
DECLARE @step FLOAT = 2 * PI()/1000;
DECLARE @x1 FLOAT, @y1 FLOAT
, @x2 FLOAT = @x + @rh * SIN(@theta)
, @y2 FLOAT = @y + @rv * COS(@theta);

WHILE @theta <= @af
SELECT
@x1 = @x2, @y1 = @y2,
@x2 = @x + @rh * SIN(@theta),
@y2 = @y + @rv * COS(@theta),
@theta += @step,
@MP = @MP + '('
+ CAST(@x1 as VARCHAR) +  ' '
+ CAST(@y1 as VARCHAR) + ','
+ CAST(@x2 as VARCHAR) +  ' '
+ CAST(@y2 as VARCHAR) + '),';

RETURN (CONVERT(GEOMETRY,'MULTILINESTRING('
+ LEFT(@MP,LEN(@MP)-1) + ')')).MakeValid();
END
GO```

The easiest way to use that function is to run a SELECT statement of it with some parameters:
`SELECT tempdb.dbo.Elipse (10, 10, 1, .5, default, default).STBuffer(0.002);`
If you decide to not specify the fourth parameter (@rv - Vertical Radius) you'll get a perfect circle!
`SELECT tempdb.dbo.Elipse (0,0,1,default,default,default).STBuffer(0.005);`
If you will specify starting and finishing angles you can get a part of an ellipse like this:
`SELECT tempdb.dbo.Elipse (10,10,1,0.5,Pi()/4,5*Pi()/4).STBuffer(0.01);`
And finally, if you combine several ellipses into one picture you can get a sphere:
```/*
Make sphere with Axial tilt 23.437%
*/
DECLARE @d FLOAT = RADIANS(23.437), /*Axial tilt*/
@LR FLOAT = Pi()/4, /* Rotation LEft-Right*/
@x FLOAT = 0, /*Coordinate X*/
@y FLOAT = 0, /*Coordinate Y*/
@w FLOAT = 0.001  /*Line width*/

DECLARE @g TABLE (g GEOMETRY);

-- Main circle
INSERT INTO @g
SELECT tempdb.dbo.Elipse (@x, @y, @r, default, default, default )

-- North Pole
INSERT INTO @g SELECT CONVERT(GEOMETRY,'POINT('
+CAST(@x as VARCHAR)+' '+CAST(@y + @r * COS(@d) as VARCHAR)
+')').STBuffer(@w)

-- Draw Equator:
INSERT INTO @g
SELECT tempdb.dbo.Elipse (@x, @y, @r, @r * SIN(@d), Pi()/2, 3*Pi()/2)

-- Draw 30 degrees north:
INSERT INTO @g
SELECT tempdb.dbo.Elipse (
@x, @y + @r * COS(Pi()/6)*COS(@d),
@r*SIN(Pi()/6),
@r * COS(Pi()/6)*COS(@d) - @r * COS(Pi()/6+@d),
ACOS( TAN(@d) / TAN(Pi()/6) )
, 2*Pi()-ACOS( TAN(@d) / TAN(Pi()/6)  ))

-- Draw 60 degrees north:
INSERT INTO @g
SELECT tempdb.dbo.Elipse (
@x, @y + @r * COS(Pi()/3)*COS(@d),
@r*SIN(Pi()/3),
@r * COS(Pi()/3)*COS(@d) - @r * COS(Pi()/3+@d),
ACOS( TAN(@d) / TAN(Pi()/3) )
, 2*Pi()-ACOS( TAN(@d) / TAN(Pi()/3) ) )

-- Draw 60 degrees South:
INSERT INTO @g
SELECT tempdb.dbo.Elipse (
@x, @y - @r * COS(Pi()/3)*COS(@d),
@r*SIN(Pi()/3),
@r * COS(Pi()/3)*COS(@d) - @r * COS(Pi()/3+@d),
Pi()-ACOS( TAN(@d) / TAN(Pi()/3) )
, Pi()+ACOS( TAN(@d) / TAN(Pi()/3) ) )

-- Draw 30 degrees south:
INSERT INTO @g
SELECT tempdb.dbo.Elipse (
@x, @y - @r * COS(Pi()/6)*COS(@d),
@r*SIN(Pi()/6),
@r * COS(Pi()/6)*COS(@d) - @r * COS(Pi()/6+@d),
Pi()-ACOS( TAN(@d) / TAN(Pi()/6) )
, Pi()+ACOS( TAN(@d) / TAN(Pi()/6)  ))

SELECT g.STBuffer(0.005) FROM @g
GO```

That sphere is supposed to represent the Earth looking at the Sun at Summer Solstice.