STDensify: Densify LineString or Polygon geometry objects

Requirement

The ability to densify a lineString or polygon ring is a common requirement across database platforms.

Some, like PostGIS, has such functionality “out of the box”.

Others, such as Oracle Locator/Spatial do not which is why a function is available with the PL/SQL (Package and Object) code that is available for download from this site.

This blog post introduces the SQL Server Spatial version which available for download from this site.

Function

CREATE FUNCTION [$(owner)].[STDensify] (
  @p_geometry geometry,
  @p_distance float,
  @p_round_xy int = 10,
  @p_round_zm int = 10
)
RETURNS geometry
As
Begin
  -- Implementation
End
Go

Description

This function add vertices to an existing vertex-to-vertex described (m)linestring or (m)polygon sdo_geometry.

New vertices are added in such a way as to maintain existing vertices, that is, no existing vertices are removed.

Densification occurs on a single vertex-to-vertex segment basis.

If segment length is < p_distance no vertices are added. The implementation does not guarantee that the added vertices will be exactly p_distance apart; mostly they will be < @p_distance.. The implementation honours 3D and 4D shapes and averages these dimension values for the new vertices. The function does not support compound objects or objects with circles, or described by arcs. Any non (m)polygon/(m)linestring shape is simply returned as it is. Arguments

@p_geometry (geometry) - (M)Linestring or (m) polygon.
@p_distance    (Float) - The desired optimal distance between added vertices.
@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 ZM ordinates are compared.

Examples

Here are a selection of examples of how to call this function.

-- Densify 2D line into 4 segments
with data as (
select geometry::STGeomFromText('LINESTRING(0 0,10 10)',0) as geom
)
select [dbo].[STDensify](a.geom,a.geom.STLength()/4.0,3,2).AsTextZM() as dGeom
  from data as a;

dGeom
LINESTRING (0 0, 2.5 2.5, 5 5, 7.5 7.5, 10 10)

-- Distance between all vertices is < 4.0
select [dbo].[STDensify](geometry::STGeomFromText('LINESTRING (5 5, 5 7, 7 7, 7 5, 5 5)',0),4.0,3,2).AsTextZM() as dGeom;

dGeom
LINESTRING (5 5, 5 7, 7 7, 7 5, 5 5)

-- Simple Straight line.
select [$(owner)].[STDensify] (geometry::STGeomFromText('LINESTRING(100 100,900 900.0)',0),125.0,3,2).AsTextZM() as dGeom;

DGeom
LINESTRING (100 100, 188.889 188.889, 277.778 277.778, 366.667 366.667, 455.556 455.556, 544.444 544.444, 633.333 633.333, 722.222 722.222, 811.111 811.111, 900 900)

-- LineString with Z
select [dbo].[STDensify] (geometry::STGeomFromText('LINESTRING(100 100 1.0,900 900.0 9.0)',0),125.0,3,2).AsTextZM() as dGeom;

dGeom
LINESTRING (100 100 1, 180 180 1.8, 260 260 2.6, 340 340 3.4, 420 420 4.2, 500 500 5, 580 580 5.8, 660 660 6.6, 740 740 7.4, 820 820 8.2, 900 900 9)

-- LineStrings with ZM
select [dbo].[STDensify] (geometry::STGeomFromText('LINESTRING(100.0 100.0 -4.56 0.99, 110.0 110.0 -6.73 1.1)',0),2.5,3,2).AsTextZM() as dGeom;

dGeom
LINESTRING (100 100 -4.56 0.99, 101.667 101.667 -4.92 1.01, 103.333 103.333 -5.28 1.03, 105 105 -5.64 1.04, 106.667 106.667 -6.01 1.06, 108.333 108.333 -6.37 1.08, 110 110 -6.73 1.1)

-- MultiLineString.
select [dbo].[STDensify](geometry::STGeomFromText('MULTILINESTRING ((0 0, 5 5, 10 10),(20 20, 25 25, 30 30))',0),2.1,3,2).AsTextZM() as dGeom;

dGeom
MULTILINESTRING ((0 0, 1.25 1.25, 2.5 2.5, 3.75 3.75, 5 5, 6.25 6.25, 7.5 7.5, 8.75 8.75, 10 10), (20 20, 21.25 21.25, 22.5 22.5, 23.75 23.75, 25 25, 26.25 26.25, 27.5 27.5, 28.75 28.75, 30 30))

-- Polygon 
select [dbo].[STDensify](
              geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5))',0),
              4.0,
              3,2
       ).AsTextZM() as dGeom;

dGeom
POLYGON ((0 0, 4 0, 8 0, 12 0, 16 0, 20 0, 20 4, 20 8, 20 12, 20 16, 20 20, 16 20, 12 20, 8 20, 4 20, 0 20, 0 16, 0 12, 0 8, 0 4, 0 0), (10 10, 10 11, 11 11, 11 10, 10 10), (5 5, 5 7, 7 7, 7 5, 5 5))

-- MultiPolygon
select [dbo].[STDensify](
              geometry::STGeomFromText('MULTIPOLYGON(((100 100,110 100,110 110,100 110,100 100)),((0 0,20 0,20 20,0 20,0 0),(10 10,10 11,11 11,11 10,10 10),(5 5,5 7,7 7,7 5,5 5)))',0),
              4.0,
              3,2
       ).AsTextZM() as dGeom;

dGeom
MULTIPOLYGON (((100 100, 103.333 100, 106.667 100, 110 100, 110 103.333, 110 106.667, 110 110, 106.667 110, 103.333 110, 100 110, 100 106.667, 100 103.333, 100 100)), ((0 0, 4 0, 8 0, 12 0, 16 0, 20 0, 20 4, 20 8, 20 12, 20 16, 20 20, 16 20, 12 20, 8 20, 4 20, 0 20, 0 16, 0 12, 0 8, 0 4, 0 0), (10 10, 10 11, 11 11, 11 10, 10 10), (5 5, 5 7, 7 7, 7 5, 5 5)))

Conclusion

The function is available with the general package of SQL Server Spatial Functions “here”:http://spdba.com.au

I hope this function is of interest to someone.