为什么使用 SQL Server 2008 地理数据类型？

我正在重新设计客户数据库，我想要与标准地址字段（街道、城市等）一起存储的新信息之一是地址的地理位置。我想到的唯一用例是，当无法找到地址时，允许用户在 Google 地图上绘制坐标，这种情况通常发生在该地区是新开发的或位于偏远/农村地区时。

我的第一个倾向是将纬度和经度存储为十进制值，但后来我想起 SQL Server 2008 R2 有一个geography数据类型。我完全没有使用经验geography，从我最初的研究来看，这对于我的场景来说似乎有点过分了。

例如，要使用存储为的纬度和经度decimal(7,4)， 我可以做这个：

insert into Geotest(Latitude, Longitude) values (47.6475, -122.1393)
select Latitude, Longitude from Geotest

但与geography，我会这样做：

insert into Geotest(Geolocation) values (geography::Point(47.6475, -122.1393, 4326))
select Geolocation.Lat, Geolocation.Long from Geotest

虽然不是that更复杂，如果不需要的话为什么要增加复杂性呢？

在我放弃使用的想法之前geography，有什么我应该考虑的吗？使用空间索引搜索位置是否比索引纬度和经度字段更快？使用有什么优点吗geography我不知道吗？或者，另一方面，是否有一些我应该知道的警告，这些警告会阻止我使用geography?

Update

@Erik Philips 提出了进行邻近搜索的能力geography，这非常酷。

另一方面，快速测试表明，一个简单的select使用时获取纬度和经度明显较慢geography（详情如下）。 ，以及对接受的答案 https://stackoverflow.com/questions/6386577/sql-server-2008-r2-geography-distance/6387558#6387558到另一个SO问题geography让我怀疑：

@SaphuA 不客气。作为旁注，使用时要非常小心 可为空的 GEOGRAPHY 数据类型列上的空间索引。有一些 严重的性能问题，因此请使 GEOGRAPHY 列不可为空 即使您必须重塑您的架构。 – 托马斯 6 月 18 日 11:18

总而言之，在权衡进行邻近搜索的可能性与性能和复杂性的权衡之后，我决定放弃使用geography在这种情况下。

我运行的测试的详细信息：

我创建了两张表，一张使用geography另一个使用decimal(9,6)对于纬度和经度：

CREATE TABLE [dbo].[GeographyTest]
(
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [Location] [geography] NOT NULL,
    CONSTRAINT [PK_GeographyTest] PRIMARY KEY CLUSTERED ( [RowId] ASC )
) 

CREATE TABLE [dbo].[LatLongTest]
(
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [Latitude] [decimal](9, 6) NULL,
    [Longitude] [decimal](9, 6) NULL,
    CONSTRAINT [PK_LatLongTest] PRIMARY KEY CLUSTERED ([RowId] ASC)
) 

并使用相同的纬度和经度值在每个表中插入一行：

insert into GeographyTest(Location) values (geography::Point(47.6475, -122.1393, 4326))
insert into LatLongTest(Latitude, Longitude) values (47.6475, -122.1393)

最后，运行以下代码表明，在我的机器上，使用时选择纬度和经度大约慢 5 倍geography.

declare @lat float, @long float,
        @d datetime2, @repCount int, @trialCount int, 
        @geographyDuration int, @latlongDuration int,
        @trials int = 3, @reps int = 100000

create table #results 
(
    GeographyDuration int,
    LatLongDuration int
)

set @trialCount = 0

while @trialCount < @trials
begin

    set @repCount = 0
    set @d = sysdatetime()

    while @repCount < @reps
    begin
        select @lat = Location.Lat,  @long = Location.Long from GeographyTest where RowId = 1
        set @repCount = @repCount + 1
    end

    set @geographyDuration = datediff(ms, @d, sysdatetime())

    set @repCount = 0
    set @d = sysdatetime()

    while @repCount < @reps
    begin
        select @lat = Latitude,  @long = Longitude from LatLongTest where RowId = 1
        set @repCount = @repCount + 1
    end

    set @latlongDuration = datediff(ms, @d, sysdatetime())

    insert into #results values(@geographyDuration, @latlongDuration)

    set @trialCount = @trialCount + 1

