## Friday, August 4, 2017

### Drawing spatial 3D Cubes =>

I do not know how to Google right and I couldn't find any blog showing how to draw a spirals using SQL Server spatial data.

So, had to do it myself and sharing it with everybody.

At first, here is the simple spiral
```DECLARE @i INT = 0, @R FLOAT = 0, @Angle FLOAT = 0
DECLARE @x1 FLOAT, @y1 FLOAT, @x2 FLOAT = 0, @y2 FLOAT = 0;
DECLARE @g GEOMETRY = CONVERT(GEOMETRY,'POINT(0 0)');

WHILE @i < 200
BEGIN
SELECT @i += 1, @R += 1
, @x1 = @x2, @y1 = @y2
, @Angle += Pi()/32
, @x2 = COS(@Angle) * @R
, @y2 = SIN(@Angle) * @R

SELECT @g = @g.STUnion(CONVERT(GEOMETRY,'LINESTRING('
+ CAST(@x1 as VARCHAR) + ' ' + CAST(@y1 as VARCHAR) + ','
+ CAST(@x2 as VARCHAR) + ' ' + CAST(@y2 as VARCHAR)
+ ')'))

END
SELECT @g.STBuffer(1)```
In this script you can play with total number of iterations (@i), with increment value of @R or with width of a line (STBuffer), but generally, you will have always the same "Archimedean" type of a spiral.

With simple modification, just by adding second data set with negated numbers we can get double spiral:
```DECLARE @i INT = 0, @R FLOAT = 0, @Angle FLOAT = 0
DECLARE @x1 FLOAT, @y1 FLOAT, @x2 FLOAT = 0, @y2 FLOAT = 0;
DECLARE @g GEOMETRY = CONVERT(GEOMETRY,'POINT(0 0)');

WHILE @i < 200
BEGIN
SELECT @i += 1, @R += 1
, @x1 = @x2, @y1 = @y2
, @Angle += Pi()/32
, @x2 = COS(@Angle) * @R
, @y2 = SIN(@Angle) * @R

SELECT @g = @g.STUnion(CONVERT(GEOMETRY,'LINESTRING('
+ CAST(@x1 as VARCHAR) + ' ' + CAST(@y1 as VARCHAR) + ','
+ CAST(@x2 as VARCHAR) + ' ' + CAST(@y2 as VARCHAR)
+ ')'))
, @g = @g.STUnion(CONVERT(GEOMETRY,'LINESTRING('
+ CAST(-@x1 as VARCHAR) + ' ' + CAST(-@y1 as VARCHAR) + ','
+ CAST(-@x2 as VARCHAR) + ' ' + CAST(-@y2 as VARCHAR)
+ ')'))
END
SELECT @g.STBuffer(1)```
In both examples we increased distance of a line from the center (@R) by the fixed value. However, if we slowly increase the increment (@Inc)  we will get Logarithmic double spiral:
```DECLARE @i INT = 0;
DECLARE @R FLOAT = 0;
DECLARE @Inc FLOAT = 1;
DECLARE @x1 FLOAT = 0, @x2 FLOAT = 0, @y1 FLOAT = 0, @y2 FLOAT = 0;
DECLARE @Angle FLOAT = 0;
DECLARE @g GEOMETRY = CONVERT(GEOMETRY,'POINT(0 0)');

WHILE @i < 100
BEGIN
SELECT @i += 1
, @R += @Inc, @Inc *= 1.02
, @x1 = @x2, @y1 = @y2
, @Angle += Pi()/32
, @x2 = COS(@Angle) * @R
, @y2 = SIN(@Angle) * @R

SELECT @g = @g.STUnion(CONVERT(GEOMETRY,'LINESTRING('
+ CAST(@x1 as VARCHAR) + ' ' + CAST(@y1 as VARCHAR) + ','
+ CAST(@x2 as VARCHAR) + ' ' + CAST(@y2 as VARCHAR)
+ ')'))
, @g = @g.STUnion(CONVERT(GEOMETRY,'LINESTRING('
+ CAST(-@x1 as VARCHAR) + ' ' + CAST(-@y1 as VARCHAR) + ','
+ CAST(-@x2 as VARCHAR) + ' ' + CAST(-@y2 as VARCHAR)
+ ')'))
END
SELECT @g.STBuffer(1);```

I really like this one, it reminds me our "Milky Way" galaxy.

If we slowly decrease the increment we get a convergent spiral:
```DECLARE @i INT = 0;
DECLARE @R FLOAT = 0;
DECLARE @Inc FLOAT = 1;
DECLARE @x1 FLOAT = 0, @x2 FLOAT = 0, @y1 FLOAT = 0, @y2 FLOAT = 0;
DECLARE @Angle FLOAT = 0;
DECLARE @g GEOMETRY = CONVERT(GEOMETRY,'POINT(0 0)');

WHILE @i < 500
BEGIN
SELECT @i += 1
, @R += @Inc, @Inc *= 0.99
, @x1 = @x2, @y1 = @y2
, @Angle += Pi()/32
, @x2 = COS(@Angle) * @R
, @y2 = SIN(@Angle) * @R

SELECT @g = @g.STUnion(CONVERT(GEOMETRY,'LINESTRING('
+ CAST(@x1 as VARCHAR) + ' ' + CAST(@y1 as VARCHAR) + ','
+ CAST(@x2 as VARCHAR) + ' ' + CAST(@y2 as VARCHAR)
+ ')'))
, @g = @g.STUnion(CONVERT(GEOMETRY,'LINESTRING('
+ CAST(-@x1 as VARCHAR) + ' ' + CAST(-@y1 as VARCHAR) + ','
+ CAST(-@x2 as VARCHAR) + ' ' + CAST(-@y2 as VARCHAR)
+ ')'))
END
SELECT @g.STBuffer(0.1);```
Don't make mistake, that is still spiral. Distance between lines gets shorter and shorter and will decrease indefinitely:

