STRound

STRound — Function which rounds the ordinates of the supplied geometry.

Function Specification

Function [dbo].[STRound] (
           @p_geometry geometry,
           @p_round_xy int = 3,
           @p_round_zm int = 2
         )
Returns geometry 

Description

The result of many geoprocessing operations in any spatial type can be geometries whose ordinates (X, Y etc) have far more decimal digits of precision than the initial geometry.

Additionally, some input GIS formats, such as shapefiles (which has no associated precision model), when loaded, can show far more decimal digits of precision in the created ordinates misrepresenting the actual accuracy of the data.

STRound takes a geometry object and some specifications of the precision of any X, Y, Z or M ordinates, applies those specifications to the geometry and returns the corrected geometry.

The @p_round_xy/@p_round_zm values are decimal digits of precision, which are used in TSQL’s ROUND function to round each ordinate value.

Parameters

    @p_geometry (geometry) - supplied geometry of any type.
    @p_round_xy (int)      - Decimal degrees of precision to which calculated ordinates are rounded.
    @p_round_zm (int)      - Decimal degrees of precision to which calculated ordinates are rounded.

Result

This function accesses all ordinates in the supplied geometry, rounding all ordinates using the supplied parameter values.

Examples

-- Geometry
-- Point
SELECT [dbo].[STRound](geometry::STPointFromText('POINT(0.345 0.282)',0),1,1).STAsText() as RoundGeom
UNION ALL 
-- MultiPoint
SELECT [dbo].[STRound](geometry::STGeomFromText('MULTIPOINT((100.12223 100.345456),(388.839 499.40400))',0),3,1).STAsText() as RoundGeom 
UNION ALL 
-- Linestring
SELECT [dbo].[STRound](geometry::STGeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),2,1).STAsText() as RoundGeom
UNION ALL 
-- LinestringZ
SELECT [dbo].[STRound](geometry::STGeomFromText('LINESTRING(0.1 0.2 0.312,1.4 45.2 1.5738)',0),2,1).AsTextZM() as RoundGeom
UNION ALL 
-- Polygon
SELECT [dbo].[STRound](geometry::STGeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0))',0),2,1).STAsText() as RoundGeom
UNION ALL 
-- MultiPolygon
SELECT [dbo].[STRound](
         geometry::STGeomFromText('MULTIPOLYGON (((160 400, 200.00000000000088 400.00000000000045, 200.00000000000088 480.00000000000017, 160 480, 160 400)), ((100 200, 180.00000000000119 300.0000000000008, 100 300, 100 200)))',0),
          2,1).STAsText() as RoundGeom

RoundGeom
POINT (0.3 0.3)
MULTIPOINT ((100.122 100.345), (388.839 499.404))
LINESTRING (0.1 0.2, 1.4 45.2)
LINESTRING (0.1 0.2 0.3, 1.4 45.2 1.6)
POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))
MULTIPOLYGON (((160 400, 200 400, 200 480, 160 480, 160 400)), ((100 200, 180 300, 100 300, 100 200)))

-- Geography
-- Can't overload existing STRound so have to use conversion functions.
SELECT [dbo].STToGeography(
         [dbo].[STRound](
           [dbo].STToGeometry(
                   geography::STGeomFromText('LINESTRING(141.29384764892390 -43.93834736282 234.82756,
                                                         141.93488793487934 -44.02323872332 235.26384)',
                                             4326),
                   4326
           ),
           7,
           3
         ),
         4326
       ).AsTextZM() as rGeom;
   
rGeom
LINESTRING (141.2938476 -43.9383474 234.828, 141.9348879 -44.0232387 235.264)

I hope this function is of use to someone.