Go to content Go to navigation and search

Home

Current SQL Server Blog Articles

    STGeometryTypes: Extracting all geometry type string values from complex geometry
    STDensify: Densify (m)LineString or (m)Polygon geometry objects
    STInsertN: Insert single vertex into a geometry
    STUpdateN: Update (replace) a single vertex within a geometry object.
    STUpdate: Replace all points equal to the supplied point with replacement point.
    STDeleteN: Delete single vertex from geometry
    STDelete: Deleting vertices in geometry objects
    STFlipVectors: Normalize direction of linestring vectors
    STConvertToLineString: Extract LineStrings in GeometryCollection to create LineString
    STLine2Cogo: Converting LineStrings to COGO XML
    STCogo2Line: Creating (Multi)LineStrings geometries from COGO XML instructions
    STVectorize: Break Linestring/Polygon elements into 2 point vectors (or 3 point circular curves)
    STScale: Function to Scale a geometry object
    TSQL String Tokenizer Function for SQL Server
    STGeometry2MBR/STGeography2MBR: Compute and return MBR ordinates
    generate_series for SQL Server 2008
    STExtractPolygon: Extract Polygons from result of STIntersection in SQL Server Spatial
    STRound: Function to round ordinates of a SQL Server Spatial geometry object
    STExtract: Extract elements of a geometry object
    STNumRings: Counting number of polygon rings
    STFilterRings: Removing rings from Polygon based on area.
    STMove: Function to Move a geometry object in SQL Server Spatial
    STCentroid*: Alternate Functions for Compute a Centroid
    STRotate: Function to rotate a geometry object in SQL Server Spatial
    STVertices: Wrapper over STDumpPoints
    STMorton: Creating a Morton number Space Key value for grid square
    Gridding a geometry or geography object (SQL Server Denali)
    On hinting spatial indexes
    RandomSearchByExtent: Random Search Procedure (2008 Spatial)
    COGO: Convert DMS String to decimal degrees floating point number.
    COGO: Converting (Google Earth) Formatted Longitude/Latitude points to decimal degrees (SQL Server)
    COGO: Convert Degrees, Minutes and Seconds values to Decimal Degrees
    COGO: DD2DMS Formatting a latitude/longitude decimal degree value
    COGO: Create point from bearing and distance
    COGO: Compute number of vertices required to stroke circle or circular arc
    COGO: Calculating the bearing between two points (SQL Server 2008 Spatial)
    COGO: Compute arc length subtended by angle centre of circle
    COGO: Compute chord length of segment of a circle (arc)
    COGO: Computing Arc To Chord (Arc2Chord) Separation
    COGO: Creating a stroked polygon from a circle's centre x,y and radius
    COGO: Finding centre and radius of a curve defined by three points: FindCircle function
    CheckRadii: Identifying Tight Radius Curves sections within LineString geometry data
    Generating random point data for SQL Server 2008 Spatial
    New Presentation on Active (Searchable) Spatial Metadata for SQL Server 2008 Spatial and FreeText
    STisGeo: Checking if a column in a table or a view is of type geometry or geography
    Vectorising geometry objects in SQL Server 2008
    STMBR2Geometry/STMBR2Geography functions
    Extracting geometry types from geometry/geography objects in SQL Server 2008
    Getting the number of coordinate dimensions of a geometry/geography object in SQL Server 2008
    STDumpPoints: A Function that Dumps the Vertices/Points in a geometry
    toGeography and toGeography Conversion Functions for SQL Server 2008 Spatial
    Write text file with spatial data from SQL Server 2008
    Loading Shapefiles into Geography type column in SQL Server 2008
    Tip #6: Correcting invalid geometries
    Tip #5: Where or where has my little column gone
    Tip #4: What Coordinate System is that?
    Tip #3: What object is that?
    Tip #2: Spatial Indexing and Primary Keys
    Tip #1: SQL Server 2008 "Katmai" - Setting SRIDs

Search

    Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

STDensify: Densify (m)LineString or (m)Polygon geometry objects

Wednesday May 01 2019 at 15:58

Keywordsdensify STDensify SQL Server Spatial TSQL
Summary

Densification TSql function for Sql Server Spatial.

I have a densify function for my PL/SQL (Package and Object) code.

