STRound: Function to round ordinates of a SQL Server Spatial geometry object
The result of many geoprocessing operations in any spatial type can be geometries with ordinates (X, Y etc) that have far more decimal digits of precision than the initial geometry.
I present here a simple function STRound that 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.
USE [GISDB] -- Change this to your database GO -- Drop if exists -- DROP FUNCTION [dbo].[STRound]; -- Now create -- CREATE FUNCTION [dbo].[STRound] ( @p_geometry geometry, @p_round_xy INT = 3, @p_round_zm INT = 2 ) RETURNS geometry AS BEGIN -- Implementation; GO
Testing this we get.
-- Point SELECT [dbo].[STRound](geometry::STPointFromText('POINT(0.345 0.282)',0),1,1).STAsText() AS RoundGeom; GO RoundGeom POINT (0.3 0.3) -- MultiPoint SELECT [dbo].[STRound](geometry::STGeomFromText('MULTIPOINT((100.12223 100.345456),(388.839 499.40400))',0),3,1).STAsText() AS RoundGeom; GO RoundGeom MULTIPOINT ((100.122 100.345), (388.839 499.404)) -- Linestring SELECT [dbo].[STRound](geometry::STGeomFromText('LINESTRING(0.1 0.2,1.4 45.2)',0),2,1).STAsText() AS RoundGeom; GO RoundGeom LINESTRING (0.1 0.2, 1.4 45.2) -- 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; GO RoundGeom LINESTRING (0.1 0.2 0.3, 1.4 45.2 1.6) -- Polygon SELECT [dbo].[STRound](geometry::STGeomFromText('POLYGON((0 0,10 0,10 10,0 10,0 0))',0),2,1).STAsText() AS RoundGeom; GO RoundGeom POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0)) -- 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; GO RoundGeom MULTIPOLYGON (((160 400, 200 400, 200 480, 160 480, 160 400)), ((100 200, 180 300, 100 300, 100 200)))
I hope this is useful to someone.