How to draw a Polygon in SQL Server

In my previous post 'Drawing a circle in SQL Server (almost)' I showed how to plot 360 points to draw a circle. {Well} It looked like a circle, but to SQL Server, it was just a series of points.

We can use a LineString to draw a shape, but the most appropriate type is the Polygon.

The polygon is a closed [at least 3 sided] shape, i.e. the first point is the same as the same point. If it’s not, SQL will throw an exception. SQL Server will also throw an exception if you attempt to create a polygon with just two sides..

 

   1:  -- Drawing a polygon

   2:  declare @Sides float = 3

   3:  declare @count int = 0

   4:  declare @size int = 10

   5:  declare @firstpoint varchar(400)

   6:   

   7:  declare @sql varchar(8000);

   8:   

   9:   

  10:  set @sql =  'POLYGON((' 

  11:   

  12:  while(@count < @Sides)

  13:  begin

  14:   

  15:  set @sql = @sql + cast(SIN(RADIANS((CAST(@count AS FLOAT)

  16:              /CAST(@Sides AS FLOAT)) 

  17:              * 360)) * @size as varchar) 

  18:             + ' ' +

  19:              cast(COS(RADIANS((CAST(@count AS FLOAT)

  20:              /CAST(@Sides AS FLOAT))

  21:              * 360)) * @size as varchar) + ','

  22:   

  23:  if @count = 0

  24:  set @firstpoint  = cast(SIN(RADIANS((CAST(@count AS FLOAT)

  25:              /CAST(@Sides AS FLOAT)) 

  26:              * 360)) * @size as varchar) 

  27:             + ' ' +

  28:              cast(COS(RADIANS((CAST(@count AS FLOAT)

  29:              /CAST(@Sides AS FLOAT))

  30:              * 360)) * @size as varchar) 

  31:              

  32:  set @count = @count + 1

  33:   

  34:  end

  35:   

  36:  set @sql = @sql + @firstpoint + '))'

  37:   

  38:  print @sql

  39:  DECLARE @g geometry;

  40:  SET @g = geometry::STGeomFromText(@sql, 0);

  41:  select @g

  42:   

Simply change

   1:  declare @Sides float = 3

to be how ever many sides you need.  Simple.

Here are a few examples.

All scripts can be found on my SkyDrive using the link below.