SQL server中的几何和地理数据类型

Can anyone give real example of geometry and geography datatypes in SQL server and explain me the purpose of them? I haven't seen use of these two datatypes anywhere. We have generic datatypes in sql server like varchar which can also be used to store such data. What is the benefit of using these two datatypes?

谁能给出SQL server中几何和地理数据类型的真实例子，并向我解释它们的用途?我在任何地方都没见过这两个数据类型的使用。我们在sql server中有通用数据类型，比如varchar，它也可以用来存储此类数据。使用这两个数据类型有什么好处?

4 个解决方案

#1

geometry and geography are datatypes used for storing spatial information - describing the shape and position of objects in space (usually on the surface of the earth).

几何和地理是用来存储空间信息的数据类型——描述物体在空间中的形状和位置(通常在地球表面)。

Why do you need a dedicated datatype for this sort of information? Think about how you would ORDER BY spatial information - it doesn't have a natural collation such as alphabetical for varchar, or chronological for date/time. And how would you write a query to identify features close to a given location - you can't SELECT * WHERE location BETWEEN 'Bristol' AND 'London'. So, ever since SQL Server 2008, there's been a whole range of methods (and indexes) specifically designed for doing these sorts of queries.

为什么需要专门的数据类型来处理此类信息?考虑一下你如何根据空间信息排序——它没有自然排序，比如varchar的字母排序，或者日期/时间的时间排序。你如何编写一个查询来识别靠近给定位置的特征——你不能选择“布里斯托尔”和“伦敦”之间的位置。因此，自从2008年SQL Server以来，已经有了一系列专门用于执行此类查询的方法(和索引)。

If you want practical examples of when they are used: an insurance company might use a geography field in a Customer table to record the location of every policyholder, and determine how many of them were likely to be affected by rising water levels by joining to a Rivers table which modelled river flood plains also in a geography field. Or, you might want to determine the optimal location for a new store by analysing the geographic coverage of your (and your competitors) existing outlets. Or, plan the optimal logistics route for a delivery vehicle, etc. etc.

如果你想实际的例子,当他们使用:保险公司可能使用一个地理领域客户表来记录每个投保人的位置,并确定有多少人可能受到不断上升的水位通过加入表也模仿河泛滥平原河流的地理领域。或者，您可能希望通过分析现有门店(以及您的竞争对手)的地理覆盖范围来确定新门店的最佳位置。或者，为运送车辆规划最优的物流路线等。

#2

Can you calculate the area of a polygon on the earth's surface using just varchars?

你能用光轮算出地球表面一个多边形的面积吗?

If you can I imagine that it's much more complex and less clear than Select @geog.STArea()

如果你能想象它比选择@geog.STArea()复杂得多，也不那么清晰

The use is for storing data in the appropriate type, just like the XML type is better than a varchar for XML data, etc.

这种方法用于以适当的类型存储数据，就像XML类型比XML数据的varchar更好一样。

#3

SQL Server supports two spatial data types: the geometry data type and the geography data type.

SQL Server支持两种空间数据类型:几何数据类型和地理数据类型。

The geometry type represents data in a Euclidean (flat) coordinate system. The geography type represents data in a round-earth coordinate system.

几何类型表示欧几里得(平面)坐标系中的数据。地理类型表示圆形地球坐标系中的数据。

We can find it easily in sql server 2008 and higher.

我们可以在sql server 2008和更高版本中轻松找到它。

create TABLE tab_spatial ( id int IDENTITY (1,1) primary key, zip nvarchar(20), city nvarchar(500), geo geography );

创建表tab_spatial (id int IDENTITY(1,1)主键、zip nvarchar(20)、city nvarchar(500)、geo geography);

More about spatial data type

更多关于空间数据类型的信息

#4

Ok i have an example for you. You have a table with 4 columns X1, Y1, X2, Y2. In this columns you have coordinates

我有个例子。有一个包含4列的表X1 Y1 X2 Y2。在这个列中有坐标。

x1=22.4109883
y1=44.740203
x2=22.4112528
y2=44.7404422

Now in this table you have also 3 columns declared as geometry: GEOSTART,GEOEND,GEOSEGMENT

在这个表格中，你还有3列被声明为几何学:GEOSTART,GEOEND,GEOSEGMENT

SET @sqlCommand = 'USE [' + @databaseName + '];' +
              ' UPDATE [dbo].[StreetsTable]' +
              ' SET [GEOSTART] = geometry::STGeomFromText(''POINT ('' + [X1] + '' '' + [Y1] + '')'', 4326) WHERE X1<>'''' and Y1<>'''' '; 
EXEC (@sqlCommand)


SET @sqlCommand = 'USE [' + @databaseName + '];' +
              ' UPDATE [dbo].[StreetsTable]' +
              ' SET [GEOEND] = geometry::STGeomFromText(''POINT ('' + [X2] + '' '' + [Y2] + '')'', 4326) WHERE X2<>'''' and Y2<>'''' ';
EXEC (@sqlCommand)

    SET @sqlCommand = 'USE [' + @databaseName + '];' +
                  ' UPDATE [dbo].[StreetsTable]' +
                  ' SET [GEOSEGMENT] = geometry::STGeomFromText(''LINESTRING('' + [X1] + '' '' + [Y1] + '', '' + [X2] + '' '' + [Y2] + '')'', 4326) WHERE X1<>'''' and Y1<>'''' and X2<>'''' and Y2<>'''' ';
EXEC (@sqlCommand)

After the update go and do a select on that table. Go on spatial results an select the spatial column. You will see the use of the geometry.

更新之后，在该表上执行select。进入空间结果，选择空间列。您将看到几何图形的使用。

Hope it helps you

希望它能帮助你

#1