Tuesday, September 29, 2015

Draw American flag in SSMS using Spatial methods. Step by Step.

Yep. That is possible using SQL Server Spatial functionality.
In my few prior posts I already uncovered some Spatial drawing abilities, but now I want to draw real picture.

Let's STARt from drawing stars!


1. Draw a star.


1.1. Start in the center of a star.

   - Center will be coordinates x=0, y=0
   - Circle radius = 10

1.2. Figure out ending point of one bottom ray:

   - Angle between rays is 360 / 5 = 72 degrees.
   - To calc coordinates we need a half: 36 degrees.
   - Define coordinates of ending point of the ray as
     X = radius x SIN(36)
     Y = radius x COS(36)
   - To use SIN & COS have to use "RADIANS" function.
   - RADIANS function does not accept integer!
   "SELECT X = 100 * SIN(RADIANS(36.)), Y = - 100 * COS(RADIANS(36.))"
  
   - Middle ray angle is 90 - 72 = 18 Degrees
     X = radius x COS(18)
     Y = radius x SIN(18)

   - Top ray just has coordinates
     X = 0
     Y = radius
Here is what we get:
SELECT CAST('LINESTRING(0 0, 0 10)' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0,' + CONVERT(VARCHAR, 10 * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, -10 * COS(RADIANS(36.))) + ')' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0,' + CONVERT(VARCHAR, -10 * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, -10 * COS(RADIANS(36.))) + ')' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0,' + CONVERT(VARCHAR, 10 * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, 10 * SIN(RADIANS(18.))) + ')' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0,' + CONVERT(VARCHAR, -10 * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, 10 * SIN(RADIANS(18.))) + ')' as geometry);
And here is a result:
Not a star yet, just a skeleton.

1.3. Will connect the rays:

SELECT CAST('LINESTRING(0 10, '
+ CONVERT(VARCHAR, 10 * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, -10 * COS(RADIANS(36.))) + ','
+ CONVERT(VARCHAR, -10 * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, 10 * SIN(RADIANS(18.))) + ','
+ CONVERT(VARCHAR, 10 * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, 10 * SIN(RADIANS(18.))) + ','
+ CONVERT(VARCHAR, -10 * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, -10 * COS(RADIANS(36.))) + ','
+ '0 10)' as geometry);
That is more like a star:

1.4. Pretty, but we do not need internal lines.

Lets calculate middle Ray's bottom points:
    X = radius * SIN(18) * TAN(36)
    Y = radius * SIN(18)
Here are coordinartes of lover Ray's bottom points:
    X = radius * SIN(18) * TAN(36)
    Y = radius * SIN(18)

Also, to simplify our select, replace formulas by variables:
DECLARE @r FLOAT = 10;
DECLARE @rad18 FLOAT = RADIANS(18.);
DECLARE @rad36 FLOAT = RADIANS(36.);
DECLARE @SIN18 FLOAT = SIN(@rad18);
DECLARE @COS18 FLOAT = COS(@rad18);
DECLARE @SIN36 FLOAT = SIN(@rad36);
DECLARE @COS36 FLOAT = COS(@rad36);
DECLARE @TAN36 FLOAT = TAN(@rad36);

SELECT CAST('LINESTRING(0 0, 0 10)' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0,' + CONVERT(VARCHAR, @r * @SIN36) + ' ' + CONVERT(VARCHAR, -@r * @COS36) + ')' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0,' + CONVERT(VARCHAR, -@r * @SIN36) + ' ' + CONVERT(VARCHAR, -@r * @COS36) + ')' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0,' + CONVERT(VARCHAR, @r * @COS18) + ' ' + CONVERT(VARCHAR, @r * @SIN18) + ')' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0,' + CONVERT(VARCHAR, -@r * @COS18) + ' ' + CONVERT(VARCHAR, @r * @SIN18) + ')' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0,' + CONVERT(VARCHAR, @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @r * @SIN18 ) + ')' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0,' + CONVERT(VARCHAR, -@r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @r * @SIN18 ) + ')' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0,' + CONVERT(VARCHAR, @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, -@r * @SIN18 * @SIN18 / @COS36 ) + ')' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0,' + CONVERT(VARCHAR, -@r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, -@r * @SIN18 * @SIN18 / @COS36 ) + ')' as geometry)
UNION ALL
SELECT CAST('LINESTRING(0 0, 0 ' + CONVERT(VARCHAR, -@r * @SIN18 / @COS36 ) + ')' as geometry);
Again bunch of rays:

