Some Artwork Using SSMS
Published: 15th November 2014
I did a post a while back called “A Little Happiness in SSMS” in which I abused the Spatial Results tab in SSMS using the Geometry data type in order to make a smiley face.

At the time I did say that I’d delve a little deeper into this datatype but, as you can tell, I’ve never quite gotten around to it.

What I have done in the meantime though, is just play some more.

Therefore I’ve decided to post a few of my silly drawings which might entertain people or even give you an excuse to play yourself and produce something infinitely better than mine (which wouldn’t be hard).

One thing I will say is to remember to uncheck the Show Gridlines checkbox in the Spatial Results tab. It makes things look much nicer.

So, without any more waffling… here’s a boat…

select geometry::STGeomFromText
(
'multipolygon
(
((0 0, 0 5, 10 4, 10 0, 0 0)),
((10 0, 10 4, 20 5, 20 0, 10 0)),
((20 0, 20 5, 30 4, 30 0, 20 0)),
((30 0, 30 4, 40 5, 40 0, 30 0)),
((40 0, 40 5, 50 4, 50 0, 40 0)),
((50 0, 50 4, 60 5, 60 0, 50 0)),
((60 0, 60 5, 70 4, 70 0, 60 0)),
((70 0, 70 4, 80 5, 80 0, 70 0)),
((80 0, 80 5, 90 4, 90 0, 80 0)),
((90 0, 90 4, 100 5, 100 0, 90 0)),
((44 10, 50 4, 60 5, 60 10, 44 10)),
((60 5, 60 10, 76 10, 70 4, 60 5)),
((60 10, 60 40, 61 40, 61 10, 60 10)),
((60 38, 48 14, 60 14, 60 38)),
((61 38, 72 14, 61 14, 61 38)),
((10 60, 14 60, 14 64, 10 60)),
((10 56, 14 56, 14 52, 10 56)),
((18 64, 18 60, 22 60, 18 64)),
((18 52, 18 56, 22 56, 18 52)),
((10 56, 10 60, 22 60, 22 56, 10 56)),
((14 60, 14 64, 18 64, 18 60, 14 60)),
((14 52, 14 56, 18 56, 18 52, 14 52)),
((21 65, 25 69, 27 67, 23 63, 21 65)),
((11 65, 7 69, 5 67, 9 63, 11 65)),
((11 51, 7 47, 5 49, 9 53, 11 51)),
((21 51, 25 47, 27 49, 23 53, 21 51)),
((3 56.5, 3 59.5, 8 59.5, 8 56.5, 3 56.5)),
((24 56.5, 24 59.5, 29 59.5, 29 56.5, 24 56.5)),
((14.5 66, 14.5 72, 17.5 72, 17.5 66, 14.5 66)),
((14.5 50, 14.5 44, 17.5 44, 17.5 50, 14.5 50))
)'
, 0
)


And here’s a house…

declare @corner float = 57, @corner1 float = 6,
            
@window varchar(max), @window1 varchar(max),
            
@window2 varchar(max), @window3 varchar(max),
            
@house varchar(max)

