Top 5 Recent Articles

ARTICLES CATEGORIES

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.