SQL Server 中的 Geography 数据类型与 Geometry 数据类型 [英] Geography data type vs. Geometry data type in SQL Server

查看:24
本文介绍了SQL Server 中的 Geography 数据类型与 Geometry 数据类型的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

环境:SQL Server 2012我正在使用一种在线工具,这是迄今为止我唯一能找到的,用于绘制地球上的多边形和点.http://www.birdtheme.org/useful/googletool.html我有两张桌子.一个将区域"存储为多边形,另一个表存储与我的问题无关的点.为简单起见,我将我的场景简化为 sql 变量.

Environment: SQL Server 2012 I'm using an online tool, the only one I could find so far, to plot polygons and points on the earth. http://www.birdtheme.org/useful/googletool.html I have two tables. One stores "areas" as polygons and the other table stores points amongst other things irrelevant to my question. For simplicity, I'll just reduce my scenario to sql variables.

在下面的查询中,我将地理数据类型用于众所周知的兴趣点.我在罗本岛周围画了一个多边形,罗本岛的一个点和恶魔岛的一个点.

In the query below, I'm using the geography data type for well known points of interest. I drew a polygon around Robben Island, a point in Robben Island and a point in Alcatraz.

DECLARE @robben_island geography = ('POLYGON((18.351803 -33.788421,18.382788 -33.787494,18.386736 -33.820515,18.354464 -33.822369,18.351803 -33.788421))')
DECLARE @point_in_robben_island geography= ('POINT(18.369226 -33.80554)')
DECLARE @point_in_alcatraz geography= ('POINT(-122.423401 37.827006)')

SELECT @robben_island.STContains(@point_in_robben_island)   --returns 'False', but it's not what I expected
SELECT @robben_island.STContains(@point_in_alcatraz)        --returns 'True', but it's not what I expected

上面的这个查询,如果我理解正确的话,它告诉我我的@point_in_robben_island 不包含在 @robben_island 中,而是我的 @point_in_alcatraz 存在于 @robben_island 众所周知,这是不正确的.

This query above, if I understand it correctly, tells me that my @point_in_robben_island is not contained in @robben_island, rather my @point_in_alcatraz exists in @robben_island which as we all know, is not true.

现在,当我将数据类型从地理更改为几何时,一切正常,但我担心如果我继续使用几何数据类型,我可能会遇到一些问题.我只是想知道我是否不会受到几何不能完全解释地球曲率这一事实的负面影响.触摸木头.

Now when I change the data types from geography to geometry, everything works fine, but I'm afraid that if I continue using the geometry data type I might come across a few gotchas. I'm just wondering if I won't be negatively affected by fact that geometry doesn't quite account for earth's curvature. touch wood.

DECLARE @robben_island geometry = ('POLYGON((18.351803 -33.788421,18.382788 -33.787494,18.386736 -33.820515,18.354464 -33.822369,18.351803 -33.788421))')
DECLARE @point_in_robben_island geometry= ('POINT(18.369226 -33.80554)')
DECLARE @point_in_alcatraz geometry= ('POINT(-122.423401 37.827006)')

SELECT @robben_island.STContains(@point_in_robben_island)   --returns 'True' as it should
SELECT @robben_island.STContains(@point_in_alcatraz)        --returns 'False' as it should

现在我的问题是,为什么 geography 数据类型会返回意外的结果,而几何却按预期工作?非常感谢.

Now my question is, why does the geography data type return unexpected results while geometry works as expected? Thank you very much.

推荐答案

地理类型比几何类型限制更多.它不能跨越不同的半球,必须逆时针绘制外环.

The geography type is a little bit more restrictive than geometry. It can't cross different hemispheres and the outer ring must be drawn counter-clockwise.

不幸的是(有些人认为这是一件好事),当您创建无效的地理位置时,SQL Server 2012 不再抛出错误.您需要反转 Roben Island 几何中点的顺序,例如:

Unfortunately (some find this a good thing), SQL Server 2012 no longer throws an error when you create the invalid geography. You need to invert the order of the points in the Roben Island geometry, like:

DECLARE @robben_island geography = ('POLYGON((18.351803 -33.788421, 18.354464 -33.822369,18.386736 -33.820515, 18.382788 -33.787494, 18.351803 -33.788421))')
DECLARE @point_in_robben_island geography= ('POINT(18.369226 -33.80554)')
DECLARE @point_in_alcatraz geography= ('POINT(-122.423401 37.827006)')

SELECT @robben_island.STContains(@point_in_robben_island)   --returns 'True'
SELECT @robben_island.STContains(@point_in_alcatraz)        --returns 'False'

这篇关于SQL Server 中的 Geography 数据类型与 Geometry 数据类型的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