Go to content Go to navigation and search

Home

Current SQL Server Blog Articles

    STGeometryTypes: Extracting all geometry type string values from complex geometry
    STDensify: Densify (m)LineString or (m)Polygon geometry objects
    STInsertN: Insert single vertex into a geometry
    STUpdateN: Update (replace) a single vertex within a geometry object.
    STUpdate: Replace all points equal to the supplied point with replacement point.
    STDeleteN: Delete single vertex from geometry
    STDelete: Deleting vertices in geometry objects
    STFlipVectors: Normalize direction of linestring vectors
    STConvertToLineString: Extract LineStrings in GeometryCollection to create LineString
    STLine2Cogo: Converting LineStrings to COGO XML
    STCogo2Line: Creating (Multi)LineStrings geometries from COGO XML instructions
    STVectorize: Break Linestring/Polygon elements into 2 point vectors (or 3 point circular curves)
    STScale: Function to Scale a geometry object
    TSQL String Tokenizer Function for SQL Server
    STGeometry2MBR/STGeography2MBR: Compute and return MBR ordinates
    generate_series for SQL Server 2008
    STExtractPolygon: Extract Polygons from result of STIntersection in SQL Server Spatial
    STRound: Function to round ordinates of a SQL Server Spatial geometry object
    STExtract: Extract elements of a geometry object
    STNumRings: Counting number of polygon rings
    STFilterRings: Removing rings from Polygon based on area.
    STMove: Function to Move a geometry object in SQL Server Spatial
    STCentroid*: Alternate Functions for Compute a Centroid
    STRotate: Function to rotate a geometry object in SQL Server Spatial
    STVertices: Wrapper over STDumpPoints
    STMorton: Creating a Morton number Space Key value for grid square
    Gridding a geometry or geography object (SQL Server Denali)
    On hinting spatial indexes
    RandomSearchByExtent: Random Search Procedure (2008 Spatial)
    COGO: Convert DMS String to decimal degrees floating point number.
    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
    CheckRadii: 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
    STisGeo: Checking if a column in a table or a view is of type geometry or geography
    Vectorising geometry objects in SQL Server 2008
    STMBR2Geometry/STMBR2Geography 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
    STDumpPoints: A Function that Dumps the Vertices/Points in a geometry
    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.

