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

Question

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

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

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

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

但是对于地理，我会这样做:

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

虽然它并没有那么复杂，但如果不需要的话为什么要增加复杂性呢？

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

<小时/>

更新

@Erik Philips 提出了利用地理进行邻近搜索的功能，这非常酷。

另一方面，快速测试表明，使用地理(详细信息如下)时，获取纬度和经度的简单select 速度明显变慢。 ，以及对 accepted answer 的评论关于地理的另一个问题让我感到怀疑:

@SaphuA You're welcome. As a sidenote be VERY carefull of using a spatial index on a nullable GEOGRAPHY datatype column. There are some serious performance issue, so make that GEOGRAPHY column non-nullable even if you have to remodel your schema. – Tomas Jun 18 at 11:18

总而言之，在权衡进行邻近搜索的可能性与性能和复杂性的权衡之后，我决定在本例中放弃使用地理。

<小时/>

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

我创建了两个表，一个使用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 倍。

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

结果:

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

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

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

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

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

Answer 1

如果您计划进行任何空间计算，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();
}

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

Explanation of spatial within Entity Framework。

使用 Creating High Performance Spatial Databases 更新

正如我在 Noel Abrahams Answer 上指出的:

A note on space, each coordinate is stored as a double-precision floating-point number that is 64 bits (8 bytes) long, and 8-byte binary value is roughly equivalent to 15 digits of decimal precision, so comparing a decimal(9,6) which is only 5 bytes, isn't exactly a fair comparison. Decimal would have to be a minimum of Decimal(15,12) (9 bytes) for each LatLong (total of 18 bytes) for a real comparison.

因此比较存储类型:

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'

结果:

name    rows    data     
Geo     20000   728 KB   
LatLon  20000   560 KB

地理数据类型占用的空间增加了 30%。

此外，地理数据类型不仅限于存储 Point，还可以存储 LineString, CircularString, CompoundCurve, Polygon, CurvePolygon, GeometryCollection, MultiPoint, MultiLineString, and MultiPolygon and more 。任何尝试存储超出点(例如 LINESTRING(1 1, 2 2) 实例)的最简单的地理类型(如纬度/经度)都会为每个点带来额外的行，以及用于对每个点的顺序进行排序的列另一列用于对行进行分组。 SQL Server 还具有用于地理数据类型的方法，其中包括计算 Area, Boundary, Length, Distances, and more 。

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

更新2

如果您打算进行距离、面积等任何计算，那么在地球表面上正确计算这些内容是很困难的。 SQL Server 中存储的每个地理类型也存储有 Spatial Reference ID 。这些 id 可以属于不同的球体(地球是 4326)。这意味着 SQL Server 中的计算实际上将在地球表面上正确计算(而不是可能穿过地球表面的 as-the-crow-flies)。