A SQL Server Spatial version exists as follows:

  1. CREATE FUNCTION [$(owner)].[STDensify] (
  2.   @p_geometry geometry,
  3.   @p_distance FLOAT,
  4.   @p_round_xy INT = 10,
  5.   @p_round_zm INT = 10
  6. )
  7. RETURNS geometry
  8. AS
  9. BEGIN
  10.   -- Implementation
  11. END
  12. 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

  1. @p_geometry (geometry) - (M)Linestring OR (m) polygon.
  2. @p_distance    (FLOAT) - The desired optimal distance BETWEEN added vertices.
  3. @p_round_xy      (INT) - DECIMAL degrees OF PRECISION TO which calculated XY ordinates are rounded.
  4. @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.

  1. -- Densify 2D line into 4 segments
  2. WITH DATA AS (
  3. SELECT geometry::STGeomFromText('LINESTRING(0 0,10 10)',0) AS geom
  4. )
  5. SELECT [dbo].[STDensify](a.geom,a.geom.STLength()/4.0,3,2).AsTextZM() AS dGeom
  6.   FROM DATA AS a;
  7. --
  8. dGeom
  9. LINESTRING (0 0, 2.5 2.5, 5 5, 7.5 7.5, 10 10)
  10. --
  11. -- Distance between all vertices is < 4.0
  12. SELECT [dbo].[STDensify](geometry::STGeomFromText('LINESTRING (5 5, 5 7, 7 7, 7 5, 5 5)',0),4.0,3,2).AsTextZM() AS dGeom;
  13. --
  14. dGeom
  15. LINESTRING (5 5, 5 7, 7 7, 7 5, 5 5)
  16. --
  17. -- Simple Straight line.
  18. SELECT [$(owner)].[STDensify] (geometry::STGeomFromText('LINESTRING(100 100,900 900.0)',0),125.0,3,2).AsTextZM() AS dGeom;
  19. --
  20. DGeom
  21. 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)
  22. --
  23. -- LineString with Z
  24. SELECT [dbo].[STDensify] (geometry::STGeomFromText('LINESTRING(100 100 1.0,900 900.0 9.0)',0),125.0,3,2).AsTextZM() AS dGeom;
  25. dGeom
  26. 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)
  27. --
  28. -- LineStrings with ZM
  29. 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;
  30. dGeom
  31. 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)
  32. --
  33. GEOM
  34.   LINESTRING (1100.765 964.286, 1107.568 939.343, 1114.371 914.399, 1121.173 889.456, 1127.976 864.513, 1134.779 839.569, 1141.582 814.626, 1148.384 789.683, 1155.187 764.739, 1161.99 739.796, 1139.881 723.923, 1117.772 708.05, 1095.663 692.177, 1073.554 676.304, 1051.446 660.431, 1029.337 644.558, 1007.228 628.685, 985.119 612.812, 963.01 596.939, 941.032 610.675, 919.054 624.411, 897.076 638.148, 875.098 651.884, 853.12 665.62, 831.142 679.356, 809.164 693.093, 787.186 706.829, 765.208 720.565, 743.23 734.301, 721.252 748.038, 699.274 761.774, 677.296 775.51, 653.203 787.131, 629.11 798.753, 605.017 810.374, 580.924 821.995, 556.831 833.617, 532.738 845.238, 508.645 856.859, 484.552 868.481, 460.459 880.102, 434.63 869.26, 408.801 858.418, 382.972 847.576, 357.143 836.735, 331.314 825.893, 305.485 815.051, 279.656 804.209, 253.827 793.367, 242.53 770.043, 231.232 746.72, 219.935 723.396, 208.637 700.073, 197.34 676.749, 186.042 653.426, 174.745 630.102, 185.459 603.571, 196.173 577.041, 206.888 550.51, 217.602 523.98, 228.316 497.449, 253.543 500.85, 278.77 504.252, 303.996 507.653, 329.223 511.054, 354.45 514.456, 379.677 517.857, 404.903 521.258, 430.13 524.66, 455.357 528.061, 479.244 520.64, 503.131 513.219, 527.017 505.798, 550.904 498.377, 574.791 490.956, 598.678 483.534, 622.565 476.113, 646.452 468.692, 670.338 461.271, 694.225 453.85, 718.112 446.429, 717.262 420.493, 716.411 394.558, 715.561 368.622, 714.711 342.687, 713.86 316.751, 713.01 290.816, 698.66 270.089, 684.311 249.362, 669.962 228.635, 655.612 207.908, 641.263 187.181, 626.913 166.454, 612.564 145.727, 598.214 125, 573.271 120.181, 548.327 115.363, 523.384 110.544, 498.441 105.726, 473.497 100.907, 448.554 96.089, 423.611 91.27, 398.667 86.452, 373.724 81.633, 351.858 94.935, 329.992 108.236, 308.126 121.538, 286.261 134.84, 264.395 148.142, 242.529 161.443, 220.663 174.745, 198.797 188.047, 176.931 201.348, 155.065 214.65, 133.2 227.952, 111.334 241.254, 89.468 254.555, 67.602 267.857)
  35. --
  36. -- MultiLineString.
  37. 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;
  38. --
  39. dGeom
  40. 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))
  41. --
  42. -- Polygon
  43. SELECT [dbo].[STDensify](
  44.               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),
  45.               4.0,
  46.               3,2
  47.        ).AsTextZM() AS dGeom;
  48. dGeom
  49. 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))
  50. --
  51. -- MultiPolygon
  52. SELECT [dbo].[STDensify](
  53.               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),
  54.               4.0,
  55.               3,2
  56.        ).AsTextZM() AS dGeom;
  57. dGeom
  58. 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)))

The function is available with the general package of SQL Server Spatial Functions here

I hope this function is of interest to someone.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment