Dlaczego warto używać typu danych SQL Server 2008?
Przeprojektowuję bazę klientów i jedną z nowych informacji, które chciałbym przechowywać wraz ze standardowymi polami adresowymi (Ulica, Miasto itp.) jest geograficzną lokalizacją adresu. Jedynym przypadkiem użycia, który mam na myśli, jest umożliwienie użytkownikom mapowania współrzędnych na Mapach Google, gdy adresu nie można znaleźć inaczej, co często zdarza się, gdy obszar jest nowo opracowany lub znajduje się w odległej/wiejskiej lokalizacji.
Moim pierwszym zainteresowaniem było przechowywanie szerokości geograficznej i długość jako wartości dziesiętne, ale potem przypomniałem sobie, że SQL Server 2008 R2 ma typ danych geography
. Nie mam absolutnie żadnego doświadczenia w używaniu geography
, a z moich wstępnych badań wynika, że jest to przesada dla mojego scenariusza.
Na przykład, aby pracować z szerokością i długością geograficzną zapisaną jako decimal(7,4)
, mogę to zrobić:
insert into Geotest(Latitude, Longitude) values (47.6475, -122.1393)
select Latitude, Longitude from Geotest
Ale z geography
, zrobiłbym to:
insert into Geotest(Geolocation) values (geography::Point(47.6475, -122.1393, 4326))
select Geolocation.Lat, Geolocation.Long from Geotest
Chociaż to nie jestto znacznie bardziej skomplikowane, po co dodawać złożoności, jeśli nie muszę?
Przed Porzucam pomysł użycia geography
, czy jest coś, co powinienem rozważyć? Czy szybsze byłoby wyszukiwanie lokalizacji za pomocą indeksu przestrzennego zamiast indeksowania pól szerokości i długości geograficznej? Czy są jakieś korzyści z używania geography
, o których Nie wiem? A z drugiej strony, czy są zastrzeżenia, o których powinienem wiedzieć, które zniechęcają mnie do używania geography
?
Update
@Erik Philips wspomniał o możliwości wyszukiwania bliskości z geography
, co jest bardzo fajne.
Z drugiej strony, Szybki test pokazuje, że proste select
uzyskanie szerokości i długości geograficznej jest znacznie wolniejsze przy użyciu geography
(szczegóły poniżej). , a komentarz do zaakceptowanej odpowiedzi na inne tak pytanie na geography
mnie leery:
@SaphuA nie ma za co. Jako boczna Uwaga być bardzo ostrożnym z wykorzystaniem indeks przestrzenny w kolumnie nullable GEOGRAPHY datatype. Są pewne poważny problem z wydajnością, więc zrób ten artykuł non-nullable nawet jeśli trzeba przebudować swój schemat. - Tomas Jun 18 at 11: 18W sumie, biorąc pod uwagę prawdopodobieństwo wyszukiwania bliskości a kompromis w wydajności i złożoności, zdecydowałem się zrezygnować z używania {7]} w tym przypadku.
Szczegóły testu, który przeprowadziłem:
Stworzyłem dwie tabele, jedna za pomocą geography
, a druga za pomocą decimal(9,6)
dla szerokości i długości geograficznej:
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)
)
I wstawił jeden wiersz używając tej samej szerokości geograficznej i wartości długości geograficznej w każdej tabeli:
insert into GeographyTest(Location) values (geography::Point(47.6475, -122.1393, 4326))
insert into LatLongTest(Latitude, Longitude) values (47.6475, -122.1393)
Wreszcie, uruchomienie poniższego kodu pokazuje, że na moim komputerze wybór szerokości i długości geograficznej jest około 5 razy wolniejszy przy użyciu 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
Wyniki:
GeographyDuration LatLongDuration
----------------- ---------------
5146 1020
5143 1016
5169 1030
AvgGeographyDuration AvgLatLongDuration
-------------------- ------------------
5152 1022
Bardziej zaskakujące jest to, że nawet gdy nie zaznaczono wierszy, na przykład wybierając miejsce RowId = 2
, które nie istnieje, geography
nadal było wolniejsze:
GeographyDuration LatLongDuration
----------------- ---------------
1607 948
1610 946
1607 947
AvgGeographyDuration AvgLatLongDuration
-------------------- ------------------
1608 947
3 answers
Jeśli planujesz wykonać dowolne obliczenia przestrzenne, EF 5.0 pozwala na wyrażenia LINQ takie jak:
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();
}
Więc jest bardzo dobry powód, aby używać geografii.
Wyjaśnienie przestrzenności wewnątrz struktury podmiotu .
Zaktualizowany o Tworzenie wysokowydajnych przestrzennych Baz Danych
Jak zauważyłam na Noel Abrahams Odpowiedz :
Uwaga na przestrzeni, każda współrzędna jest przechowywana jako Liczba zmiennoprzecinkowa o podwójnej precyzji, która wynosi 64 bity (8 bajtów) długa i 8-bajtowa wartość binarna jest w przybliżeniu równa 15 cyfrom dokładności dziesiętnej, więc porównanie dziesiętnego (9,6), który wynosi tylko 5 bajtów, nie jest dokładnie sprawiedliwym porównaniem. Decimal musiałby wynosić minimum Decimal (15,12) (9 bajtów) dla każdego LatLong (łącznie 18 bajtów) dla rzeczywistego porównania.
Więc porównywanie typów pamięci:
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'
Wynik:
name rows data
Geo 20000 728 KB
LatLon 20000 560 KB
Dane geograficzne zajmują o 30% więcej miejsca.
Dodatkowo typ danych geograficznych nie jest ograniczenie do przechowywania tylko punktu, można również przechowywać LineString, CircularString, CompoundCurve, Polygon, CurvePolygon, GeometryCollection, MultiPoint, MultiLineString i MultiPolygon i więcej. Każda próba przechowywania nawet najprostszych typów geograficznych (jako Lat/Long) poza punktem (na przykład LineString (1 1, 2 2) instancja) spowoduje powstanie dodatkowych wierszy dla każdego punktu, kolumny do uporządkowania dla porządku każdego punktu i innej kolumny do grupowania wierszy. SQL Server posiada również metody dla typów danych geograficznych, które obejmują obliczanie obszaru, granicy, długości, odległości i innych .
Nierozsądne wydaje się przechowywanie szerokości i długości geograficznej jako dziesiętnej w Sql Server.
Update 2
Jeśli planujesz wykonać jakiekolwiek obliczenia, takie jak odległość, powierzchnia itp., prawidłowe obliczenie ich na powierzchni Ziemi jest trudne. Każdy typ geografii przechowywany w SQL Server jest również przechowywany z identyfikatorem odniesienia Przestrzennego. Te identyfikatory mogą być różnych sfer (ziemia jest 4326). Oznacza to, że obliczenia w SQL Server będą właściwie obliczane na powierzchni ziemi (zamiast as-the-crow-flies, które mogą być przez powierzchnię ziemi).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:18:14
Kolejną rzeczą do rozważenia jest przestrzeń dyskowa zajmowana przez każdą metodę. Typ geografii jest zapisywany jako VARBINARY(MAX)
. Spróbuj uruchomić ten skrypt:
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'
Wynik:
name rows data
Geo 20000 728 KB
LatLon 20000 400 KB
Dane geograficzne zajmują prawie dwa razy więcej miejsca.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-06-08 14:59:29
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-29 12:59:39