Top 5 Recent Articles

ARTICLES CATEGORIES

STRotate: Function to rotate a geometry object in SQL Server Spatial

If you have need for a TSQL based function for rotating geometry objects in SQL Server Spatial (Denali is required), here is a function you might find useful.

 USE [GISDB] -- Change this to your database
 GO
 -- Drop function if exists
 IF EXISTS (SELECT *
              FROM sysobjects
             WHERE id = object_id (N'[$(owner)].[STRotate]')
               AND xtype IN (N'FN', N'IF', N'TF')
 )
 BEGIN
   DROP FUNCTION [$(owner)].[STRotate];
   Print 'Dropped [$(owner)].[STRotate] ...';
 END;
 GO
 Print 'Creating [$(owner)].[STRotate] ...';
 GO
 CREATE FUNCTION [$(owner)].[STRotate]
 (
  @p_geometry  geometry,
  @p_rX        FLOAT = 0.0,
  @p_rY        FLOAT = 0.0,
  @p_angle     INT   = 0,  -- 0 to 360 degrees
  @p_round_xy  INT   = 3,
  @p_round_zm  INT   = 2
 )
   RETURNS geometry
 AS
 BEGIN
   -- Implementation
 END
 GO

Now for some tests

Rotate rectangle about itself and the origin

 SELECT '' AS name, geometry::STGeomFromText('POLYGON((1 1, 1 6, 11 6, 11 1, 1 1))',0) AS geom
 UNION ALL
 SELECT '45' + CHAR(176) + ' rotate about 0,0' AS name, dbo.Rotate(geometry::STGeomFromText('POLYGON((1 1, 1 6, 11 6, 11 1, 1 1))',0),0.0,0.0,45,3,3) AS geomO
 UNION ALL
 SELECT '45' + CHAR(176) + ' rotate about MBR centre' AS name, dbo.Rotate(geometry::STGeomFromText('POLYGON((1 1, 1 6, 11 6, 11 1, 1 1))',0),(a.minx + a.maxx) / 2.0,(a.miny + a.maxy) / 2.0,45,3,3) AS geom
   FROM dbo.geometry2mbr(geometry::STGeomFromText('POLYGON((1 1, 1 6, 11 6, 11 1, 1 1))',0) ) AS a;

The result looks like this (the background light blue rectangle is the original):

Point

 SELECT a.intValue AS oid,
        CAST(a.intValue AS VARCHAR) + CHAR(176) AS label,
        dbo.Rotate(geometry::STGeomFromText('POINT(0 10 0)',0),0,0,a.IntValue,3,3).STBuffer(1) AS geom
   FROM dbo.generate_series(0,350,10) a;

The result looks like this:

Linestring

 SELECT a.intValue AS oid,
        CAST(a.intValue AS VARCHAR) + CHAR(176) AS label,
        dbo.Rotate(geometry::STGeomFromText('LINESTRING(0 0, 10 0)',0),0,0,a.IntValue,3,3).STBuffer(0.05) AS geom
   FROM dbo.generate_series(0,350,10) a;

The result looks like this:

Curved polygon

 SELECT a.intValue AS deg,
        CAST(a.intValue AS VARCHAR) + CHAR(176) AS label,
        dbo.Rotate(geometry::STGeomFromText('CURVEPOLYGON(COMPOUNDCURVE(CIRCULARSTRING(9.962 -0.872,10.1 0,9.962 0.872),(9.962 0.872,0 0,9.962 -0.872)))',0),0,0,a.IntValue,3,3).STBuffer(0.00001) AS geom
   FROM dbo.generate_series(0,350,10) a;

The result looks like this:

I hope this is of use to someone.

The source code can be downloaded