STDeleteN: Delete single vertex from geometry

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 ST_DeleteN function, which deletes a single coordinate from the supplied geometry.

 CREATE FUNCTION [dbo].[STDeleteN]
 (
   @p_geometry geometry,
   @p_position INT = 1,
   @p_round_xy INT = 3,
   @p_round_zm INT = 2
 )
 RETURNS geometry
 AS
 BEGIN
   RETURN [dbo].[STDelete] (
             @p_geometry,
             CAST(ISNULL(@p_position,1) AS VARCHAR(10)),
             @p_round_xy,
             @p_round_zm
          );
 END
 GO

Description

Function that removes a single, nominated, coordinates from the supplied geometry.
The function does not process POINT or GEOMETRYCOLLECTION geometries.
The point to be deleted is supplied as a single integer.
The point number can be supplied as -1 (last number), or 1 to the total number of points in a WKT representation of the object.
A point number does not refer to a specific point within a specific sub-geometry eg point number 1 in the 2nd interiorRing in a polygon object.

Arguments

 @p_geometry     (geometry) - supplied geometry OF any TYPE.
 @p_position   (INT) - Valid point NUMBER IN geometry.
 @p_round_xy          (INT) - Rounding VALUE FOR XY ordinates.
 @p_round_zm          (INT) - Rounding VALUE FOR ZM ordinates.

Examples

 SELECT 'Single Point - No Action' AS message,
        [dbo].[STDeleteN](geometry::STGeomFromText('POINT(0 0 1 1)',0),1,3,2).AsTextZM() AS WKT
 GO
 message WKT
 Single Point - No Action  POINT (0 0 1 1)
 SELECT 'MultiPoint - No Action'   AS message,
        [dbo].[STDeleteN](geometry::STGeomFromText('MULTIPOINT((0 0 1 1))',0),1,3,2).AsTextZM() AS WKT
 GO
 message WKT
 MultiPoint - No Action  MULTIPOINT ((0 0 1 1))
 SELECT 'MultiPoint - All Points'  AS message,
        t.IntValue,
        [dbo].[STDeleteN](geometry::STGeomFromText('MULTIPOINT((1 1 1 1),(2 2 2 2),(3 3 3 3))',0),t.IntValue,3,2).AsTextZM() AS WKT
   FROM [dbo].[generate_series](-1,4,1) AS t
 GO
 message IntValue  WKT
 MultiPoint - ALL Points -1  MULTIPOINT ((1 1 1 1), (2 2 2 2))
 MultiPoint - ALL Points 0 MULTIPOINT ((1 1 1 1), (2 2 2 2), (3 3 3 3))
 MultiPoint - ALL Points 1 MULTIPOINT ((2 2 2 2), (3 3 3 3))
 MultiPoint - ALL Points 2 MULTIPOINT ((1 1 1 1), (3 3 3 3))
 MultiPoint - ALL Points 3 MULTIPOINT ((1 1 1 1), (2 2 2 2))
 MultiPoint - ALL Points 4 MULTIPOINT ((1 1 1 1), (2 2 2 2), (3 3 3 3))
 SELECT 'LineString - All Points' AS message,
        t.IntValue,
        [dbo].[STDeleteN](geometry::STGeomFromText('LINESTRING(1 1, 2 2, 3 3, 4 4)',0),t.IntValue,3,2).AsTextZM() AS WKT
   FROM [dbo].[generate_series](-1,5,1) AS t
 GO
  message IntValue  WKT
 LineString - ALL Points -1  LINESTRING (1 1, 2 2, 3 3)
 LineString - ALL Points 0 LINESTRING (1 1, 2 2, 3 3, 4 4)
 LineString - ALL Points 1 LINESTRING (2 2, 3 3, 4 4)
 LineString - ALL Points 2 LINESTRING (1 1, 3 3, 4 4)
 LineString - ALL Points 3 LINESTRING (1 1, 2 2, 4 4)
 LineString - ALL Points 4 LINESTRING (1 1, 2 2, 3 3)
 LineString - ALL Points 5 LINESTRING (1 1, 2 2, 3 3, 4 4)
 SELECT 'MultiLineString - All Points' AS message,
        t.IntValue,
        [dbo].[STDeleteN](geometry::STGeomFromText('MULTILINESTRING((1 1,2 2,3 3),(4 4,5 5,6 6))',0),t.IntValue,3,2).AsTextZM() AS WKT
   FROM [dbo].[generate_series](-1,7,1) AS t
 GO
 message IntValue  WKT
 MultiLineString - ALL Points  -1  MULTILINESTRING ((1 1, 2 2, 3 3), (4 4, 5 5))
 MultiLineString - ALL Points  0 MULTILINESTRING ((1 1, 2 2, 3 3), (4 4, 5 5, 6 6))
 MultiLineString - ALL Points  1 MULTILINESTRING ((2 2, 3 3), (4 4, 5 5, 6 6))
 MultiLineString - ALL Points  2 MULTILINESTRING ((1 1, 3 3), (4 4, 5 5, 6 6))
 MultiLineString - ALL Points  3 MULTILINESTRING ((1 1, 2 2), (4 4, 5 5, 6 6))
 MultiLineString - ALL Points  4 MULTILINESTRING ((1 1, 2 2, 3 3), (5 5, 6 6))
 MultiLineString - ALL Points  5 MULTILINESTRING ((1 1, 2 2, 3 3), (4 4, 6 6))
 MultiLineString - ALL Points  6 MULTILINESTRING ((1 1, 2 2, 3 3), (4 4, 5 5))
 MultiLineString - ALL Points  7 MULTILINESTRING ((1 1, 2 2, 3 3), (4 4, 5 5, 6 6))
 WITH poly AS (
   SELECT geometry::STGeomFromText('POLYGON((326000.0 5455000.0,327000.0 5455000.0,326820 5455440,326500.0 5456000.0,326000.0 5455000.0))',0) AS poly
 )
 SELECT 'ExteriorRing -- Sufficient Points - Note first and last point avoided.' AS message,
        t.IntValue,
        [dbo].[STDeleteN](a.poly,t.intValue,3,2).AsTextZM() AS t
   FROM poly AS a CROSS apply [dbo].[generate_series](2,a.poly.STNumPoints()-1,1) AS t
 GO
 message IntValue  t
 ExteriorRing -- Sufficient Points - Note first and last point avoided.  2 POLYGON ((326000 5455000, 326820 5455440, 326500 5456000, 326000 5455000))
 ExteriorRing -- Sufficient Points - Note first and last point avoided.  3 POLYGON ((326000 5455000, 327000 5455000, 326500 5456000, 326000 5455000))
 ExteriorRing -- Sufficient Points - Note first and last point avoided.  4 POLYGON ((326000 5455000, 327000 5455000, 326820 5455440, 326000 5455000))
 SELECT 'SAME OUT' AS message,
        [dbo].[STDeleteN](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),
                         3,3,2).AsTextZM() AS WKT
 GO
 message WKT
 SAME OUT  GEOMETRYCOLLECTION (POINT (2 3 4), LINESTRING (2 3 4, 3 4 5), POLYGON ((326000 5455000, 327000 5455000, 326500 5456000, 326000 5455000)))
 SELECT 'LineString - Points' AS message,
        [dbo].[STDelete](geometry::STGeomFromText('LINESTRING(1 1, 2 2, 3 3, 4 4)',0),'1,2',3,2).AsTextZM() AS WKT
 GO
 message WKT
 LineString - Points LINESTRING (3 3, 4 4)
 SELECT 'LineString - Last Point' AS message,
        [dbo].[STDeleteN](geometry::STGeomFromText('LINESTRING(1 1, 2 2, 3 3, 4 4)',0),-1,3,2).AsTextZM() AS WKT
 GO
 message WKT
 LineString - LAST Point LINESTRING (1 1, 2 2, 3 3)

