SQL Server 2012，geography.STContains，结果错误? [英] 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

推荐答案

现在我发现了问题.用户从右下方开始绘制多边形并顺时针绘制.如果我从最大纬度重新排序点，然后通过按 long, lat 排序逆向进行，它会起作用.为它找到了一个帮手，但只有在您知道这是错误的"时才有效:

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 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.STContains，结果错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！