# linestring back into polygon form so that I can do an STArea()

• ### 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

• 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
• 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
• 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