SQL Geography point inside polygon not returning true on STIntersect (but returns true using Geometry)

Question

I don't want to resort in converting my geography data to geometry just so it returns true in STIntersect.

Here is the code in SQL:

DECLARE @point GEOGRAPHY = GEOGRAPHY::Point(1, 1, 4326)
DECLARE @polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 4326)

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

The following returns false (0), however if I use:

DECLARE @point GEOMETRY = GEOMETRY::Point(1, 1, 4326)
DECLARE @polygon GEOMETRY = GEOMETRY::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 4326)

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

It returns true, is there something I am missing? All I know is geography is 3D plane and geometry is a flat map, however I am using the earth for calculation if the point is in the polygon.

PS: It doesn't work as well with STContains, STWithin, STOverlaps

Using Microsoft SQL Server 2012

Answer 1

This works:

DECLARE @point GEOGRAPHY = GEOGRAPHY::Point(1, 1, 4326)
DECLARE @polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)

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

You have to be careful with the "direction" in which you describe a polygon for geography - consider a polygon defined as a circle around the equator - did you intend to specify the northern hemisphere or the southern one?

See here:

In an ellipsoidal system, a polygon has no meaning, or is ambiguous, without an orientation. For example, does a ring around the equator describe the northern or southern hemisphere? If we use the geography data type to store the spatial instance, we must specify the orientation of the ring and accurately describe the location of the instance. The interior of the polygon in an ellipsoidal system is defined by the left-hand rule.

Answer 2

You need to apply ReorientObject to interchange interior regions and exterior regions.

DECLARE @point geography  = geography::Parse('POINT (-109.81715474571 32.2371931437342)');  
DECLARE @polygon geography  = geography::Parse('multipolygon (((-127.24365234375 37.944197500754,
-80.68359375 37.944197500754,
-80.68359375 24.966140159913,
-127.24365234375 24.966140159913,
-127.24365234375 37.944197500754)))
');  
SELECT @point.STIntersects(@polygon.ReorientObject())

The below images will demonstrate the difference