Why Sql server returns 1 for STIntersects where it should return 0?

Question

I have bunch of multipolygons records. Long story short, I have one point that belongs to just one multipolygon. When you visualise that with QGis or similar software that is correct. But sql server returns STIntersects = 1 for not just multipolygon in this post but for like ~65% of them. From 26 records 17 says STIntersects = 1 and the one that actually should be 1 says 0.

This is point:

DECLARE @point geography;
set @point = (select geography::STPointFromText('POINT(7.6027101675492395 46.549656011507068)', 4326)

This is multipolygon that should not contain pervious point but it is:

DECLARE @m geography;

set @m = https://pastebin.pl/view/70e8a7d6 (file is to big)

print @m.STIntersects(@point) --says 1 (it should say 0)

I don't know what to do. I used GeoJSON.Net.Contrib.MsSqlSpatial to convert Geo json data to sql geography.

I am not rally familiar with this stuff, I mean Sql server Geography stuff or math behind it.

All I can do is guess and my best guess it that multipolygons somehow got messed up (i don't know how to prove this) or sql server problem or similar.

This is sql server ver.:

Microsoft SQL Azure (RTM) - 12.0.2000.8 
    Feb 23 2022 11:32:53 
    Copyright (C) 2021 Microsoft Corporation

Any idea?

UPDATE From what I can see on Microsoft website here: https://learn.microsoft.com/en-us/sql/relational-databases/spatial/multipolygon?view=sql-server-ver15

multipolygons are like this:

MULTIPOLYGON(((1 1, 1 -1, -1 -1, -1 1, 1 1)),((1 1, 3 1, 3 3, 1 3, 1 1)))

Is there any c# lib that can convert geojson to correct multipolygion?

with brackets around each polygon, where my MULTIPOLYGON is one polygon.

Answer 1

There are two things you need to pay attention to when working with Sql Server.

1. difference between geography and geometry types. You are using geography, which works on Earth sphere. Edges between vertices are spherical geodesics. QGIS mostly works on planar map with planar edges, and that corresponds to Sql Server's geometry type.

2. when working with geography, you need to pay attention to polygon orientation.

I cannot find any Microsoft's docs on that, but here is reasonable explanation: https://alastaira.wordpress.com/2012/01/27/ring-orientation-bigger-than-a-hemisphere-polygons-and-the-reorientobject-method-in-sql-server-2012/

BigQuery also has similar orientation rules and description of the rule: https://cloud.google.com/bigquery/docs/geospatial-data#polygon_orientation

You can either

• switch to geometry type (but you should really think about other differences)
• reverse the polygon using https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/reorientobject-geography-data-type (I would reverse all polygons for which Sql Server's StArea method returns area larger than hemisphere).