Top 5 Recent Articles

ARTICLES CATEGORIES

STUpdateN: Update (replace) a single vertex within a geometry object.

SQL Server Spatial, unlike spatial database type systems such as PostGIS, does not provide coordinate editing functions:

  • STAddPoint
  • STUpdatePoint
  • STDeletePoint

These functions are not a part of either the SQL or OGC standards. In fact neither provide an API for the SQL editing of geometry objects. If one wishes to do so, one must program replacement functions. For SQL based solutions (eg based on TSQL) one must manipulate a geometry’s Well Know Text (WKT) description. This is pretty ugly (unless you are an Oracle Locator/Spatial user and so can access the array holding the ordinate values), so to wrap such access in specific SQL accessible functions is of use.

This article introduces the STUpdateN function, which updates (replaces) a single vertex within the supplied geometry object.

 CREATE FUNCTION [dbo].[STUpdateN]
 (
   @p_geometry   geometry,
   @p_replace_pt geometry,
   @p_position   INT,
   @p_round_xy   INT = 3,
   @p_round_zm   INT = 2
 )
   RETURNS geometry
 AS
 BEGIN
     -- Implementation
 END
 GO

Description

Function that updates coordinate identified by p_position with the new point (p_replace_point) in the supplied geometry.

@p_position Values:
1. null -> defaults to 1;
2. -1 -> maximum number of points ie STNumPoints(p_geometry)
3. Greater than STNumPoints(p_geometry) -> maximum number of points ie STNumPoints(p_geometry)

The updated coordinate’s ordinates are rounded to @p_round_xy number of decimal digits of precision.

Arguments

 @p_geometry      (geometry) - supplied geometry OF any TYPE.
 @p_replace_point (geometry) - Replacement coordinate
 @p_position      (INT)      - Coordinate POSITION IN @p_geometry: BETWEEN 1 AND STNumPoints.
 @p_round_xy      (INT)      - DECIMAL degrees OF PRECISION TO which calculated XY ordinates are rounded.
 @p_round_zm      (INT)      - DECIMAL degrees OF PRECISION TO which calculated zm ordinates are rounded.

Examples

 SELECT [dbo].[STUpdateN](geometry::STGeomFromText('POINT(0 0 1 1)',0),
                          geometry::STGeomFromText('POINT(1 1 1 1)',0),
                          1,
                          0,1).AsTextZM() AS WKT
 GO
 WKT
 POINT (1 1 1 1)
 SELECT [dbo].[STUpdateN](geometry::STGeomFromText('MULTIPOINT((1 1 1 1),(2 2 2 2),(3 3 3 3))',0),
                          geometry::STGeomFromText('POINT(2.1 2.1 2 2)',0),
                          2,
                          1,1).AsTextZM() AS WKT
 GO
 WKT
 MULTIPOINT ((1 1 1 1), (2.1 2.1 2 2), (3 3 3 3))
 SELECT [dbo].[STUpdateN](geometry::STGeomFromText('LINESTRING(1 1, 2 2, 3 3, 4 4)',0),
                          geometry::STGeomFromText('POINT(2.1 2.5)',0),
                          3,
                          1,1).AsTextZM() AS WKT
 GO
 WKT
 LINESTRING (1 1, 2 2, 2.1 2.5, 4 4)
 SELECT [dbo].[STUpdateN](geometry::STGeomFromText('MULTILINESTRING((1 1,2 2,3 3),(4 4,5 5,6 6))',0),
                          geometry::STGeomFromText('POINT(3.1 3.3)',0),
                          3,
                          1,1).AsTextZM() AS WKT
 GO
 WKT
 MULTILINESTRING ((1 1, 2 2, 3.1 3.3), (4 4, 5 5, 6 6))
 SELECT [dbo].[STUpdateN](geometry::STGeomFromText('POLYGON((1 1,10 1,10 10,1 10,1 1),(2 2,9 2,9 9,2 9,2 2))',0),
                          geometry::STGeomFromText('POINT(9.1 2.1)',0),
                          7,
                          1,1).AsTextZM() AS WKT
 GO
 WKT
 POLYGON ((1 1, 10 1, 10 10, 1 10, 1 1), (2 2, 9.1 2.1, 9 9, 2 9, 2 2))
 SELECT [dbo].[STUpdateN](geometry::STGeomFromText('POLYGON((326000.0 5455000.0,327000.0 5455000.0,326500.0 5456000.0,326000.0 5455000.0))',28355),
                          geometry::STGeomFromText('POINT(326100.0 5455100.0)',28355),
                          2,
                          1,1).AsTextZM() AS WKT
 GO
 WKT
 POLYGON ((326000 5455000, 326100 5455100, 326500 5456000, 326000 5455000))
 SELECT [dbo].[STUpdateN](geometry::STGeomFromText('GEOMETRYCOLLECTION(POINT(2 3 4),LINESTRING(2 3 4,3 4 5),POLYGON((326000.0 5455000.0,327000.0 5455000.0,326500.0 5456000.0,326000.0 5455000.0)))',0),
                          geometry::STGeomFromText('POINT(3.1 4.1 5.1)',0),
                          3,
                          1,1).AsTextZM() AS WKT
 GO
 WKT
 GEOMETRYCOLLECTION (POINT (2 3 4), LINESTRING (2 3 4, 3.1 4.1 5.1), POLYGON ((326000 5455000, 327000 5455000, 326500 5456000, 326000 5455000)))
 -- FAILURE
 SELECT [dbo].[STUpdateN](geometry::STGeomFromText('COMPOUNDCURVE(CIRCULARSTRING(9.962 -0.872,10.1 0,9.962 0.872),(9.962 0.872,0 0,9.962 -0.872))',0),
                          geometry::STGeomFromText('POINT(9.9 0.9)',0),
                          3,
                          1,1).AsTextZM() AS WKT
 GO
 Msg 6522, Level 16, State 1, Line 3
 A .NET Framework error occurred during execution OF user-defined ROUTINE OR aggregate "geometry":
 System.FormatException: 24134: Sequential parts OF a compound curve must have one common endpoint. ADD a common endpoint. ALL coordinates, including optional Z AND M, must be equal.
 System.FormatException:
    at Microsoft.SqlServer.Types.WellKnownTextReader.ParseCompoundCurveText()
    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).

The function can be downloaded from here