Зачем использовать тип географии данных 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

Хотя это не намного сложнее, зачем добавлять сложность, если мне не нужно?

Прежде чем отказаться от идеи использования geography, есть ли что-нибудь, что я должен рассмотреть? Будет ли быстрее искать местоположение с использованием пространственного индекса или индексации полей широты и долготы? Есть ли преимущества в использовании geography, о которых я не знаю? Или, с другой стороны, есть ли оговорки, которые я должен знать о том, что препятствует мне использовать geography?

Update

@Erik Philips продемонстрировала возможность проводить поиск по близости с помощью geography, что очень круто.

С другой стороны, быстрый тест показывает, что простой select для получения широты и долготы значительно медленнее при использовании geography (подробнее см. ниже)., и комментарий к принятому отвечу на другой вопрос SO на geography имеет меня leery:

@SaphuA Добро пожаловать. В качестве опоры следует ОЧЕНЬ осторожно использовать пространственный индекс в столбце типа данных с нулевым значением GEOGRAPHY. Есть некоторые серьезная проблема с производительностью, поэтому сделайте столбец GEOGRAPHY непустым даже если вам нужно перестроить схему. - Tomas 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

Результаты:

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

Ответы

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();
}

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

CREATE TABLE dbo.Geo
(
    geo geography

)

GO

CREATE TABLE dbo.LatLon
(
    lat decimal(9, 6)
,   lon decimal(9, 6)

)

GO

INSERT dbo.Geo
SELECT geography::Point(36.204824, 138.252924, 4326) UNION ALL
SELECT geography::Point(51.5220066, -0.0717512, 4326) 

GO 10000

INSERT dbo.LatLon
SELECT  36.204824, 138.252924 UNION
SELECT 51.5220066, -0.0717512

GO 10000

EXEC sp_spaceused 'dbo.Geo'
EXEC sp_spaceused 'dbo.LatLon'

name    rows    data     
Geo     20000   728 KB   
LatLon  20000   400 KB

    CREATE FUNCTION [dbo].[fn_GreatCircleDistance]
(@Latitude1 As Decimal(38, 19), @Longitude1 As Decimal(38, 19), 
            @Latitude2 As Decimal(38, 19), @Longitude2 As Decimal(38, 19), 
            @ValuesAsDecimalDegrees As bit = 1, 
            @ResultAsMiles As bit = 0)
RETURNS decimal(38,19)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar  decimal(38,19)

    -- Add the T-SQL statements to compute the return value here
/*
Credit for conversion algorithm to Chip Pearson
Web Page: www.cpearson.com/excel/latlong.aspx
Email: [email protected]
Phone: (816) 214-6957 USA Central Time (-6:00 UTC)
Between 9:00 AM and 7:00 PM

Ported to Transact SQL by Paul Burrows BCIS
*/
DECLARE  @C_RADIUS_EARTH_KM As Decimal(38, 19)
SET @C_RADIUS_EARTH_KM = 6370.97327862
DECLARE  @C_RADIUS_EARTH_MI As Decimal(38, 19)
SET @C_RADIUS_EARTH_MI = 3958.73926185
DECLARE  @C_PI As Decimal(38, 19)
SET @C_PI =  pi()

DECLARE @Lat1 As Decimal(38, 19)
DECLARE @Lat2 As Decimal(38, 19)
DECLARE @Long1 As Decimal(38, 19)
DECLARE @Long2 As Decimal(38, 19)
DECLARE @X As bigint
DECLARE @Delta As Decimal(38, 19)

If @ValuesAsDecimalDegrees = 1 
Begin
    set @X = 1
END
Else
Begin
    set @X = 24
End 

-- convert to decimal degrees
set @Lat1 = @Latitude1 * @X
set @Long1 = @Longitude1 * @X
set @Lat2 = @Latitude2 * @X
set @Long2 = @Longitude2 * @X

-- convert to radians: radians = (degrees/180) * PI
set @Lat1 = (@Lat1 / 180) * @C_PI
set @Lat2 = (@Lat2 / 180) * @C_PI
set @Long1 = (@Long1 / 180) * @C_PI
set @Long2 = (@Long2 / 180) * @C_PI

-- get the central spherical angle
set @Delta = ((2 * ASin(Sqrt((power(Sin((@Lat1 - @Lat2) / 2) ,2)) + 
    Cos(@Lat1) * Cos(@Lat2) * (power(Sin((@Long1 - @Long2) / 2) ,2))))))

If @ResultAsMiles = 1 
Begin
    set @ResultVar = @Delta * @C_RADIUS_EARTH_MI
End
Else
Begin
    set @ResultVar = @Delta * @C_RADIUS_EARTH_KM
End

    -- Return the result of the function
    RETURN @ResultVar

END