sql-server-2008 테이블 - SQL Server 2008 geography 데이터 형식을 사용하는 이유는 무엇입니까?




디자인 변경 (4)

나는 고객 데이터베이스를 재 설계하고 표준 주소 필드 (거리, 도시 등)와 함께 저장하려는 새로운 정보 중 하나는 주소의 지리적 위치입니다. 내가 염두에 두어야 할 유일한 유스 케이스는 주소를 찾을 수없는 경우 사용자가 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 을 사용하지 못하게 막을 수 있습니까?

최신 정보

@ 에릭 필립스는 geography 근접 검색을 할 수있는 능력을 갖추 었습니다. 매우 시원합니다.

반면에, 위도와 경도를 얻는 간단한 selectgeography (아래의 세부 사항)를 사용할 때 상당히 느리다는 것을 빠른 테스트가 보여줍니다. , geography 에 관한 다른 질문에 대한 대답 에 대한 의견은 나에게 경의를 표한다.

@ 시애틀 오신 것을 환영합니다. sidenote는 nullable GEOGRAPHY 데이터 유형 컬럼에서 공간 인덱스를 사용하는 것을 매우 신중하게 고려해야합니다. 심각한 성능 문제가 있으므로 스키마를 리모델링해야하는 경우에도 GEOGRAPHY 컬럼을 null이 허용되지 않도록하십시오. - Tomas Jun 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)

마지막으로 다음 코드를 실행하면 내 컴퓨터에서 geography 사용할 때 위도와 경도를 선택하는 속도가 약 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

Answers

공간 계산을 할 계획이라면 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();
}

그렇다면 지리학을 사용하는 정당한 이유가 있습니다.

Entity Framework 내의 공간에 대한 설명입니다 .

고성능 공간 데이터베이스 생성으로 업데이트

내가 노엘 아브라함 (Noel Abrahams) 에 대해 지적한 바와 같이,

공간에 대한 메모는 각 좌표가 64 비트 (8 바이트) 길이의 배정 밀도 부동 소수점 숫자로 저장되며 8 바이트 이진 값은 십진수 정밀도 15 자리와 대략 동일하므로 10 진수 (9 , 6)은 단지 5 바이트이지만 정확하게 공정한 비교는 아닙니다. 실제 비교를 위해 십진수는 각 LatLong (총 18 바이트)에 대해 최소 십진수 (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'

결과:

name    rows    data     
Geo     20000   728 KB   
LatLon  20000   560 KB

지형 데이터 유형은 30 % 더 많은 공간을 차지합니다.

또한 지리 데이터 유형은 Point 저장에만 국한되지 않으며 LineString, CircularString, CompoundCurve, Polygon, CurvePolygon, GeometryCollection, MultiPoint, MultiLineString 및 MultiPolygon 등을 저장할 수도 있습니다. 포인트 (예 : LINESTRING (1, 2 2) 인스턴스)를 넘어 가장 단순한 지리 유형 (위도 / 경도)을 저장하려는 경우 각 점에 대한 추가 행, 각 점의 순서에 대한 정렬 열 줄을 그룹화하기위한 또 다른 열이 있습니다. 또한 SQL Server에는 Area, Boundary, Length, Distances 등을 계산하는 Geography 데이터 형식에 대한 메서드가 있습니다 .

Latitude와 Longitude를 Sql Server에 Decimal로 저장하는 것은 현명하지 못한 방법입니다.

업데이트 2

거리, 면적 등과 같은 계산을 할 계획이라면 지구의 표면을 적절히 계산하는 것은 어렵습니다. SQL Server에 저장된 각 지리 유형은 공간 참조 ID 와 함께 저장됩니다. 이 이드는 다른 구 (지구는 4326) 일 수 있습니다. 즉, SQL Server의 계산은 실제로 지구 표면을 통과 할 수있는 as-the-crow-flies 아닌 지구 표면에서 올바르게 계산됩니다.


    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

고려해야 할 또 다른 사항은 각 방법에 의해 채택 된 저장 공간입니다. 지리 유형은 VARBINARY(MAX) 로 저장됩니다. 이 스크립트를 실행 해보십시오 :

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

지형 데이터 유형은 거의 두 배의 공간을 차지합니다.


SQL Server에서 BLOB 에 대해 아래 데이터 유형을 사용할 수 있습니다.

Binary : 최대 8,000 바이트의 고정 크기.

VarBinary(n) : 최대 8,000 바이트의 가변 크기입니다 (n은 최대 크기를 지정합니다).

VarBinary(max) : 가변 크기, 2GB 제한.

BLOB이란 무엇입니까?





sql-server-2008 geolocation geocoding