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.

Counting number of polygon rings SQL Server Spatial

Monday January 02 2012 at 03:37

Keywordscount rings polygon multipolygon sql server spatial inner outer
Summary

This article presents a simple wrapper function that allows one to count the number of inner and outer rings of a polygon or multipolygon geometry via a single call.

Here is an “ease of use” function that allows you to count the number of rings of a polygon or multipolygon geometry object in SQL Server Spatial.

  1. USE [GISDB] -- Change this to your database
  2. GO
  3. -- Function: GetNumRings
  4. --
  5. CREATE FUNCTION GetNumRings(@p_geometry geometry)
  6. RETURNS INT
  7. AS
  8. BEGIN
  9.   DECLARE
  10.     @v_GeometryType VARCHAR(1000),
  11.   @v_geom         geometry,
  12.   @v_geomn        INT = 0,
  13.   @v_ringn        INT = 0;
  14.   BEGIN
  15.     IF ( @p_geometry IS NULL )
  16.      RETURN 0;
  17.     SET @v_GeometryType = @p_geometry.STGeometryType();
  18.     IF ( @v_GeometryType IN ('Point','MultiPoint','LineString','MultiLineString' ) )
  19.      RETURN 0;
  20.     IF ( @v_GeometryType IN ('Polygon','CurvePolygon') )
  21.     BEGIN
  22.       SET @v_ringn = 1 + @p_geometry.STNumInteriorRing();
  23.     END;
  24.     IF ( @v_GeometryType = 'MultiPolygon' )
  25.     BEGIN
  26.       SET @v_geomn  = 1;
  27.       WHILE ( @v_geomn <= @p_geometry.STNumGeometries() )
  28.       BEGIN
  29.         SET @v_geom  = @p_geometry.STGeometryN(@v_geomn);
  30.         SET @v_ringn = @v_ringn + 1 + @v_geom.STNumInteriorRing();
  31.         SET @v_geomn = @v_geomn + 1;
  32.       END;
  33.     END;
  34.   IF ( @v_GeometryType = 'GeometryCollection' )
  35.   BEGIN
  36.     SET @v_geomn  = 1;
  37.       WHILE ( @v_geomn <= @p_geometry.STNumGeometries() )
  38.       BEGIN
  39.        SET @v_ringn = @v_ringn + dbo.GetNumRings(@p_geometry.STGeometryN(@v_geomn));
  40.          SET @v_geomn = @v_geomn + 1;
  41.       END;
  42.     END;
  43.     RETURN @v_ringn;
  44.   END;
  45. END
  46. GO

Some tests.

  1. SELECT 'Point' AS gtype, dbo.GetNumRings(geometry::STGeomFromText('POINT(0 0)',0)) AS numRings
  2. UNION ALL
  3. SELECT 'MultiPoint' AS gtype, dbo.GetNumRings(geometry::STGeomFromText('MULTIPOINT((0 0),(20 0))',0)) AS numRings
  4. UNION ALL
  5. SELECT 'LineString' AS gtype, dbo.GetNumRings(geometry::STGeomFromText('LINESTRING(0 0,20 0,20 20,0 20,0 0)',0)) AS numRings
  6. UNION ALL
  7. SELECT 'MultiLineString' AS gtype, dbo.GetNumRings(geometry::STGeomFromText('MULTILINESTRING((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)) AS numRings
  8. UNION ALL
  9. SELECT 'Polygon Outer' AS gtype, dbo.GetNumRings(geometry::STGeomFromText('POLYGON((0 0,20 0,20 20,0 20,0 0))',0)) AS numRings
  10. UNION ALL
  11. SELECT 'Polygon' AS gtype, dbo.GetNumRings(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)) AS numRings
  12. UNION ALL
  13. SELECT 'MultiPolygon' AS gtype, dbo.GetNumRings(geometry::STGeomFromText('MULTIPOLYGON (((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)), ((80 80, 100 80, 100 100, 80 100, 80 80)), ((110 110, 150 110, 150 150, 110 150, 110 110)))',0)) AS numRings
  14. UNION ALL
  15. SELECT 'CurvePolygon' AS gtype, dbo.GetNumRings(geometry::STGeomFromText('CURVEPOLYGON(COMPOUNDCURVE((0 -23.43778, 0 23.43778),CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),(-90 23.43778, -90 -23.43778),CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778)))',0)) AS numRings
  16. UNION ALL
  17. SELECT 'GeometryCollection' AS gtype, dbo.GetNumRings(geometry::STGeomFromText('GEOMETRYCOLLECTION(CURVEPOLYGON(COMPOUNDCURVE((0 -23.43778, 0 23.43778),CIRCULARSTRING(0 23.43778, -45 23.43778, -90 23.43778),(-90 23.43778, -90 -23.43778),CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778))), COMPOUNDCURVE(CIRCULARSTRING(-90 -23.43778, -45 -23.43778, 0 -23.43778), (0 -23.43778, 0 23.43778)))',0)) AS numRings;

Resulting in:

gtype numRings
Point 0
MultiPoint 0
LineString 0
MultiLineString 0
Polygon Outer 1
Polygon 3
MultiPolygon 5
CurvePolygon 1
GeometryCollection 1

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