1.5. Will connect rays ones again:

DECLARE @r FLOAT = 10;
DECLARE @rad18 FLOAT = RADIANS(18.);
DECLARE @rad36 FLOAT = RADIANS(36.);
DECLARE @SIN18 FLOAT = SIN(@rad18);
DECLARE @COS18 FLOAT = COS(@rad18);
DECLARE @SIN36 FLOAT = SIN(@rad36);
DECLARE @COS36 FLOAT = COS(@rad36);
DECLARE @TAN36 FLOAT = TAN(@rad36);

SELECT CAST('LINESTRING(0 10, '
+ CONVERT(VARCHAR, @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @r * @SIN18 ) + ','
+ CONVERT(VARCHAR, @r * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, @r * SIN(RADIANS(18.))) + ','
+ CONVERT(VARCHAR, @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, -@r * @SIN18 * @SIN18 / @COS36 ) + ','
+ CONVERT(VARCHAR, @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, -@r * COS(RADIANS(36.))) + ','
+ '0 ' + CONVERT(VARCHAR, -@r * @SIN18 / @COS36 ) + ','
+ CONVERT(VARCHAR, -@r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, -@r * COS(RADIANS(36.))) + ','
+ CONVERT(VARCHAR, -@r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, -@r * @SIN18 * @SIN18 / @COS36 ) + ','
+ CONVERT(VARCHAR, -@r * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, @r * SIN(RADIANS(18.))) + ','
+ CONVERT(VARCHAR, -@r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @r * @SIN18 ) + ','
+ '0 10)' as geometry);
Now we get a real star:

1.6. Very nice star and we will learn how to move the star on our field:


Will add extra coordinates X and Y
Now we can place our star wherever we want:
DECLARE @r FLOAT = 10;
DECLARE @rad18 FLOAT = RADIANS(18.);
DECLARE @rad36 FLOAT = RADIANS(36.);
DECLARE @SIN18 FLOAT = SIN(@rad18);
DECLARE @COS18 FLOAT = COS(@rad18);
DECLARE @SIN36 FLOAT = SIN(@rad36);
DECLARE @COS36 FLOAT = COS(@rad36);
DECLARE @TAN36 FLOAT = TAN(@rad36);

DECLARE @x FLOAT = 50;
DECLARE @y FLOAT = 50;