select @window = '((' + convert(varchar(10), @corner) + ' ' + convert(varchar(10), @corner1) + ', ' + convert(varchar(10), @corner) + ' ' + convert(varchar(10), @corner1+17) + ', ' + convert(varchar(10), @corner+20) + ' ' + convert(varchar(10), @corner1+17) + ', ' + convert(varchar(10), @corner+20) + ' ' + convert(varchar(10), @corner1) + ', ' + convert(varchar(10), @corner) + ' ' + convert(varchar(10), @corner1) + ')),
(('
+ convert(varchar(10), @corner+0.5) + ' ' + convert(varchar(10), @corner1+0.5) + ', ' + convert(varchar(10), @corner+0.5) + ' ' + convert(varchar(10), @corner1+16.5) + ', ' + convert(varchar(10), @corner+19.5) + ' ' + convert(varchar(10), @corner1+16.5) + ', ' + convert(varchar(10), @corner+19.5) + ' ' + convert(varchar(10), @corner1+0.5) + ', ' + convert(varchar(10), @corner+0.5) + ' ' + convert(varchar(10), @corner1+0.5) + ')),
(('
+ convert(varchar(10), @corner+1) + ' ' + convert(varchar(10), @corner1+1) + ', ' + convert(varchar(10), @corner+1) + ' ' + convert(varchar(10), @corner1+16) + ', ' + convert(varchar(10), @corner+19) + ' ' + convert(varchar(10), @corner1+16) + ', ' + convert(varchar(10), @corner+19) + ' ' + convert(varchar(10), @corner1+1) + ', ' + convert(varchar(10), @corner+1) + ' ' + convert(varchar(10), @corner1+1) + ')),
(('
+ convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+2) + ')),
(('
+ convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+2) + ')),
(('
+ convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+9) + ')),
(('
+ convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+9) + ')),'


select @corner1 = 30
select @window1 = '((' + convert(varchar(10), @corner) + ' ' + convert(varchar(10), @corner1) + ', ' + convert(varchar(10), @corner) + ' ' + convert(varchar(10), @corner1+17) + ', ' + convert(varchar(10), @corner+20) + ' ' + convert(varchar(10), @corner1+17) + ', ' + convert(varchar(10), @corner+20) + ' ' + convert(varchar(10), @corner1) + ', ' + convert(varchar(10), @corner) + ' ' + convert(varchar(10), @corner1) + ')),
(('
+ convert(varchar(10), @corner+0.5) + ' ' + convert(varchar(10), @corner1+0.5) + ', ' + convert(varchar(10), @corner+0.5) + ' ' + convert(varchar(10), @corner1+16.5) + ', ' + convert(varchar(10), @corner+19.5) + ' ' + convert(varchar(10), @corner1+16.5) + ', ' + convert(varchar(10), @corner+19.5) + ' ' + convert(varchar(10), @corner1+0.5) + ', ' + convert(varchar(10), @corner+0.5) + ' ' + convert(varchar(10), @corner1+0.5) + ')),
(('
+ convert(varchar(10), @corner+1) + ' ' + convert(varchar(10), @corner1+1) + ', ' + convert(varchar(10), @corner+1) + ' ' + convert(varchar(10), @corner1+16) + ', ' + convert(varchar(10), @corner+19) + ' ' + convert(varchar(10), @corner1+16) + ', ' + convert(varchar(10), @corner+19) + ' ' + convert(varchar(10), @corner1+1) + ', ' + convert(varchar(10), @corner+1) + ' ' + convert(varchar(10), @corner1+1) + ')),
(('
+ convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+2) + ')),
(('
+ convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+2) + ')),
(('
+ convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+9) + ')),
(('
+ convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+9) + ')),'


select @corner = 104, @corner1 = 6
select @window2 = '((' + convert(varchar(10), @corner) + ' ' + convert(varchar(10), @corner1) + ', ' + convert(varchar(10), @corner) + ' ' + convert(varchar(10), @corner1+17) + ', ' + convert(varchar(10), @corner+20) + ' ' + convert(varchar(10), @corner1+17) + ', ' + convert(varchar(10), @corner+20) + ' ' + convert(varchar(10), @corner1) + ', ' + convert(varchar(10), @corner) + ' ' + convert(varchar(10), @corner1) + ')),
(('
+ convert(varchar(10), @corner+0.5) + ' ' + convert(varchar(10), @corner1+0.5) + ', ' + convert(varchar(10), @corner+0.5) + ' ' + convert(varchar(10), @corner1+16.5) + ', ' + convert(varchar(10), @corner+19.5) + ' ' + convert(varchar(10), @corner1+16.5) + ', ' + convert(varchar(10), @corner+19.5) + ' ' + convert(varchar(10), @corner1+0.5) + ', ' + convert(varchar(10), @corner+0.5) + ' ' + convert(varchar(10), @corner1+0.5) + ')),
(('
+ convert(varchar(10), @corner+1) + ' ' + convert(varchar(10), @corner1+1) + ', ' + convert(varchar(10), @corner+1) + ' ' + convert(varchar(10), @corner1+16) + ', ' + convert(varchar(10), @corner+19) + ' ' + convert(varchar(10), @corner1+16) + ', ' + convert(varchar(10), @corner+19) + ' ' + convert(varchar(10), @corner1+1) + ', ' + convert(varchar(10), @corner+1) + ' ' + convert(varchar(10), @corner1+1) + ')),
(('
+ convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+2) + ')),
(('
+ convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+2) + ')),
(('
+ convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+9) + ')),
(('
+ convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+9) + ')),'


select @corner1 = 30
select @window3 = '((' + convert(varchar(10), @corner) + ' ' + convert(varchar(10), @corner1) + ', ' + convert(varchar(10), @corner) + ' ' + convert(varchar(10), @corner1+17) + ', ' + convert(varchar(10), @corner+20) + ' ' + convert(varchar(10), @corner1+17) + ', ' + convert(varchar(10), @corner+20) + ' ' + convert(varchar(10), @corner1) + ', ' + convert(varchar(10), @corner) + ' ' + convert(varchar(10), @corner1) + ')),
(('
+ convert(varchar(10), @corner+0.5) + ' ' + convert(varchar(10), @corner1+0.5) + ', ' + convert(varchar(10), @corner+0.5) + ' ' + convert(varchar(10), @corner1+16.5) + ', ' + convert(varchar(10), @corner+19.5) + ' ' + convert(varchar(10), @corner1+16.5) + ', ' + convert(varchar(10), @corner+19.5) + ' ' + convert(varchar(10), @corner1+0.5) + ', ' + convert(varchar(10), @corner+0.5) + ' ' + convert(varchar(10), @corner1+0.5) + ')),
(('
+ convert(varchar(10), @corner+1) + ' ' + convert(varchar(10), @corner1+1) + ', ' + convert(varchar(10), @corner+1) + ' ' + convert(varchar(10), @corner1+16) + ', ' + convert(varchar(10), @corner+19) + ' ' + convert(varchar(10), @corner1+16) + ', ' + convert(varchar(10), @corner+19) + ' ' + convert(varchar(10), @corner1+1) + ', ' + convert(varchar(10), @corner+1) + ' ' + convert(varchar(10), @corner1+1) + ')),
(('
+ convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+2) + ')),
(('
+ convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+7) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+2) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+2) + ')),
(('
+ convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+9) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+3) + ' ' + convert(varchar(10), @corner1+9) + ')),
(('
+ convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+14) + ', ' + convert(varchar(10), @corner+17) + ' ' + convert(varchar(10), @corner1+9) + ', ' + convert(varchar(10), @corner+11) + ' ' + convert(varchar(10), @corner1+9) + '))'


select @house = 'multipolygon
(
((0 -20, 0 0, 180 0, 180 -20, 0 -20)),
((83 0, 78 -20, 102 -20, 97 0, 83 0)),
((50 0, 50 50, 130 50, 130 0, 50 0)),
((40 50, 50 70, 130 70, 140 50, 40 50)),
((83 0, 83 20, 97 20, 97 0, 83 0)),
((83.5 0, 83.5 19.5, 96.5 19.5, 96.5 0, 83.5 0)),
((84 0, 84 19, 96 19, 96 0, 84 0)),
((87 10, 87 17, 89 17, 89 10, 87 10)),
((91 10, 91 17, 93 17, 93 10, 91 10)),
((88 6, 88 7, 92 7, 92 6, 88 6)),
'
+ @window + @window1 + @window2 + @window3 + '
)'

select geometry::STGeomFromText
(
@house, 0
)


And here’s the previous boat, but this time with a little colour…

declare @shapes table
(
      
id int identity,
      
shape geometry
)
insert into @shapes
select geometry::STGeomFromText
(
'multipolygon
(
((0 0, 0 5, 10 4, 10 0, 0 0)),
((10 0, 10 4, 20 5, 20 0, 10 0)),
((20 0, 20 5, 30 4, 30 0, 20 0)),
((30 0, 30 4, 40 5, 40 0, 30 0)),
((40 0, 40 5, 50 4, 50 0, 40 0)),
((50 0, 50 4, 60 5, 60 0, 50 0)),
((60 0, 60 5, 70 4, 70 0, 60 0)),
((70 0, 70 4, 80 5, 80 0, 70 0)),
((80 0, 80 5, 90 4, 90 0, 80 0)),
((90 0, 90 4, 100 5, 100 0, 90 0))
)'
, 0)
union all
select geometry::STGeomFromText
(
'multipolygon
(
((44 10, 50 4, 60 5, 60 10, 44 10)),
((60 5, 60 10, 76 10, 70 4, 60 5))
)'
, 0)
union all
select geometry::STGeomFromText
(
'multipolygon
(
((0 0, 0.1 0, 0 0.1, 0 0))
)'
, 0)
union all
select geometry::STGeomFromText
(
'multipolygon
(
((60 38, 48 14, 60 14, 60 38)),
((61 38, 72 14, 61 14, 61 38))
)'
, 0)
union all
select geometry::STGeomFromText
(
'multipolygon
(
((60 10, 60 40, 61 40, 61 10, 60 10))
)'
, 0)

while (select COUNT(*) from @shapes) < 32
begin
      insert into
@shapes
      
select geometry::STGeomFromText
      
(
      
'multipolygon
      (
      ((0 0, 0.1 0, 0 0.1, 0 0))
      )'
, 0)
end

insert into
@shapes
select geometry::STGeomFromText
(
'multipolygon
(
((10 60, 14 60, 14 64, 10 60)),
((10 56, 14 56, 14 52, 10 56)),
((18 64, 18 60, 22 60, 18 64)),
((18 52, 18 56, 22 56, 18 52)),
((10 56, 10 60, 22 60, 22 56, 10 56)),
((14 60, 14 64, 18 64, 18 60, 14 60)),
((14 52, 14 56, 18 56, 18 52, 14 52)),
((21 65, 25 69, 27 67, 23 63, 21 65)),
((11 65, 7 69, 5 67, 9 63, 11 65)),
((11 51, 7 47, 5 49, 9 53, 11 51)),
((21 51, 25 47, 27 49, 23 53, 21 51)),
((3 56.5, 3 59.5, 8 59.5, 8 56.5, 3 56.5)),
((24 56.5, 24 59.5, 29 59.5, 29 56.5, 24 56.5)),
((14.5 66, 14.5 72, 17.5 72, 17.5 66, 14.5 66)),
((14.5 50, 14.5 44, 17.5 44, 17.5 50, 14.5 50))
)'
, 0
)

select *
from @shapes
order by id


Comments:
NB: Comments will only appear once they have been moderated.