Top 5 Recent Articles

ARTICLES CATEGORIES

STDelete: Deleting vertices in geometry objects

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_Delete function, which deletes one or more coordinates from the supplied geometry.

 CREATE FUNCTION [dbo].[STDelete]
 (
   @p_geometry   geometry,
   @p_point_list VARCHAR(MAX) = '1',
   @p_round_xy   INT = 3,
   @p_round_zm   INT = 2
 )
 RETURNS geometry
 AS
 BEGIN
   -- Implementation
 END

Description

Function removes one or more coordinates from the supplied geometry.
The function does not process POINT or GEOMETRYCOLLECTION geometries.
The list of points to be deleted is supplied as a comma separated string of point numbers.
The point numbers are from 1 to the total number of points in a WKT representation of the object.
Point numbers do not refer to specific points 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_point_line    (VARCHAR) - Comma separated list OF point numbers FROM 1 TO the total NUMBER IN a geometry's WKT representation.
 @p_round_xy          (int) - Rounding value for XY ordinates.
 @p_round_zm          (int) - Rounding value for ZM ordinates.

Examples

 SELECT 'LineString - Last Point' AS msg,
        [dbo].[STDelete](geometry::STGeomFromText('LINESTRING(1 1, 2 2, 3 3, 4 4)',0),'-1',3,2).AsTextZM() AS WKT
 GO
msg WKT
LineString – Last Point LINESTRING (1 1, 2 2, 3 3) 
 SELECT 'MULTIPOLYGON Single Point - OK' AS msg,
        [dbo].[STDelete] (
          geometry::STGeomFromText('MULTIPOLYGON (((0 0, 5 0, 10 0, 5 5, 0 0)),((20 20, 25 20, 30 20, 25 30, 20 20),(22 22, 25 26, 28 22, 22 22)))',0),
          '2',3,2).AsTextZM() AS WKT
 GO
msg WKT
MULTIPOLYGON Single Point – OK MULTIPOLYGON (((0 0, 10 0, 5 5, 0 0)), ((20 20, 25 20, 30 20, 25 30, 20 20), (22 22, 25 26, 28 22, 22 22))) 
 SELECT 'MULTIPOLYGON Two Points - Fail' AS msg,
        [dbo].[STDelete](
          geometry::STGeomFromText('MULTIPOLYGON (((0 0, 5 0, 10 0, 5 5, 0 0)),((20 20, 25 20, 30 20, 25 30, 20 20),(22 22, 25 26, 28 22, 22 22)))',0),
          '2,3',3,2) AS t
 GO
 Msg 6522, Level 16, State 1, Line 15
 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.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)
 .

The function can be downloaded from here