Top 5 Recent Articles

ARTICLES CATEGORIES

STInsertN: Insert single vertex into a 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_InsertN function, which inserts a new coordinate in a supplied geometry at specific position.

 CREATE FUNCTION [dbo].[STInsertN]
 (
   @p_geometry geometry,
   @p_point    geometry,
   @p_position INTEGER,
   @p_round_xy INT   = 3,
   @p_round_zm INT   = 2
 )
 RETURNS geometry
 AS
 BEGIN
   -- Implementation details in source code.
 END
 GO

Description

Function that inserts the coordinate p_point into position p_position in the supplied geometry.
p_position Values: 1. null -> defaults to 1; 2. -1 -> maximum number of points ie STNumPoints(p_geometry) 3. Greater than STNumPoints(p_geometry) -> maximum number of points ie STNumPoints(p_geometry) The inserted coordinate’s ordinates are rounded to p_round_xy number of decimal digits of precision.

Arguments

 @p_geometry (geometry) - Supplied geometry OF any TYPE.
 @p_point    (geometry) - INSERT coordinate
 @p_position (INT)      - Coordinate POSITION IN @p_geometry: BETWEEN 1 AND STNumPoints.
 @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

 -- Null p_geometry Parameter returns p_point
 SELECT 1 AS testid,
        [dbo].[STInsertN](NULL,
                           geometry::Point(9,9,0) /* 2D */,
                           1,3,NULL).AsTextZM() AS geom
 GO
 testid  geom
 1 POINT (9 9)
 -- Null p_geometry Parameter returns p_point
 SELECT 2 AS testid,
        [dbo].[STInsertN](NULL,
                           geometry::STPointFromText('POINT(9 9 0)',0) /* 3D */,
                           1,3,2).AsTextZM() AS geom
 GO
 testid  geom
 2 POINT (9 9 0)
 -- No point to add so return geometry
 SELECT 3 AS testid,
        [dbo].[STInsertN](geometry::STGeomFromText('LINESTRING(0 0, 10 0)',0),
                       NULL,
                       1,3,2).AsTextZM() AS geom
 GO
 testid  geom
 3 LINESTRING (0 0, 10 0)
 -- Geometry Collections not supported, so is returned.
 SELECT 4 AS testid,
        [dbo].[STInsertN](geometry::STGeomFromText('GEOMETRYCOLLECTION(POINT(2 3 4),LINESTRING(2 3 4,3 4 5))',0),
                           geometry::Point(9,9,0),
                           1,3,2).AsTextZM() AS geom
 GO
 testid  geom
 4 GEOMETRYCOLLECTION (POINT (2 3 4), LINESTRING (2 3 4, 3 4 5))
 -- p_point must be point, so geometry is returned.
 SELECT 5 AS testid,
        [dbo].[STInsertN](geometry::STGeomFromText('LINESTRING(0 0, 10 0)',0),
                          geometry::STGeomFromText('POLYGON((1 1, 1 6, 11 6, 11 1, 1 1))',0),
                          1,3,2).AsTextZM() AS geom
 GO
 testid  geom
 5 LINESTRING (0 0, 10 0)
 -- Insert from begining to end
 SELECT 6 AS testid,
        a.IntValue AS insert_position,
        [dbo].[STInsertN](geometry::STGeomFromText('LINESTRING(0 0, 10 0)',0),
                           geometry::Point(9,9,0),
                           a.IntValue,
                           0,
                           2).AsTextZM() AS geom
   FROM [dbo].[generate_series](-1,4,1) a
 GO
 testid  insert_position geom
 6 -1  LINESTRING (0 0, 10 0, 9 9)
 6 0 LINESTRING (9 9, 0 0, 10 0)
 6 1 LINESTRING (9 9, 0 0, 10 0)
 6 2 LINESTRING (0 0, 9 9, 10 0)
 6 3 LINESTRING (0 0, 10 0, 9 9)
 6 4 LINESTRING (0 0, 10 0, 9 9)
 SELECT 7 AS testid,
        [dbo].[STInsertN](geometry::STGeomFromText('MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))',0),
                               geometry::Point(0.5,0.5,0),
                               2,
                               3,2).AsTextZM() AS geom;
 GO
 testid  geom
 7 MULTILINESTRING ((0 0, 0.5 0.5, 1 1, 1 2), (2 3, 3 2, 5 4))
 -- Add point to start of multipoint
 SELECT 8 AS testid,
        [dbo].[STInsertN](geometry::STGeomFromText('MULTIPOINT(1 2 3)',0), /* 3D */
                           geometry::Point(9.4,9.7,0), /* 2D */
                           1,
                           3,
                           2).AsTextZM() AS geom
 GO
 testid  geom
 8 MULTIPOINT ((9.4 9.7), (1 2 3))
 -- Add point to end of multipoint
 SELECT 9 AS testid,
        [dbo].[STInsertN](geometry::STGeomFromText('MULTIPOINT(1 2 3)',0),
                           geometry::Point(9.4,9.7,0),
                           -1,
                           3,
                           2).AsTextZM() AS geom
 GO
 testid  geom
 9 MULTIPOINT ((1 2 3), (9.4 9.7))
 -- Point -> Multipoint from two points
 SELECT 10 AS testid,
        t.intValue AS POSITION,
        [dbo].[STInsertN](geometry::STGeomFromText('POINT(0 0 0)',  0),
                          geometry::STGeomFromText('POINT(3 3 2 2)',0),
                          t.IntValue,
                          1,2).AsTextZM() AS geom
   FROM dbo.Generate_Series(-1,1,1) AS t
  WHERE t.IntValue <> 0
 GO
 testid  POSITION  geom
 10  -1  MULTIPOINT ((0 0 0), (3 3 2))
 10  1 MULTIPOINT ((3 3 2), (0 0 0))
 WITH geoms AS (
           SELECT 1 AS id, geometry::Point(16394506.234,-5283738.5676878,3857)  AS p_point,
                  0 AS p_insert_point, 2 AS p_precision
 UNION ALL SELECT 2 AS id, geometry::STGeomFromText('MULTIPOINT(1 2 3)',3857)   AS p_point,
                  1 AS p_insert_point, 0 AS p_precision
 UNION ALL SELECT 3 AS id, geometry::STGeomFromText('MULTIPOINT(1 2 3 4)',3857) AS p_point,
                  2 AS p_insert_point, 0 AS p_precision
 )
 SELECT 11 AS testid,
        [dbo].[STInsertN](a.p_point,
                           geometry::Point(1111111.234,-222222222.567,3857),
                           a.p_insert_point,
                           a.p_precision,
                           2).AsTextZM() AS geom
  FROM geoms a
 GO
 testid  geom
 11  MULTIPOINT ((1111111.23 -222222222.57), (16394506.23 -5283738.57))
 11  MULTIPOINT ((1111111 -222222223), (1 2 3))
 11  MULTIPOINT ((1 2 3 4), (1111111 -222222223))
 -- Insert Point with NULL Z ordinates
 SELECT 12 AS testid,
        a.IntValue AS InsertPosn,
        [dbo].[STInsertN](geometry::STGeomFromText('LINESTRING (63.29 914.361 NULL 1, 73.036 899.855 NULL 18.48, 80.023 897.179 NULL 25.96, 79.425 902.707 NULL 31.52, 91.228 903.305 NULL 43.34, 79.735 888.304 NULL 62.23, 98.4 883.584 NULL 81.49, 115.73 903.305 NULL 107.74, 102.284 923.026 NULL 131.61, 99.147 899.271 NULL 155.57, 110.8 902.707 NULL 167.72, 90.78 887.02 NULL 193.15, 96.607 926.911 NULL 233.47, 95.71 926.313 NULL 234.55, 95.412 928.554 NULL 236.81, 101.238 929.002 NULL 242.65, 119.017 922.279 NULL 261.66)',0),
                          geometry::STGeomFromText('POINT (80.5823 901.3054 NULL 30)',0),
                          a.IntValue,
                          1,2).AsTextZM() AS geom
   FROM [dbo].[generate_series](-1,4,1) a
 GO
 testid  InsertPosn  geom
 12  -1  LINESTRING (63.3 914.4 NULL 1, 73 899.9 NULL 18.48, 80 897.2 NULL 25.96, 79.4 902.7 NULL 31.52, 91.2 903.3 NULL 43.34, 79.7 888.3 NULL 62.23, 98.4 883.6 NULL 81.49, 115.7 903.3 NULL 107.74, 102.3 923 NULL 131.61, 99.1 899.3 NULL 155.57, 110.8 902.7 NULL 167.72, 90.8 887 NULL 193.15, 96.6 926.9 NULL 233.47, 95.7 926.3 NULL 234.55, 95.4 928.6 NULL 236.81, 101.2 929 NULL 242.65, 119 922.3 NULL 261.66, 80.6 901.3 NULL 30)
 12  0 LINESTRING (80.6 901.3 NULL 30, 63.3 914.4 NULL 1, 73 899.9 NULL 18.48, 80 897.2 NULL 25.96, 79.4 902.7 NULL 31.52, 91.2 903.3 NULL 43.34, 79.7 888.3 NULL 62.23, 98.4 883.6 NULL 81.49, 115.7 903.3 NULL 107.74, 102.3 923 NULL 131.61, 99.1 899.3 NULL 155.57, 110.8 902.7 NULL 167.72, 90.8 887 NULL 193.15, 96.6 926.9 NULL 233.47, 95.7 926.3 NULL 234.55, 95.4 928.6 NULL 236.81, 101.2 929 NULL 242.65, 119 922.3 NULL 261.66)
 12  1 LINESTRING (80.6 901.3 NULL 30, 63.3 914.4 NULL 1, 73 899.9 NULL 18.48, 80 897.2 NULL 25.96, 79.4 902.7 NULL 31.52, 91.2 903.3 NULL 43.34, 79.7 888.3 NULL 62.23, 98.4 883.6 NULL 81.49, 115.7 903.3 NULL 107.74, 102.3 923 NULL 131.61, 99.1 899.3 NULL 155.57, 110.8 902.7 NULL 167.72, 90.8 887 NULL 193.15, 96.6 926.9 NULL 233.47, 95.7 926.3 NULL 234.55, 95.4 928.6 NULL 236.81, 101.2 929 NULL 242.65, 119 922.3 NULL 261.66)
 12  2 LINESTRING (63.3 914.4 NULL 1, 80.6 901.3 NULL 30, 73 899.9 NULL 18.48, 80 897.2 NULL 25.96, 79.4 902.7 NULL 31.52, 91.2 903.3 NULL 43.34, 79.7 888.3 NULL 62.23, 98.4 883.6 NULL 81.49, 115.7 903.3 NULL 107.74, 102.3 923 NULL 131.61, 99.1 899.3 NULL 155.57, 110.8 902.7 NULL 167.72, 90.8 887 NULL 193.15, 96.6 926.9 NULL 233.47, 95.7 926.3 NULL 234.55, 95.4 928.6 NULL 236.81, 101.2 929 NULL 242.65, 119 922.3 NULL 261.66)
 12  3 LINESTRING (63.3 914.4 NULL 1, 73 899.9 NULL 18.48, 80.6 901.3 NULL 30, 80 897.2 NULL 25.96, 79.4 902.7 NULL 31.52, 91.2 903.3 NULL 43.34, 79.7 888.3 NULL 62.23, 98.4 883.6 NULL 81.49, 115.7 903.3 NULL 107.74, 102.3 923 NULL 131.61, 99.1 899.3 NULL 155.57, 110.8 902.7 NULL 167.72, 90.8 887 NULL 193.15, 96.6 926.9 NULL 233.47, 95.7 926.3 NULL 234.55, 95.4 928.6 NULL 236.81, 101.2 929 NULL 242.65, 119 922.3 NULL 261.66)
 12  4 LINESTRING (63.3 914.4 NULL 1, 73 899.9 NULL 18.48, 80 897.2 NULL 25.96, 80.6 901.3 NULL 30, 79.4 902.7 NULL 31.52, 91.2 903.3 NULL 43.34, 79.7 888.3 NULL 62.23, 98.4 883.6 NULL 81.49, 115.7 903.3 NULL 107.74, 102.3 923 NULL 131.61, 99.1 899.3 NULL 155.57, 110.8 902.7 NULL 167.72, 90.8 887 NULL 193.15, 96.6 926.9 NULL 233.47, 95.7 926.3 NULL 234.55, 95.4 928.6 NULL 236.81, 101.2 929 NULL 242.65, 119 922.3 NULL 261.66)

The function can be downloaded from here