Top 5 Recent Articles

ARTICLES CATEGORIES

STUpdate: Replace all points equal to the supplied point with replacement point.

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 STUpdate function, which updates (replaces) all geometry points that are equal to the supplied point with the replacement point.

 CREATE FUNCTION [$(owner)].[STUpdate]
 (
   @p_geometry    geometry,
   @p_original_pt geometry,
   @p_replace_pt  geometry,
   @p_round_xy    INT = 3,
   @p_round_zm    INT = 2
 )
   RETURNS geometry
 AS
 BEGIN
     -- Implementation
 END
 GO

Description

Function that updates all coordinates that equal p_point with the supplied p_replace_point.
The p_round_xy value is used when comparing geometry point’s XY ordinates to p_point’s.
The updated coordinate’s XY ordinates are rounded to p_round_xy number of decimal digits of precision. The updated coordinate’s ZM ordinates are rounded to p_round_ZM number of decimal digits of precision.

Arguments

 @p_geometry      (geometry) - supplied geometry OF any TYPE.
 @p_point         (geometry) - Original coordinate TO be replaced.
 @p_replace_point (geometry) - Replacement coordinate
 @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].[STUpdate](geometry::STGeomFromText('POINT(0 0 1 1)',0),
                         geometry::STGeomFromText('POINT(0 0 1 1)',0),
                         geometry::STGeomFromText('POINT(1 1 1 1)',0),2,1).AsTextZM() AS WKT
 GO
 WKT
 POINT (1 1 1 1)
 SELECT [dbo].[STUpdate](geometry::STGeomFromText('MULTIPOINT((1 1 1 1),(2 2 2 2),(3 3 3 3))',0),
                         geometry::STGeomFromText('POINT(2 2 2 2)',0),
                         geometry::STGeomFromText('POINT(2.1 2.1 2 2)',0),2,1).AsTextZM() AS WKT
 GO
 WKT
 MULTIPOINT ((1 1 1 1), (2.1 2.1 2 2), (3 3 3 3))
 SELECT [dbo].[STUpdate](geometry::STGeomFromText('LINESTRING(1 1, 2 2, 3 3, 4 4)',0),
                         geometry::STGeomFromText('POINT(3 3)',0),
                         geometry::STGeomFromText('POINT(2.1 2.5)',0),2,1).AsTextZM() AS WKT
 GO
 WKT
 LINESTRING (1 1, 2 2, 2.1 2.5, 4 4)
 SELECT [dbo].[STUpdate](geometry::STGeomFromText('MULTILINESTRING((1 1,2 2,3 3),(4 4,5 5,6 6))',0),
                         geometry::STGeomFromText('POINT(3 3)',0),
                         geometry::STGeomFromText('POINT(3.1 3.3)',0),2,1).AsTextZM() AS WKT
 GO
 WKT
 MULTILINESTRING ((1 1, 2 2, 3.1 3.3), (4 4, 5 5, 6 6))
 SELECT [dbo].[STUpdate](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.962 0.872)',0),
                         geometry::STGeomFromText('POINT(9.9 0.9)',0),2,1).AsTextZM() AS WKT
 GO
 WKT
 COMPOUNDCURVE (CIRCULARSTRING (9.962 -0.872, 10.1 0, 9.9 0.9), (9.9 0.9, 0 0, 9.962 -0.872))
 -- 'Polygon - First and lat point of ring update.
 SELECT [dbo].[STUpdate](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(1 1)',0),
                         geometry::STGeomFromText('POINT(1.1 1.1)',0),2,1).AsTextZM() AS WKT
 GO
 WKT
 POLYGON ((1.1 1.1, 10 1, 10 10, 1 10, 1.1 1.1), (2 2, 9 2, 9 9, 2 9, 2 2))
 SELECT [dbo].[STUpdate](geometry::STGeomFromText('POLYGON((326000.0 5455000.0,327000.0 5455000.0,326500.0 5456000.0,326000.0 5455000.0))',28355),
                         geometry::STGeomFromText('POINT(326000.0 5455000.0)',28355),
                         geometry::STGeomFromText('POINT(326100.0 5455100.0)',28355),2,1).AsTextZM() AS WKT
 GO
 WKT
 POLYGON ((326100 5455100, 327000 5455000, 326500 5456000, 326100 5455100))
 SELECT [dbo].[STUpdate](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 4 5)',0),
                         geometry::STGeomFromText('POINT(3.1 4.1 5.1)',0),2,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)))

The function can be downloaded from here