CODEWITHSUNDEEP
c#sql-servergisgeojson
cs0815
cs0815

Reputation: 17388

Arcs/vertices to MultiPolygon

I have a list of Arcs (vertices) in this form:

public class Arc
{
    public Guid Id { get; set; }
    public double X1 { get; set; }
    public double Y1 { get; set; }
    public double X2 { get; set; }
    public double Y2 { get; set; }
}

which I can serialize into a MultiLineString GeoJSON like this in the case of 2 Arcs:

{ "type": "MultiLineString",
    "coordinates": [
    [ [100.0, 0.0], [101.0, 1.0] ],
    [ [102.0, 2.0], [103.0, 3.0] ]
    ]
}

My underlying data actually represent Polygons. More precisely a MultiPolygon:

{ "type": "MultiPolygon",
    "coordinates": [
      [[[102.0, 2.0], [103.0, 2.0], [103.0, 3.0], [102.0, 3.0], [102.0, 2.0]]],
      [[[100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0]],
       [[100.2, 0.2], [100.8, 0.2], [100.8, 0.8], [100.2, 0.8], [100.2, 0.2]]]
      ]
    }

Just curious, is it possible to transform the original Arcs into a MultiPolygon? I am also thinking about doing it via SQL Server's GIS functionalities. Did anyone ever encounter a similar problem? Any pointers would be very much appreciated. Many thanks.

PS:

The arcs are currently disjoint and when combined should form a closed polygon. The following picture shows a part of a potential polygon.

enter image description here

Upvotes: 0

Views: 194

Answers (1)

Ben Thul
Ben Thul

Reputation: 32687

Here you go. I present this with the huge caveat that I wrote it very iteratively and if I came across this from someone else in production code, I'd have a talk with them. :)

declare @g geography = geography::STGeomFromText('MULTILINESTRING((100 0, 101 1), (102 2, 103 3))', 4236);
declare @multiPoly geography;

with cte as (
    select *
    from dbo.Numbers as n
    cross apply (
        select *
        from (values 
            (@g.STGeometryN(number).STStartPoint().Lat, @g.STGeometryN(number).STStartPoint().Long, 1),
            (@g.STGeometryN(number).STEndPoint().Lat, @g.STGeometryN(number).STStartPoint().Long, 2),
            (@g.STGeometryN(number).STEndPoint().Lat, @g.STGeometryN(number).STEndPoint().Long, 3),
            (@g.STGeometryN(number).STStartPoint().Lat, @g.STGeometryN(number).STEndPoint().Long, 4),
            (@g.STGeometryN(number).STStartPoint().Lat, @g.STGeometryN(number).STStartPoint().Long, 5)
        ) as x(Lat, Long, n)
    ) as decomp
    where n.Number <= @g.STNumGeometries()
)
select @multiPoly = geography::STGeomFromText(
    'MULTIPOLYGON (' + stuff((
        select ', ((' + stuff((
            select ', ' + cast(cte.Long as varchar(10)) + ' ' + cast(cte.Lat as varchar(10))
            from cte
            where Number = n.Number
            order by cte.n
            for xml path('')
        ), 1, 2, '') + '))'
        from dbo.Numbers as n
        where n.Number <= @g.STNumGeometries()
        for xml path('')
        ), 1, 2, '') + ')'
    , 4236);


select @multiPoly;

Upvotes: 1

Related Questions