STContains-多边形内点的测试失败 [英] STContains - tests failing for points INSIDE polygon

问题描述

首先，SQL版本:

Microsoft SQL Server 2016(RTM)-13.0.1601.5(X64)2016年4月29日23:23:58版权所有(c)Windows Server 2012 R2 Standard 6.3(Build 9600 :)上的Microsoft Corporation Standard Edition(64位) (管理程序)

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

我声明了一个封闭的多边形(使用左手法则正确定向)，根据SQL，该多边形是有效的:

I declare a single closed polygon (correctly oriented using left-hand rule), which according to SQL is valid:

DECLARE @g geography;
SET @g = geography::STPolyFromText('POLYGON((-141.251221 60.177476, -134.351807 52.952478, -124.859620 48.440718, -123.623658 48.163009, -123.132020 48.232578, -122.906113 48.998367, -95.169068 48.966372, -94.980927 49.021337, -94.627991 48.700606, -93.907013 48.594450, -93.845215 48.495351, -93.510132 48.517642, -93.197022 48.583549, -92.743836 48.507408, -92.422486 48.211991, -91.573792 48.021520, -90.973664 48.095065, -90.146256 48.021693, -89.428711 47.911409, -88.442688 48.232120, -84.935303 46.850463, -84.819947 46.614678, -84.531556 46.397991, -84.200593 46.493917, -84.117508 46.124608, -83.620034 45.782175, -82.591267 45.290317, -82.166749 43.592965, -82.661133 42.606311, -82.886353 42.391659, -83.097840 42.332805, -83.187104 42.071357, -83.135605 41.842088, -82.865410 41.657411, -82.463379 41.587347, -81.068116 42.119252, -80.049134 42.334835, -78.887330 42.783953, -78.868104 42.953046, -78.995820 43.161758, -79.090577 43.433616, -78.664856 43.557148, -76.766968 43.541223, -76.398926 44.064539, -74.852601 44.971250, -71.471558 44.957647, -70.846711 45.205884, -70.589219 45.379779, -70.207787 45.900179, -69.238587 47.291005, -68.891488 47.116066, -68.297196 47.291936, -67.850361 47.016678, -67.864953 45.667941, -65.966034 43.191802, -49.229737 45.124550, -59.425049 68.026407, -75.421143 74.789051, -73.663331 78.324047, -57.315674 82.569900, -71.378174 83.545629, -112.335206 79.852216, -141.163331 70.394745, -141.251221 60.177476 ))', 4326);
SELECT @g.IsValidDetailed() AS 'IsValidDetailed', @g.STIsClosed() AS 'STIsClosed', @g.EnvelopeAngle() AS 'EnvelopeAngle';

执行上述查询后，SQL响应:

When the above query is executed, SQL responds with:

IsValidDetailed STIsClosed  EnvelopeAngle
24400: Valid    True        33.0780372828377

为了进行视觉验证，我绘制了多边形(通过Google地图)，还包括了多边形内的3个点以及多边形外的3个点.下面是该多边形的屏幕快照，并指向显示整个多边形的缩放级别(红色区域表示包围该多边形的边界"):

For visual verification, I plotted the polygon (via Google maps) and also included 3 points INSIDE the polygon, as well as 3 points OUTSIDE the polygon. Below is a screen snapshot of the polygon and points at a zoom level that shows the full polygon (the red area denotes the "bounds" that enclose the polygon):

在地图上放大时，您可以开始查看各个点，即:

Zooming in on the map, you can start to see the individual points, i.e.:

进一步放大，您会看到两个点非常接近多边形的边缘，一个点在内部(绿色)，一个在外部(红色):

Zooming in further, you can see two of the points are very close to the edge of the polygon, one inside (green), one outside (red):

以下查询用于测试多边形内的点:

The following query was used to test for points that are INSIDE the polygon:

SELECT lat, lon, 
       @g.STContains( geography::Point( lat, lon, 4326 ) ) AS STContains,
       geography::Point( lat, lon, 4326 ).STWithin( @g ) AS STWithin
 FROM ( VALUES ( 49.119404, -102.988586 ),
               ( 49.004776, -102.996875 ),
               ( 48.975416, -103.005152 )
       ) points( lat, lon )

SQL响应:

lat         lon         STContains  STWithin
49.119404   -102.988586 False       False
49.004776   -102.996875 False       False
48.975416   -103.005152 False       False

-期望STContains和STwithin为多边形内的点返回"True"?

-- Was expecting STContains and STwithin to return "True" for points inside the polygon???

以下查询用于测试多边形的外点:

The following query was used to test for the points OUTSIDE of the polygon:

SELECT lat, lon, 
       @g.STContains( geography::Point( lat, lon, 4326 ) ) AS STContains,
       geography::Point( lat, lon, 4326 ).STWithin( @g ) AS STWithin
 FROM ( VALUES ( 48.975409, -103.005151 ),
               ( 48.974153, -103.005144 ),
               ( 48.951739, -103.005627 )
       ) points( lat, lon )

SQL响应:

lat         lon         STContains  STWithin
48.975409   -103.005151 False       False
48.974153   -103.005144 False       False
48.951739   -103.005627 False       False

-预期结果，因为所有点都在多边形的外侧.

-- Expected results as all points are OUTSIDE of the polygon.

问题:

为什么对于包含在多边形内的点，STContains/STWithin失败了?

Why is STContains / STWithin failing for points that are INSIDE the polygon?

我必须假定自己做错了什么，但我遵循构造多边形的规则(左手)，SQL表示该多边形有效".

I must assume I am doing something wrong, but I have followed the rules (left-hand) for constructing the polygon and SQL indicates the polygon is "valid".

任何建议将不胜感激.

推荐答案

在球形几何中，纬度线不是直线.大圆弧是.多边形的最近边缘是-122.906113 48.998367, -95.169068 48.966372，它位于经度为-103的第49条平行线以北.

Because in spherical geometry, lines of latitude are not straight lines. Great circle arcs are. The nearest edge of your polygon is -122.906113 48.998367, -95.169068 48.966372, which is well north of the 49th parallel at -103 longitude.

select geography::STLineFromText('LINESTRING(-122.906113 48.998367, -95.169068 48.966372)', 4326);

是

这篇关于STContains-多边形内点的测试失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！