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?

score 1 · Accepted Answer · edited Oct 02 '15 at 01:56

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.

Ben, you've been answering geography questions for a long time. Can you explain the behaviour of the coordinate order (see my answer), or would you agree it's a bug? — Jon Bellamy, Oct 01 '15 at 08:54
So here you're basically creating separate boxes for every time it crosses a latitude/longitude limit, correct? I'd thought of doing this but hoped I wouldn't have to. Will try it out this evening - thanks. — Mansfield, Oct 01 '15 at 11:53
@JonBellamy: Perhaps, but I'm an amateur (i.e. I've never done geospatial work for money). But I did sleep on it and have what I think is a reasonable explanation of the behavior we're seeing. — Ben Thul, Oct 01 '15 at 14:23
@BenThul Interesting theory and it makes sense. I'm going to look into it more. I'm starting to wonder if it's how Microsoft implemented WKT in geometry and geography. WKT specifies that exterior rings are counterclockwise and interior rings are clockwise. Perhaps it's actually true for geometry types but they decided to reverse it for geography types as some form of differentiator?? Thanks for your thoughts. — Jon Bellamy, Oct 01 '15 at 21:13
Well… geography and geometry differ in one very big way with respect to this issue: geography instances are inherently bounded (i.e. they can never be larger than the full globe) whereas geometry instances are not (i.e. they live on the proverbial infinite plane). So, it is unreasonable to have orientation for a simple ring matter in a geometry instance because the resulting polygon would be unbounded. Of course, these are the ramblings and observations of an amateur. ;) — Ben Thul, Oct 01 '15 at 22:18
Looks like this does pretty much what I want. Now to write all this into c#....Thanks again! — Mansfield, Oct 02 '15 at 02:12

score 1 · Answer 2 · answered Oct 01 '15 at 08:53

Like Ben, I can't explain what's happening. I double, treble and even quadruple checked the coordinate order and couldn't find anything to determine why it creates the globe with a hole (essentially). Your order was SW -> SE -> NE -> NW -> WS - exactly as I would have done it and I suspect this may be a bug on SQL's part.

One thing that did immediately alarm me however was that those coordinates were not for that view on a google map - trust me - I've been working with them far too long. By estimation, your coordinates should have been something nearer:

SW: Long 78, Lat 7 NE: Long 44 Lat 83.

Your google map showing the markers showed why your ReorientObject() call actually produced the correct result (even though you shouldn't have needed to reorient anything).

Never-the-less, there is a better way to create the bounding box here:

-- Define NE
declare @ne geography = geography::Point(83.4871233224387, 63.5599894667969, 4326);
- Define SW
declare @sw geography = geography::Point(-3.62943382709019, 86.0599894667969, 4326);

-- Define BOX
declare @box geography = geography::STGeomFromWKB(geometry::STGeomFromWKB(@ne.STUnion(@sw).STAsBinary(), 4326).STEnvelope().STAsBinary(), 4326);

select @box -- shows polygon
select @box.ToString -- shows WKT

It's interesting to see that the coordinate order is defined as if the geography were a hole - it goes SE -> SW -> NW -> NE -> SE, keeping the interior on the right. This would be right for GEOMETRY, but not GEOGRAPHY and yet it produces the right answer. This is why I think it may be a bug? - Or I'm missing something.

Lastly, to explain creating the box:

Take the union of both points (creating a single geometry of MULTIPOINT type)
Convert the MULTIPOINT to geometry type
Use the geometry type's STEnvelope() method to create a bounding box containing the extremities
Convert to BOX (POLYGON) back to geography type

I hope that helps you (and anyone else).

Yeah, I probably accidentally dragged the map and couldn't get it back to the right position. Still, for example purposes they still work. — Mansfield, Oct 01 '15 at 11:51
It seems this code just produces the exact same thing as the reoriented object, which is not what I want. — Mansfield, Oct 02 '15 at 01:54
@Mansfield I clearly misunderstood your requirements. Could you elaborate then please. That code produces a rectangle based on the bounds of a Google map which I thought it what you want? — Jon Bellamy, Oct 02 '15 at 06:40
That's what I want, but I want the "other way around" bounds, for lack of a better way to say it. Run the code in the other answer, that gives me more or less what I'm looking for. — Mansfield, Oct 02 '15 at 11:49
@Mansfield Ran it and now I understand. You want a band across the globe, limited by the latitudes of the view and less the view itself. I don't think there's any cleaner way to do that beyond Ben's answer. — Jon Bellamy, Oct 04 '15 at 21:08
The view itself is actually the bands across the globe, it was just hard to represent. Thanks for your help nonetheless. — Mansfield, Oct 05 '15 at 03:05

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

2 Answers2