STMove: 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. -- Delete if Exists
  6. --
  7. IF EXISTS (SELECT *
  8.              FROM sysobjects
  9.             WHERE id = object_id (N'[dbo].[STMove]')
  10.               AND xtype IN (N'FN', N'IF', N'TF')
  11. )
  12. BEGIN
  13.   DROP FUNCTION  [dbo].[STMove];
  14.   PRINT 'Dropped [dbo].[STMove] ...';
  15. END;
  16. GO
  17. -- Create
  18. --
  19. CREATE FUNCTION [dbo].[STMove]
  20. (
  21.   @p_geometry geometry,
  22.   @p_dX       FLOAT,
  23.   @p_dY       FLOAT,
  24.   @p_dZ       FLOAT,
  25.   @p_dM       FLOAT,
  26.   @p_round_xy INT = 3,
  27.   @p_round_zm INT = 2
  28. )
  29. RETURNS geometry
  30. BEGIN
  31.   DECLARE
  32.      @v_coord          INT = 0,
  33.      @v_rows           INT = 0,
  34.      @v_wkt            VARCHAR(MAX) = '',
  35.      @v_token          VARCHAR(MAX),
  36.      @v_delim          VARCHAR(MAX),
  37.      @v_geometry       geometry,
  38.      @v_x              FLOAT = 0.0,
  39.      @v_y              FLOAT = 0.0,
  40.      @v_z              FLOAT = NULL,
  41.      @v_m              FLOAT = NULL;
  42.   BEGIN
  43.     IF ( @p_geometry IS NULL )
  44.       RETURN NULL;
  45.     IF ( @p_dX IS NULL AND @p_dY IS NULL AND @p_dZ IS NULL AND @p_dM IS NULL )
  46.        RETURN CAST('One of deltas X,Y,Z and M must not be NULL.' AS VARCHAR(MAX)); -- geometry);
  47.     SET @v_coord = 0;
  48.     SET @v_rows  = 0;
  49.     DECLARE Tokens CURSOR FAST_FORWARD FOR
  50.       SELECT t.token, t.separator
  51.         FROM dbo.Tokenizer(@p_geometry.AsTextZM(),' ,()') AS t;
  52.     OPEN Tokens;
  53.     FETCH NEXT FROM Tokens INTO @v_token, @v_delim;
  54.     WHILE @@FETCH_STATUS = 0
  55.     BEGIN
  56.        IF ( @v_token IS NULL )  -- double delimiter
  57.        BEGIN
  58.           SET @v_wkt = @v_wkt + @v_delim
  59.        END
  60.        ELSE
  61.        BEGIN
  62.           IF ( @v_token NOT LIKE '[-0-9]%' )
  63.           BEGIN
  64.              SET @v_wkt = @v_wkt + @v_token + LTRIM(@v_delim)
  65.           END
  66.           ELSE -- @v_token LIKE '[0-9]%' )  
  67.           BEGIN
  68.              SET @v_coord = @v_coord + 1;
  69.              IF ( @v_coord = 1 ) SET @v_x = CAST(@v_token AS FLOAT)
  70.              IF ( @v_coord = 2 ) SET @v_y = CAST(@v_token AS FLOAT)
  71.              IF ( @v_coord = 3 ) SET @v_z = CAST(@v_token AS FLOAT)
  72.              IF ( @v_coord = 4 ) SET @v_m = CAST(@v_token AS FLOAT)
  73.              IF ( @v_delim IN (',',')') )
  74.              BEGIN
  75.                 SET @v_wkt = @v_wkt +
  76.                    LTRIM(STR(round(@v_x + @p_dX, @p_round_x),24,@p_round_x)) + ' ' +
  77.                    LTRIM(STR(round(@v_y + @p_dY, @p_round_y),24,@p_round_y)) +
  78.                    CASE WHEN @v_z IS NULL THEN '' ELSE ' ' + LTRIM(STR(round(@v_z + @p_dZ, @p_round_z),24,@p_round_z)) END +
  79.                    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;
  80.                 SET @v_coord = 0;
  81.              END;
  82.            END;
  83.        END;
  84.        FETCH NEXT FROM Tokens INTO @v_token, @v_delim;
  85.     END;
  86.     CLOSE Tokens
  87.     DEALLOCATE Tokens
  88.     SET @v_geometry = geometry::STGeomFromText(@v_wkt,@p_geometry.STSrid);
  89.     RETURN @v_geometry;
  90.   END
  91. END
  92. 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 [8]

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 #

great function, thanks for sharing!!

except, i keep getting an error about invalid geometry. what’s weird is it alternates between working, and giving me the error, without my making any changes in between. i run it, get the error, run it again, it works. this keeps happening, without any clear logic as to when it fails and why.

it fails sometimes when i try to move ‘simple’ features. but it fails most often when i try to run it on ‘merged’ features created using a geometry::UnionAggregate() function

i don’t doubt there is bad geometry in the dataset. it is a parcel layer for a major city that it’s my job to clean up. there are over 600,000 features, and i’ve encountered every sort of geometry error you can imagine.

i’ve tried running @geometry.MakeValid() before running your function to no avail

below is the error i’m getting. any insight would be much appreciated.
thanks again,

Msg 6522, Level 16, State 1, Procedure ParcelMatch_1M_Translated, Line 17 [Batch Start Line 47]
A .NET Framework error occurred during execution of user-defined routine or aggregate “geometry”:
System.FormatException: 24119: The Polygon input is not valid because the start and end points of the exterior ring are not the same. Each ring of a polygon must have the same start and end points.
System.FormatException: at Microsoft.SqlServer.Types.GeometryValidator.ValidatePolygonRing(Int32 iRing, Int32 cPoints, Double firstX, Double firstY, Double lastX, Double lastY) at Microsoft.SqlServer.Types.Validator.Execute(Transition transition) at Microsoft.SqlServer.Types.ForwardingGeoDataSink.EndFigure() at Microsoft.SqlServer.Types.WellKnownTextReader.ParseLineStringText() at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePolygonText() at Microsoft.SqlServer.Types.WellKnownTextReader.ParseMultiPolygonText() at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type) at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid) at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)

eric 20 February 2019, 03:11 #