SQL Server Spatial – MSSQL Geography STIntersects Returns 1 When 0 is Expected

spatialsql server

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.

Best Answer

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.

Related Question