SQL Geography Intersect always returns 1

Question

I'm trying to determine whether a point is within a polygon. The results I'm getting back appear to be always returning 1 regardless of whether the point is within the boundary or not.

DECLARE @point GEOGRAPHY = GEOGRAPHY::Point(54.2225,-4.5366, 4326)
DECLARE @polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((54.2826 -4.4420, 54.2904 -4.6564, 54.0467 -4.7031, 54.2826 -4.4420))', 4326)

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

I'm using SQL Express 2014 running locally

Answer 1

You have a classic problem. The order in which you specify points in a geometry polygon is meaningful. As you've defined it, you've created a polygon that is the whole globe minus a tiny hole. Luckily, both the test for this problem and the fix are fairly easy.

DECLARE @polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((54.2826 -4.4420, 54.2904 -4.6564, 54.0467 -4.7031, 54.2826 -4.4420))', 4326)
set @polygon = @polygon.ReorientObject();
select @polygon.EnvelopeAngle()

If you comment out the call to ReorientObject(), you'll see that the envelope angle is 180 degrees. That is the heuristic that I use personally to see if there's a ring orientation problem. You could also check the area with STArea(). Either way, the re-oriented object is the what you were probably expecting and should give you better results when doing intersection tests!