MSSQL地理:: stintersects在打开或非常靠近边界框边缘时返回不正确的结果。 “修复”的正确方法是什么这

发布于 2025-02-08 09:28:33 字数 1434 浏览 4 评论 0原文

考虑以下内容（源自多边形内部的点在上面的stintersect上不返回true（但使用几何形状返回true））

-- TEST CASE1
DECLARE @point1 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (0 -0.0000001)', 4326)
DECLARE @polygon1 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)
SELECT @polygon1.STIntersects(@point1), @point1.STIntersects(@polygon1), @point1.STDistance(@polygon1)

-- TEST CASE2
DECLARE @point2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)
SELECT @polygon2.STIntersects(@point2), @point2.STIntersects(@polygon2), @point2.STDistance(@polygon2)

：

测试案例1返回true（0距离），尽管该点落在多边形之外。
测试案例2返回假（1M距离），尽管该点位于多边形的边缘。

我应该如何处理地理点的舍入错误。

Backound

测试2-是现实世界中的问题。边界框是从点集群atlas.data.boundingbox.frompositions（coords）（Azure Maps）得出的。当使用边界框中选择项目时，结果集中缺少项目，即使这些项目与创建边界框的相同点相同！

如果需要的话，我可以稍微扩展边界框，或者选择距离＆lt的数据； 2米。我不禁认为我在某处错过了一个技巧，而我的Google-foo缺乏。

我已经尝试过range（xx）没有运气（或不了解），

也许期望您“只是”需要+/- epsilon？

想法欢迎 xxx

原文

Consider the following (derived from SQL Geography point inside polygon not returning true on STIntersect (but returns true using Geometry))

-- TEST CASE1
DECLARE @point1 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (0 -0.0000001)', 4326)
DECLARE @polygon1 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)
SELECT @polygon1.STIntersects(@point1), @point1.STIntersects(@polygon1), @point1.STDistance(@polygon1)

-- TEST CASE2
DECLARE @point2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)
SELECT @polygon2.STIntersects(@point2), @point2.STIntersects(@polygon2), @point2.STDistance(@polygon2)

In the above:

Test case 1 returns TRUE (0 distance), although the point falls outside of the polygon.
Test case 2 returns FALSE (1m distance), although the point lies on the edge of the polygon.

How should I handle the rounding errors of geography points.

Backgound

Test 2 - is a real-world issue. The bounding box is derived from a point cluster atlas.data.BoundingBox.fromPositions(coords) (azure maps). When the items are selected from SQL using the bounding box there are items missing from the result set even though these are the same points used to create the bounding box!

If I need, I could expand the bounding box slightly or select data where distance < 2 metres. I can't help but think that I'm missing a trick somewhere and my google-foo is lacking.

I've tried Range(xx) no luck (or not understood)

Perhaps the expectation is that you "just" need to +/-EPSILON?

Ideas welcome
x x x

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蓝眸 2025-02-15 09:28:33

使用边界框时，我以前遇到过SQL。问题归结于地理对象与几何对象。当使用地理时，点之间的线沿着测量路径（遵循地球曲率的路径）遵循。因此，当使用地理时，“边界框”实际上表示一个看起来更像以下图像的区域：

< img src =“ https://i.sstatic.net/smlx0.png” alt =“在此处输入图像说明”>

这就是为什么几何形状具有stenvelope方法和地理位置。使用地理时，边界圆更加准确。开发器，包封方法）。您会惊讶地知道，大多数主要的地图平台实际上将其空间数据存储为几何，以基于方形边界框进行渲染和查询。

您的情况有一些解决方案；

解决方案1 ，

由于您的查询形状来自墨卡托地图，因此使用几何形状而不是地理学将更准确地工作（也更快）。一个需要注意的是，如果您需要支持穿越反梅尔德人的形状（-180/180经度），则它将无法正常工作。第二个警告是，所有距离都将以度为单位，而不是米。这就是这样的样子：

-- TEST CASE1
DECLARE @point1 GEOMETRY = GEOMETRY::STGeomFromText('POINT (0 -0.0000001)', 4326)
DECLARE @polygon1 GEOMETRY = GEOMETRY::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)
SELECT @polygon1.STIntersects(@point1), @point1.STIntersects(@polygon1), @point1.STDistance(@polygon1)

-- TEST CASE2
DECLARE @point2 GEOMETRY = GEOMETRY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOMETRY = GEOMETRY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)
SELECT @polygon2.STIntersects(@point2), @point2.STIntersects(@polygon2), @point2.STDistance(@polygon2)

这就是输出的样子：

要获得米而不是学位的距离，您可以将几何形状转换为地理。请注意，这将与您在Mercator地图上的视觉上看到的内容保持一致，但在空间上是准确的。这是一个示例：

DECLARE @point2 GEOMETRY = GEOMETRY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOMETRY = GEOMETRY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)

DECLARE @polygonGeo2 GEOGRAPHY = GEOGRAPHY::STGeomFromWKB(@polygon2.STAsBinary(), 4326)

SELECT GEOGRAPHY::STGeomFromWKB(@point2.STAsBinary(), 4326).STDistance(@polygonGeo2)

使用众所周知的二进制文件在几何和地理之间转换非常快。

