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

问题描述

环境：SQL Server 2012
我使用了一个在线工具，这是迄今为止唯一可以找到的工具，用于绘制地球上的多边形和点。 http://www.birdtheme.org/useful/googletool.html
我有两张桌子。一个将区域存储为多边形，其他表格存储点与其他内容无关，而与我的问题无关。
为简单起见，我只是将我的场景简化为sql变量。

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

  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'，但这不是我所期望的
 SELECT @ robben_island.STContains（@point_in_alcatraz）--returns'True'，但这不是我的预计
  

上面的这个查询，如果我理解正确，告诉我我的 @point_in_robben_island 不包含在 @robben_island ，而我@point_in_alcatraz在 @robben_island 存在我们都知道，这是不正确的。

现在，当我将数据类型从地理更改为几何时，一切正常，但恐怕不会如果我继续使用几何数据类型，我可能会遇到一些疑难问题。我只是想知道，如果我不会受到几何不能完全解释地球曲率的事实的消极影响。触摸木头。

  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） - 返回'True'，因为它应该是
 SELECT @ robben_island.STContains（@point_in_alcatraz） - 返回'False'，因为它应该是
  

现在我的问题是，为什么地理数据类型返回意外结果，而几何按预期工作？非常感谢。

解决方案

地理类型比几何更具限制性。它不能穿过不同的半球，而且外环必须逆时针方向拉。

不幸的是（有些人觉得这是件好事），当你创建无效的地理区域时，SQL Server 2012不会再抛出错误。您需要反转Roben Island几何中点的顺序，如：

  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） - 返回'False'
  

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

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

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.

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中的地理数据类型与几何数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！