[GIS] MSSQL : How to build a geography with holes using the SqlGeographyBuilder

csqlsql server

I'm trying to save a polygon with holes in it in a spatial layer using the SqlGeographyBuilder.

Using the below code, it doesn't take into account any holes.

How do i tell the SqlGeographyBuilder to start drawing a hole in the polygon?

case "polygon":
    {
        SqlGeographyBuilder gb = new SqlGeographyBuilder();
        gb.SetSrid(4326);
        gb.BeginGeography(OpenGisGeographyType.Polygon);
        gb.BeginFigure(vm.Coordinates[0].Latitude, vm.Coordinates[0].Longitude);
        foreach (var cooodinate in vm.Coordinates.Skip(1))
        {
            gb.AddLine(cooodinate.Latitude, cooodinate.Longitude);
        }
        gb.AddLine(vm.Coordinates[0].Latitude, vm.Coordinates[0].Longitude);
        gb.EndFigure();
        gb.EndGeography();

        DbGeography geog = DbGeography.FromText((gb.ConstructedGeography.STArea() < gb.ConstructedGeography.ReorientObject().STArea() ? gb.ConstructedGeography.ToString() : gb.ConstructedGeography.ReorientObject().ToString()), 4326);
        feature.Geog = geog;
        _unitOfWork.Save();
        break;
    }

Best Answer

See sample 3 of the below msdn blog concerning 'complex' polygon building...which is simply adding an interior enclosed shape (as the code comment states) - this is no more than demonstrating how to add hole(s) [as you call them] by nesting Begin/End Figures within the Begin/End Geography block:

https://blogs.msdn.microsoft.com/davidlean/2008/10/30/sql-2008-spatial-samples-part-3-of-9-sql-builder-api/

The sample is actually written as VB.NET but should be principally the same in C# (looks like you did not paste all of your code, and you're getting coords from vm, presumably a data structure set up from a file read, perhaps some other source).

Also, I believe SQL Spatial follows 'left-hand rule' meaning the interior rings are entered in the clockwise direction (the polygon is to the left of the vector) - so be careful of that. If you plot the coordinates, or mentally picture these 2 triangle constructions (I'm rusty, so needed scratch paper), you'll see the vectors for the outer triangle (1st figure in the code) is oriented counterclockwise, thus maintaining the left-hand rule.

Below is a copy of the part of that code showing the nested Figure construction, copied as-is (untested):

Dim g As New SqlGeographyBuilder 
g.SetSrid(4326)  ‘<= Must set First 
g.BeginGeography(OpenGisGeographyType.Polygon)
        ‘ Exterior shape         
        g.BeginFigure(-33, 151)   ‘Note: Lat, Long format 
                g.AddLine(-33, 154) 
                g.AddLine(-30, 154) 
                g.AddLine(-33, 151)   ‘Note: Last Point same as First 
        g.EndFigure()
        ‘ Interior "Enclosed" shape 
        g.BeginFigure(-32.5, 152) 
                g.AddLine(-31, 153.5) 
                g.AddLine(-32.5, 153) 
                g.AddLine(-32.5, 152) 
        g.EndFigure() 
g.EndGeography()

Furthermore, in contrast to the above polygon w/ hole construction, see sample 5 (same blog entry) for MultiPolygon construction (otherwise in ArcGIS known as a multi-part polygon) -- the ring orientations of these disjoint figures are both counterclockwise, see below (again, an untested copy of VB.NET code posted on the provided msdn blog page):

Dim b As New SqlGeographyBuilder 
b.SetSrid(4326) ‘Must set 1st 
b.BeginGeography(OpenGisGeographyType.MultiPolygon)

    b.BeginGeography(OpenGisGeographyType.Polygon) 
        b.BeginFigure(-33, 151) 
            b.AddLine(-31, 152) 
            b.AddLine(-30, 152) 
            b.AddLine(-33, 151) 
        b.EndFigure() 
    b.EndGeography()

    b.BeginGeography(OpenGisGeographyType.Polygon) 
        b.BeginFigure(-33, 155) 
            b.AddLine(-31, 156) 
            b.AddLine(-30, 156) 
            b.AddLine(-33, 155) 
        b.EndFigure() 
    b.EndGeography() 
b.EndGeography()
Related Question