When I was a kid, we would cut Christmas trees and snowflakes out of construction paper and tape them to the windows at school. Of course, today we must use databases and code!

I’ve been doing some map reporting for a client lately with Reporting Services and SQL Server Spatial queries. In the Holiday season, just for fun we were volleying holiday themed queries between friends in the SQL Server community. Arnie Rowland replied with this query that draws a Christmas tree. To view it, paste this query into the SSMS query window and run it against any edition of SQL Server 2008 or newer. Note that the ornaments on the tree are placed at random.

SELECT geometry::Parse(‘POLYGON((

4 0, 0 0, 3 2, 1 2, 3 4, 1 4, 3 6, 2 6,

4 8, 6 6, 5 6, 7 4, 5 4, 7 2, 5 2, 8 0, 4 0

))’)

UNION ALL

SELECT geometry::Parse(‘POLYGON((

2.5 0, 3 -1, 5 -1, 5.5 0, 2.5 0

))’)

UNION ALL

SELECT geometry::Parse(‘POLYGON((

4 7.5, 3.5 7.25, 3.6 7.9, 3.1 8.2, 3.8 8.2,

4 8.9, 4.2 8.2, 4.9 8.2, 4.4 7.9, 4.5 7.25, 4 7.5

))’)

UNION ALL

SELECT geometry::Point(

RAND(CHECKSUM(NEWID())) * 5 +1.5,

RAND(CHECKSUM(NEWID())) * 6, 0

).STBuffer(0.3)

FROM master.dbo.spt_values

WHERE type = ‘P’ AND number BETWEEN 1 AND 20;

Not to be outdone, Itzik Ben-Gan replied with this query which uses fractal math to generate a Koch Snowflake:

SET NOCOUNT ON;

USE tempdb;— Definition of GetEndPoint table function

IF OBJECT_ID(‘dbo.GetEndPoint’) IS NOT NULL

DROP FUNCTION dbo.GetEndPoint;

GOCREATE FUNCTION dbo.GetEndPoint

(

@x AS FLOAT,

@y AS FLOAT,

@dist AS FLOAT,

@angle AS FLOAT

) RETURNS TABLE

AS

RETURN

SELECT @x + @dist * COS(PI()*@angle/180) AS x, @y + @dist * SIN(PI()*@angle/180) AS y;

GO— Definition of KochSnowflakeColored table function

IF OBJECT_ID(‘dbo.KochSnowflakeColored’) IS NOT NULL

DROP FUNCTION dbo.KochSnowflakeColored;

GOCREATE FUNCTION dbo.KochSnowflakeColored

(

@iterations AS INT

) RETURNS TABLE

AS

RETURN

WITH Koch(iteration, length, angle, x0, y0, x1, y1) AS

(— anchor members

— side b

SELECT

1 AS iteration, 100E0 as length, 60E0 AS angle,

0E0 AS x0, 0E0 AS y0, x as x1, y as y1

FROM dbo.GetEndPoint(0E0, 0E0, 100E0, 60E0) AS PUNION ALL

— side a

SELECT

1 AS iteration, 100E0 as length, 300E0 AS angle,

x AS x0, y AS y0, 100E0 AS x1, 0E0 AS y1

FROM dbo.GetEndPoint(0E0, 0E0, 100E0, 60E0) AS PUNION ALL

— side c

SELECT

1 AS iteration, 100E0 as length, 180E0 AS angle,

100E0 AS x0, 0E0 AS y0, 0E0 AS x1, 0E0 AS y1UNION ALL

— recursive members

— segment 1

SELECT

P.iteration + 1 AS iteration, A1.length, A1.angle,

P.x0, P.y0, A2.x AS x1, A2.y AS y1

FROM Koch AS P

CROSS APPLY (SELECT P.length / 3 AS length, P.angle AS angle) AS A1

CROSS APPLY dbo.GetEndPoint(P.x0, P.y0, A1.length, A1.angle) AS A2

WHERE P.iteration < @iterationsUNION ALL

— segment 2

SELECT

P.iteration + 1 AS iteration, A1.length, A1.angle,

A2.x AS x0, A2.y AS y0, A3.x AS x1, A3.y AS y1

FROM Koch AS P

CROSS APPLY (SELECT P.length / 3 AS length,

P.angle – CAST(P.angle AS INT) + CAST(P.angle + 60E0 AS INT) % 360 AS angle) AS A1

CROSS APPLY dbo.GetEndPoint(P.x0, P.y0, A1.length, P.angle) AS A2

CROSS APPLY dbo.GetEndPoint(A2.x, A2.y, A1.length, A1.angle) AS A3

WHERE P.iteration < @iterationsUNION ALL

— segment 3

SELECT

P.iteration + 1 AS iteration, A1.length, A1.angle,

A3.x AS x0, A3.y AS y0, A2.x AS x1, A2.y AS y1

FROM Koch AS P

CROSS APPLY (SELECT P.length / 3 AS length,

P.angle – CAST(P.angle AS INT) + CAST(P.angle + 300E0 AS INT) % 360 AS angle) AS A1

CROSS APPLY dbo.GetEndPoint(P.x0, P.y0, A1.length * 2, P.angle) AS A2

CROSS APPLY dbo.GetEndPoint(A2.x, A2.y, A1.length, A1.angle + 180E0) AS A3

WHERE P.iteration < @iterationsUNION ALL

— segment 4

SELECT

P.iteration + 1 AS iteration, A1.length, A1.angle,

A2.x AS x0, A2.y AS y0, P.x1, P.y1

FROM Koch AS P

CROSS APPLY (SELECT P.length / 3 AS length, P.angle AS angle) AS A1

CROSS APPLY dbo.GetEndPoint(P.x0, P.y0, A1.length * 2, A1.angle) AS A2

WHERE P.iteration < @iterations

)

SELECT

GEOMETRY::STLineFromText(‘LINESTRING(‘

+ STR(x0, 12, 9) + ‘ ‘

+ STR(y0, 12, 9) + ‘,’

+ STR(x1, 12, 9) + ‘ ‘

+ STR(y1, 12, 9) + ‘ ‘

+ ‘)’, 0) AS line

FROM Koch

WHERE iteration = @iterations;

GO— Merry Christmas

SELECT * FROM dbo.KochSnowflakeColored(6);

My point in making this post was to say “Merry Christmas” and “Happy Holidays” using the tools that we all know and love. As a side note, there are two articles on SQL Server Magazine that describe the techniques used to create the snowflake:

http://sqlmag.com/t-sql/ctes-multiple-recursive-members

http://sqlmag.com/t-sql/ctes-multiple-recursive-members-part-2

This is awesome! Thank you Paul.

LikeLike

Paul,

Thanks for this treat!

A minor comment: the snowflake query as provided did not work on some DB instances I tested, but did work on other DB instances. I found that the capitalized keyword GEOMETRY is not accepted on a case sensitive / binary collation DB instance. After change the keyword to lower case (geometry) – as you used in the Christmas tree query, the query works on all SQL 2008 and later DB instances that I tested (both case sensitive / binary and case insensitive).

I also played around with a larger number of Christmas tree ornaments (change WHERE clause BETWEEN 1 AND 20 to 1 AND 100).

Thanks again!

Scott R.

LikeLike