Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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