New Data Types in SQL Server 2008 Part 3

Polygon
Polygon is the two-dimensional surface which contains multiple points connected to each other. Polygon is a closed shape.

DECLARE @g geometry;
SET @g = geometry::Parse('POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))')

As Polygon is a closed shape, you can see from the example above that the start and end points are the same. As a Polygon is a closed object, you can use all the function you used with LineString shapes. There are a few additional functions which you can also use for Polygon shape.

SELECT @g.STArea();
SELECT @g.STCentroid().ToString();

MultiPoint MultiLineString MultiPolygon
Multipoint is collection of zero or more points.

DECLARE @g geometry;
SET @g = geometry::STMPointFromText('MULTIPOINT((10 20), (3 4 5))', 0);

If you want to extract only one point you can use the following method.

SELECT @g.STGeometryN(1).ToString();

MultiLineString and MultiPolygon are also as same as MultiPoint. The following examples give you an understanding of these functions.

SET @g = geometry::Parse('MULTILINESTRING((0 0, 2 1), (3 1 0, 1 1))');
SELECT @g.STGeometryN(2).ToString();
SET @g = geometry::Parse('MULTIPOLYGON(((0 0, 0 3, 3 3, 3 0, 0 0)),((9 9, 9 10, 10 9, 9 9)))');
SELECT @g.STGeometryN(1).ToString();

For overlapping polygons you may need to execute an additional method named MakeValid(). Let us illustrate this with three polygons.

((0 0, 0 3, 3 3, 3 0, 0 0)), ((1 1, 1 2, 2 1, 1 1)), ((9 9, 9 10, 10 9, 9 9)))

You can see that the second polygon falls inside the first polygon and this does not follow the OGC standards. If you run the following script an error will be returned.

DECLARE @g geometry;
SET @g = geometry::Parse('MULTIPOLYGON(((0 0, 0 3, 3 3, 3 0, 0 0)), ((1 1, 1 2, 2 1, 1 1)), ((9 9, 9 10, 10 9, 9 9)))');
SELECT @g.STGeometryN(2).STAsText();

The Error is;

System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.

A an error has been specified, we need to use the MakeValid function. The MakeValid function will shift the first two polygons into one polygon.

DECLARE @g geometry;
SET @g = geometry::Parse('MULTIPOLYGON(((0 0, 0 3, 3 3, 3 0, 0 0)), ((1 1, 1 2, 2 1, 1 1)), ((9 9, 9 10, 10 9, 9 9)))');
If  @g.STIsValid() = 0
SET @g = @g.MakeValid();
SELECT @g.STGeometryN(2).STAsText();

The STisValid() method will verify whether the polygon follows the the OGC standards. If not MakeValid() method will convert the above polygon into a valid polygon.