Top 5 Recent Articles
- Algorithms (20)
- All (400)
- Biography (1)
- Blog (45)
- Business Requirements (1)
- Commentary (1)
- 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) (4)
- Open Source (18)
- Oracle Spatial and Locator (193)
- Partitioning (1)
- PostGIS (34)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (35)
- Space Curves (9)
- Spatial Database Functions (108)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (15)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
Removing Spikes in SQL Server Spatial Linestrings
The creation of spatial data from GPS observations is a universal thing for GIS today.
Linestring data can be created from a sequence of GPS point observations or GPS tracks.
In either case, it is common to see spikes within the linestrings formed from GPS data.
A spike is “a sharp, abrupt and narrow deviation from the normal course of a linestring”.
Graphically it can look like this:
The method I take for removing or resolving spikes is to systematically take three sequential vertices and compute the deflection angle subtended in the middle. If the subtended angle is less than some threshold value, a spike exists and is resolved by removing the middle vertex. The next three vertex triplet can then be selected and processed (formed from the remaining 2 vertices plus the next one) until finished.
There is an issue that must be raised. That is that if a linestring is invalid (STIsValid() = 0) – see second example above – vertex access functions such as STNumPoints() and STPointN() do not work.
with data as ( select geometry::STGeomFromText('LINESTRING(-1 1,0 0,0 1,0 0,1 1)',0) as geom -- Invalid linestring see second example above. ) select a.geom.STNumPoints() as numPoints, a.geom.STPointN(2) as point from data as a; Msg 6522, Level 16, State 1, Line 4 A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly. System.ArgumentException: at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid() at Microsoft.SqlServer.Types.SqlGeometry.STNumPoints()
See also another article on the issue of access to invalid geometries.
So that a common function can be used to process all three examples above, the best method is to have the function process Well Known Text (WKT).
Here is such a function:
CREATE FUNCTION [dbo].[STRemoveSpikesByWKT] ( @p_linestring varchar(max), @p_srid int = 0, @p_angle_threshold float = 0.5, @p_round_xy int = 3, @p_round_z int = 2, @p_round_m int = 2 ) Returns varchar(max) AS .... -- With a wrapper function that calls it. CREATE Function [dbo].[STRemoveSpikes] ( @p_linestring geometry, @p_angle_threshold float = 0.5, @p_round_xy int = 3, @p_round_z int = 2, @p_round_m int = 2 ) Returns geometry AS ...
Some examples as provided below which process two of the examples shown in the introduction.
with data as ( select geometry::STGeomFromText('LINESTRING(-1 1,0 0,0 1,0 0,1 1)',0) as geom ), spikeRemoval as ( select geometry::STGeomFromText([dbo].[STRemoveSpikesByWKT](a.geom.STAsText(),a.geom.STSrid,1.0,3,2,1),a.geom.STSrid) as geom from data as a ) select a.geom.STIsValid() as isValid, a.geom.STAsText() as geom from spikeRemoval as a; isValid geom 1 LINESTRING (-1 1, 0 0, 1 1)
with data as ( select geometry::STGeomFromText('LINESTRING(-1 1,-0.025 0,0 1,0.025 0,1 1)',0) as geom ), spikeRemoval as ( select [dbo].[STRemoveSpikes](a.geom,5.0,3,2,1) as geom from data as a ) select a.geom.STIsValid() as isValid, a.geom.STAsText() as geom from spikeRemoval as a; isValid geom 1 LINESTRING (-1 1, -0.025 0, 0.025 0, 1 1)
These functions are available in the collection of TSQL functions available from the Shop on this website.