SQL Server 2012,geography.ST包含错误结果? [英] SQL Server 2012, geography.STContains, wrong result?
问题描述
我正在使用SQL Server 2012,geography.STContains
(文档),我不明白为什么下面的代码会失败.
I'm using SQL Server 2012, geography.STContains
(documentation) and I don't get why the code below fails.
如果我切换到geometry
,它将起作用.
If I switch to geometry
it works.
有人可以解释吗?
//丹尼尔
declare @geo geography
set @geo = geography::STPolyFromText('POLYGON ((17.519133203852 59.8297423369731, 17.5190071588812 59.8296936773323, 17.5189979955459 59.8298203729009, 17.5191345140461 59.8298223425544, 17.519133203852 59.8297423369731))', 4326)
-- Is not within
declare @p1 geography
set @p1 = geography::STPointFromText('POINT(17.5184709839477 59.829925754067)', 4326)
-- Is within
declare @p2 geography
set @p2 = geography::STPointFromText('POINT(17.519060 59.829774)', 4326)
select
@geo.STContains(@p1), -- should be 0 is 1
@geo.STContains(@p2) -- should be 1 is 0
更新: 如果我将它们反转,就可以了,但是我不明白这一点:
UPDATED: If I invert them it works just fine, but then I don't get this:
declare @geo geography
set @geo = geography::STPolyFromText('POLYGON ((17.519133203852 59.8297423369731, 17.5190071588812 59.8296936773323, 17.5189979955459 59.8298203729009, 17.5191345140461 59.8298223425544, 17.519133203852 59.8297423369731))', 4326)
select
@geo.STAsText() Polygon,
@geo.STPointN(1).STAsText() Point1,
@geo.STPointN(1).Lat Point1Latitud,
@geo.STPointN(1).Long Point1Longitude
这将导致以下结果:
Polygon
POLYGON ((17.519133203852 59.8297423369731, 17.5190071588812 59.8296936773323, 17.5189979955459 59.8298203729009, 17.5191345140461 59.8298223425544, 17.519133203852 59.8297423369731))
Point1
POINT (17.519133203852 59.8297423369731)
Point1Latitud
59,8297423369731
Point1Longitude
17,519133203852
推荐答案
现在我发现了问题.用户从右下角开始绘制多边形,然后顺时针旋转.如果我从最大的纬度对点进行重新排序,然后按较长的纬度进行排序,那么它会起作用.找到了它的助手,但是只有在您知道错了"的情况下,它才有效:
Now I found the issue. The user drew the polygon starting lower-right and went clockwise. If I reorder the points from biggest latitude and then go counterwise by sorting on long, lat it works. Found a helper for it, but that only works if you "know it's wrong":
if(sqlGeography.EnvelopeAngle() > 90)
sqlGeography ? sqlGeography.ReorientObject();
只需组合一个可以解决我的问题的小解决方案: https://github.com/danielwertheim/GeographyFactory
Just put together a small solution that will fix my values: https://github.com/danielwertheim/GeographyFactory
以及与此有关的博客文章: http://danielwertheim.se/sqlgeography-in-sql-server-2012-polygon-must-start-on-correct-position/
and a blogpost about it: http://danielwertheim.se/sqlgeography-in-sql-server-2012-polygon-must-start-on-correct-position/
以及关于实际问题"的后续规则,即左手规则:
and a follow up about the real "issue", the left-hand rule:
http://danielwertheim.se/sqlgeography-in-sql-server-2012-polygon-must-start-on-correct-position-no/
这篇关于SQL Server 2012,geography.ST包含错误结果?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!