4

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

Braydie
  • 716
  • 9
  • 27

1 Answers1

9

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!

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • Thanks for the answer - that makes a lot of sense. I'm now using the following code but am still getting a return value of '0' `DECLARE @point GEOGRAPHY = GEOGRAPHY::Point(54.1826, -4.5420, 4326) --INSIDE --DECLARE @point GEOGRAPHY = GEOGRAPHY::Point(57.1826, -3.5420, 4326) --OUTSIDE DECLARE @polygon GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((54.2826 -4.4420, 54.2904 -4.6564, 54.0467 -4.7031, 54.2826 -4.4420))', 4326) if @polygon.EnvelopeAngle() = 180 set @polygon = @polygon.ReorientObject(); SELECT @polygon.STContains(@point)` – Braydie Jan 11 '17 at 09:18
  • 2
    You're gonna love this: The `POINT()` method takes the arguments in opposite order of the WKT that you're providing for the polygon! That is, as you've defined it, you've switched the latitude and longitude. Check `SELECT @polygon.STPointN(1).[Lat], @point.[Lat]` – Ben Thul Jan 11 '17 at 11:54
  • Ah wow, I would never have found that! Thanks again! I've reversed the order of my coords in the ctor of the Point but appear to still be getting zero... My brain is a little fuzzed now, but I don't think that is correct..? – Braydie Jan 11 '17 at 12:33
  • :) No worries! The point that you have declared as inside is *very close* to the polyogn, but isn't quite inside of it. To see this, I ran `select @polygon union all select @point.STBuffer(1000)` and looked at the spatial results tab in Management Studio. This will show the polygon and a disc centered around your point. From what I see, the point is just outside the boundary of the polygon. How are you getting those coordinates? – Ben Thul Jan 11 '17 at 12:41
  • I had no idea that tab existed - that's very useful! I've used mapbox to get the coordinates because I'm writing an application that will take a point (a users current location) and check see whether it falls within a certain boundary. Is the approach I'm taking the most appropriate way of checking that? – Braydie Jan 11 '17 at 13:44
  • Yep! What I was getting at with my question was if these were contrived coordinates, that might explain the disconnect between your expectations and the results you're seeing. But checking intersection is how I'd do it! – Ben Thul Jan 11 '17 at 13:48
  • 1
    Thanks @Ben, I'll try it with some real data and hopefully that will yield some more interesting results. Really appreciate your help - I've learned a lot – Braydie Jan 11 '17 at 14:05