SQL Server – Spatial fun in SQL Server – creating an XMAS tree

Hi Friends,

I got this code from Spatial forum sometime back when SQL Server 2008 was launched. The script was lying on my disk. I was preparing for an upcoming session on Spatial Data so came across this script and thought of sharing this with you all. Apologies to the original author, I do not remember his name, but the credit goes to him 🙂 – this script is fun !!!

Copy this script in SSMS query editor. Select All and run. In the Results pane, click on Spatial tab and see the output 🙂

USE tempdb
GO
	 
	 
-- Prepare the scene 
 
CREATE TABLE #ChristmasScene 
 
    ( 

     item VARCHAR(32) 
 
    ,shape GEOMETRY 
    ) ; 

--Put up the tree and star 
 
INSERT  INTO #ChristmasScene 
 
	VALUES  ( 'Tree', 
	 
         '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))' ), 
 
        ( 'Base', 'POLYGON((2.5 0, 3 -1, 5 -1, 5.5 0, 2.5 0))' ), 
 
        ( 'Star', 
 
         '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))' ) 
	 
--Decorate the tree 
 
DECLARE @i INT = 0 
 
   ,@x INT
 
   ,@y INT ; 

WHILE ( @i < 20 ) 
	 
    BEGIN
 
        INSERT  INTO #ChristmasScene 
	 
        VALUES  ( 'Bauble' + CAST(@i AS VARCHAR(8)), 
 
                  GEOMETRY::Point(RAND() * 5 + 1.5, RAND() * 6, 0).STBuffer(0.3) ) 
	 
        SET @i = @i + 1 ; 

    END
 
 --Christmas Greeting 
	 
INSERT  INTO #ChristmasScene 

VALUES  ( 'M', 
 
         'POLYGON((0 10, 0 11, 0.25 11, 0.5 10.5, 0.75 11, 1 11, 1 10, 0.75 10, 0.75 10.7, 0.5 10.2, 0.25 10.7, 0.25 10, 0 10))' ), 
 
        ( 'E', 
 
         'POLYGON((1 10, 1 11, 2 11, 2 10.8, 1.25 10.8, 1.25 10.6, 1.75 10.6, 1.75 10.4, 1.25 10.4, 1.25 10.2, 2 10.2, 2 10, 1 10))' ), 
 
        ( 'R', 

         'POLYGON((2 10, 2 11, 3 11, 3 10.5, 2.4 10.5, 3 10, 2.7 10, 2.2 10.4, 2.2 10, 2 10),(2.2 10.8, 2.8 10.8, 2.8 10.7, 2.2 10.7, 2.2 10.8))' ), 
 
        ( 'R', 
 
         'POLYGON((3 10, 3 11, 4 11, 4 10.5, 3.4 10.5, 4 10, 3.7 10, 3.2 10.4, 3.2 10, 3 10),(3.2 10.8, 3.8 10.8, 3.8 10.7, 3.2 10.7, 3.2 10.8))' ), 
 
        ( 'Y', 
 
         'POLYGON((4 11, 4.2 11, 4.5 10.6, 4.8 11, 5 11, 4.6 10.5, 4.6 10, 4.4 10, 4.4 10.5, 4 11))' ), 
 
        ( 'C', 
 
	         'POLYGON((0 9, 0 10, 1 10, 1 9.8, 0.2 9.8, 0.2 9.2, 1 9.2, 1 9, 0 9))' ), 
 
       ( 'H', 
 
         'POLYGON((1 9, 1 10, 1.2 10, 1.2 9.6, 1.8 9.6, 1.8 10, 2 10, 2 9, 1.8 9, 1.8 9.4, 1.2 9.4, 1.2 9, 1 9))' ), 
 
	        ( 'R', 
 
         'POLYGON((2 9, 2 10, 3 10, 3 9.5, 2.4 9.5, 3 9, 2.7 9, 2.2 9.4, 2.2 9, 2 9),(2.2 9.8, 2.8 9.8, 2.8 9.7, 2.2 9.7, 2.2 9.8))' ), 
 
        ( 'I', 
 
         'POLYGON((3.2 9, 3.2 9.2, 3.4 9.2, 3.4 9.8, 3.2 9.8, 3.2 10, 3.8 10, 3.8 9.8, 3.6 9.8, 3.6 9.2, 3.8 9.2, 3.8 9, 3.2 9))' ), 
 
        ( 'S', 
 
         'POLYGON((4 9, 4 9.2, 4.8 9.2, 4.8 9.4, 4 9.4, 4 10, 5 10, 5 9.8, 4.2 9.8, 4.2 9.6, 5 9.6, 5 9, 4 9))' ), 
 
       ( 'T', 
 
         'POLYGON((5 9.8, 5 10, 6 10, 6 9.8, 5.6 9.8, 5.6 9, 5.4 9, 5.4 9.8, 5 9.8))' ), 
 
        ( 'M', 
 
         'POLYGON((6 9, 6 10, 6.25 10, 6.5 9.5, 6.75 10, 7 10, 7 9, 6.75 9, 6.75 9.7, 6.5 9.2, 6.25 9.7, 6.25 9, 6 9))' ), 
	 
        ( 'A', 
 
         'POLYGON((7 9, 7 10, 8 10, 8 9, 7.75 9, 7.75 9.3, 7.25 9.3, 7.25 9, 7 9),(7.25 9.5, 7.25 9.8, 7.75 9.8, 7.75 9.5, 7.25 9.5))' ), 
 
        ( 'S', 
 
         'POLYGON((8 9, 8 9.2, 8.8 9.2, 8.8 9.4, 8 9.4, 8 10, 9 10, 9 9.8, 8.2 9.8, 8.2 9.6, 9 9.6, 9 9, 8 9))' ) ; 
 
--Admire the scene 
 
SELECT  * 
 
FROM    #ChristmasScene  -- Tidy up the pine needles and put away the decorations 
 
DROP TABLE #ChristmasScene

 

 

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

One Comment on “SQL Server – Spatial fun in SQL Server – creating an XMAS tree”

  1. Wow..Just tried it..Looks really great !!!!

    So artistic for a SQL guy to develop it…I have seen javascript developers developing some stuff like this but never from an SQL guy. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *