4.4 Abfragen mit Geodaten

Klemens Konopasek
SQL Server 2008 R2
Der schnelle Einstieg
Abfragen, Transact-SQL,
Entwicklung und Verwaltung
An imprint of Pearson Education
München • Boston • San Francisco • Harlow, England
Don Mills, Ontario • Sydney • Mexico City
Madrid • Amsterdam
SQL-Anweisungen verwenden
GE
Geschirr
HH
Haushalt
HW
Heimwerken
KG
Küchengeschirr
PC
Computer
SP
Spielwaren
(10 Zeile(n) betroffen)
Diese Tabelle soll mit den Inhalten der Tabelle xGruppenMerge1 abgeglichen werden.
Deren Inhalt unterscheidet sich von dem der Tabelle tblArtikelGruppen in drei Einträgen:
ArtGr ArtGrText
----- -----------------------BE
Besteck
BR
Blu-ray Discs
BU
Bücher und Zeitschriften
...
(10 Zeile(n) betroffen)
Es gibt eine weitere Artikelgruppe Blu-ray Discs mit dem Kürzel BR, die Bezeichnung der
Artikelgruppe BU lautet statt Bücher nun Bücher und Zeitschriften. Die Artikelgruppe Spielwaren (SP) ist nicht mehr enthalten. Ziel ist es, die beiden Tabellen derart abzugleichen,
dass die Inhalte der Tabelle tblArtikelGruppen exakt jenen der Tabelle xGruppenMerge1 entsprechen. Dazu waren bisher folgende drei Einzelanweisungen erforderlich:
Um die in der Tabelle xGruppenMerge1 enthaltenen Änderungen an bestehenden Datensätzen zu übernehmen, wird eine UPDATE-Anweisung verwendet.
UPDATE g
SET g.ArtGrText = m.ArtGrText
FROM dbo.tblArtikelGruppen g
INNER JOIN dbo.xGruppenMerge1 m ON g.ArtGr = m.ArtGr
WHERE g.ArtGrtext != m.ArtGrText;
In dieser Anweisung werden die zwei Tabellen über das Artikelgruppenkürzel miteinander verknüpft. Dort wo sich die Bezeichnung unterscheidet, erfolgt ein Update. Nach
dem Ausführen dieser Anweisung finden wir in der Tabelle tblArtikelGruppen die aktualisierte Bezeichnung Bücher und Zeitschriften vor.
Neue Einträge werden mit der nachfolgenden INSERT-Anweisung eingebaut. Hierbei ist ein
OUTER JOIN nötig, um jene Datensätze zu finden, die in der Tabelle bisher nicht vorhanden
waren.
INSERT INTO dbo.tblArtikelGruppen (ArtGr, ArtGrText)
SELECT m.ArtGr, m.ArtGrText
FROM dbo.tblArtikelGruppen g
RIGHT OUTER JOIN dbo.xGruppenMerge1 m ON g.ArtGr = m.ArtGr
WHERE g.ArtGr IS NULL;
Nach dem Ausführen dieser Anweisung, ist auch die Artikelgruppe Blu-ray Discs in der
Zieltabelle zu finden.
187
Kapitel 4 SQL – Zugriff auf Daten
Schließlich müssen noch nicht mehr benötigte Artikelgruppen, die in der Abgleichtabelle
nicht mehr vorhanden sind, aus der Originaltabelle gelöscht werden. Die einfachste Variante hierfür ist die folgende Anweisung:
DELETE FROM dbo.tblArtikelGruppen
WHERE ArtGr NOT IN( SELECT ArtGr
FROM dbo.xGruppenMerge1);
Nach dem Ausführen der letzten Anweisung fehlt nun auch die Artikelgruppe Spielwaren; die beiden Tabellen sind nun synchronisiert.
Mit der MERGE-Anweisung gelingt dies alles in einer einzigen Anweisung, die für mein
Dafürhalten auch einfacher und weniger komplex ist als die beschriebenen Anweisungen in Summe. Die nachfolgende Tabelle beschreibt die Bestandteile von MERGE.
Bestandteil
Beschreibung
MERGE
MERGE gibt die Tabelle an, in der die Daten abgeglichen werden
sollen. Dies ist das Ziel des Schreibvorgangs.
USING
USING definiert die Herkunftstabelle für die Synchronisation.
Zusätzlich enthält dieser Teil eine Bedingung, wie die Datensätze
der beiden Tabellen zusammenzufügen sind. Diese entspricht in
ihrer Syntax einer JOIN-Bedingung.
MATCHED
MATCHED legt fest, was geschieht, wenn eine Übereinstimmung
festgestellt wird. Üblicherweise erfolgt dann ein UPDATE.
NOT MATCHED
NOT MATCHED legt fest, was geschieht, falls keine Übereinstimmung
ermittelt wurde.
NOT MATCHED BY TARGET Dies definiert genauer, auf welcher Seite etwas fehlt. BY TARGET
legt fest, dass etwas gefunden worden ist, was in der Zieltabelle
nicht vorhanden ist. Typischerweise folgt daraufhin ein INSERT in
diese Tabelle.
NOT MATCHED BY SOURCE Im Gegensatz zur vorhergehenden Variante wird hier festgelegt,
was geschieht, wenn etwas in der Ursprungstabelle für den
Abgleich nicht mehr vorhanden ist. In der Regel werden die
Datensätze aus der Zieltabelle gelöscht.
OUTPUT
Hiermit kann optional eine Information über die betroffenen
Datensätze ausgegeben werden.
Tabelle 4.2: Bestandteile einer MERGE-Anweisung
Auch für die MERGE-Anweisung gelten natürlich die Regeln der referenziellen
Integrität. Daher habe ich im Beispiel für das Löschen die Artikelgruppe
Spielwaren vorgesehen. Dieser Gruppe ist kein Artikel zugeordnet und kann
daher problemlos gelöscht werden.
Die komplette MERGE-Anweisung zur Synchronisation der Tabellen tblArtikelGruppen und
xGruppenMerge1 hat folgende Syntax:
188
SQL-Anweisungen verwenden
MERGE dbo.tblArtikelGruppen g
USING dbo.xGruppenMerge1 m ON g.ArtGr = m.ArtGr
WHEN MATCHED AND (g.ArtGrText != m.ArtGrText) THEN
UPDATE SET g.ArtgrText = m.ArtGrText
WHEN NOT MATCHED BY TARGET THEN
INSERT (ArtGr, ArtGrText)
VALUES(m.ArtGr, m.ArtGrText)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Ganz wichtig ist es, eine MERGE-Anweisung mit einem Semikolon abzuschließen. Bei den klassischen SQL-Anweisungen ist dieses beim SQL Server zwar
optional, bei der MERGE- Anweisung allerdings nicht. Das Fehlen des Semikolons führt zu einem Fehler.
Meldung 10713, Ebene 15, Status 1, Zeile 9
Eine MERGE-Anweisung muss durch ein Semikolon (;) abgeschlossen werden.
In der ersten Zeile (MERGE) wird die Zieltabelle angegeben, in der die Änderungen vorgenommen werden sollen. In der zweiten Zeile (USING) wird die Ursprungstabelle für die
Änderungen festgelegt. Wie bei einem JOIN macht es Sinn, diesen Tabellen kurze Tabellen-Aliasnamen zu geben. Hinter dem Schlüsselwort ON wird wie bei einem JOIN festgelegt, wie die beiden Tabellen zusammenzufügen sind. In unserem Beispiel über die
Spalte ArtGr der beiden Tabellen.
MERGE dbo.tblArtikelGruppen g
USING dbo.xGruppenMerge1 m ON g.ArtGr = m.ArtGr
Existiert eine Übereinstimmung und unterscheidet sich die Artikelgruppenbezeichnung,
so soll der Text abgeglichen werden. Ohne die Zusatzbedingung, dass die Bezeichnungen
unterschiedlich sein sollen, würden alle Artikelpreise – auch die unveränderten – erneut
überschrieben. Dies würde einen unnötigen Aufwand bedeuten. Die folgenden Bedingungen werden immer mit dem Schlüsselwort WHEN eingeleitet.
WHEN MATCHED AND (g.ArtGrText != m.ArtGrText) THEN
UPDATE SET g.ArtgrText = m.ArtGrText
Kommt eine Artikelgruppe in der Zieltabelle nicht vor, wird sie eingefügt. Die Anweisung dazu ist fast mit der Syntax einer normalen INSERT-Anweisung identisch. Lediglich
der Name der Zieltabelle muss hier nicht mehr angegeben werden.
WHEN NOT MATCHED BY TARGET THEN
INSERT (ArtGr, ArtGrText)
VALUES(m.ArtGr, m.ArtGrText)
Nicht mehr in der Ursprungstabelle enthaltene Datensätze sollen gelöscht werden. Dafür
genügt es, das Schlüsselwort DELETE anzugeben.
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
189
Kapitel 4 SQL – Zugriff auf Daten
Wenn Sie diese Anweisung ausführen, werden die beiden Tabellen synchronisiert. Sie
erzielen dasselbe Ergebnis wie mit den drei zuvor erläuterten separaten Anweisungen.
Die Tabelle xGruppenMerge2 enthält dieselben Daten wie die Tabelle tblArtikelGruppen zu Beginn. Sie müssen also nur in der Anweisung die Tabelle
xGruppenMerge1 durch xGruppenMerge2 ersetzen, um wieder den Ausgangszustand herzustellen. Anschließend können Sie den Vorgang beim Testen
wiederholen.
Anstatt nur der DML-Standardmeldung (X Zeile(n) betroffen) kann mit MERGE auch ein
detailliertes Ergebnis ausgegeben werden. Dazu muss am Ende der Anweisung OUTPUT
ergänzt werden. Über $action wird die Anweisungsart abgerufen: über die Tabellen
inserted die neuen und über deleted die alten Daten. Diese beiden internen Tabellen sind
ähnlich wie Trigger zu sehen. (Lesen Sie dazu Kapitel 6.)
MERGE dbo.tblArtikelGruppen g
USING dbo.xGruppenMerge1 m ON g.ArtGr = m.ArtGr
WHEN MATCHED AND (g.ArtGrText != m.ArtGrText) THEN
UPDATE SET g.ArtgrText = m.ArtGrText
WHEN NOT MATCHED BY TARGET THEN
INSERT (ArtGr, ArtGrText)
VALUES(m.ArtGr, m.ArtGrText)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action, inserted.*, deleted.*;
liefert:
$action
---------INSERT
UPDATE
DELETE
ArtGr
----BR
BU
NULL
ArtGrText
-------------------------Blu-ray Discs
Bücher und Zeitschriften
NULL
ArtGr
----NULL
BU
SP
ArtGrText
-------------------NULL
Bücher
Spielwaren
(3 Zeile(n) betroffen)
Bei INSERT sehen wir logischerweise nur die neuen Werte, bei UPDATE die neuen sowie die
alten. Im Falle von DELETE könne nur die alten Werte angezeigt werden.
Zusammenfassend ist die MERGE-Anweisung den drei separat ausgeführten
Anweisungen nicht nur hinsichtlich ihrer Leistung überlegen, auch die Syntax ist wesentlich weniger komplex, da man sich um keine OUTER JOINBefehle oder Ähnliches kümmern muss.
4.3.4
Den Abfrage-Designer im Abfrageeditor einsetzen
Nachdem wir aus ausführlich mit den verschiedenen Varianten zur Erstellung von Abfragen und SQL-Anweisungen beschäftigt haben, möchte ich Sie nun noch auf die Integration
von Abfrageeditor und Abfrage-Designer aufmerksam machen. Direkt aus dem Abfrage-
190
SQL-Anweisungen verwenden
editor heraus kann der Abfrage-Designer aufgerufen werden, um beim Schreiben von SQLAnweisungen zu helfen.
Um den Abfrage-Designer zu nutzen, wählen Sie im Abfrageeditor im Kontextmenü den
Befehl ABFRAGE IN EDITOR ENTWERFEN... aus. Der Abfrage-Designer öffnet sich in einem
neuen Fenster und kann zum Generieren von SQL-Anweisungen verwendet werden.
Beim Schließen des Designers wird die generierte Anweisung in das Abfrageeditor-Fenster übernommen. Um eine bereits vorhandene Anweisung im Designer zu bearbeiten,
markieren Sie diese, bevor Sie den Designer über das Kontextmenü öffnen. Sie wird dann
übernommen und dargestellt.
Abbildung 4.30: Abfrage-Designer im Abfrageeditor einsetzen
Anders als bei Sichten können wie bei Abfragen mit dem Designer auf diesem Weg auch
DML-Anweisungen generiert werden. Ändern Sie dazu den Abfragetyp über das Kontextmenü. Die nachfolgende Grafik zeigt eine mit dem Designer erstellte UPDATE-Anweisung.
191
Kapitel 4 SQL – Zugriff auf Daten
Wenn Sie sich nach diesem SQL-Überblick detaillierter mit SQL beschäftigen
möchten, so empfehle ich Ihnen zum Beispiel das Buch Relationale Datenbanken
und Standard-SQL von Günter Matthiesen und Michael Unterstein, ISBN 978-38273-2656-0, das ebenfalls im Verlag Addison-Wesley erschienen ist.
Abbildung 4.31: UPDATE-Anweisung im Abfrage-Designer
4.4
Abfragen mit Geodaten
Als tolle Neuerung in der Version 2008 bringt der SQL Server die Unterstützung von Geodaten mit. Damit sind Sie mit dem SQL Server nun in der
Lage, ihre Daten auch grafisch sichtbar zu machen.
Ich möchte Ihnen in diesem Abschnitt zeigen, wie man in SQL-Anweisungen mit diesem
Thema umgeht. Dazu müssen wir natürlich auch einiges über das Thema Geodaten an sich
erfahren. Typischerweise verwendet man dies direkt in SQL-Anweisungen im Abfrageeditor und nicht mit Hilfe des Abfrage-Designers. In Kombination mit Systemen wie zum Beispiel Virtual Earth können diese Daten auch optisch ansprechend ausgegeben werden.
In Kapitel 3 habe ich bereits die beiden neuen Datentypen geography und geometry erwähnt.
Diese können verwendet werden, um Positionen auf der Erde über ihre Koordinaten festzulegen. Worin bestehen die Unterschiede zwischen diesen beiden Datentypen?
왘 Der Datentyp geography verwendet ein geodätisches Modell, welches Längen- und
Breitengrade für die Beschreibung der Position verwendet. Dabei wird die Krümmung der Erde berücksichtigt.
192
Abfragen mit Geodaten
왘 Der Datentyp geometry verwendet ein Modell mit einer ebenen Fläche. Positionen
werden über ihre Koordinaten bestimmt. Da die Koordinaten hier nicht auf die Ausprägung der Erdkoordinaten beschränkt sind und der Bereich frei gewählt werden
kann, kann der Datentyp auch für andere 2D-Modelle verwendet werden. Zum Beispiel können die Grundfläche eines Bürogebäudes abgebildet und die Plätze der Mitarbeiter darin über deren Koordinaten festgelegt werden.
Wenn man sicht ernsthaft und tiefer mit dem Thema beschäftigt, sollte man
sich auch ein wenig mit der Theorie von Geodaten beschäftigen. Hierbei gibt
es verschiedene internationale Standards, die auch bei der Implementierung
von Microsoft berücksichtigt wurden. Ich habe in diesem Abschnitt für Sie
ein paar kleine und überschaubare Beispiele vorbereitet, die einen einfachen Einstieg
in die Thematik ermöglichen sollen.
4.4.1
Typen im Geodatenmodell
Beim Einsatz von Geodaten werden unterschiedliche Typen verwendet und miteinander
in Beziehung gebracht. Diese sind vom Open Geospatial Consortium (OGC) definiert
und festgelegt worden.
왘 Punkte: Punkte sind der einfachste Typ. Sie werden durch einen Längen- und Breiten-
grad bei geography oder über eine X- und eine Y-Koordinate bei geometry definiert. So ist
zum Beispiel der Hauptplatz meiner Heimatstadt Graz durch die Angabe 47,07109°
nördlicher Breite und 15,43811° östlicher Länge bestimmt.
왘 Linien: Linien werden durch mindestens zwei Punkte definiert. Werden mehr als zwei
Punkte verwendet, muss eine Linie keine Gerade mehr sein.
왘 Gebiete: Mindestens drei unterschiedliche Punkte sind notwendig, um ein Gebiet zu
definieren.
Inhalt der Geo-Typen definieren
Bei der Definition der Typen werden die englischen Begriffe benutzt. So wird für »Punkt«
der Begriff Point verwendet, für »Linie« Line und für »Gebiet« wird Polygon benutzt.
Um einen Typ festzulegen wird meist der sogenannte Well-Known Text (WKT) verwendet.
Dieser String enthält die Koordinateninformationen in einer bestimmten Syntax. Betrachten wir die Syntax für Point, Line und Polygon:
왘 Point: POINT(28 55) bestimmt den Punkt mit den Koodinaten 28 auf der X-Achse und
55 auf der Y-Achse. Bei geography würde dies 28° östlicher Länge und 55° nördlicher
Breite entsprechen.
왘 Line: Mittels LINESTRING(25 35,30 40,40 65)wird eine Linie, die aus drei Punkten besteht,
festgelegt. Jeder dieser drei Punkte wird über die zwei Koordinaten analog zu Point festgelegt. Die Punkte werden mit Komma voneinander getrennt.
193
Kapitel 4 SQL – Zugriff auf Daten
왘 Polygon: Ein Polygon wird über POLYGON((40 50,50 60,60 50,40 50)) aus mindestens
drei Punkten gebildet. Im Beispiel sehen Sie vier Punkte, da ein Polygon immer
dadurch geschlossen werden muss, dass Start- und Endpunkt übereinstimmen. Daher
wird der Startpunkt am Ende nochmals angegeben. Die Punkte des Polygons werden
zudem in Klammern gesetzt.
Zum Visualisieren dieser Typen nutzen wir das Management Studio. Denn dieses ist in
der Lage, Geodaten auch grafisch darzustellen. Davor müssen wir aus dem Well-Known
Text SQL Server-interne Daten umwandeln. Das realisieren wir mit einer der Methoden
STPointFromText, STLineFromText und STPolyFromText. Zur Darstellung eines Punktes
verwenden wir folgende Anweisung:
Geometry::STPointFromText('POINT(28 55)', 0)
Um das Ergebnis anzuzeigen, setzen wir ein SELECT davor und führen die Anweisung aus:
SELECT 'Point' As Typ, Geometry::STPointFromText('POINT(28 55)', 0) As Darstellung;
Wir erhalten:
Abbildung 4.32: Point aus WKT
Das an sich ist noch nicht sehr spannend. Wie Sie in der Abbildung 4.32 sehen, taucht im
Ergebnisbereich des Management Studios das neues Register RÄUMLICHE ERGEBNISSE
auf. Klicken Sie auf dieses Register, wird Ihnen das Ergebnis in einem Koordinatenraster
angezigt. Dies ist bei einem einzelnen Punkt, der dazu noch genau auf dem Raster zu liegen kommt und zudem kaum sichtbar ist, nicht wirklich beeindruckend.
Abbildung 4.33: Räumliches Ergebnis
194
Abfragen mit Geodaten
Etwas mehr bekommen wir zu sehen, wenn wir dasselbe mit einer Linie durchführen.
SELECT 'Line' As Typ,
Geometry::STLineFromText('LINESTRING(25 35, 30 40, 40 65)', 0) As Darstellung;
Hier macht sicht nun auch die Dummy-Spalte mit dem Aliasnamen Typ bemerkbar.
Denn bei den räumlichen Ergebnissen ist beim Punkt die Option BEZEICHNUNGSSPALTE
AUSWÄHLEN noch grau gewesen. Bei einer Linie oder einem Polygon kann diese aber
aktiviert und damit eine Beschriftung in der Darstellung ergänzt werden. Der Text Linie
aus der von mir gewählten Spalte Typ erscheint neben der Linie in der Grafik. Dies wird
für spätere Beispiele noch von Bedeutung sein.
Abbildung 4.34: Linie aus LINESTRING
Zum Abschluss schauen wir uns noch das Polygon an. Es ist aus mindestens drei Punkten zu definieren, zusätzlich müssen der Anfangs- und der Endpunkt übereinstimmen.
SELECT 'Polygon' AS Typ,
Geometry::STPolyFromText('POLYGON((40 50, 50 60, 60 50, 40 50))', 0) As Darstellung;
Abbildung 4.35: Polygon
195
Kapitel 4 SQL – Zugriff auf Daten
Alle drei Typen gibt es auch in einer Multi-Ausformung:
왘 Multipoint: Ein Objekt besteht aus mehreren Punkten. Dies ist durchaus mit einem
Polygon vergleichbar, aber ohne die Verbindungslinien und ohne die Fläche.
왘 Multiline: Mehrere Linien bilden ein Objekt. Die Linien können sich schneiden, an
einem oder zwei Enden aneinanderstoßen und Ähnliches. Sämtliche denkbaren Varianten sind hier möglich.
왘 Multipolygon: Mehrere Polygone, die einander auch schneiden dürfen, sofern geo-
metry verwendet wird. Bei geography ist dies nicht möglich.
Betrachten wir alle sechs Varianten gemeinsam. Um sie in einem Ergebnis anzuzeigen,
müssen wir die einzelnen Anweisungen mit UNION zu einem Gesamtergebnis zusammenfügen.
SELECT 'Point' As Typ,
Geometry::STPointFromText('POINT(28 55)', 0) As Darstellung
UNION ALL
SELECT 'Line',
Geometry::STLineFromText('LINESTRING(25 35, 30 40, 40 65)', 0)
UNION ALL
SELECT 'Polygon',
Geometry::STPolyFromText('POLYGON((40 50, 50 60, 60 50, 40 50))', 0)
UNION ALL
SELECT 'Multipoint',
Geometry::STMPointFromText('MULTIPOINT(35 35, 36 35, 37 36)', 0)
UNION ALL
SELECT 'Multiline',
Geometry::STMLineFromText('MULTILINESTRING((45 65, 56 66),
(47 66, 58 68))', 0)
UNION ALL
SELECT 'Multipolygon',
Geometry::STMPolyFromText('MULTIPOLYGON(((45 42, 49 46, 52 40, 45 42),
(45 35, 49 39, 52 32, 47 31, 45 35)))', 0);
Was ist beim Schreiben der WKT-Variante für die Multi-Typen zu berücksichtigen?
Bei Multipoint werden die einzelnen Punkte durch Kommata voneinander getrennt. Bei
Multiline werden die einzelnen Linien so wie die einzelnen Polygone bei Multipolygonen jeweils in runde Klammern gesetzt.
Achten Sie bitte darauf, dass die Methoden der Multi-Varianten sich gegenüber den Einzel-Varianten durch das M an dritter Stelle unterscheiden.
Da leider die Bezeichnungsspalte bei diesem Ergebnis nicht ausgewählt werden kann
– weil Point und Multipoint dies nicht unterstützen – habe ich in der letzten Grafik die
Beschriftungen manuell ergänzt und auch die Punkte etwas vergrößert, damit diese besser zu erkennen sind.
196
Copyright
Daten, Texte, Design und Grafiken dieses eBooks, sowie die eventuell angebotenen
eBook-Zusatzdaten sind urheberrechtlich geschützt. Dieses eBook stellen wir
lediglich als persönliche Einzelplatz-Lizenz zur Verfügung!
Jede andere Verwendung dieses eBooks oder zugehöriger Materialien und
Informationen, einschliesslich
•
der Reproduktion,
•
der Weitergabe,
•
des Weitervertriebs,
•
der Platzierung im Internet,
in Intranets, in Extranets,
•
der Veränderung,
•
des Weiterverkaufs
•
und der Veröffentlichung
bedarf der schriftlichen Genehmigung des Verlags.
Insbesondere ist die Entfernung oder Änderung des vom Verlag vergebenen
Passwortschutzes ausdrücklich untersagt!
Bei Fragen zu diesem Thema wenden Sie sich bitte an: [email protected]
Zusatzdaten
Möglicherweise liegt dem gedruckten Buch eine CD-ROM mit Zusatzdaten bei. Die
Zurverfügungstellung dieser Daten auf unseren Websites ist eine freiwillige Leistung
des Verlags. Der Rechtsweg ist ausgeschlossen.
Hinweis
Dieses und viele weitere eBooks können Sie rund um die Uhr
und legal auf unserer Website
http://www.informit.de
herunterladen