CODEWITHSUNDEEP
sql-serversql-server-2012
KregHEk
KregHEk

Reputation: 462

SQL Server geography polygon intersections

I have an issue in SQL Server 2012. When I trying to get intersection of two polygons:

DECLARE @boundingRect geography;
SET @boundingRect = N'POLYGON((27 30, 170 30, 170 80, 27 80, 27 30))'
DECLARE @boundingRect2 geography;
SET @boundingRect2 = N'POLYGON((84 56, 84.1 56, 84.1 56.1, 84 56.1, 84 56))'
SELECT @boundingRect.STIntersection(@boundingRect2).ToString()

it returns GEOMETRYCOLLECTION EMPTY. But it must return second polygon because @boundingRect contains @boundingRect2. If I change to

SET @boundingRect = N'POLYGON((27 20, 170 20, 170 80, 27 80, 27 20))'

it's working fine. Why? What am I doing wrong?

Upvotes: 0

Views: 311

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

From Spatial Data Types:

In an ellipsoidal system, a polygon has no meaning, or is ambiguous, without an orientation. For example, does a ring around the equator describe the northern or southern hemisphere? If we use the geography data type to store the spatial instance, we must specify the orientation of the ring and accurately describe the location of the instance. The interior of the polygon in an ellipsoidal system is defined by the left-hand rule.

And, indeed, if I reverse the order in which the points are specified for @boundingRect, it returns a result:

DECLARE @boundingRect geography;
SET @boundingRect = N'POLYGON((27 30, 27 80, 170 80, 170 30, 27 30))'
DECLARE @boundingRect2 geography;
SET @boundingRect2 = N'POLYGON((84 56, 84.1 56, 84.1 56.1, 84 56.1, 84 56))'
SELECT @boundingRect.STIntersection(@boundingRect2).ToString()

Result:

POLYGON ((84 56.1, 84 56, 84.1 56, 84.1 56.1, 84 56.1))

Upvotes: 1

Related Questions