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)
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.
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