SQL Geography Datatype , checking Point in Polygon , STIntersects producing wrong result

Question

I am trying to check point in polygon, but STIntersect producing wrong result, below is given both point and polygon.

DECLARE @point GEOGRAPHY = GEOGRAPHY::Point(25.631119, 54.666181, 4326)

DECLARE @polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON 
((55.368827104612151 25.270499845366924, 55.368226289792808 
25.268578811810126, 55.367410898252288 25.269529630198974, 
55.368054628415855 25.270364015710307, 55.368827104612151 
25.270499845366924))', 4326)

SELECT @polygon.STIntersects(@point), @point.STIntersects(@polygon)

Result is returning 1, however it should return 0, because i pick the point out side of fence,,

Am I making anything wrong with polygon coordinates? Please advice me for any stupid mistake of mine as I am new in spatial data types.

I've not looked at your data in detail but whenever this sort of question is asked, it's almost certainly because you've got your polygon the wrong way around. I.e. on a sphere a polygon doesn't have an "inside" and an "outside" - it splits the sphere into two regions, *either* of which may be considered to be the inside or the outside. Is a ring around the equator enclosing the northern hemisphere or the south? To fix that, you need to obey the "left hand rule" when defining your polygon. — Damien_The_Unbeliever, Oct 19 '17 at 12:08
It seems that there are some issues in defining points of polygon, i see that sql server follow left hand rule for polygon. But i am providing interface to my users on website where they can define polygon via leafletJS draw. How can i force users to follow Left to right rule?. Is there any way — Kashif Ali, Oct 20 '17 at 01:25
Quick help will be appreciated as i m stuck and i do not know how to move on..... — Kashif Ali, Oct 20 '17 at 13:34

score 2 · Accepted Answer · answered Oct 20 '17 at 13:48

In order to "fix" this, we need to make some form of assumption. If the assumption cannot be "instruct the users to enter polygons that obey the left-hand rule" then we have to use something else.

Here, I'm assuming that the correct "sense" for the polygon is whichever way around means that it's enclosing the smallest area:

DECLARE @point GEOGRAPHY = GEOGRAPHY::Point(25.631119, 54.666181, 4326)

DECLARE @polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON 
((55.368827104612151 25.270499845366924,
55.368226289792808 25.268578811810126,
55.367410898252288 25.269529630198974, 
55.368054628415855 25.270364015710307,
55.368827104612151 25.270499845366924))', 4326)

SET @polygon = CASE
    WHEN @polygon.STArea() > @polygon.ReorientObject().STArea()
        THEN @polygon.ReorientObject()
    ELSE @polygon
    END

SELECT @polygon

(You may also wish to do a plain SELECT @polygon before the CASE to see what the original looked like)

However, take careful note of what the above assumption means - if your users may wish to provide "the whole planet minus this small area around 55 degrees East/25 degrees North", there's now no way for you to accept such a value.

References - STArea ReorientObject

I like to use `@polygon.EnvelopeAngle()` as my heuristic, but anything you do is going to have the issues that you've described. — Ben Thul, Oct 22 '17 at 21:32

SQL Geography Datatype , checking Point in Polygon , STIntersects producing wrong result

1 Answers1