Drawing spirals with spatial data.

<= Databasae Fragmentation Report

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;
DECLARE @Angle FLOAT = Radians(137.5);

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.
If you need to know more about Spirals then read Wikipedia.