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.

Drawing a circle in SQL Server (almost)

I've just started playing with the Geo spatial data types in SQL Server 2008 R2 Express.
The first thing I wanted to do was draw a circle.  How difficult could it be?

I could not find an example on the interweb - so I put one together.

   1:  DECLARE @Points AS FLOAT = 360;

   2:   

   3:  DECLARE @count AS INT = 0;

   4:   

   5:  DECLARE @size AS INT = 1;

   6:   

   7:  DECLARE @tmpTable TABLE (

   8:      geo geometry);

   9:   

  10:  WHILE (@count < @Points)

  11:      BEGIN

  12:          INSERT INTO @tmpTable

  13:          SELECT geometry::STGeomFromText('POINT(' + CAST (SIN(RADIANS((CAST (@count AS FLOAT) / CAST (@Points AS FLOAT)) * @Points)) * @size AS VARCHAR) + ' ' + CAST (COS(RADIANS((CAST (@count AS FLOAT) / CAST (@Points AS FLOAT)) * @Points)) * @size AS VARCHAR) + ')', 4326) AS Posn;

  14:          SET @count = @count + 1;

  15:      END

  16:   

  17:  SELECT geo

  18:  FROM   @tmpTable;  

Now choose the 'Spatial Results' tab - you might have never seen this before. It only appears if the results set has a geospatial data type in it.

The observant of you might notice, it's not actually a circle. Just 360 different point (there is a different!) But I'll cover that in my next blog post.

[UPDATED: Added link to scripts]
All scripts can be found on my SkyDrive using the link below.