The following examples all fail.

 SELECT 'Polygon - All Points' AS message,
        t.IntValue,
        [dbo].[STDeleteN](geometry::STGeomFromText('POLYGON((1 1,10 1,10 10,1 10,1 1),(2 2,9 2,9 9,2 9,2 2))',0),t.intValue,3,2) AS t
   FROM [dbo].[generate_series](-1,11,1) AS t
 GO
 Msg 6522, Level 16, State 1, Line 1
 A .NET Framework error occurred during execution OF user-defined ROUTINE OR aggregate "geometry":
 System.FormatException: 24121: The Polygon INPUT IS NOT valid because the START AND END points OF the interior ring NUMBER 1 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.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).
 SELECT 'ExteriorRing -- Insufficient Points' AS message,
        t.IntValue,
        [dbo].[STDeleteN](geometry::STGeomFromText('POLYGON((326000.0 5455000.0,327000.0 5455000.0,326500.0 5456000.0,326000.0 5455000.0))',0),t.intValue,3,2) AS t
   FROM [dbo].[generate_series](2,5,1) AS t
 GO
 Msg 6522, Level 16, State 1, Line 20
 A .NET Framework error occurred during execution OF user-defined ROUTINE OR aggregate "geometry":
 System.FormatException: 24118: The Polygon INPUT IS NOT valid because the exterior ring does NOT have enough points. Each ring OF a polygon must contain at least four 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.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).
 SELECT 'Polygon with InteriorRing (end up with insufficient points)' AS message,
        [dbo].[STDeleteN](geometry::STGeomFromText('POLYGON((326000.0 5455000.0,327000.0 5455000.0,326500.0 5456000.0,326000.0 5455000.0),
                                                           (326500.0 5455500.0,326550.0 5455200.0,326450.0 5455200.0,326500.0 5455500.0))',0),
                              t.intValue,3,2) AS t
   FROM [dbo].[generate_series](-1,11,1) AS t
 GO
 Msg 6522, Level 16, State 1, Line 38
 A .NET Framework error occurred during execution OF user-defined ROUTINE OR aggregate "geometry":
 System.FormatException: 24120: The Polygon INPUT IS NOT valid because the interior ring NUMBER 1 does NOT have enough points. Each ring OF a polygon must contain at least four 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.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