6

I'm experiencing an MSSQL Geography issue I can't explain. Assume that I'm math-challenged, but it seems like the following point should NOT intersect the following polygon:

DECLARE @t_point geography;  
DECLARE @t_polygon geography;
set @t_polygon = geography::STPolyFromText('POLYGON((1 1, 5 5, 5 0, 1 1))', 4326);
set @t_point = geography::Point(0, 0, 4326);


select
  @t_polygon.STAsText(), 
  @t_point.STAsText(),
  @t_point.STIntersects(@t_polygon), 
  @t_polygon.STIntersects(@t_point);

When I execute this, though, I get the following result:

POLYGON ((1 1, 5 5, 5 0, 1 1))  POINT (0 0) 1   1

The last two columns are both 1 where I would expect 0. How can a point in space at 0,0 intersect with a polygon that does not touch or encompass 0,0?

-1, -1 has the same behavior, as does 100, 100.

Even if I change the point to a polygon in a completely different space, MSSQL says that they intersect.

set @t_polygon = geography::STPolyFromText('POLYGON((10 10, 50 50, 50 10, 10 10))', 4326);
set @t_point = geography::STPolyFromText('POLYGON((1 1, 5 5, 5 0, 1 1))', 4326);

I also tried STGeomFromText().

I'm sure I must be doing something wrong, but I can't imagine what that might be.

9

The reason you are having a problem is that the polygon is wound the wrong way which results in a polygon that covers the entire world except for your area of interest.

Try

DECLARE @t_point geography;  
DECLARE @t_polygon geography;
set @t_polygon = geography::STPolyFromText('POLYGON((1 1, 5 0, 5 5, 1 1))', 4326);
set @t_point = geography::Point(0, 0, 4326);

It is a bit strange, but given that with geographies we are dealing with a sphere, there needs to be a way to determine which side of the boundary is the interior of the polygon. With SQL Server, this is done by making the exterior boundary be defined as anti clockwise.

Geometries don't care which way they are wound, but prefer anti clockwise. There is a function for geographies ReorientObject that change the direction of the object.

  • You are...correct. It seems SUPER strange, but you are indeed correct that it behaves the way I expect when I reverse the direction of the coordinates. – Mike Forget Nov 28 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.