Go to content Go to navigation and search

Home

Current SQL Server Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Function to Move a geometry object in SQL Server Spatial

Sunday January 01 2012 at 08:38

Keywordsmove sql server denali spatial geometry polygon linestring point TSQL STMove shift delta
Summary

A T-Sql based function for moving a Sql Server Spatial geometry object some arbitrary delta or shift.

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

  1. USE [GISDB] -- Change this to your database
  2. GO
  3. -- Function: Move
  4. --
  5. CREATE FUNCTION dbo.STMove(@p_geometry  geometry,
  6.                          @p_dX        FLOAT,
  7.                          @p_dY        FLOAT,
  8.                          @p_dZ        FLOAT,
  9.                          @p_dM        FLOAT,
  10.                          @p_round_x   INT = 3,
  11.                          @p_round_y   INT = 3,
  12.                          @p_round_z   INT = 2,
  13.                          @p_round_m   INT = 2)
  14. RETURNS geometry
  15. BEGIN
  16.   DECLARE
  17.      @v_coord          INT = 0,
  18.      @v_rows           INT = 0,
  19.      @v_wkt            VARCHAR(MAX) = '',
  20.      @v_token          VARCHAR(MAX),
  21.      @v_delim          VARCHAR(MAX),
  22.      @v_geometry       geometry,
  23.      @v_x              FLOAT = 0.0,
  24.      @v_y              FLOAT = 0.0,
  25.      @v_z              FLOAT = NULL,
  26.      @v_m              FLOAT = NULL;
  27.   BEGIN
  28.     IF ( @p_geometry IS NULL )
  29.       RETURN NULL;
  30.     IF ( @p_dX IS NULL AND @p_dY IS NULL AND @p_dZ IS NULL AND @p_dM IS NULL )
  31.        RETURN CAST('One of deltas X,Y,Z and M must not be NULL.' AS VARCHAR(MAX)); -- geometry);
  32.     SET @v_coord = 0;
  33.     SET @v_rows  = 0;
  34.     DECLARE Tokens CURSOR FAST_FORWARD FOR
  35.       SELECT t.token, t.separator
  36.         FROM dbo.Tokenizer(@p_geometry.AsTextZM(),' ,()') AS t;
  37.     OPEN Tokens;
  38.     FETCH NEXT FROM Tokens INTO @v_token, @v_delim;
  39.     WHILE @@FETCH_STATUS = 0
  40.     BEGIN
  41.        IF ( @v_token IS NULL )  -- double delimiter
  42.        BEGIN
  43.           SET @v_wkt = @v_wkt + @v_delim
  44.        END
  45.        ELSE
  46.        BEGIN
  47.           IF ( @v_token NOT LIKE '[-0-9]%' )
  48.           BEGIN
  49.              SET @v_wkt = @v_wkt + @v_token + LTRIM(@v_delim)
  50.           END
  51.           ELSE -- @v_token LIKE '[0-9]%' )  
  52.           BEGIN
  53.              SET @v_coord = @v_coord + 1;
  54.              IF ( @v_coord = 1 ) SET @v_x = CAST(@v_token AS FLOAT)
  55.              IF ( @v_coord = 2 ) SET @v_y = CAST(@v_token AS FLOAT)
  56.              IF ( @v_coord = 3 ) SET @v_z = CAST(@v_token AS FLOAT)
  57.              IF ( @v_coord = 4 ) SET @v_m = CAST(@v_token AS FLOAT)
  58.              IF ( @v_delim IN (',',')') )
  59.              BEGIN
  60.                 SET @v_wkt = @v_wkt +
  61.                    LTRIM(STR(round(@v_x + @p_dX, @p_round_x),24,@p_round_x)) + ' ' +
  62.                    LTRIM(STR(round(@v_y + @p_dY, @p_round_y),24,@p_round_y)) +
  63.                    CASE WHEN @v_z IS NULL THEN '' ELSE ' ' + LTRIM(STR(round(@v_z + @p_dZ, @p_round_z),24,@p_round_z)) END +
  64.                    CASE WHEN @v_m IS NULL THEN '' ELSE ' ' + LTRIM(STR(round(@v_m + @p_dM, @p_round_m),24,@p_round_m)) END + @v_delim;
  65.                 SET @v_coord = 0;
  66.              END;
  67.            END;
  68.        END;
  69.        FETCH NEXT FROM Tokens INTO @v_token, @v_delim;
  70.     END;
  71.     CLOSE Tokens
  72.     DEALLOCATE Tokens
  73.     SET @v_geometry = geometry::STGeomFromText(@v_wkt,@p_geometry.STSrid);
  74.     RETURN @v_geometry;
  75.   END
  76. END
  77. GO

