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

user_1820869 0
Viewed 16124

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

geography point polygon geometry sql
2 Answers

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 enter image description here enter image description here

user_18551770
Related