Please help me

I have the following sql code

DECLARE  @polygon geography
SET @polygon = 'POLYGON((-73.6965649914466 41.4459906683457,-73.6688781089674 41.4399017487713,-73.6969460096452 41.4295218755254,-73.6965649914466 41.4459906683457))'

SELECT @polygon

It compiles but when I look at the spatial results tab the polygon is not correct.

Can anybody help me to understand why is that?

EDIT

If i use ReorientObject then it works, but, when i should used it?

SELECT @polygon.ReorientObject()

CodePudding user response：

The points are around the wrong way, this is one of those Left Hand rule issues.

Ultimately this occurs when the Application Interface is using logic or a library that uses the Right Hand rule. Some toolkits have a flag so you can reverse this, if you are manually constructing the SQL query to write to the database, you should update that logic to inject the points in the correct sequence.

In this case the middle points in your polygon need to be reversed:

DECLARE  @polygon geography
-- First attempt
SET @polygon = 'POLYGON((-73.6965649914466 41.4459906683457,-73.6688781089674 41.4399017487713,-73.6969460096452 41.4295218755254,-73.6965649914466 41.4459906683457))'
-- Fixed points
SET @polygon = 'POLYGON((-73.6965649914466 41.4459906683457,-73.6969460096452 41.4295218755254,-73.6688781089674 41.4399017487713,-73.6965649914466 41.4459906683457))'

SELECT @polygon

Unfortunately, in this case MakeValid() will not work, But when we know this is the problem we can use ReorientObject() to reverse the points:

DECLARE  @polygon geography
SET @polygon = 'POLYGON((-73.6965649914466 41.4459906683457,-73.6688781089674 41.4399017487713,-73.6969460096452 41.4295218755254,-73.6965649914466 41.4459906683457))'
SELECT @polygon.ReorientObject()

This is a good reference for these types of issues: Inverted Polygons? How to Troubleshoot SQL Server's Left Hand Rule

How to know?

A simple way to determine if the orientation is correct is to use the STIsValid() function. If the polygon is not valid, then we can use .MakeValid() to try to fix the points.

In cases like this where the polygon is actually valid, we can compare the area to the inverse area and pick the smaller of the two:

DECLARE  @polygon geography
SET @polygon = 'POLYGON((-73.6965649914466 41.4459906683457,-73.6688781089674 41.4399017487713,-73.6969460096452 41.4295218755254,-73.6965649914466 41.4459906683457))'
SELECT @polygon.STIsValid() AS STIsValid, @polygon.STArea() AS STArea
UNION ALL
SELECT @polygon.ReorientObject().STIsValid(), @polygon.ReorientObject().STArea()

So we could use SQL logic like this:

DECLARE  @polygon geography
SET @polygon = 'POLYGON((-73.6965649914466 41.4459906683457,-73.6688781089674 41.4399017487713,-73.6969460096452 41.4295218755254,-73.6965649914466 41.4459906683457))'

DECLARE @Area real, @InvertedArea real;
SELECT @Area = @polygon.STArea()
     , @InvertedArea = @polygon.ReorientObject().STArea();
IF (@InvertedArea < @Area)
BEGIN
    SELECT @polygon = @polygon.ReorientObject();
END

SELECT @polygon;

It is best to validate the data as part of the write operation instead of on every read. If you have a Service Orientated Architecture, then the obvious place would be in the API layer, however you could even use an after or instead of trigger to _correct the data if you do not have control over the code.

If you post the SQL or application code that is performing the write, then I can provide a pragmatic solution for you, but hopefully this information will be enough to get you on your way.