Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Some Artwork Using SSMS
Published: Jul 01, 2016
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


Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.