webentwicklung-frage-antwort-db.com.de

Gründe für die Verwendung des Geografiedatentyps SQL Server 2008

Ich gestalte eine Kundendatenbank neu und eine der neuen Informationen, die ich zusammen mit den Standardadressfeldern (Straße, Stadt usw.) speichern möchte, ist der geografische Standort der Adresse. Der einzige Anwendungsfall, den ich im Auge habe, besteht darin, den Benutzern zu ermöglichen, die Koordinaten auf Google Maps abzubilden, wenn die Adresse ansonsten nicht gefunden werden kann, was häufig der Fall ist, wenn das Gebiet neu erschlossen wurde oder sich an einem abgelegenen/ländlichen Ort befindet.

Meine erste Neigung bestand darin, Breiten- und Längengrade als Dezimalwerte zu speichern, aber dann fiel mir ein, dass SQL Server 2008 R2 den Datentyp geography hat. Ich habe absolut keine Erfahrung mit geography, und nach meinen ersten Recherchen scheint es für mein Szenario übertrieben zu sein.

Um beispielsweise mit Breiten- und Längengraden zu arbeiten, die als decimal(7,4) gespeichert sind, kann Folgendes ausgeführt werden:

insert into Geotest(Latitude, Longitude) values (47.6475, -122.1393)
select Latitude, Longitude from Geotest

aber mit geography würde ich das machen:

insert into Geotest(Geolocation) values (geography::Point(47.6475, -122.1393, 4326))
select Geolocation.Lat, Geolocation.Long from Geotest

Obwohl es nicht viel komplizierter ist , warum Komplexität hinzufügen, wenn ich es nicht muss?

Bevor ich auf die Idee, geography zu verwenden, verzichte, gibt es etwas, das ich beachten sollte? Wäre es schneller, einen Ort mit einem räumlichen Index zu suchen, als die Felder für Breite und Länge zu indizieren? Gibt es Vorteile bei der Verwendung von geography, die mir nicht bekannt sind? Oder gibt es auf der anderen Seite Vorbehalte, die mich davon abhalten würden, geography zu verwenden?


Aktualisieren

@Erik Philips hat die Möglichkeit zur Annäherungssuche mit geography erwähnt, was sehr cool ist.

Andererseits zeigt ein schneller Test, dass ein einfaches select, um den Breiten- und Längengrad zu erhalten, bei Verwendung von geography erheblich langsamer ist (Details unten). , und ein Kommentar zu akzeptierte Antwort zu einer anderen SO Frage zu geography hat mich misstrauisch gemacht:

@ SaphuA Gern geschehen. Seien Sie SEHR vorsichtig, wenn Sie einen räumlichen Index für eine nullfähige GEOGRAPHY-Datentypspalte verwenden. Es gibt einige schwerwiegende Leistungsprobleme. Machen Sie diese GEOGRAPHY-Spalte daher nicht ungültig, auch wenn Sie Ihr Schema neu gestalten müssen. - Tomas 18. Juni um 11:18 Uhr

Alles in allem habe ich mich entschlossen, in diesem Fall auf die Verwendung von geography zu verzichten, um die Wahrscheinlichkeit von Proximity-Suchen im Vergleich zum Kompromiss zwischen Leistung und Komplexität abzuwägen.


Details des Tests, den ich durchgeführt habe:

Ich habe zwei Tabellen erstellt, eine mit geography und eine andere mit decimal(9,6) für Breite und Länge:

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)
) 

und fügte eine einzelne Zeile mit den gleichen Breiten- und Längenwerten in jede Tabelle ein:

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

Schließlich zeigt die Ausführung des folgenden Codes, dass die Auswahl des Breiten- und Längengrads auf meinem Computer bei Verwendung von geography ungefähr fünfmal langsamer ist.

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

Ergebnisse:

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

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

Überraschender ist, dass geography auch dann langsamer war, wenn keine Zeilen ausgewählt wurden, z. B. wenn ausgewählt wurde, wo RowId = 2 Nicht vorhanden ist:

GeographyDuration LatLongDuration
----------------- ---------------
1607              948
1610              946
1607              947

AvgGeographyDuration AvgLatLongDuration
-------------------- ------------------
1608                 947
104
Jeff Ogata

Wenn Sie vorhaben, räumliche Berechnungen durchzuführen, ermöglicht EF 5.0 LINQ-Ausdrücke wie:

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();
}

Dann gibt es einen sehr guten Grund, Geographie zu verwenden.

Erklärung des Raums innerhalb von Entity Framework .

Aktualisiert mit Erstellen von räumlichen Hochleistungsdatenbanken

Wie ich auf Noel Abrahams Antwort bemerkt habe:

Als Anmerkung zum Leerzeichen wird jede Koordinate als Gleitkommazahl mit doppelter Genauigkeit und einer Länge von 64 Bit (8 Byte) gespeichert. Ein 8-Byte-Binärwert entspricht ungefähr 15 Stellen mit Dezimalgenauigkeit, sodass eine Dezimalzahl (9) verglichen wird , 6) was nur 5 Bytes ist, ist nicht gerade ein fairer Vergleich. Für einen echten Vergleich müsste die Dezimalzahl für jede LatLong (insgesamt 18 Byte) mindestens 15,12 (9 Byte) betragen.

So vergleichen Sie Speichertypen:

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'

Ergebnis:

name    rows    data     
Geo     20000   728 KB   
LatLon  20000   560 KB

Der Geografie-Datentyp belegt 30% mehr Speicherplatz.

Darüber hinaus ist der Geografie-Datentyp nicht nur auf das Speichern eines Punkts beschränkt. Sie können auch LineString, CircularString, CompoundCurve, Polygon, CurvePolygon, GeometryCollection, MultiPoint, MultiLineString und MultiPolygon und mehr speichern. Jeder Versuch, auch die einfachsten Geografietypen (als Lat/Long) über einen Punkt hinaus zu speichern (z. B. LINESTRING (1 1, 2 2)), führt zu zusätzlichen Zeilen für jeden Punkt, einer Spalte für die Sequenzierung in der Reihenfolge der einzelnen Punkte und eine weitere Spalte zum Gruppieren von Zeilen. SQL Server verfügt auch über Methoden für die Geografie-Datentypen, einschließlich der Berechnung von Fläche, Grenze, Länge, Entfernungen und mehr .

Es scheint unklug, Breite und Länge als Dezimalzahl in SQL Server zu speichern.

Update 2

Wenn Sie vorhaben, Berechnungen wie Entfernung, Fläche usw. durchzuführen, ist es schwierig, diese über der Erdoberfläche richtig zu berechnen. Jeder in SQL Server gespeicherte Geografietyp wird auch mit einer Raumbezugs-ID gespeichert. Diese IDs können aus verschiedenen Sphären bestehen (die Erde ist 4326). Dies bedeutet, dass die Berechnungen in SQL Server tatsächlich korrekt über der Erdoberfläche berechnet werden (anstelle von Luftlinie , die durch die Erdoberfläche verlaufen könnte). .

enter image description here

65
Erik Philips

Eine andere zu berücksichtigende Sache ist der Speicherplatz, der von jeder Methode beansprucht wird. Der Geografietyp wird als VARBINARY(MAX) gespeichert. Versuchen Sie, dieses Skript auszuführen:

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'

Ergebnis:

name    rows    data     
Geo     20000   728 KB   
LatLon  20000   400 KB

Der Geografie-Datentyp nimmt fast doppelt so viel Platz ein.

6
Noel Abrahams