SQL Server 2008 지역 데이터 형식을 사용하는 이유는 무엇입니까?

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

그렇지 않다하더라도 것을 I가없는 경우 훨씬 더, 왜 추가 복잡성을 복잡?

사용에 대한 아이디어를 포기하기 전에 geography고려해야 할 사항이 있습니까? 위도 및 경도 필드를 인덱싱하는 것보다 공간 인덱스를 사용하여 위치를 검색하는 것이 더 빠를까요? geography내가 알지 못하는 사용에 대한 이점 이 있습니까? 또는 반대로, 어느 것이 사용하지 못하게할지 알아야 할주의 사항이 geography있습니까?

---

최신 정보

@Erik Philips는를 사용하여 근접 검색을 수행 할 수있는 기능을 도입했습니다 geography.

다른 한편으로, 빠른 테스트는 select위도와 경도를 얻는 간단한 것이 사용할 때 상당히 느리다 는 것을 보여줍니다 geography(아래 세부 정보 참조). , 그리고 다른 SO 질문에 대한 대답 에 대한 의견 geography은 저를 걱정스럽게 만듭니다.

@SaphuA 천만에요. 참고로 nullable GEOGRAPHY 데이터 유형 열에서 공간 인덱스를 사용하는 데 매우주의해야합니다. 심각한 성능 문제가 있으므로 스키마를 리모델링해야하는 경우에도 GEOGRAPHY 열을 Null이 불가능하게 만드십시오. – Tomas 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

결과 :

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

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

더 놀라운 것은 행이 선택되지 않은 경우에도, 예를 들어 RowId = 2존재하지 않는 where를 선택하는 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();
}

그렇다면 지리를 사용해야하는 아주 좋은 이유가 있습니다.

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

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

내가 노엘 아브라함스 답변 에서 언급했듯이 :

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

결과:

name    rows    data     
Geo     20000   728 KB   
LatLon  20000   560 KB

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

Additionally the geography datatype is not limited to only storing a Point, you can also store LineString, CircularString, CompoundCurve, Polygon, CurvePolygon, GeometryCollection, MultiPoint, MultiLineString, and MultiPolygon and more. Any attempt to store even the simplest of Geography types (as Lat/Long) beyond a Point (for example LINESTRING(1 1, 2 2) instance) will incur additional rows for each point, a column for sequencing for the order of each point and another column for grouping of lines. SQL Server also has methods for the Geography data types which include calculating Area, Boundary, Length, Distances, and more.

It seems unwise to store Latitude and Longitude as Decimal in Sql Server.

Update 2

If you plan on doing any calculations like distance, area, etc, properly calculating these over the surface of the earth is difficult. Each Geography type stored in SQL Server is also stored with a Spatial Reference ID. These id's can be of different spheres (the earth is 4326). This means that the calculations in SQL Server will actually calculate correctly over the surface of the earth (instead of as-the-crow-flies which could be through the surface of the earth).

---

Another thing to consider is the storage space taken up by each method. The geography type is stored as a VARBINARY(MAX). Try running this script:

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'

Result:

name    rows    data     
Geo     20000   728 KB   
LatLon  20000   400 KB

The geography data-type takes up almost twice as much space.

---

    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: chip@cpearson.com
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

참고URL : https://stackoverflow.com/questions/7409051/why-use-the-sql-server-2008-geography-data-type