SQL Geography.STContains returns false values

Question

I am creating several geography variables and I want to check if a set of coordinates exists inside the location. I use STContains to determine that. For most of the variables this seems to be working but I cannot understand some cases like the following:

CREATE TABLE MAP_AREA (Position geography)

INSERT INTO MAP_AREA (Position)
VALUES (0xE610000002242D0000007DD0B359F519374025068195432F63C0956588635D8C36403F355EBA492063C0CB10C7BAB81D364054E3A59BC41063C0956588635D7C354096438B6CE7FF62C0DA1B7C6132A53440BE9F1A2FDDF062C0A913D044D8203440C74B378941EC62C06A4DF38E53943340C3F5285C8FEA62C0CE88D2DEE00B33401D5A643BDFEB62C0371AC05B208132404E62105839F062C0C7293A92CB8F31403F355EBA490063C02E90A0F831C630400AD7A3703D1A63C0EE5A423EE8D92F40E5D022DBF94663C0A167B3EA73752F40BE9F1A2FDD5863C070CE88D2DE402F401D5A643BDF6B63C0265305A3925A2F4025068195438B63C08F53742497FF2F404C37894160A963C003780B24286E304048E17A14AEBF63C09FCDAACFD5E63040C74B378941D063C068226C787A853140EE7C3F355EDE63C0E86A2BF6972D3240643BDF4F8DE763C004E78C28ED7D3240BC74931804FE63C0E0BE0E9C33823240068195438B1464C09D8026C286A7324083C0CAA1452A64C0EF38454772293340E5D022DBF94A64C09F3C2CD49AA6334062105839B45C64C027A089B0E13934400AD7A3703D6A64C03FC6DCB584EC3440E3A59BC4207464C0CDCCCCCCCC9C35407F6ABC74937864C02EFF21FDF6553640A4703D0AD77764C058A835CD3B0E374021B07268917164C058A835CD3BAE37406DE7FBA9F16664C04F401361C34338405EBA490C025764C003780B2428AE3840D7A3703D0A4764C0386744696F003940068195438B3464C0E8D9ACFA5C5D3940E17A14AE471564C0E0BE0E9C33823940A245B6F3FDFC63C04BC8073D9B853940F0A7C64B37E563C022FDF675E06C3940B81E85EB51D063C0B537F8C2643A3940B0726891EDBC63C0BD5296218EF538401D5A643BDFAB63C035EF384547B23840B4C876BE9F8E63C05BD3BCE3145D384017D9CEF7537B63C0772D211FF4DC3740560E2DB29D5363C00C93A9825189374077BE9F1A2F4163C07DD0B359F519374025068195432F63C001000000010000000001000000FFFFFFFF0000000003)

DECLARE @position GEOGRAPHY
SET @position = GEOGRAPHY::Point(70,70,4326)

SELECT MAP.POSITION.STContains(@position)
FROM MAP_AREA MAP

In this case I get positive values regardless the position. The geography includes all the world except the geography? Have anyone encounter something similar?

I get a zero from this code (admittedly 2014 not 2016, but it would be surprising if it changed that starkly between versions). — Damien_The_Unbeliever, Aug 21 '18 at 09:11
@Damien_The_Unbeliever you are right, I updated the question. The problem is that somehow the schema includes all the word but my geography.. — Manos, Aug 21 '18 at 09:46
Yes, now you've switched it to the problem I had expected on reading the title. Draw a line around the equator. Am I enclosing the *northern* hemisphere or the *southern* hemisphere? There's no "natural" inside or outside when you draw a polygon on the surface of a globe. So SQL Server uses the [left-hand rule](https://docs.microsoft.com/en-us/sql/relational-databases/spatial/spatial-data-types-overview?view=sql-server-2017#orientation-of-spatial-data). Reverse the order you visit the points in your polygon. There is also a `ReorientObject` function if you already have the `geography` already — Damien_The_Unbeliever, Aug 21 '18 at 09:51
And you could have easily seen this if you'd included `Position` in your result set - assuming you're using SSMS. It includes a *visualizer* so that you can *see* how SQL Server is treating your shapes. — Damien_The_Unbeliever, Aug 21 '18 at 09:53
@Damien_The_Unbeliever this is the answer! Thank you, for your prompt response. ReorientObject solved my issue. — Manos, Aug 21 '18 at 10:02

score 0 · Answer 1 · answered Jan 04 '21 at 04:39

The left hand rule is the source of the issue as correctly pointed out by @Damien_The_Unbeliever . And oddly, GeoJSON standard follows a right hand rule which seems to go the other way. Hope these folks align at some point.

Since I cannot control which circular order the data will come in, I have no choice but to do this check every time before I store the geodata in db. Here's a python function I wrote to check the polygon given (in WKT but you can customize this for the binary), reverse it if it's not left-hand-rule and return the reversed polygon for further ops. It uses sql server but doesn't do any DB writes.

def ensureProperPolygon(shapewkt):
    # resolve inverted polygon issues if any

    s1 = f"select geography::Parse('{shapewkt}').STArea() - geography::Parse('{shapewkt}').ReorientObject().STArea()"
    testarea = dbconnection.makeQuery(s1,output='oneValue')
    if testarea > 0:
        cf.logmessage("Inverting the polygon to comply with left hand rule")
        s2 = f"select geography::Parse('{shapewkt}').ReorientObject().STAsText()"
        newshape = dbconnection.makeQuery(s2,output='oneValue')
        return newshape
    else:
        return shapewkt

Note: The dbconnection.makeQuery() is just my program's one-stop function to handle db ops; you can replace it with engine.execute() or so at your end.

What it does is calculate area of the polygon as given and of its reversed twin and get their difference. The wrong one will have area roughly equal to that of the planet (minus your actual area) and will be greater. So if diff is positive, then the original shape is wrong and has to be reversed.

This does entail db server usage; in my use case I have plenty to spare so doing this. There would be a way to do this in python itself, for now I didn't want to get into geospatial libraries, technicalities etc so using this. If there is a quick way to do it in python itself, inviting other answers.

SQL Geography.STContains returns false values

1 Answers1