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.

Post data that actually reproduces the problem. People can't guess why you get the result you get. Perhaps the shape isn't what you want, or the conversion from GeoJSON to WKT didn't work as expected. Have you tried visualizing the WKT text? — Panagiotis Kanavos, May 05 '22 at 23:19
you can do it in sql https://techcommunity.microsoft.com/t5/sql-server-blog/loading-geojson-data-into-sql-server/ba-p/384601 — nbk, May 05 '22 at 23:45
You can see the difference between `geometry` and `geography` here https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=af15dbc95241d8670ff327bb0b543419 — Charlieface, May 06 '22 at 03:23

Michael Entin · Accepted Answer · 2022-05-06T05:05:33.810

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

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.
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).

"QGIS mostly works on planar map with planar edges, and that corresponds to Sql Server's geometry type. This was a case." Thank you. — pregmatch, May 06 '22 at 11:20

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

1 Answers1