Polygon.Intersects fails with SqlServer

[Dona278]

I try to explain my issue with a dummy models because i can't copy-paste the structure of the project.

public class Event
{
    public int EventId { get; set; }

    [Column(TypeName = "geography")]
    public Point Coordinate { get; set; }
}

I've used event.Coordinate.Distance(pointCoordinate) method in some query without any problems (thanks to the work between EFCore and NTS) to perform a search around a requested point, but today I've got a problem with the query with polygon.Intersects(event.Coordinate).

I created a closed polygon in this way:

"zone": [
   {
      "latitude": 44.09035922289142,
      "longitude": 12.433433532714844
   },
   {
      "latitude": 44.09035922289142,
      "longitude": 12.763023376464844
   },
   {
      "latitude": 43.97471931286352,
      "longitude": 12.763023376464844
   },
   {
      "latitude": 43.97471931286352,
      "longitude": 12.433433532714844
   },
   {
      "latitude": 44.09035922289142,
      "longitude": 12.433433532714844
   },
]

var polygonCoordinates = zone
    .Select(c => new GeoAPI.Geometries.Coordinate(c.Longitude, c.Latitude))
    .ToList();

var geometryFactory = NtsGeometryServices.Instance.CreateGeometryFactory(srid: 4326);
var polygon = geometryFactory.CreatePolygon(polygonCoordinates.ToArray());

// See: https://github.com/NetTopologySuite/NetTopologySuite.IO.SqlServerBytes/issues/4#issuecomment-458938163
polygon.Normalize();
polygon.Reverse();

// Throw exception
var events = await _context.Events
   .Where(event => polygon.Intersects(event.Coordinate))
   .ToListAsync(cancellationToken);

Exception thrown: System.ArgumentException: When writing a SQL Server geography value, the shell of a polygon must be oriented counter-clockwise. To write polygons without a shell, set SkipGeographyChecks

After some tests with zone coordinates I tried to switch latitude with longitude and I see that the .IsCCW property on polygon object (that will be used as a check before throwing the exception described above) is true so no exception due to the shell of polygon but the generated query is invalid:

exec sp_executesql N'SELECT [event].[EventId], [event].[Coordinate]
FROM [Event] AS [event]
WHERE @__polygon.STIntersects([event].[Coordinate]) = 1',
N'@__polygon varbinary(112)',@__polygon=0xE610000001040500000000000000EBDD2840F69A19E4900B464000000000AB862940F69A19E4900B464000000000AB862940A3C3399AC3FC454000000000EBDD2840A3C3399AC3FC454000000000EBDD2840F69A19E4900B464001000000020000000001000000FFFFFFFF0000000003

which throw another exception: System.Data.SqlClient.SqlException: Cannot call methods on varbinary

It seems that the creation of a geography polygon based on varbinary before calling .STIntersects() is lacking. Am I wrong? I'm using the coordinates to create polygon in a wrong way?

[ajcvickers]

Duplicate of #14595

@Dona278 You should be able to get this to work using the same workaround as shown for #14595. Specifically, call Intersects on the mapped property rather than on the parameter:

var events = await _context.Events
   .Where(event => event.Coordinate.Intersects(polygon))
   .ToListAsync(cancellationToken);

Please comment here let us know if this doesn't work.

[Dona278]

Thank you @ajcvickers for the reply and yes, the latest issue is a duplicated of #14595 and I've already tried to change the query in the suggested format but I have no results, and the coordinates are correct. I think that it's related to the latest attempt that I tried: switch the latitude with longitude. Using the correct order of longitude and latitude during creation of the polygon I cannot go ahead to the query because of the exception mentioned above: System.ArgumentException: When writing a SQL Server geography value, the shell of a polygon must be oriented counter-clockwise. To write polygons without a shell, set SkipGeographyChecks. But here I saw that the parameter mentioned into the exception was removed and the suggested workaround

polygon.Normalize();
polygon.Reverse();

doesn't work... I'm wrong? What can I do?

[Dona278]

Ok, I used the query as @ajcvickers has mentioned and I tried the easiest attempt ever: reverse the coordinates received from client request before the creation of the polygon 🤦‍♂️. Client application sends coordinates in this order: Top-Left -> Top-Right -> Bottom-Right -> Bottom-Left -> Top-Left and before, when I had the query written as a Sql query instead of with EF, I didn't have any problems. The two lines of code

polygon.Normalize();
polygon.Reverse();

are not served

[ajcvickers]

Re-opening to check on Normalize and Reverse.

[ajcvickers]

With regard to Normalize and Reverse:

• For Reverse to be useful here it would need to be called on the rings inside the polygon object.
• Normalize is not relevant here--it's only really used for some cases in NTS, but not to "normalize for the database in use" or anything like that.