Now for some tests

Point

  1. SELECT geometry::STGeomFromText('POINT(0 0 0)',0).STBuffer(0.5) AS geom
  2. UNION ALL
  3. SELECT dbo.STMove(geometry::STGeomFromText('POINT(0 0 0)',0),a.IntValue,b.IntValue,0,NULL,3,3,2,2).STBuffer(0.2) AS geom
  4.   FROM dbo.generate_series(0,10,1) a
  5.        CROSS apply
  6.        dbo.generate_series(1,9,1) b;

The result looks like this:

Linestring

  1. SELECT dbo.STMove(geometry::STGeomFromText('LINESTRING(0 0, 10 0)',0),5,a.IntValue,NULL,NULL,3,3,2,2).STBuffer(0.05) AS geom
  2.   FROM dbo.generate_series(0,10,1) a;

The result looks like this:

Move rectangle 10,10

  1. SELECT '' AS name, geometry::STGeomFromText('POLYGON((1 1, 1 6, 11 6, 11 1, 1 1))',0) AS geom
  2. UNION ALL
  3. SELECT 'Move 10,10' AS name, dbo.STMove(geometry::STGeomFromText('POLYGON((1 1, 1 6, 11 6, 11 1, 1 1))',0),10.0,10.0,NULL,NULL,3,3,2,2) AS geom;

The result looks like this:

Curved polygon

  1. SELECT 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).STCurveToLine() AS geom
  2. UNION ALL
  3. SELECT dbo.STMove(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),3,3,NULL,NULL,3,3,2,2).STCurveToLine() AS geom;

The result looks like this:

I hope this is of use to someone.If you have need for a TSQL based function for moving geometry objects in SQL Server Spatial (Denali is required), here is a function you might find useful.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [4]

Hi,
I’m trying to use your .move function. But I am extracting the coordinate information I want to move the points to from another spatial table. See Below.
ObjectID FacilityID XCoord YCoord
4 249069 502241.815402247 1315553.76328807
1 248846 502336.456289079 1315693.39588282
2 2877237 502119.67227383 1315295.02988166
3 2877289 502760.221517663 1315000.71255691
How would I call your .move function to move points in one table to the coordinates in this table where the ObjectID’s match?

— David Frame · 12 March 2015, 01:34 · #

The STMove function is for use in translating a SQL Server Spatial GEOMETRY object. You don’t have a geometry object. All you have are coordinates stored as numbers in two columns (x and y). If you wish to translate/move the coordinates you can apply the delta x and delta y values directly via an UPDATE statement.

If you wish to match and copy these ordinates to another table you need to do a table join which is not a spatial problem.

regards
Simon

— Simon · 12 March 2015, 18:47 · #

Help Simon
This move I can use to create a offset with out a draw a new complete Union feature , like offset pointer for left o right point for a geocoder?

Best regards

— Roberto · 16 December 2016, 20:47 · #

Roberto,
Play with the function with your data.
It should do what you want.
There is a package of TSQL functions including this one for download at this link:

http://www.spatialdbadvisor.com/source_code_form

Simon

— Simon Greener · 16 December 2016, 21:13 · #