How can I tell SQL Server to treat large polygons properly?

Question

I'd like to represent a large square as a polygon in a SQL Server database. This polygon spans almost the entire map.

Here are the boundary co-ordinates (latitude, longitude)

North-East Corner: { 83.4871233224387, 63.5599894667969 }
South-West Corner: { -3.62943382709019, 86.0599894667969 }

Here's what it looks like on a map (these are the map bounds for a zoomed-out google map)

Here's what SQL Server thinks when I try to draw a polygon:

 declare @p5 sys.geography;
 set @p5 = geography::STGeomFromText('POLYGON((86.0599894667969 -3.62943382709019, 63.5599894667969 -3.62943382709019, 63.5599894667969 83.4871233224387, 86.0599894667969 83.4871233224387, 86.0599894667969 -3.62943382709019))', 4326);

 select @p5

It thinks I'm not following the left hand rule properly, but I am, I just want an absolutely massive polygon.

If I use reorientobject, I get the inverse, which is also not what I want:

Here are those two points plotted on a map for reference:

How can I resolve this issue?

Answer 1

I can't explain what's going on here in an elegant manner, but I was able to hack my way to a polygon that's pretty close to what you're looking for:

declare @p1 geography = geography::STGeomFromText('
    POLYGON((
        63 83,
        0  83,
        0  -3,
        63 -3,
        63 83
    ))', 4326)
,@p2 geography = geography::STGeomFromText('
    POLYGON((
        86 83,
        86 -3,
        180  -3,
        180  83,
        86 83
    ))', 4326)
,@p3 geography = geography::STGeomFromText('
    POLYGON((
        180 83,
        180 -3,
        -90 -3,
        -90 83,
        180 83
    ))', 4326)
,@p4 geography = geography::STGeomFromText('
    POLYGON((
        -90 83,
        -90 -3,
        0   -3,
        0   83,
        -90 83
    ))', 4326);

select @p1.STUnion(@p2).STUnion(@p3).STUnion(@p4).STAsText();

Edit: I did have some time to sleep on it and came up with a reasonable interpretation of the behavior without it being a bug in SQL. For any four such points (that is, the corners of a box), they define four distinct shapes. I'll describe them in terms of the specific case we're dealing with here, but I think it generalizes.

The four shapes are: the small box that the OP obtained when calling ReorientObject on the original polygon, its complement (aka the original polygon), the desired shape, and its complement.

Moreover, note that it doesn't matter what order you specify points in a ring in. Which is really what we're describing here (a ring that is). Geographic polygons needn't be, and frankly in the general case probably aren't, squares.

Once you accept the latter, that it's easy to obtain only two of the four shapes isn't that far of a leap. Note too that of the four polygons that those four points define, three of them are really big. So, I'm guessing that SQL has a heuristic that is able to figure out which of those is smallest because frankly that's what's more likely to be defined in a real world application. Then, depending on the order in which the points were defined, you either get that polygon (i.e. the smallest of the four) or its complement.