Datenbanken und GIS

1
Datenbanken und GIS
Carsten Busch
Überblick (i) GIS und DB
Geschichte, Vor- und Nachteile von DatenbankManagement-Systemen (DBMS)
Aufbau und Architektur von DBMS
Datenbanktheorie
– Datenmodellierung
– Normalformenlehre
– Nutzung von CASE-Tools (Computer Aided Software
Engineering)
SQL
– Erzeugen neuer Datenbanken (Überblick)
– Änderung von Dateninhalten (Überblick)
– Datenabfrage
Verknüpfung von DBMS und CAD/GIS
2
3
Überblick (ii) GIS und DB
OpenGIS Spezifikation (Simple Feature
Definition for SQL)
PostGIS / PostgreSQL
– Speicherung von Geometriedaten (ESRI Formate)
– Raumbezogene Abfragen (spatial SQL)
Kartenserver
– Layoutdefinition
– Visualisierung der Daten aus PostGIS/ESRI
Formate)
4
CAD/GIS und Datenbanken
Trends
– Gruppenorientierte Entwicklungsarbeit,
gleichzeitige Bearbeitung einer
Zeichnung/GIS-Daten durch mehrere
Anwender
– Bearbeitung einer Zeichnung/GIS-Daten
an unterschiedlichen Standorten
– Übergang vom Datei- zum
Datenbanksystem
Produkte
– Oracle
• Geodatenserver für ORACLE unter Nutzung
der Spatial Cartridge
– ESRI (ARC View/ARC Info)
• Spatial Database Engine (SDE)
– Geotask
• Geotask Server für DB2, Oracle
– PostgreSQL (open source)
• Erweiterung PostGIS
• Geotools.org
• Sourceforge.org
5
Historische Entwicklung der Datenbanken
6
1. Generation (Zeitraum: fünfziger Jahre)
– Bearbeitung von Daten (rechnen, zählen) im Batch Betrieb, jedes Programm
mit eigenen Datenbestand, nur sequentiellen Zugriff, Datenträger:
Lochkarten oder Magnetbänder
2. Generation (Zeitraum: sechziger Jahre)
– Dialogbetrieb und Direktzugriff möglich, Magnetplatten, Dateisysteme
3. Generation (Zeitraum: 1965-1975)
– Unterscheidung zwischen logischen und physischen Informationen,
Datenmodelle: hierarchisches Modell, Netzwerkmodell
4. Generation (Zeitraum: 1975 bis heute )
– Klare Trennung zwischen logischen und physikalischen Datenmodellen,
Übergang von satz- zu mengenorientierter Verarbeitung der Daten, Theorie
relationaler Datenbanken
5. Generation (Zeitraum: 1990 bis heute)
–
Objektorientierte Datenbanken, Grundlage: Objektorientierte
Programmierung
Grundlegende Anforderungen an
Datenbanken
Redundanzfreiheit
Vielfachverwendbarkeit
Unabhängigkeit
Konsistenz
Persistenz
Strukturflexibilität
Benutzerfreundlichkeit
Organisation des Mehrbenutzerbetriebs
Leistungssteuerung
Hohe Verfügbarkeit
7
Anwendungsprogramme im
Zentrum
Dateien 1
Dateien 2
AP1
AP2
Lohn
Konstruktion
AP3
Bestellwesen
Dateien 3
8
9
Vorteile:
geringe Anfangsinvestitionen
keine Vernetzung notwendig
für kleine DV-Anwendungen
ausreichend
physische Datenverwaltung kann selbst
geplant werden
Nachteile:
Unkontrollierte Datenredundanz
Inkonsistenzen, fehlende Integrität
Geringe Datensicherheit /
Zuverlässigkeit
Mehraufwand für Erfassung und
Wartung
Größerer Speicherbedarf
Schlechter Datenschutz
Datenabhängigkeit
10
11
Daten im Zentrum
AP1
AP2
Lohn
Konstruktion
Datenbank-Management-System
(DBMS)
Dateien 1 Dateien 2
Dateien 3
AP3
Bestellwesen
12
Vorteile:
Vermeidung unkontrollierter Datenredundanz
Datenunabhängigkeit
Datenintegrität
Datensicherheit (Recovery)
Schutz der Daten vor unberechtigtem Zugriff
geringerer Aufwand bei der Datenverwaltung
kurze Entwicklungszeit der
Anwendungsprogramme
Nachteile:
Hohe Anfangsinvestitionen
DBMS oft sehr komplex
speziell geschultes Personal notwendig
Abhängigkeit von zentralen Funktionen
Bereitstellung des DBMS = Vernetzung
Übergang vom Dateisystem zum DBMS
verursacht oft hohe Kosten
13
Aufbau eines DBMS
Anwendungsprogramme/Datenbankanfragen
Datenbank-Management-System
Primärdaten
Sekundärdaten
14
15
Anwendungen von DBMS (i)
Client-Server Architektur
Anwendungen von DBMS (ii)
Applikationsserver-Architektur
16
Datenfluss/-verteilung
Services:
Geotiff
ECW
SRID
WMS
…
raster/grid data
Shape
DXF
Geo-SQL
DGN
…
vector data
Open GeoSpatial SQL (Geo-SQL)
Web Map Service (WMS)
Web Feature Service (WFS)
Web Coverage Service (WCS)
ArcGIS
Geomedia
Erdas
Grass
…
17
Desktop GIS
I-Explorer
gdlib, tiff, png,…
PostgreSQL
GEOS, GDAL
Apache
PostGIS
Mapserver
PHP
…
Unix/Linux
Windows XP + Cygwin
GIS Database Management Server
Mozilla
Firefox
Opera
…
Internet/Intranet
Tourist Info
GPS NAV
Env. Info
City Maps
…
CD-based-Information
Beispiel UMN / PostGIS Mehrschichten
2
1
3
18
SQL
19
Sprache zur Abfrage- und Verwaltung von RDBMS
SQL = Structured Query Language
aus System R von IBM hervorgegangen
1986 Verabschiedung des ersten
Standards SQL-86
1987 von ISO fast komplett
übernommen
1992 SQL-(9)2
200x SQL-3
20
Vorteile von SQL
auf Bearbeitung relationaler
Datenbanken zugeschnitten
alle wesentlichen Datenbankaktionen
werden durch SQL ausgeführt
"nur" Einarbeitung in Zusatztools
SQL-92 Standard in vielen Produkten
implementiert
21
Nachteile von SQL
Sprache ist nicht vollständig
oft herstellereigene SQL-Erweiterungen
gültige SQL-Standard bleibt hinter
gegenwärtigen Nutzeranforderungen
zurück
22
Einordnung von SQL
Sprache der vierten Generation (4-GL)
3.GL
open(buecher)
while(not eof(buecher))
{
read(buch)
if(buch.leihfrist>0)
print(buch.autor,buch.titel)
}
close(buecher)
4.GL
select autor,titel
from buecher
where leihfrist>0
23
SQL Befehlsklassen
Datendefinition
create table|view
Datengewinnung
select from where ...
Datenmodifikation
insert, update, delete
Datenschutz
grant, revoke
Datensicherheit
commit, rollback
Datenorganisation
create tablespace ...
24
Erstellen einer Datenbank
CREATE DATABASE name
CREATE TABLE tabellenname(
spalte_1
typ_1
[NOT NULL],
spalte_2
typ_2
[NOT NULL],
spalte_n
typ_n
[NOT NULL])
NULL, spezieller Datentyp
– Achtung: ""<>NULL<>0
...
25
Ändern/Löschen einer Datenbank
Änderungen
– ALTER DATABASE name
– ALTER TABLE name
Löschen
– DROP DATABASE name
– DROP TABLE tabellenname
26
Eingabe/Änderung der Daten
INSERT INTO
buecher(buch_nr,autor,titel) VALUES
(1,'Goethe','Kabale und Liebe')
UPDATE buecher SET autor = 'Schiller'
WHERE buch_nr = 1
DELETE FROM buecher WHERE
autor = 'Goethe'
27
SELECT-Befehl (1)
wichtigster Befehl von SQL
Datenabfrage
Änderungsbefehle (INSERT, UPDATE,
DELETE) nutzen SELECT-Befehl
ebenfalls
Realisierung von Zeilenselektion,
Spaltenauswahl und Tabellenverbund
28
SELECT-Befehl (2)
Tabelle 1
Tabelle 2
SELECT
Ergebnistabelle
Tabelle 3
29
Einfache Abfragen mit SELECT
Projektion von Tabellenspalten
ORDER BY
Selektion von Tabellenzeilen (WHEREKlausel)
AND, OR, NOT, BETWEEN
30
Projektion
SELECT Spaltenauswahl FROM
Tabellenname(n)
SELECT * FROM leser
SELECT
leser_nr,name,wohnort,ausleihzahl,anmelded
atum FROM leser
SELECT autor,titel FROM buecher
31
DISTINCT
Tabellen enthalten in SQL
Multimengen!
Unterdrückung von gleichen Zeilen mit
DISTINCT
SELECT DISTINCT autor FROM
buecher
32
Sortierung von Ergebnissen
ORDER BY
– SELECT autor,titel FROM buecher ORDER
BY autor ASC
– SELECT * FROM buecher ORDER BY autor
DESC
– SELECT autor,titel,ausleihzahl FROM
buecher ORDER BY autor,ausleihzahl
– SELECT autor,titel,ausleihzahl FROM
buecher ORDER BY 1,3
33
Selektion (WHERE-Klausel)
SELECT * FROM buecher WHERE
ausleihzahl=0
SELECT * FROM buecher WHERE
ausleihzahl=0 AND leihfrist<>0
SELECT * FROM buecher WHERE
ausleihzahl=0 AND NOT leihfrist=0
34
Logische Operatoren
AND, OR, NOT
– autor="Goethe" OR (autor="Schiller" AND
NOT leihfrist=0)
– autor="Goethe" OR autor="Schiller" AND
NOT leihfrist=0
– Prioritäten der Operatoren
– KLAMMERUNG!!
– de Morgansche Regeln
35
Alternative für den OR-Operator
IN
– SELECT * FROM buecher WHERE
autor="Goethe" OR author="Schiller"
– SELECT * FROM buecher WHERE autor
IN ("Goethe", "Schiller")
36
Alternative für den AND-Operator
BETWEEN
– SELECT * FROM buecher WHERE
leihfrist>=1 AND leihfrist<=14
– SELECT * FROM buecher WHERE leihfrist
BETWEEN 1 AND 14
37
Platzhalter für Strings
LIKE
– SELECT * FROM buecher WHERE autor
LIKE "A%"
– "%" beliebig viele Zeichen, "_" genau ein
Zeichen
– reguläre Ausdrücke können nicht
verwendet werden z.B. a(b)*a
38
Arithmetik
Grundrechenoperationen +,-,*,/
SELECT
einnahmen,ausgaben,einnahmenausgaben FROM finanzen
39
Skalare Funktionen
numerische Funktionen
– ABS(),COS(),ROUND(),FLOOR(),...
Datumsfunktionen
– GETDATE(),DATEADD(),DATENAME(),..
.
Zeichenkettenfunktionen
– LOWER(),UPPER(),REVERSE()
40
Aggregatfunktionen (1)
SQL-92 Standard definiert
Aggregatfunktionen
MIN,MAX,SUM,AVG,COUNT
DB-Hersteller integrieren oft weitere
Aggregatfunktionen
SELECT
SUM(einnahmen),SUM(ausgaben)
FROM finanzen
41
Aggregatfunktionen (2)
Zählen der Ergebniszeilen mit COUNT
– SELECT COUNT(*) FROM buecher
Unterdrückung von
Mehrfachaufzählungen
– SELECT COUNT(DISTINCT autor) FROM
buecher
42
Aggregatfunktionen (3)
möglich:
– SELECT MAX(ausleihzahl) FROM buecher
nicht möglich:
– SELECT autor,titel,MAX(ausleihzahl)
FROM buecher
43
Gruppierung von Ergebnissen(1)
faßt Zeilen der Ergebnistabelle nach
bestimmten Kriterien zusammen
– SELECT autor,SUM(ausleihzahl) FROM
buecher GROUP BY autor
Selektion der zusammengefaßten
Ergebnisse durch HAVING
– SELECT autor,SUM(ausleihzahl) FROM
buecher GROUP BY autor HAVING
SUM(ausleihzahl)>10
44
Gruppierung von Ergebnissen(2)
Falsch:
SELECT s1,s2,s3 FROM tabelle GROUP BY
s1,s2
s1
a
b
b
a
s2
x
y
y
x
s3
2
4
6
10
s1
s2
s3
a
x
2 ,1 0
b
y
4 ,6
45
Gruppierung von Ergebnissen(3)
Richtig:
SELECT s1,s2,f(s3) tabelle GROUP BY s1,s2
SELECT s1,f(s2),s3 tabelle GROUP BY s1,s3
f ist dabei ein Aggregatfunktion!