Tuesday, August 8, 2017

Drawing SQL Sphere

<= 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*/
@r FLOAT = 1, /*Radius*/
@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.