STScale: Function to Scale a geometry object

Here is a simple scaling function for use with SQL Server Spatial’s geometry data type.

 USE [GISDB] -- Change this to your database
 GO
 -- Drop function if exists
 --
 IF EXISTS (SELECT *
              FROM dbo.sysobjects
             WHERE id = object_id (N'[dbo].[STScale]'))
 DROP FUNCTION [dbo].[STScale]
 GO
 -- Now create the function
 --
 CREATE FUNCTION [dbo].[STScale](
   @p_geometry  geometry,
   @p_xFactor   FLOAT,
   @p_yFactor   FLOAT,
   @p_zFactor   FLOAT,
   @p_mFactor   FLOAT,
   @p_round_x   INT = 3,
   @p_round_y   INT = 3,
   @p_round_z   INT = 2,
   @p_round_m   INT = 2
 )
   RETURNS geometry
 BEGIN
   DECLARE
      @v_xFactor        FLOAT = CASE WHEN @p_xFactor IS NULL THEN 1.0 ELSE @p_xFactor END,
      @v_yFactor        FLOAT = CASE WHEN @p_yFactor IS NULL THEN 1.0 ELSE @p_yFactor END,
      @v_zFactor        FLOAT = CASE WHEN @p_zFactor IS NULL THEN 1.0 ELSE @p_zFactor END,
      @v_mFactor        FLOAT = CASE WHEN @p_mFactor IS NULL THEN 1.0 ELSE @p_mFactor END,
      @v_coord          INT = 0,
      @v_wkt            VARCHAR(MAX) = '',
      @v_token          VARCHAR(MAX),
      @v_delim          VARCHAR(MAX),
      @v_geometry       geometry,
      @v_x              FLOAT = 0.0,
      @v_y              FLOAT = 0.0,
      @v_z              FLOAT = NULL,
      @v_m              FLOAT = NULL;
   BEGIN
     IF ( @p_geometry IS NULL )
       RETURN NULL;
     IF ( @p_xFactor IS NULL AND @p_yFactor IS NULL AND @p_zFactor IS NULL AND @p_mFactor IS NULL )
        RETURN CAST('One of scale factors X,Y,Z and M must not be NULL.' AS VARCHAR(MAX)); -- geometry);
     SET @v_coord = 0;
     DECLARE Tokens CURSOR FAST_FORWARD FOR
       SELECT t.token, t.separator
         FROM dbo.Tokenizer(@p_geometry.AsTextZM(),' ,()') AS t;
     OPEN Tokens;
     FETCH NEXT FROM Tokens INTO @v_token, @v_delim;
     WHILE @@FETCH_STATUS = 0
     BEGIN
        IF ( @v_token IS NULL )  -- double delimiter
        BEGIN
           SET @v_wkt = @v_wkt + @v_delim
        END
        ELSE
        BEGIN
           IF ( @v_token NOT LIKE '[-0-9]%' )
           BEGIN
              SET @v_wkt = @v_wkt + @v_token + LTRIM(@v_delim)
           END
           ELSE -- @v_token LIKE '[0-9]%' )
           BEGIN
              SET @v_coord = @v_coord + 1;
              IF ( @v_coord = 1 ) SET @v_x = CAST(@v_token AS FLOAT)
              IF ( @v_coord = 2 ) SET @v_y = CAST(@v_token AS FLOAT)
              IF ( @v_coord = 3 ) SET @v_z = CAST(@v_token AS FLOAT)
              IF ( @v_coord = 4 ) SET @v_m = CAST(@v_token AS FLOAT)
              IF ( @v_delim IN (',',')') )
              BEGIN
                 SET @v_wkt = @v_wkt +
                    LTRIM(STR(round(@v_x * @v_xFactor, @p_round_x),24,@p_round_x)) + ' ' +
                    LTRIM(STR(round(@v_y * @v_yFactor, @p_round_y),24,@p_round_y)) +
                    CASE WHEN @v_z IS NULL THEN '' ELSE ' ' + LTRIM(STR(round(@v_z * @v_zFactor, @p_round_z),24,@p_round_z)) END +
                    CASE WHEN @v_m IS NULL THEN '' ELSE ' ' + LTRIM(STR(round(@v_m * @v_mFactor, @p_round_m),24,@p_round_m)) END + @v_delim;
                 SET @v_coord = 0;
              END;
            END;
        END;
        FETCH NEXT FROM Tokens INTO @v_token, @v_delim;
     END;
     CLOSE Tokens
     DEALLOCATE Tokens
     SET @v_geometry = geometry::STGeomFromText(@v_wkt,@p_geometry.STSrid);
     RETURN @v_geometry;
   END
 END
 GO

Now some tests.

 SELECT a.stype, a.geom.AsTextZM() AS geomWKT
   FROM (SELECT 'Scaled2D' AS stype, dbo.STScale(geometry::STGeomFromText('LINESTRING (1 2 3, 1 1 1)',0), 0.5,0.75,NULL,NULL, 2,2,2,2) AS geom
         UNION ALL
         SELECT 'Original' AS stype, geometry::STGeomFromText('LINESTRING (1 2 3, 1 1 1)',0) AS geom ) a;

Results

sType geomWKT
Scaled2D LINESTRING (0.5 1.5 3, 0.5 0.75 1)
Original LINESTRING (1 2 3, 1 1 1)
 SELECT a.stype, a.geom.AsTextZM() AS geomWKT
   FROM (SELECT 'Scaled3D' AS stype, dbo.STScale(geometry::STGeomFromText('LINESTRING (1 2 3, 1 1 1)',0), 0.5, 0.75, 0.8,NULL, 2,2,2,2) AS geom
         UNION ALL
         SELECT 'Original' AS stype, geometry::STGeomFromText('LINESTRING (1 2 3, 1 1 1)',0) AS geom ) a;

Results

sType geomWKT
Scaled3D LINESTRING (0.5 1.5 2.4, 0.5 0.75 0.8)
Original LINESTRING (1 2 3, 1 1 1)

Finally, a polygon.

 SELECT a.stype, a.geom.AsTextZM() AS geomWKT
   FROM (SELECT 'Original' AS stype, geometry::STGeomFromText('POLYGON ((1 1, 2 1, 2 2, 1 2, 1 1))',0) AS geom
         UNION ALL
         SELECT 'Scaled2D' AS stype, dbo.STScale(geometry::STGeomFromText('POLYGON ((1 1, 2 1, 2 2, 1 2, 1 1))',0), 2.0, 2.0, NULL,NULL, 2,2,2,2) AS geom
         UNION ALL
         SELECT 'ScaledYOnly' AS stype, dbo.STScale(geometry::STGeomFromText('POLYGON ((1 1, 2 1, 2 2, 1 2, 1 1))',0), NULL, 2.0, NULL,NULL, 2,2,2,2) AS geom ) a;

Results

sType geomWKT
Original POLYGON ((1 1, 2 1, 2 2, 1 2, 1 1))
Scaled2D POLYGON ((2 2, 4 2, 4 4, 2 4, 2 2))
ScaledYOnly POLYGON ((1 2, 2 2, 2 4, 1 4, 1 2))

I hope this is of use to someone.

Leave a Reply

Your email address will not be published. Required fields are marked *