0

I show you the conclusion after many headaches and tests.

If we execute the following script in any SSMS session under SQL Server 2012 (also tested in 2017):

DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STPolyFromText('POLYGON ((-2.2141931466053393 36.848142880725426, -2.1632066297350296 36.864255247830073, 3.0526676842932088 39.266689645726004, 3.168352172454167 39.329935703712941, 3.2286305251469463 39.370418565526464, 3.2322979289615716 39.374091534163213, 3.2372882795963895 39.379457236292687, 3.2583498367577581 39.409984643625563, 3.3506438583660594 39.556107032723332, 3.4300534340816529 39.699235599046659, 3.2674327158297669 42.289964571287427, 3.1599698848294775 42.435144600606137, 0.79329389164208441 42.99441296630598, -7.6685442882152826 43.77780739075277, -8.0476876236197672 43.711457921649725, -9.2115225666636089 43.160194190763086, -9.2984566034556444 43.054102468725411, -9.2715498704469024 42.881894543711283, -7.5169998811285126 37.556527784974641, -7.4389506340710883 37.345028476444256, -7.4300533647696216 37.33933723597093, -7.426814647077407 37.337972629773219, -7.4171018386796526 37.336039770123939, -7.36341431379385 37.33029040340196, -4.5559445740609537 37.01597060730704, -2.2141931466053393 36.848142880725426))', 4326);
SET @h = geography::STPointFromText('POINT (-5.7805724666961673 43.604738856455796)', 4326);
select @g.STIntersects(@h)

We obtain the result 1. Which means that the geometries intersect. Which is not true and you can see clearly if we represent the geometries in GIS tools, such as ArcMap, QGIS or visualize in sites like https://clydedacruz.github.io/openstreetmap-wkt-playground/.

This does not happen only with that point, they can test with others such as the following:

POINT (-5.7808907869201684 43.607612302768608)
POINT (-5.7867532730156022 43.607109291914668)
POINT (-5.7910420343533673 43.607409757130171)
POINT (-5.7962209295114038 43.605527381457819)
POINT (-5.8379991303640395 43.609944466702757)
POINT (-5.8372379698022909 43.613519832305009)
POINT (-5.8339925740272829 43.616976768767834)
POINT (-5.832657139630153 43.620206197447274)
POINT (-5.827899502105284 43.624465756821465)
POINT (-5.8230287455979495 43.6276474699738)

Clarify that it is not a problem of orientation of the ring. Other interior points appear as intersecting.

I have not found an explanation, just a possibility of a bug in SQL server. This makes me very distrustful of the STIntersects () function that I use in countless places in my code.

I would appreciate any response.

Improve this question
4
  • 3
    With points so close to the edges of the polygon, you have to consider whether the lines between successive points of the polygon are "straight lines" (whatever that may mean when we're realing dealing with a spheroid) or arcs of a great circle. Commented Apr 4, 2018 at 12:20
  • Maybe the difference (to the other tools you are using) comes from a different SRID? Commented Apr 6, 2018 at 7:39
  • I've done a bunch of tinkering on this question but with no explanation better than @Damien_The_Unbeliever gave . As far as I can tell with spatial queries, the points are ≈ 5km away from the border of your polygon. Commented Apr 6, 2018 at 14:24
  • @RazvanSocol: Part of the tinkering I mention above was to interpret all of the points and polygon in all SRIDs that SQL knows about. They all show intersection. But great minds think alike! :) Commented Apr 6, 2018 at 14:25

3 Answers 3

Reset to default
1

After a lot of thinking, my conclusion is that the problem is the projection used in geography for the operations and graphic representations. If we make the problem with geometry the result is different. You can see here:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STPolyFromText('POLYGON ((-2.2141931466053393 36.848142880725426, -2.1632066297350296 36.864255247830073, 3.0526676842932088 39.266689645726004, 3.168352172454167 39.329935703712941, 3.2286305251469463 39.370418565526464, 3.2322979289615716 39.374091534163213, 3.2372882795963895 39.379457236292687, 3.2583498367577581 39.409984643625563, 3.3506438583660594 39.556107032723332, 3.4300534340816529 39.699235599046659, 3.2674327158297669 42.289964571287427, 3.1599698848294775 42.435144600606137, 0.79329389164208441 42.99441296630598, -7.6685442882152826 43.77780739075277, -8.0476876236197672 43.711457921649725, -9.2115225666636089 43.160194190763086, -9.2984566034556444 43.054102468725411, -9.2715498704469024 42.881894543711283, -7.5169998811285126 37.556527784974641, -7.4389506340710883 37.345028476444256, -7.4300533647696216 37.33933723597093, -7.426814647077407 37.337972629773219, -7.4171018386796526 37.336039770123939, -7.36341431379385 37.33029040340196, -4.5559445740609537 37.01597060730704, -2.2141931466053393 36.848142880725426))', 4326);
SET @h = geometry::STPointFromText('POINT (-5.7805724666961673 43.604738856455796)', 4326);
select @g.STIntersects(@h)
select @g union all select @h

I hope that it is useful for someone.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

The problem is not with the STIntersects() method but with the precision that SQL Server uses to store geospatial data, per this blog

SQL Server stores geography and geometry coordinates as binary data, adhering to the IEEE-754 standard for binary floating-point arithmetic. Based on this standard, each coordinate is stored as a double-precision floating-point number that is 64 bits (8 bytes) long.

According to Microsoft Docs

The error tolerance for the geography methods can be as large as 1.0e-7 * extents. The extents refer to the approximate maximal distance between points of the geographyobject.

Using the below code you can see the precision changes.

DECLARE @p1 geography;
DECLARE @p2 geography;
DECLARE @h geography;
SET @p1 = geography::STPointFromText('POINT (-7.6685442882152826 43.77780739075277)', 4326);
SET @p2 = geography::STPointFromText('POINT (0.79329389164208441 42.99441296630598)', 4326);
SET @h = geography::STPointFromText('POINT (-5.7805724666961673 43.604738856455796)', 4326);

select @p1.Lat, @p1.Long
select @p2.Lat, @p2.Long
select @h.Lat, @h.Long
Improve this answer

1 Comment

You have done a good observation, but with that precision the point continue being outside and STIntersects indicates that it is inside. That precision has a tolerance lower that centimeters and the point is outside for meters.
0

Try running this in SSMS:

SELECT @g UNION ALL SELECT @h

As you can see in the "Spatial results" tab, the point is inside the polygon (regardless of the projection used).

Improve this answer

3 Comments

That is the question. Really the point is not inside of the polygon. You can draw the shape in other places like clydedacruz.github.io/openstreetmap-wkt-playground or QGIS and you can see that actually It is not inside. You can use the next WKT to see it, with GEOMETRYCOLLECTION(POLYGON(...),POINT()):
I'd say that's a bug in the clydedacruz site. Try this one instead: gcmap.com/…
It's also tested in QGIS and ArcGIS Pro. It looks more a bug in SQL Server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.