end

select * 
from #results

select avg(GeographyDuration) as AvgGeographyDuration, avg(LatLongDuration) as AvgLatLongDuration
from #results

drop table #results

Results:

GeographyDuration LatLongDuration
----------------- ---------------
5146              1020
5143              1016
5169              1030

AvgGeographyDuration AvgLatLongDuration
-------------------- ------------------
5152                 1022

更令人惊讶的是，即使没有选择任何行，例如选择 whereRowId = 2，不存在，geography仍然较慢：

GeographyDuration LatLongDuration
----------------- ---------------
1607              948
1610              946
1607              947

AvgGeographyDuration AvgLatLongDuration
-------------------- ------------------
1608                 947

如果您计划进行任何空间计算，EF 5.0 允许使用 LINQ 表达式，例如：

private Facility GetNearestFacilityToJobsite(DbGeography jobsite)
{   
    var q1 = from f in context.Facilities            
             let distance = f.Geocode.Distance(jobsite)
             where distance < 500 * 1609.344     
             orderby distance 
             select f;   
    return q1.FirstOrDefault();
}

那么使用地理就有一个很好的理由。

实体框架内空间的解释 http://msdn.microsoft.com/en-us/data/hh859721.aspx.

更新为创建高性能空间数据库 http://sqlbits.com/Sessions/Event5/creating_high_performance_spatial_databases

正如我所指出的诺埃尔·亚伯拉罕的回答 https://stackoverflow.com/a/10951302/209259:

注意空间，每个坐标都存储为 64 位（8 字节）长的双精度浮点数，8 字节二进制值大致相当于 15 位十进制精度，因此比较十进制（9 ,6) 只有 5 个字节，这并不是一个公平的比较。对于每个 LatLong（总共 18 个字节），Decimal 必须至少为 Decimal(15,12)（9 个字节）才能进行实际比较。

因此比较存储类型：

CREATE TABLE dbo.Geo
(    
geo geography
)
GO

CREATE TABLE dbo.LatLng
(    
    lat decimal(15, 12),   
    lng decimal(15, 12)
)
GO

INSERT dbo.Geo
SELECT geography::Point(12.3456789012345, 12.3456789012345, 4326) 
UNION ALL
SELECT geography::Point(87.6543210987654, 87.6543210987654, 4326) 

GO 10000

INSERT dbo.LatLng
SELECT  12.3456789012345, 12.3456789012345 
UNION
SELECT 87.6543210987654, 87.6543210987654

GO 10000

EXEC sp_spaceused 'dbo.Geo'

EXEC sp_spaceused 'dbo.LatLng'

Result:

name    rows    data     
Geo     20000   728 KB   
LatLon  20000   560 KB

地理数据类型多占用 30% 的空间。

另外，地理数据类型不仅限于存储点，还可以存储LineString、CircularString、CompoundCurve、Polygon、CurvePolygon、GeometryCollection、MultiPoint、MultiLineString 和 MultiPolygon 等 http://technet.microsoft.com/en-us/library/bb933790.aspx。任何尝试存储超出点（例如 LINESTRING(1 1, 2 2) 实例）的最简单的地理类型（如纬度/经度）都会为每个点带来额外的行，以及用于对每个点的顺序进行排序的列另一列用于对行进行分组。 SQL Server 还具有用于地理数据类型的方法，其中包括计算面积、边界、长度、距离等 http://technet.microsoft.com/en-us/library/bb933917.aspx.

在 Sql Server 中将纬度和经度存储为十进制似乎是不明智的。

Update 2

如果您打算进行距离、面积等任何计算，那么在地球表面上正确计算这些内容是很困难的。 SQL Server 中存储的每个地理类型也存储有一个空间参考 ID http://en.wikipedia.org/wiki/SRID。这些 id 可以属于不同的球体（地球是 4326）。这意味着 SQL Server 中的计算实际上将在地球表面上正确计算（而不是乌鸦飞翔 http://en.wikipedia.org/wiki/As_the_crow_flies可能穿过地球表面）。