SELECT CAST('LINESTRING('
+ CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r) + ','
+ CONVERT(VARCHAR, @x + @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
+ CONVERT(VARCHAR, @x + @r * COS(RADIANS(18.)) ) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
+ CONVERT(VARCHAR, @x + @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
+ CONVERT(VARCHAR, @x + @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
+ CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 / @COS36 ) + ','
+ CONVERT(VARCHAR, @x - @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
+ CONVERT(VARCHAR, @x - @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
+ CONVERT(VARCHAR, @x - @r * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
+ CONVERT(VARCHAR, @x - @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
+ CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r)
+ ')' AS GEOMETRY);

1.7. To color our star we have to build a Polygon   

MOST IMPORTANT: Polygon line must end at the same point where it started.
DECLARE @r FLOAT = 10;
DECLARE @rad18 FLOAT = RADIANS(18.);
DECLARE @rad36 FLOAT = RADIANS(36.);
DECLARE @SIN18 FLOAT = SIN(@rad18);
DECLARE @COS18 FLOAT = COS(@rad18);
DECLARE @SIN36 FLOAT = SIN(@rad36);
DECLARE @COS36 FLOAT = COS(@rad36);
DECLARE @TAN36 FLOAT = TAN(@rad36);

DECLARE @x FLOAT = 50;
DECLARE @y FLOAT = 50;

SELECT CAST('POLYGON(('
+ CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r) + ','
+ CONVERT(VARCHAR, @x + @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
+ CONVERT(VARCHAR, @x + @r * COS(RADIANS(18.)) ) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
+ CONVERT(VARCHAR, @x + @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
+ CONVERT(VARCHAR, @x + @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
+ CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 / @COS36 ) + ','
+ CONVERT(VARCHAR, @x - @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
+ CONVERT(VARCHAR, @x - @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
+ CONVERT(VARCHAR, @x - @r * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
+ CONVERT(VARCHAR, @x - @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
+ CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r)
+ '))' AS GEOMETRY);
Now star is colored:

1.8. And now we will make all 50 stars at once:

DECLARE @rad18 FLOAT = RADIANS(18.);
DECLARE @rad36 FLOAT = RADIANS(36.);
DECLARE @SIN18 FLOAT = SIN(@rad18);
DECLARE @COS18 FLOAT = COS(@rad18);
DECLARE @SIN36 FLOAT = SIN(@rad36);
DECLARE @COS36 FLOAT = COS(@rad36);
DECLARE @TAN36 FLOAT = TAN(@rad36);

DECLARE @x FLOAT;
DECLARE @y FLOAT;

DECLARE @r FLOAT = 6.16/2; -- Star Radius
DECLARE @hd FLOAT = 6.3; -- Horisontal distance between stars
DECLARE @vd FLOAT = 5.4; -- Vertical distance between stars

DECLARE @h TINYINT = 0, @v TINYINT = 0; -- Horisontal & Vertical Star counters

DECLARE @Stars TABLE(Star GEOMETRY);

WHILE @h < 11
BEGIN
SET @h += 1;
WHILE @v < 9
BEGIN
    SET @v += 2;
    SELECT
        @x = @hd * (@h + (@v + 1) % 2),
        @y = @vd * (@v + (@h + 1) % 2) * (-1);
    INSERT INTO @Stars(Star)
    SELECT CAST('POLYGON(('
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r) + ','
    + CONVERT(VARCHAR, @x + @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
    + CONVERT(VARCHAR, @x + @r * COS(RADIANS(18.)) ) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
    + CONVERT(VARCHAR, @x + @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x + @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x - @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
    + CONVERT(VARCHAR, @x - @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x - @r * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
    + CONVERT(VARCHAR, @x - @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r)
    + '))' AS GEOMETRY);

    IF @v = 8 and @h % 2 = 0 SET @v = 9;
END
SET @v = 0;
END

SELECT * FROM @Stars;
Here are our 50 stars:

1.9. Let's make all 50 stars as one POLYGON:

DECLARE @rad18 FLOAT = RADIANS(18.);
DECLARE @rad36 FLOAT = RADIANS(36.);
DECLARE @SIN18 FLOAT = SIN(@rad18);
DECLARE @COS18 FLOAT = COS(@rad18);
DECLARE @SIN36 FLOAT = SIN(@rad36);
DECLARE @COS36 FLOAT = COS(@rad36);
DECLARE @TAN36 FLOAT = TAN(@rad36);

DECLARE @x FLOAT;
DECLARE @y FLOAT;

DECLARE @r FLOAT = 6.16/2; -- Star Radius
DECLARE @hd FLOAT = 6.3; -- Horisontal distance between stars
DECLARE @vd FLOAT = 5.4; -- Vertical distance between stars

DECLARE @h TINYINT = 0, @v TINYINT = 0; -- Horisontal & Vertical Star counters

DECLARE @StarPolygon VARCHAR(MAX) = '';

WHILE @h < 11
BEGIN
SET @h += 1;
WHILE @v < 9
BEGIN
    SET @v += 2;
    SELECT
        @x = @hd * (@h - 1 + (@v + 1) % 2),
        @y = @vd * (@v - 1 + (@h + 1) % 2) * (-1);

    SET @StarPolygon += ',('
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r) + ','
    + CONVERT(VARCHAR, @x + @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
    + CONVERT(VARCHAR, @x + @r * COS(RADIANS(18.)) ) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
    + CONVERT(VARCHAR, @x + @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x + @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x - @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
    + CONVERT(VARCHAR, @x - @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x - @r * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
    + CONVERT(VARCHAR, @x - @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r)
    + ')';

    IF @v = 8 and @h % 2 = 0 SET @v = 9;
END
SET @v = 0;
END

SELECT CAST('POLYGON(' + SUBSTRING(@StarPolygon, 2, @@TEXTSIZE) + ')' AS GEOMETRY);
Here are all stars as one POLYGON:

1.10. Final step with stars is putting them in a box:

DECLARE @rad18 FLOAT = RADIANS(18.);
DECLARE @rad36 FLOAT = RADIANS(36.);
DECLARE @SIN18 FLOAT = SIN(@rad18);
DECLARE @COS18 FLOAT = COS(@rad18);
DECLARE @SIN36 FLOAT = SIN(@rad36);
DECLARE @COS36 FLOAT = COS(@rad36);
DECLARE @TAN36 FLOAT = TAN(@rad36);

DECLARE @x FLOAT;
DECLARE @y FLOAT;

DECLARE @r FLOAT = 6.16/2; -- Star Radius
DECLARE @hd FLOAT = 6.3; -- Horisontal distance between stars
DECLARE @vd FLOAT = 5.4; -- Vertical distance between stars

DECLARE @h TINYINT = 0, @v TINYINT = 0; -- Horisontal & Vertical Star counters

DECLARE @StarPolygon VARCHAR(MAX) = '';
-- DECLARE @Stars TABLE(Star GEOMETRY);

WHILE @h < 11
BEGIN
SET @h += 1;
WHILE @v < 9
BEGIN
    SET @v += 2;
    SELECT
        @x = @hd * (@h - 1 + (@v + 1) % 2),
        @y = @vd * (@v - 1 + (@h + 1) % 2) * (-1);

    SET @StarPolygon += ',('
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r) + ','
    + CONVERT(VARCHAR, @x + @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
    + CONVERT(VARCHAR, @x + @r * COS(RADIANS(18.)) ) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
    + CONVERT(VARCHAR, @x + @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x + @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x - @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
    + CONVERT(VARCHAR, @x - @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x - @r * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
    + CONVERT(VARCHAR, @x - @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r)
    + ')';

    IF @v = 8 and @h % 2 = 0 SET @v = 9;
END
SET @v = 0;
END

SELECT CAST('POLYGON('
+ '(0 0,0 ' + CONVERT(VARCHAR, @vd * -10)
+ ',' + CONVERT(VARCHAR, @hd * 12) + ' ' + CONVERT(VARCHAR, @vd * -10)
+ ',' + CONVERT(VARCHAR, @hd * 12) + ' 0,0 0),'
+ SUBSTRING(@StarPolygon, 2, @@TEXTSIZE) + ')' AS GEOMETRY);
We are done with most difficult part of the American Flag:

2. Stripes.

2.1. Stripes are pretty easy:

DECLARE @A FLOAT = 100; -- Flag Width
DECLARE @B FLOAT = 190; -- Flag Length
DECLARE @UA FLOAT = @A * 7 / 13; -- UNION Length
DECLARE @UB FLOAT = 76; -- UNION Length
DECLARE @s TINYINT = 1; -- Stripe Count
DECLARE @StripePolygon VARCHAR(MAX) = '';

WHILE @s <= 13
BEGIN
SELECT @StripePolygon += ',('
+ CASE WHEN @s < 9 THEN CONVERT(VARCHAR, @UB) ELSE '0' END + ' '
+ CONVERT(VARCHAR, (@s - 1) * @A / -13) + ','

+ CASE WHEN @s < 9 THEN CONVERT(VARCHAR, @UB) ELSE '0' END + ' '
+ CONVERT(VARCHAR, @s * @A / -13) + ','

+ CONVERT(VARCHAR, @B) + ' '
+ CONVERT(VARCHAR, @s * @A / -13) + ','

+ CONVERT(VARCHAR, @B) + ' '
+ CONVERT(VARCHAR, (@s - 1) * @A / -13) + ','

+ CASE WHEN @s < 9 THEN CONVERT(VARCHAR, @UB) ELSE '0' END + ' '
+ CONVERT(VARCHAR, (@s - 1) * @A / -13) + ')', @s += 2;
END

SELECT CAST('POLYGON(' + SUBSTRING(@StripePolygon, 2, @@TEXTSIZE) + ')' AS GEOMETRY);
Here they are.Remind me flag of Greece.

2.2. Will finish with stripes by framing them:

DECLARE @A FLOAT = 100; -- Flag Width
DECLARE @B FLOAT = 190; -- Flag Length
DECLARE @UA FLOAT = @A * 7 / 13; -- UNION Length
DECLARE @UB FLOAT = 76; -- UNION Length
DECLARE @s TINYINT = 1; -- Stripe Count
DECLARE @StripePolygon VARCHAR(MAX) = '';

WHILE @s <= 13
BEGIN
SELECT @StripePolygon += ',('
+ CASE WHEN @s < 9 THEN CONVERT(VARCHAR, @UB) ELSE '0' END + ' '
+ CONVERT(VARCHAR, (@s - 1) * @A / -13) + ','

+ CASE WHEN @s < 9 THEN CONVERT(VARCHAR, @UB) ELSE '0' END + ' '
+ CONVERT(VARCHAR, @s * @A / -13) + ','

+ CONVERT(VARCHAR, @B) + ' '
+ CONVERT(VARCHAR, @s * @A / -13) + ','

+ CONVERT(VARCHAR, @B) + ' '
+ CONVERT(VARCHAR, (@s - 1) * @A / -13) + ','

+ CASE WHEN @s < 9 THEN CONVERT(VARCHAR, @UB) ELSE '0' END + ' '
+ CONVERT(VARCHAR, (@s - 1) * @A / -13) + ')', @s += 2;
END

SELECT CAST('POLYGON((0 0, 0 '
+ CONVERT(VARCHAR, -@A) + ', '
+ CONVERT(VARCHAR, @B) + ' ' + CONVERT(VARCHAR, -@A) + ', '
+ CONVERT(VARCHAR, @B) + ' 0,0 0),'
+ '(0.1 -0.1,0.1 '
+ CONVERT(VARCHAR, 0.1 - @A ) + ', '
+ CONVERT(VARCHAR, @B - 0.1) + ' ' + CONVERT(VARCHAR, 0.1 - @A) + ', '
+ CONVERT(VARCHAR, @B - 0.1) + ' -0.1,0.1 -0.1)'
+ ')' AS GEOMETRY)
UNION ALL
SELECT CAST('POLYGON(' + SUBSTRING(@StripePolygon, 2, @@TEXTSIZE) + ')' AS GEOMETRY);
Here how they look in frame:

3. Now will combine Stars and Stripes:

DECLARE @A FLOAT = 100; -- Flag Width
DECLARE @B FLOAT = 190; -- Flag Length
DECLARE @UA FLOAT = @A * 7 / 13; -- UNION Length
DECLARE @UB FLOAT = 76; -- UNION Length
DECLARE @s TINYINT = 1; -- Stripe Count

DECLARE @StripePolygon VARCHAR(MAX) = '';
DECLARE @r FLOAT = 6.16/2; -- Star Radius
DECLARE @hd FLOAT = @UB/12; -- Horisontal distance between stars
DECLARE @vd FLOAT = 5.4; -- Vertical distance between stars

DECLARE @h TINYINT = 0, @v TINYINT = 0; -- Horisontal & Vertical Star counters
DECLARE @x FLOAT;
DECLARE @y FLOAT;

DECLARE @rad18 FLOAT = RADIANS(18.);
DECLARE @rad36 FLOAT = RADIANS(36.);
DECLARE @SIN18 FLOAT = SIN(@rad18);
DECLARE @COS18 FLOAT = COS(@rad18);
DECLARE @SIN36 FLOAT = SIN(@rad36);
DECLARE @COS36 FLOAT = COS(@rad36);
DECLARE @TAN36 FLOAT = TAN(@rad36);

DECLARE @StarPolygon VARCHAR(MAX) = '';

DECLARE @Flag TABLE(Polygon GEOMETRY, ID INT IDENTITY(1,1))

WHILE @h < 11
BEGIN
SET @h += 1;
WHILE @v < 9
BEGIN
    SET @v += 2;
    SELECT
        @x = @hd * (@h - 1 + (@v + 1) % 2),
        @y = @vd * (@v - 1 + (@h + 1) % 2) * (-1);

    SET @StarPolygon += ',('
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r) + ','
    + CONVERT(VARCHAR, @x + @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
    + CONVERT(VARCHAR, @x + @r * COS(RADIANS(18.)) ) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
    + CONVERT(VARCHAR, @x + @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x + @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x - @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
    + CONVERT(VARCHAR, @x - @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x - @r * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
    + CONVERT(VARCHAR, @x - @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r)
    + ')';

    IF @v = 8 and @h % 2 = 0 SET @v = 9;
END
SET @v = 0;
END

INSERT INTO @Flag(Polygon)
SELECT CAST('POLYGON('
+ '(0 0,0 ' + CONVERT(VARCHAR, @vd * -10)
+ ',' + CONVERT(VARCHAR, @hd * 12) + ' ' + CONVERT(VARCHAR, @vd * -10)
+ ',' + CONVERT(VARCHAR, @hd * 12) + ' 0,0 0),'
+ SUBSTRING(@StarPolygon, 2, @@TEXTSIZE) + ')' AS GEOMETRY);

WHILE @s <= 13
BEGIN
SELECT @StripePolygon += ',('
+ CASE WHEN @s < 9 THEN CONVERT(VARCHAR, @UB) ELSE '0' END + ' '
+ CONVERT(VARCHAR, (@s - 1) * @A / -13) + ','

+ CASE WHEN @s < 9 THEN CONVERT(VARCHAR, @UB) ELSE '0' END + ' '
+ CONVERT(VARCHAR, @s * @A / -13) + ','

+ CONVERT(VARCHAR, @B) + ' '
+ CONVERT(VARCHAR, @s * @A / -13) + ','

+ CONVERT(VARCHAR, @B) + ' '
+ CONVERT(VARCHAR, (@s - 1) * @A / -13) + ','

+ CASE WHEN @s < 9 THEN CONVERT(VARCHAR, @UB) ELSE '0' END + ' '
+ CONVERT(VARCHAR, (@s - 1) * @A / -13) + ')', @s += 2;
END

INSERT INTO @Flag(Polygon)
SELECT CAST('POLYGON((0 0, 0 '
+ CONVERT(VARCHAR, -@A) + ', '
+ CONVERT(VARCHAR, @B) + ' ' + CONVERT(VARCHAR, -@A) + ', '
+ CONVERT(VARCHAR, @B) + ' 0,0 0),'
+ '(0.1 -0.1,0.1 '
+ CONVERT(VARCHAR, 0.1 - @A ) + ', '
+ CONVERT(VARCHAR, @B - 0.1) + ' ' + CONVERT(VARCHAR, 0.1 - @A) + ', '
+ CONVERT(VARCHAR, @B - 0.1) + ' -0.1,0.1 -0.1)'
+ ')' AS GEOMETRY)

INSERT INTO @Flag(Polygon)
SELECT CAST('POLYGON(' + SUBSTRING(@StripePolygon, 2, @@TEXTSIZE) + ')' AS GEOMETRY);

SELECT * FROM @Flag;
Picture is good, but colors are ugly:

4. Coloring. Very important step.

As you noticed, on all prior pics all colors are random. That is because we CAN'T control them!
However, even though they are random, but they are random in the certain order and we can use that order for our needs.

Let's Look at colors we  can use:
DECLARE @SSMSColourPalette table (id INT IDENTITY(1,1), geom geometry)

DECLARE @x INT = 0, @y INT = 0;
WHILE @y < 100
BEGIN
WHILE @x < 20
BEGIN
INSERT INTO @SSMSColourPalette VALUES(
'POLYGON((' + CAST(@x AS VARCHAR(32)) + ' ' + CAST(@y AS VARCHAR(32)) + ','
+ CAST(@x + 1 AS VARCHAR(32)) + ' ' + CAST(@y AS VARCHAR(32)) + ','
+ CAST(@x + 1 AS VARCHAR(32)) + ' ' + CAST(@y + 1 AS VARCHAR(32)) + ','
+ CAST(@x AS VARCHAR(32)) + ' ' + CAST(@y + 1 AS VARCHAR(32)) + ','
+ CAST(@x AS VARCHAR(32)) + ' ' + CAST(@y AS VARCHAR(32)) + '))'
)
SET @x = @x + 1;
END
SET @x = 0;
SET @y = @y + 1;
END

SELECT * FROM @SSMSColourPalette
ORDER BY id;
Here are the firs couple of hundred colors fro selected 2000:
In order to get necessary color we have to do draw some empty objects, which will hold colors we want to skip using following script:

SELECT TOP 140
CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' AS GEOMETRY)  
FROM sys.messages;


5. And Now our Final Step and the Final script of drawing the Flag:


GO

DECLARE @A FLOAT = 100; -- Flag Width
DECLARE @B FLOAT = 190; -- Flag Length
DECLARE @UA FLOAT = @A * 7 / 13; -- UNION Length
DECLARE @UB FLOAT = 76; -- UNION Length
DECLARE @s TINYINT = 1; -- Stripe Count

DECLARE @StripePolygon VARCHAR(MAX) = '';
DECLARE @r FLOAT = 6.16/2; -- Star Radius
DECLARE @hd FLOAT = @UB/12; -- Horisontal distance between stars
DECLARE @vd FLOAT = 5.4; -- Vertical distance between stars

DECLARE @h TINYINT = 0, @v TINYINT = 0; -- Horisontal & Vertical Star counters
DECLARE @x FLOAT;
DECLARE @y FLOAT;

DECLARE @rad18 FLOAT = RADIANS(18.);
DECLARE @rad36 FLOAT = RADIANS(36.);
DECLARE @SIN18 FLOAT = SIN(@rad18);
DECLARE @COS18 FLOAT = COS(@rad18);
DECLARE @SIN36 FLOAT = SIN(@rad36);
DECLARE @COS36 FLOAT = COS(@rad36);
DECLARE @TAN36 FLOAT = TAN(@rad36);

DECLARE @StarPolygon VARCHAR(MAX) = '';

DECLARE @Flag TABLE(Polygon GEOMETRY, ID INT IDENTITY(1,1))

-- Building SET of 50 stars
WHILE @h < 11
BEGIN
SET @h += 1;
WHILE @v < 9
BEGIN
    SET @v += 2;
    SELECT
        @x = @hd * (@h - 1 + (@v + 1) % 2),
        @y = @vd * (@v - 1 + (@h + 1) % 2) * (-1);

    -- Building Individual star
    SET @StarPolygon += ',('
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r) + ','
    + CONVERT(VARCHAR, @x + @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
    + CONVERT(VARCHAR, @x + @r * COS(RADIANS(18.)) ) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
    + CONVERT(VARCHAR, @x + @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x + @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x - @r * SIN(RADIANS(36.))) + ' ' + CONVERT(VARCHAR, @y - @r * COS(RADIANS(36.))) + ','
    + CONVERT(VARCHAR, @x - @r * @SIN18 * @COS18 / @COS36 ) + ' ' + CONVERT(VARCHAR, @y - @r * @SIN18 * @SIN18 / @COS36 ) + ','
    + CONVERT(VARCHAR, @x - @r * COS(RADIANS(18.))) + ' ' + CONVERT(VARCHAR, @y + @r * SIN(RADIANS(18.))) + ','
    + CONVERT(VARCHAR, @x - @r * @SIN18 * @TAN36 ) + ' ' + CONVERT(VARCHAR, @y + @r * @SIN18 ) + ','
    + CONVERT(VARCHAR, @x) + ' ' + CONVERT(VARCHAR, @y + @r)
    + ')';

    IF @v = 8 and @h % 2 = 0 SET @v = 9;
END
SET @v = 0;
END

-- Building SET of 13 stripes
WHILE @s <= 13
BEGIN
-- Building Individual stripe
SELECT @StripePolygon += ',('
+ CASE WHEN @s < 9 THEN CONVERT(VARCHAR, @UB) ELSE '0' END + ' ' + CONVERT(VARCHAR, (@s - 1) * @A / -13) + ','
+ CASE WHEN @s < 9 THEN CONVERT(VARCHAR, @UB) ELSE '0' END + ' ' + CONVERT(VARCHAR, @s * @A / -13) + ','
+ CONVERT(VARCHAR, @B) + ' ' + CONVERT(VARCHAR, @s * @A / -13) + ','
+ CONVERT(VARCHAR, @B) + ' ' + CONVERT(VARCHAR, (@s - 1) * @A / -13) + ','
+ CASE WHEN @s < 9 THEN CONVERT(VARCHAR, @UB) ELSE '0' END + ' ' + CONVERT(VARCHAR, (@s - 1) * @A / -13) + ')'
, @s += 2;
END

---------------------------------------------------------------------------------------------------------------

-- Adjust Frame Color
INSERT INTO @Flag(Polygon)
SELECT TOP 149 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' AS GEOMETRY) FROM sys.messages

-- Adding Frame
INSERT INTO @Flag(Polygon)
SELECT CAST('POLYGON((0 0, 0 '
+ CONVERT(VARCHAR, -@A) + ', '
+ CONVERT(VARCHAR, @B) + ' ' + CONVERT(VARCHAR, -@A) + ', '
+ CONVERT(VARCHAR, @B) + ' 0,0 0),'
+ '(0.1 -0.1,0.1 '
+ CONVERT(VARCHAR, 0.1 - @A ) + ', '
+ CONVERT(VARCHAR, @B - 0.1) + ' ' + CONVERT(VARCHAR, 0.1 - @A) + ', '
+ CONVERT(VARCHAR, @B - 0.1) + ' -0.1,0.1 -0.1)'
+ ')' AS GEOMETRY)

-- Adjust UNION Color
INSERT INTO @Flag(Polygon)
SELECT TOP 140 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' AS GEOMETRY) FROM sys.messages

-- Adding UNION
INSERT INTO @Flag(Polygon)
SELECT CAST('POLYGON('
+ '(0 0,0 ' + CONVERT(VARCHAR, @vd * -10)
+ ',' + CONVERT(VARCHAR, @hd * 12) + ' ' + CONVERT(VARCHAR, @vd * -10)
+ ',' + CONVERT(VARCHAR, @hd * 12) + ' 0,0 0),'
+ SUBSTRING(@StarPolygon, 2, @@TEXTSIZE) + ')' AS GEOMETRY);

-- Adjust Stripe's Color
INSERT INTO @Flag(Polygon)
SELECT TOP 4 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' AS GEOMETRY) FROM sys.messages

-- Adding Strips
INSERT INTO @Flag(Polygon)
SELECT CAST('POLYGON(' + SUBSTRING(@StripePolygon, 2, @@TEXTSIZE) + ')' AS GEOMETRY);

-- Adjust Stars' Color
INSERT INTO @Flag(Polygon)
SELECT TOP 28 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' AS GEOMETRY) FROM sys.messages

-- Adding Stars
INSERT INTO @Flag(Polygon)
SELECT CAST('POLYGON(' + SUBSTRING(@StarPolygon, 2, @@TEXTSIZE) + ')' AS GEOMETRY);

SELECT * FROM @Flag;

And here is the beautiful result:

Actually, American flag is easy to draw. I wonder if somebody woud draw Mexican Flag.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)