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)
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
Documentation
- MySQL Spatial General Functions
- Oracle LRS Objects
- Oracle Spatial Exporter (Java + pl/SQL)
- Oracle Spatial Object Functions
- Oracle Spatial Object Functions (Multi Page)
- PostGIS pl/pgSQL Functions
- SC4O Oracle Java Topology Suite (Java + pl/SQL)
- SQL Server Spatial General TSQL Functions
- SQL Server Spatial LRS TSQL Functions