Pages

Saturday, 20 August 2011

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.


3456360


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