Go to content Go to navigation and search

Home

Current SQL Server Blog Articles

    SQL Server Spatial: Converting a LineString to COGO XML
    SQL Server Spatial: Creating (Multi)LineStrings geometries from COGO XML instructions
    New Version of Package of TSQL Spatial Functions
    Vectorize/Segmentize SQL Server 2012
    Coordinate Editing Functions for SQL Server Spatial
    Function to Scale a geometry object for SQL Server Spatial
    TSQL String Tokenizer Function for SQL Server
    geography/geometry to MBR helper functions
    generate_series for SQL Server 2008
    Extract Polygons from result of STIntersection in SQL Server Spatial
    Function to round ordinates of a SQL Server Spatial geometry object
    Extract elements of SQL Server Spatial geometry object
    Counting number of polygon rings SQL Server Spatial
    Filtering Polygon Rings in SQL Server Spatial
    Function to Move a geometry object in SQL Server Spatial
    Alternate Centroid Functions for SQL Server Spatial
    Function to Rotate geometry objects in SQL Server Spatial
    A GetVertices wrapper for DumpPoints in SQL Server 2008 Spatial
    Creating a Morton number Space Key generator for SQL Server
    Gridding a geometry or geography object (SQL Server Denali)
    On hinting spatial indexes
    Random Search Procedure (SQL Server 2008 Spatial)
    COGO: Converting (Google Earth) Formatted Longitude/Latitude points to decimal degrees (SQL Server)
    COGO: Convert Degrees, Minutes and Seconds values to Decimal Degrees
    COGO: DD2DMS Formatting a latitude/longitude decimal degree value
    COGO: Create point from bearing and distance
    COGO: Compute number of vertices required to stroke circle or circular arc
    COGO: Calculating the bearing between two points (SQL Server 2008 Spatial)
    COGO: Compute arc length subtended by angle centre of circle
    COGO: Compute chord length of segment of a circle (arc)
    COGO: Computing Arc To Chord (Arc2Chord) Separation
    COGO: Creating a stroked polygon from a circle's centre x,y and radius
    COGO: Finding centre and radius of a curve defined by three points: FindCircle function
    Identifying Tight Radius Curves sections within LineString geometry data
    Generating random point data for SQL Server 2008 Spatial
    New Presentation on Active (Searchable) Spatial Metadata for SQL Server 2008 Spatial and FreeText
    Checking is a column in a table or a view is of type geometry or geography
    Extracting geometry type string values from geometry/geography objects in SQL Server 2008 Spatial
    Vectorising geometry objects in SQL Server 2008
    MBR to geography/geometry helper functions
    Extracting geometry types from geometry/geography objects in SQL Server 2008
    Getting the number of coordinate dimensions of a geometry/geography object in SQL Server 2008
    A Dump Points Function for SQL Server 2008 Spatial
    toGeography and toGeography Conversion Functions for SQL Server 2008 Spatial
    Write text file with spatial data from SQL Server 2008
    Loading Shapefiles into Geography type column in SQL Server 2008
    Tip #6: Correcting invalid geometries
    Tip #5: Where or where has my little column gone
    Tip #4: What Coordinate System is that?
    Tip #3: What object is that?
    Tip #2: Spatial Indexing and Primary Keys
    Tip #1: SQL Server 2008 "Katmai" - Setting SRIDs

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 [7]

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 · #

The SQL here is different from the valid SQL for MySQL. How do I convert this into MySQL stored function?
I am new to writing SQL Code in general.

— Divij Sehgal · 19 December 2017, 17:36 · #

If you are willing to make a small donation via my website then I would be willing to create a MySQL version for you.

Please contact me directly: simon at spatialdbadvisor dot com.

regards
Simon

— Simon Greener · 19 December 2017, 19:18 · #

Divij,

I have written a function called ST_Move, and also functions called ST_Rotate and ST_Scale all for MySQL.

Examples can be seen at a new MySQL Blog page that is accessible from the home page.

Also, some documentation can be seen via this link: http://spatialdbadvisor.com/files/MySQL.html

Make a reasonable donation and I will release them to you.

Simon

— Simon Greener · 20 December 2017, 19:57 · #