[解決済み] なぜSQL Server 2008の地理データ型を使用するのか？

質問

私は顧客データベースを再設計しており、標準的な住所フィールド (通り、市など) と共に保存したい新しい情報の 1 つは、住所の地理的な位置情報です。 私が考えている唯一の使用例は、住所が見つからない場合、ユーザーが Google マップで座標をマッピングできるようにすることです。

最初に思いついたのは、緯度と経度を 10 進値で保存することでした。しかし、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 ?

更新情報

Erik Philips は、近接検索を行うために geography を使った近接検索の機能を紹介しました。

一方、簡単なテストでは、単純な select を使って緯度と経度を取得した場合、著しく遅くなることがわかります。 geography (を使用した場合、著しく遅くなります（詳細は後述）。 に関するコメント、および 受付回答 の別のSOの質問に対するコメント geography は私を警戒しています。

SaphuA どういたしまして。余談ですが 空間インデックスの使用には非常に注意が必要です。パフォーマンス上 そのGEOGRAPHYカラムを非NULLにすることです。 スキーマを作り直す必要があったとしても。- Tomas Jun 18 at 11:18

全体として、近接検索を行う可能性とパフォーマンスおよび複雑さのトレードオフを比較検討した結果、私は geography を使用しないことにしました。

実行したテストの詳細です。

2つのテーブルを作成し、1つは 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)
) 

で、各テーブルに同じ緯度と経度の値を使って1行ずつ挿入しました。

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

最後に、以下のコードを実行すると、私のマシンでは、緯度と経度を選択する際に 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

どのように解決するのですか？

空間計算を行う場合、EF5.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();
}

それから、Geographyを使うのには非常に良い理由があります。

Entity Framework内の空間についての説明 .

を更新しました。 高性能な空間データベースの構築

で述べたように ノエル・エイブラハムズ 回答 :

スペースに関する注意点として、各座標は 64 ビット (8 バイト) 長の倍精度浮動小数点数として保存され、8 バイトの 2 進値は 10 進法の 15 桁精度とほぼ同等なので、わずか 5 バイトの decimal(9,6) を比較することは必ずしも公正な比較とは言えません。10 進法では、各緯度経度に対して最低でも Decimal(15,12) (9 バイト) (合計 18 バイト) でなければ、本当の比較にはならないでしょう。

つまり、ストレージの種類を比較すると

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%多くスペースを取ります。

さらに、geography データ型は Point を格納することだけに限定されません。 LineString, CircularString, CompoundCurve, Polygon, CurvePolygon, GeometryCollection, MultiPoint, MultiLineString, and MultiPolygon などのデータも格納することができます。 . 最も単純な地理タイプ（緯度/経度）であっても、ポイント（例えば、LINESTRING(1 1, 2 2) インスタンス）を超えて保存しようとすると、各ポイントの行、各ポイントの順序のためのシーケンス用の列、および線のグループ化用の列が追加で発生することになります。 SQL Serverには、Geographyデータ型に対するメソッドも用意されており、次のような計算が可能です。 面積、境界線、長さ、距離、その他 .

Sql Serverで緯度と経度をDecimalで保存することは賢明ではないように思えます。

更新2

距離や面積などの計算を行う予定がある場合、地球の表面上でこれらを適切に計算することは困難です。 SQL Server に保存されている各 Geography タイプは、それぞれ 空間参照 ID . これらの ID は異なる球体のものである可能性があります (地球は 4326 です)。 これは、SQL Server での計算が、実際には地球の表面上で正しく計算されることを意味します (代わりに as-the-crow-flies の代わりに、地球の表面を通して計算されることになります。）