Hochschule für Technik und Wirtschaft

Hochschule für Technik und Wirtschaft
Fakultät Informatik / Mathematik
Prof. Dr. G. Gräfe
Lehrveranstaltung "Erweiterte Datenbanktechnologien / Medienarchive“
Aufgabenstellungen für die Übungen
Geo-Datenbanken mit SQL-Server 2008
10. Aufgabenkomplex: Verwaltung von Geometrien
In der von Ihnen erstellten Datenbank Verkauf sollen weiterhin spatiale Informationen
eines Handelsunternehmens verwaltet werden. Vom Unternehmen wurde Bauland zur
Errichtung eines neuen Auslieferungslagers erworben. Die für das Bauvorhaben
relevanten bzw. auf dieser Fläche vorhandenen und geplanten geografischen Objekte
sollen in der Tabelle GeoLager erfasst werden.
1.
Loggen Sie sich auf dem lokalen Datenbankserver (MS SQL Server 2008) ein,
rufen dort die Datenbank mit dem Namen „Verkauf“ auf und stellen den
Sicherungsmode der Datenbank ohne Protokollsicherung mit dem Befehl ein:
ALTER DATABASE Verkauf SET RECOVERY SIMPLE
2.
Erstellen Sie in der Datenbank folgende Tabelle zur Verwaltung von Geometrien
entsprechend der angegebenen Tabellendefinition.
GeoLager
Spaltenname
GeoID
Bezeich
Objekt
3.
Typ
int
varchar(15)
geometry
Bemerkung
Identy (1,1)
Fügen Sie in die Tabelle die folgenden Geometrien ein:
Bezeichnung
Grundstück
Strasse
Wald
See mit
Insel
Windrad
Lagerhalle
Eingang
Objektklasse
Polygon
LineString
Polygon
Polygon mit
Ausschnitt
Point
Polygon
Point
Koordinaten
0 0, 0 160, 80 210, 140 210, 140 150, 90 0
70 -10, 125 75, 160 85
80 150, 120 160, 160 220, 70 230
20 140, 60 160, 60 200, 30 220, 10 180;
40 170, 50 180, 30 190, 25 180
15 15
50 70, 80 90, 80 130, 20 130, 20 70
70 75
Bitte beachten Sie, dass die Linienzüge von Polygonen geschlossen sein müssen!
Lassen Sie sich anschließend die Ergebnisse sowohl in Tabellenform als auch
grafisch anzeigen!
Vergleichen Sie die Ausgabe mit der nachfolgenden Abbildung:
DBS3_Prakt3-WS11
1 // 4
4.
Führen Sie mit den Geometrien der Datenbank Verkauf die folgenden Abfragen
aus:
a. Berechnen Sie die Größe der Fläche und den Umfang des Grundstücks.
Notieren Sie sich den Wert des Flächeninhaltes.
b. Ermitteln Sie die Gesamtlänge der Strasse
c. Ermitteln Sie den Abstand des Windrades vom Eingangsbereich der
Lagerhalle.
d. Wie lang ist der Teil der Strasse, der das Grundstück schneidet?
e. Lassen Sie sich zunächst den Anteil der Waldfläche anzeigen, die zum
Grundstück gehört. Berechnen Sie anschließend die Größe der gesamten
Waldfläche sowie des zum Grundstück gehörenden Anteils am Wald.
f. Lassen Sie sich zunächst das Grundstück ohne den See (aber mit Inselanteil)
ausgeben und berechnen Sie anschließend die Grundstücksgröße ohne die
Fläche des Sees. Vergleichen Sie den ermittelten Wert mit dem Wert von 4a.
g. Um den See soll eine Pufferzone von 25 Meter eingehalten werden. Lassen
Sie sich die Pufferzone anzeigen. Geben Sie alle Objekte aus, die sich (ggf.
auch teilweise) in dieser Pufferzone befinden.
DBS3_Prakt3-WS11
2 // 4
Hochschule für Technik und Wirtschaft
Fakultät Informatik / Mathematik
Prof. Dr. G. Gräfe
Lehrveranstaltung "Erweiterte Datenbanktechnologien / Medienarchive“
Aufgabenstellungen für die Übungen
Geo-Datenbanken mit SQL-Server 2008
11. Aufgabenkomplex: Verwaltung von geografischen Informationen
In der Datenbank Verkauf sollen auch räumliche Abfragen über die einzelnen Shops
des Handelsunternehmens möglich sein. Dafür müssen die exakten geografischen
Koordinaten der Shops erfasst und spatiale Informationen zu den Ländern hinterlegt
werden
2.
Punktkoordinaten
1.
Löschen Sie in der Datenbank Verkauf zunächst die Datensätze der Tabelle Shop.
Entfernen Sie zuvor die Fremdschlüsselbeziehungen der Tabellen Mitarbeiter und
Bestand zu Shop.
2.
Erweitern Sie die Tabelle Shop um eine Spalte Coordinate mit dem Datentyp
geography.
Legen Sie in dieser Tabelle einen neuen Shop mit der ID 24 und der Bezeichnung
„Brutto“ an. Der Shop befindet sich auf der Reichenbachstr. 11, 01077 Dresden in
Sachsen (‚01‘).
Die aufgenommenen GPS-Koordinaten lauten: N 51 02.051 E 13 44.144.
3.
Kopieren Sie die in der Excel-Datei Shop_Koordinaten (Verzeichnis
„I:/prakt/graefe/DBS3“) erzeugten INSERT-Befehle (Spalte mit den Befehlen) in ihre
im SQL Server Management Studio geöffnete Scriptdatei und führen Sie die
INSERT-Befehle aus.
Lassen Sie sich die Shops von Sachsen mit allen Attributen, die Koordinaten
zusätzlich im WKT-Format, anzeigen.
4.
Ermitteln Sie den Abstand der Shops von der HTW. Der Standort der HTW wird mit
der GPS-Koordinate N51 02.263 E13 44.090 festgelegt. Die Anzeige soll
aufsteigend nach der Distanz erfolgen.
5.
Erweitern Sie die unter 4 erzeugte Anfrage und lassen sich zusätzlich noch den in
den jeweiligen Shops vorhandenen Bestand an Kammsteaks (Artnr 1110) anzeigen.
Lohnt sich der Weg zu einem der in Sachsen gelegenen Shops?
6.
Ermitteln Sie die Abstände zwischen allen Shops und lassen sich die Shop_IDs, die
Orte (von … zu …) aufsteigend nach der Entfernung anzeigen.
Flächen
7.
Erweitern Sie die Tabelle Geographie um eine Spalte Flaeche mit dem Datentyp
geography.
DBS3_Prakt3-WS11
3 // 4
8.
Kopieren Sie die SQL-Dateien
Bundeslaender_4326_DEU_import1.sql ,
Bundeslaender_4326_DEU_import2.sql (je nur 8 Zeilen) und
Bundeslaender_4326_AUT_CHE_import.sql
aus dem Verzeichnis „I:/prakt/graefe/DBS3“ in „C:/Temp“. Führen Sie die SQLSkripte in ihreim SQL Server Management Studio aus und fügen Sie die Flächen
der einzelnen Bundesländer durch Ausführen der Update-Skripte ein.
9.
Lassen Sie sich die Inhalte der Tabelle Geographie und die räumlichen Ergebnisse
für alle Länder und das Land Sachsen anzeigen.
Wie sieht die Definition der Flaeche im WKT-Format aus?
10. Ermitteln Sie den Flächeninhalt für alle deutschen Bundesländer und lassen sich
diesen absteigend nach der Größe anzeigen.
11. Ordnen Sie unter Nutzung spatialer Methoden die Shops den jeweiligen
Bundesländern zu.
Lassen Sie sich die Bundesländer und die jeweiligen Shops anzeigen. Machen Sie
die Punkte der Shops unter Nutzung der Methode STBuffer sichtbar.
12. Legen Sie je einen Spatial Index auf die räumlichen Attribute der Tabellen
Geographie und Shop.
13. Lassen Sie sich anzeigen, von welchen Bundesländern das Bundesland Hessen
begrenzt wird.
14. Erstellen Sie einen View, der den Bestandswert (Menge*VPreis für alle Artikel) aller
Shops eines Bundeslandes zusammengefasst darstellt.
Ermitteln Sie nun unter Nutzung dieses Views und des ermittelten Flächeninhaltes
pro Bundesland den Bestandswert pro km2. Lassen Sie sich das Bundesland, die
Region, die Fläche und den Bestandswert pro km2 anzeigen.
DBS3_Prakt3-WS11
4 // 4