Pages

Thursday, 28 July 2011

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.