请注意，如果您使用point1/polygon1进行此操作，则结果为0，因为米的距离极小（〜1cm）。

解决方案2

如果您有计划进行的其他空间查询，例如在一定距离内找到所有点，则可能不需要将数据转换为几何。处理此操作的三种方法，

为您的点的几何版本添加第二列，然后在该列中使用解决方案1。不利的一面是，数据库最终会变得更大，如果行点会更改位置，则最终必须做更多的工作使事情保持同步。
用几何多边形作为查询的一部分，将点转换为几何形状。

-- TEST CASE2
DECLARE @point2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOMETRY = GEOMETRY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)

SELECT @polygon2.STIntersects(GEOMETRY::STGeomFromWKB(@point2.STAsBinary(), 4326)), 
    GEOMETRY::STGeomFromWKB(@point2.STAsBinary(), 4326).STIntersects(@polygon2), 
    GEOMETRY::STGeomFromWKB(@point2.STAsBinary(), 4326).STDistance(@polygon2)

通过利用ChamevendeAggregate来计算边界框点的地理边界圆。

DECLARE @point2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)

Declare @env Geography = Geography::EnvelopeAggregate(@polygon2);

SELECT @point2.STIntersects(@env), @point2.STDistance(@env)

这可能是最简单，最快的方法，但是当我遇到这个解决方案时，您将需要对此进行更多测试。

I came across this before with SQL when using bounding boxes. The issue comes down to geography object vs geometry object. When using a geography, the lines between points follow a geodesic path (a path that follows the curvature of the earth). So, when using geography, a "bounding box" actually represents an area that looks more like the following image:

This is why Geometry has a STEnvelope method and Geography doesn't. When working with Geography, a bounding circle is more accurate.EnvelopeCenter, EnvelopeAngle methods). You would be surprised to know that most major map platforms actually store their spatial data as geometries for the purpose of rendering and queries based on square bounding boxes.

There are a couple of solutions to your situation;

Solution 1

Since your query shape is coming from a Mercator map, using Geometry instead of Geography will work much more accurately (it is also faster). One caveat is that it won't work well if you need to support shapes that cross the anti-Merdian (-180/180 longitude). A second caveat is that all distances will be in degrees, not meters. Here is what this would look like:

-- TEST CASE1
DECLARE @point1 GEOMETRY = GEOMETRY::STGeomFromText('POINT (0 -0.0000001)', 4326)
DECLARE @polygon1 GEOMETRY = GEOMETRY::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 4326)
SELECT @polygon1.STIntersects(@point1), @point1.STIntersects(@polygon1), @point1.STDistance(@polygon1)

-- TEST CASE2
DECLARE @point2 GEOMETRY = GEOMETRY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOMETRY = GEOMETRY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)
SELECT @polygon2.STIntersects(@point2), @point2.STIntersects(@polygon2), @point2.STDistance(@polygon2)

Here is what the output looks like:

To get distances in meters rather than degrees, you can convert the geometry to geography. Note that this will not align with what your see visually on a Mercator map but will be spatially accurate. Here is an example:

DECLARE @point2 GEOMETRY = GEOMETRY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOMETRY = GEOMETRY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)

DECLARE @polygonGeo2 GEOGRAPHY = GEOGRAPHY::STGeomFromWKB(@polygon2.STAsBinary(), 4326)

SELECT GEOGRAPHY::STGeomFromWKB(@point2.STAsBinary(), 4326).STDistance(@polygonGeo2)

Converting between Geometry and Geography using well known binary is extremely fast.

Note that if you do this with point1/polygon1 the result is 0 because the distance in meters is extremely small (~1cm).

Solution 2

Convert your data to Geometry may not be desired if you have other spatial queries you plan to make, such as finding all points within a certain distance in meters. Three ways to handle this,

Add a second column for a geometry version of your points and use solution 1 with that column. The downside to this is that the database ends up being bigger and you end up having to do more work keeping things in sync if a rows point changes location.
Convert the points to geometry on the fly as part of the query with a geometry polygon.

-- TEST CASE2
DECLARE @point2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOMETRY = GEOMETRY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)

SELECT @polygon2.STIntersects(GEOMETRY::STGeomFromWKB(@point2.STAsBinary(), 4326)), 
    GEOMETRY::STGeomFromWKB(@point2.STAsBinary(), 4326).STIntersects(@polygon2), 
    GEOMETRY::STGeomFromWKB(@point2.STAsBinary(), 4326).STDistance(@polygon2)

Calculate the geography bounding circle for the points of your bounding box by leveraging EnvelopeAggregate.

DECLARE @point2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POINT (-4.278563 55.833035)', 4326)
DECLARE @polygon2 GEOGRAPHY = GEOGRAPHY::STGeomFromText('POLYGON ((
-4.351235 55.833035, 
-4.245494 55.833035,
-4.245494 55.879491, 
-4.351235 55.879491, 
-4.351235 55.833035))', 4326)

Declare @env Geography = Geography::EnvelopeAggregate(@polygon2);

SELECT @point2.STIntersects(@env), @point2.STDistance(@env)

This would likely be the easiest and fastest method, but you will want to test this some more as I just came across this solution.

回复收藏 0 原文

~没有更多了~