If we change radius/distance by a function of square root from @i we get Fermat's spiral:
```DECLARE @i INT = 0;
DECLARE @R FLOAT = 0;
DECLARE @Inc FLOAT = 1;
DECLARE @x1 FLOAT = 0, @x2 FLOAT = 0, @y1 FLOAT = 0, @y2 FLOAT = 0;
DECLARE @Angle FLOAT = 0;
DECLARE @g GEOMETRY = CONVERT(GEOMETRY,'POINT(0 0)');

WHILE @i < 500
BEGIN
SELECT @i += 1
, @R = SQRT(@i)
, @x1 = @x2, @y1 = @y2
, @Angle += Pi()/32
, @x2 = COS(@Angle) * @R
, @y2 = SIN(@Angle) * @R

SELECT @g = @g.STUnion(CONVERT(GEOMETRY,'LINESTRING('
+ CAST(@x1 as VARCHAR) + ' ' + CAST(@y1 as VARCHAR) + ','
+ CAST(@x2 as VARCHAR) + ' ' + CAST(@y2 as VARCHAR)
+ ')'))
END
SELECT @g.STBuffer(0.1);```
Using Fermat's spiral's formula we can draw beautiful sunflower:
```DECLARE @t TABLE (g GEOMETRY);
DECLARE @i INT = 0;

WHILE @i < 1000
BEGIN
SET @i += 1
INSERT INTO @t
SELECT CONVERT(GEOMETRY,'POINT('
+ CAST(COS(@i *@Angle) * SQRT(@i) as VARCHAR) + ' '
+ CAST(SIN(@i *@Angle) * SQRT(@i) as VARCHAR)
+ ')').STBuffer(0.4);
END
SELECT * FROM @t ;```
Another interesting spiral type is Hyperbolic:
```DECLARE @i INT = 0, @R FLOAT = 0, @Angle FLOAT = 0
DECLARE @x1 FLOAT, @y1 FLOAT, @x2 FLOAT = 0, @y2 FLOAT = 0;
DECLARE @g GEOMETRY = CONVERT(GEOMETRY,'POINT(0 0)');

WHILE @i < 200
BEGIN
SELECT @i += 1, @R += 1
, @x1 = @x2, @y1 = @y2
, @Angle += Pi()/16
, @x2 = COS(@Angle) / @Angle
, @y2 = SIN(@Angle) / @Angle

If @x1 != 0
SELECT @g = @g.STUnion(CONVERT(GEOMETRY,'LINESTRING('
+ CAST(@x1 as VARCHAR) + ' ' + CAST(@y1 as VARCHAR) + ','
+ CAST(@x2 as VARCHAR) + ' ' + CAST(@y2 as VARCHAR)
+ ')'))

END
SELECT @g.STBuffer(0.001);```
The most complicated is famous Fibonacci Golden Spiral:
```DECLARE @t table(g geometry, id int identity(1,1))
DECLARE @i INT = 0, @R FLOAT = SQRT(2)/2;
DECLARE @x1 FLOAT, @y1 FLOAT, @x2 FLOAT = 0, @y2 FLOAT = 0;
DECLARE @x3 FLOAT, @y3 FLOAT
DECLARE @N1 INT = 0, @N2 INT = 1, @S INT;

WHILE @i < 21
BEGIN
SELECT @i += 1
, @x1 = @x2, @y1 = @y2
, @x2 = @x1 + @N2 * CASE WHEN @i%4 in (0,1) THEN 1 ELSE -1 END
, @y2 = @y1 + @N2 * CASE WHEN @i%4 in (1,2) THEN 1 ELSE -1 END
, @x3 = @x1 + CASE @i%4
WHEN 1 THEN @N2 * @R
WHEN 2 THEN @N2 * (@R-1)
WHEN 3 THEN @N2 * -@R
ELSE @N2 * (1-@R) END
, @y3 = @y1 + CASE @i%4
WHEN 1 THEN @N2 * (1-@R)
WHEN 2 THEN @N2 * @R
WHEN 3 THEN @N2 * (@R-1)
ELSE @N2 * (-@R) END
, @S = @N1 + @N2, @N1 = @N2, @N2 = @S

INSERT INTO @t
SELECT CONVERT(GEOMETRY,'CIRCULARSTRING('
+ CAST(@x1 as VARCHAR) + ' ' + CAST(@y1 as VARCHAR) + ','
+ CAST(@x3 as VARCHAR) + ' ' + CAST(@y3 as VARCHAR) + ','
+ CAST(@x2 as VARCHAR) + ' ' + CAST(@y2 as VARCHAR) + ')').STBuffer(10)
END
SELECT * FROM @t;```

If you need to know more about Spatial Data in SQL Server then read BOL.