Go to content Go to navigation and search

Home

Current SQL Server Blog Articles


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.

Function to round ordinates of a SQL Server Spatial geometry object

Tuesday January 03 2012 at 06:08

Keywordsround ordinate coordinate tolerance geometry SQL Server Spatial
Summary

The presented function allows a user to round the ordinates of a Sql Server Spatial geometry object to the user’s understood geometric precision.

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

STRoundGeometry calls STMove which is described in a related article

  1. USE [GISDB] -- Change this to your database
  2. GO
  3. -- Drop if exists
  4. --
  5. DROP FUNCTION dbo.STRoundGeometry;
  6. -- Now create
  7. --
  8. CREATE FUNCTION dbo.STRoundGeometry(@p_geometry geometry,
  9.                                     @p_round_x   INT = 3,
  10.                                     @p_round_y   INT = 3,
  11.                                     @p_round_z   INT = 2,
  12.                                     @p_round_m   INT = 2)
  13. RETURNS geometry
  14. AS
  15. BEGIN
  16.   DECLARE
  17.     @v_geom geometry;
  18.   BEGIN
  19.     IF ( @p_geometry IS NULL )
  20.       RETURN @p_geometry;
  21.     SET @v_geom = dbo.STMove(@p_geometry,0.0,0.0,0.0,0.0,@p_round_x,@p_round_y,@p_round_z,@p_round_m);
  22.   END;
  23.   RETURN @v_geom;
  24. END;
  25. GO

Testing this we get.

  1. SELECT dbo.STRoundGeometry(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.                            2,2,1,1).STAsText() AS geom;

Which results in:

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

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