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