고객 데이터베이스를 재 설계하고 있으며 표준 주소 필드 (거리, 도시 등)와 함께 저장하려는 새로운 정보 중 하나는 주소의 지리적 위치입니다. 내가 염두에 둔 유일한 사용 사례는 주소를 찾을 수 없을 때 사용자가 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 % 더 많은 공간을 차지합니다.
또한 지리 데이터 유형은 Point 저장에만 국한되지 않고 LineString, CircularString, CompoundCurve, Polygon, CurvePolygon, GeometryCollection, MultiPoint, MultiLineString 및 MultiPolygon 등을 저장할 수도 있습니다 . 포인트 (예 : LINESTRING (1 1, 2 2) 인스턴스)를 넘어서 가장 단순한 지리 유형 (위도 / 경도)을 저장하려는 시도는 각 포인트에 대한 추가 행, 각 포인트의 순서에 대한 열을 생성합니다. 라인 그룹화를위한 또 다른 열. SQL Server에는 면적, 경계, 길이, 거리 등의 계산을 포함하는 지리 데이터 유형에 대한 메서드도 있습니다 .
위도와 경도를 SQL Server에 Decimal로 저장하는 것은 현명하지 않은 것 같습니다.
업데이트 2
거리, 면적 등과 같은 계산을 할 계획이라면 지표면에서이를 올바르게 계산하기가 어렵습니다. SQL Server에 저장된 각 지리 유형도 공간 참조 ID 와 함께 저장됩니다 . 이 ID는 다른 구체 일 수 있습니다 (지구는 4326). 즉, SQL Server의 계산이 실제로 지구 표면을 통과 할 수있는 까마귀처럼 지구 표면에서 올바르게 계산됩니다 .
답변
고려해야 할 또 다른 사항은 각 방법이 차지하는 저장 공간입니다. 지리 유형은 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지리 데이터 유형은 거의 두 배의 공간을 차지합니다.
답변
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