locked
linestring back into polygon form so that I can do an STArea() RRS feed

  • Question

  • Hello,

    I am trying to get just the exterior ring from a polygon or multipolygon.  I am now using STBoundary() but it returns of course a linestring.  This is fine, but I need to get the resulting linestring back into polygon form so that I can do an STArea() on it.  Is this possible?

    Thanks,

    Derek


    derek
    Thursday, January 20, 2011 6:39 PM

Answers

  • Jderekito -

    If I understand you correctly, you're trying to "fill in" the holes in a polygon, to make it as if it was defined with an exterior ring only and no interior rings, right?

    There's no inbuilt for this. The "nice" solution would be to write your own CLR routine using the SqlGeometry Sink. However, I've just knocked up the following "nasty" solution which should work just as well, by ripping out the appropriate coordinates from the WKB of the polygon and inserting them into a new instance.

     

    CREATE FUNCTION FillGeometry (@Shape geometry)
    RETURNS geometry
    AS
    BEGIN
     DECLARE @SRID int = @Shape.STSrid;
     DECLARE @FilledShape geometry;
     DECLARE @MultiGeom geometry = geometry::STGeomFromText('GEOMETRYCOLLECTION EMPTY', @SRID)
     DECLARE @i int = 1
     WHILE @i <= @Shape.STNumGeometries()
     BEGIN
      IF @Shape.STGeometryN(@i).STDimension() < 2
       SET @FilledShape = @Shape.STGeometryN(@i)
      ELSE BEGIN
       DECLARE @ExteriorRingBinary varbinary(max) = @Shape.STGeometryN(@i).STExteriorRing().STAsBinary();
       SET @FilledShape = 
        geometry::STGeomFromWKB(0x01 + 0x03000000 + 0x01000000 +
         SUBSTRING(@ExteriorRingBinary, 6, DATALENGTH(@ExteriorRingBinary)), @SRID)
      END
      SET @MultiGeom = @FilledShape.STUnion(@MultiGeom)
      SET @i = @i + 1
     END
     RETURN @MultiGeom
    END
    GO
    

     

     

    Tested as follows:

     

    DECLARE @Test TABLE (
    geom geometry
    );
    
    INSERT INTO @Test VALUES 
    -- Point should remain unchanged
    ('POINT(0 0)'),
    -- Linestring
    ('LINESTRING(2 0, 10 2)'),
    -- Polygon
    ('POLYGON((10 0, 20 0, 20 10, 10 10, 10 0))'),
    -- Polygon with interior ring
    ('POLYGON((4 4, 7 4, 7 7, 4 7, 4 4), (5 5, 6 5, 6 6, 5 6, 5 5))'),
    -- Polygon with two interior rings
    ('POLYGON((0 10, 10 10, 10 20, 0 20, 0 10), (2 12, 4 12, 4 14, 2 14, 2 12), (6 16, 8 18, 8 19, 6 19, 6 16) )'),
    -- Multipolygon, one with ring, one without
    ('MULTIPOLYGON(((12 12, 15 12, 15 15, 12 15, 12 12)), ((16 12, 20 12, 20 16, 16 16, 16 12), ( 17 13, 18 13, 18 14, 17 14, 17 13)))');
    
    SELECT 
    geom,
    dbo.FillGeometry(geom)
    FROM @Test
    

     


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, January 24, 2011 11:52 AM
    Answerer
  • You could also use the new FilterArtifactsGeography/FilterArtifactsGeometry functions in the SQL Server Spatial Tools Project .

    - Ed


    Ed Katibah Spatial Ed Blog
    Monday, January 24, 2011 5:53 PM

All replies

  • Hi jderekito,

     

    Did you mean you want to get the area of this polygon or multipolygon?

    If so, we could use STArea method against this polygon or multipolygon directly.

     

    Before further investigation, please provide us some sample data and the expected result.

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Monday, January 24, 2011 8:30 AM
  • Jderekito -

    If I understand you correctly, you're trying to "fill in" the holes in a polygon, to make it as if it was defined with an exterior ring only and no interior rings, right?

    There's no inbuilt for this. The "nice" solution would be to write your own CLR routine using the SqlGeometry Sink. However, I've just knocked up the following "nasty" solution which should work just as well, by ripping out the appropriate coordinates from the WKB of the polygon and inserting them into a new instance.

     

    CREATE FUNCTION FillGeometry (@Shape geometry)
    RETURNS geometry
    AS
    BEGIN
     DECLARE @SRID int = @Shape.STSrid;
     DECLARE @FilledShape geometry;
     DECLARE @MultiGeom geometry = geometry::STGeomFromText('GEOMETRYCOLLECTION EMPTY', @SRID)
     DECLARE @i int = 1
     WHILE @i <= @Shape.STNumGeometries()
     BEGIN
      IF @Shape.STGeometryN(@i).STDimension() < 2
       SET @FilledShape = @Shape.STGeometryN(@i)
      ELSE BEGIN
       DECLARE @ExteriorRingBinary varbinary(max) = @Shape.STGeometryN(@i).STExteriorRing().STAsBinary();
       SET @FilledShape = 
        geometry::STGeomFromWKB(0x01 + 0x03000000 + 0x01000000 +
         SUBSTRING(@ExteriorRingBinary, 6, DATALENGTH(@ExteriorRingBinary)), @SRID)
      END
      SET @MultiGeom = @FilledShape.STUnion(@MultiGeom)
      SET @i = @i + 1
     END
     RETURN @MultiGeom
    END
    GO
    

     

     

    Tested as follows:

     

    DECLARE @Test TABLE (
    geom geometry
    );
    
    INSERT INTO @Test VALUES 
    -- Point should remain unchanged
    ('POINT(0 0)'),
    -- Linestring
    ('LINESTRING(2 0, 10 2)'),
    -- Polygon
    ('POLYGON((10 0, 20 0, 20 10, 10 10, 10 0))'),
    -- Polygon with interior ring
    ('POLYGON((4 4, 7 4, 7 7, 4 7, 4 4), (5 5, 6 5, 6 6, 5 6, 5 5))'),
    -- Polygon with two interior rings
    ('POLYGON((0 10, 10 10, 10 20, 0 20, 0 10), (2 12, 4 12, 4 14, 2 14, 2 12), (6 16, 8 18, 8 19, 6 19, 6 16) )'),
    -- Multipolygon, one with ring, one without
    ('MULTIPOLYGON(((12 12, 15 12, 15 15, 12 15, 12 12)), ((16 12, 20 12, 20 16, 16 16, 16 12), ( 17 13, 18 13, 18 14, 17 14, 17 13)))');
    
    SELECT 
    geom,
    dbo.FillGeometry(geom)
    FROM @Test
    

     


    Beginning Spatial with SQL Server http://www.apress.com/book/view/1430218290
    Monday, January 24, 2011 11:52 AM
    Answerer
  • You could also use the new FilterArtifactsGeography/FilterArtifactsGeometry functions in the SQL Server Spatial Tools Project .

    - Ed


    Ed Katibah Spatial Ed Blog
    Monday, January 24, 2011 5:53 PM