Datenbeschaffung über OSM. Stefan Maihack Dipl. Ing. (FH) Datum:

(1)

1

Datenbeschaffung über OSM

Stefan Maihack Dipl. Ing. (FH) Datum: 21.04.2018

(2)

MySQL – Datenbeschaffung

1. Möglichkeit - Osmosis

• Osmosis ist ein Commandoline-Tool zum Verarbeiten von OSM-Daten

• Anwendungsbeispiele:

 Erstellen von Dumps (Datenbankauszüge) aus einer Planet.osm (OSM.Datei der ganzen Erde).

 Importieren von Planetendumps in eine Datenbank

 Änderungs-Datensätze (Change Sets) unter Benutzung der Datenbank „history tables“ (Tabellen von Änderungen) erzeugen.

• Change Sets auf eine lokale Datenbank anwenden.

• Zwei Planet-Dateien vergleichen und Änderungs-Datensätze (Change Set) daraus erstellen.

• Neusortieren von Daten in einer Planet Datei.

• Daten aus einem rechteckigen Kartenausschnitt (Bounding Box) oder einem Polygon extrahieren.

(3)

3

MySQL – Datenbeschaffung

1. Möglichkeit - Osmosis

• Die folgenden Schritte erläutern einen Weg, wie man Daten von OpenStreetMap in eigene MySQL-Dateien überträgt.

1. Das Datenbankmodell muss vorhanden sein.

2. Daten von OpenStreetMap downloaden.

 Über OSM-Homepage: über OSM-Homepage XML-Daten eines Kartenausschnitts herunterladen.

 Planet-Datei: Die Datei „planet-latest.osm.bz2“ downloaden.

 Länder-Extrakte: Firmen, wie z.B. „geofabrik“ bieten Länderextrakte auf ihrer Downloadseite an.

 OSM-Datei selbst zuschneiden: Das Programm „OSMOSIS“ dient dazu OSM-Dateien zu bearbeiten, so kann man Dateien umwandeln, Teile (geographisch und/oder thematisch) herauskopieren.

http://wiki.openstreetmap.org/wiki/Osmosis

(4)

MySQL – Datenbeschaffung

1. Möglichkeit - Osmosis

OSMOSIS-Befehl der alle Arztadressen aus der Boundingbox von Bruchsal heraus sucht.

time /t (Zeitpunkt ausgeben)

..\osmosis\bin\osmosis --read-xml file="baden-wuerttemberg.osm.bz2„ (Datenquelle)

--bb left=8.3294 right=8.8684 bottom=49.0235 top=49.2364 (Rechteckausschnitt)

--tf accept-nodes amenity=doctors (Schlüssel)

--write-xml "aerzte_Bruchsal.osm„ (Schlüsselwert)

time /t (Zeitpunkt ausgeben)

(5)

MySQL – Datenbeschaffung

OSM-Datentypen

• Grundlegende Datentypen:

 Die Daten bestehen entweder aus Punkte, Linien (Sammlung von Punkten) oder Relationen (Gruppen von Objekte). Auch kann jedes Objekt noch sogenannte Tags haben.

• Eigenschaften der Daten:

 Punkte (nodes) werden durch ihre Koordinaten (in WGS-84) verortet und können alle möglichen „Points of Interest“ beschreiben, z. B. Geschäfte, Sehenswürdigkeiten etc.

 Linien (ways) beschreiben linienhafte Objekte, z.B. Straßen. Wenn Linien

geschlossen sind, so können sie als Polygone angesehen werden (hängt von dem Tag ab) und können dadurch alle Art von Flächen beschreiben (Gebäude und Wasserflächen).

 Relationen gruppieren Objekte thematisch zusammen, wobei jedes Objekt eine Rolle zugewiesen bekommt.

5

(6)

MySQL – Datenbeschaffung

Eigenschaften der OSM-Daten

• OSM lässt prinzipiell alle möglichen Schlüssel und Werte zu.

• Alle Elemente sollten in Englisch eingetragen werden.

• Folgende Themengebiete sind in den Daten enthalten:

Wege, Barrieren, Fahrradwege, Wirtschaftswege, Wasserläufe, Eisenbahn, Flughafen, Seilbahnen, Energieversorgung, Zivilisationsbauten/Kunstbauten,

Freizeit, Nutzung/Einrichtung, Amt / Büro / Dienststelle / Geschäftsstelle, Geschäfte, Handwerk, Notfall, Tourismus, Historisch, Landnutzung,

Militär, Natur, Geologie

• Link zu den möglichen Schlüssel-Werte-Paare http://wiki.openstreetmap.org/wiki/DE:Map_Features

(7)

MySQL – Datenbeschaffung

Eigenschaften der OSM-Daten

• Ein Tag ist eine Beschreibung eines Objektes in der OSM zur Kategorisierung und zur Auszeichnung mit zusätzlichen Informationen.

• Beispiele: (http://wiki.openstreetmap.org/wiki/DE:Map_Features)

 Das Tag highway dient der Weise, um Straßen zu kennzeichnen. Es gibt

Vereinbarungen über ihre Verwendung in den einzelnen Ländern, die auf der Wiki- OSM Steite beschrieben sind.

 Das Tag highway fallen auch die ganzen Wasserwege; Bäche, Flüße,etc.

 Das Tag landuse wird benutzt für Wohngebiete, Mischgebiete. Unter dieses Tag fallen auch die Einkaufszentren, die dann als Polygon gekennzeichnet werden.

 Das Tag leisure werden Orte beschrieben, die Hauptsächlich mit der Gestaltung der Freizeit zusammenhängen.

 Das Tag natural beschreibt natürliche Elemente, wie z.B. Landschaften oder geologische Gegebenheiten.

7

(8)

MySQL – Datenbeschaffung

Eigenschaften der OSM-Daten - Beispiele

(9)

MySQL – Datenbeschaffung

OSMOSIS

• Osmosis dient dazu OSM-Dateien zu bearbeiten, so kann man Dateien umwandeln, Teile (geographisch und/oder thematisch) extrahieren, u.s.w.

• Osmosis ist ein Kommandozeilen-Programm.

• Installation:

1. Das Programm herunterladen (http://wiki.openstreetmap.org/wiki/Osmosis) 2. Die Datei entpacken, man erhält folgende Verzeichnisse:

/bin /config /lib /script changes copying readme

• Beispiel: Die Stadt Wien aus den Komplettdaten von Österreich herausschneiden:

osmosis-0.38/bin/osmosis --read-pbf file=austria.osm.pbf --bounding-box top=48.502 left=15.479 bottom=48.325 right=15.718 completeWays=yes --write-xml

file=map.osm

9

(10)

MySQL – Datenbeschaffung

Die wichtigsten OSMOSIS-Befehle

• –read-xml file: Input osm-file

• –bb: Befehl zum Extrahieren

• left, right, bottom, top: Begrenzungen der bounding box

• –tf: Filtern von Tags (key=value). Hier: highway=bus_stop für alle Bushaltestellen.

• accept-nodes: Nodes werden für die Filterung akzeptiert. Hier könnte auch noch „–tf reject-ways“ bzw. „–tf reject-relations“ stehen. Dies würde bedeuten, dass ways und relations nicht beachtet werden.

• –write-xml: extrahierte und neu geschriebene Ausgabedatei

Befehle zum extrahieren aller Bushaltestellen der Heidelberger Altstadt:

osmosis --read-xml file="baden-wuerttemberg.osm" --bb left=8.69661 right=8.71781 bottom=49.40548 top=49.41488

--tf accept-nodes highway=bus_stop --write-xml "busstop_hd_altstadt.osm"

(11)

MySQL – Datenbeschaffung

OSMOSIS-Beispiele

• Zwei Beispiele, die Nürnberg und einen kleinen Bereich darum aus einer

Deutschland-OSM-Datei oder möglicherweise einer Planet.osm-Datei extrahieren:

osmosis \ --read-xml enableDateParsing=no file=germany.osm.bz2

\ --bounding-box top=49.5138 left=10.9351 bottom=49.3866 right=11.201 \ --write-xml nürnberg.osm.bz2

bzcat planet.osm.bz2 | osmosis \ --read-xml

enableDateParsing=no file=/dev/stdin \ --bounding-box

top=49.5138 left=10.9351 bottom=49.3866 right=11.201 \ --write- xml file=-\ | bzip2 > nürnberg.osm.bz2

• Der zweite Ansatz verwendet bzip2 direkt zum extrahieren und komprimieren, weil es effektiver ist als der eingebaute Archivierer.

• bzcat ist das Werkzeug, dass man auch unter Windows einsetzen sollte, wenn man gute Performance haben möchte. Nur ist /dev/stdin kein gültiger Dateipfad in

Windows. Aber man kann CONIN$ als Pfadangabe verwenden.

11

(12)

MySQL – Datenbeschaffung

OSMOSIS-Beispiele

osmosis --read-xml baden-wuerttemberg.osm --tf accept-relations

boundary=administrative --used-way --used-node --wx admin_grenzen.osm Alle administrativen Grenzen in Baden-Württemberg werden extrahiert.

Nürnberg und einen kleinen Bereich darum aus der Deutschland-OSM-Datei extrahieren osmosis \ --read-xml enableDateParsing=no file=germany.osm.bz2 \

--bounding-box top=49.5138 left=10.9351 bottom=49.3866 right=11.201 \ --write-xml nürnberg.osm.bz2

(13)

MySQL – Datenbeschaffung

OSMOSIS-Beispiele

Erstellen von 16 individuellen BZ2-OSM-Files

osmosis \ --rx full/planet.osm.bz2 \ --tee 16 \ --bp

file=polygons/europe/germany/baden-wuerttemberg.poly \ --wx baden-

wuerttemberg.osm.bz2 \ --bp file=polygons/europe/germany/bayern.poly \ --wx bayern.osm.bz2 \ --bp file=polygons/europe/germany/berlin.poly \ --wx

berlin.osm.bz2 \ --bp file=polygons/europe/germany/brandenburg.poly \ --wx brandenburg.osm.bz2 \ --bp file=polygons/europe/germany/bremen.poly \ --wx bremen.osm.bz2 \ --bp file=polygons/europe/germany/hamburg.poly \ --wx hamburg.osm.bz2 \ --bp file=polygons/europe/germany/hessen.poly \ --wx hessen.osm.bz2 \ --bp file=polygons/europe/germany/mecklenburg-

vorpommern.poly \ --wx mecklenburg-vorpommern.osm.bz2 \ --bp file=polygons/europe/germany/niedersachsen.poly \ --wx

niedersachsen.osm.bz2 \ --bp file=polygons/europe/germany/nordrhein- westfalen.poly \ --wx nordrhein-westfalen.osm.bz2 \ --bp

file=polygons/europe/germany/rheinland-pfalz.poly \ --wx rheinland- pfalz.osm.bz2 \ --bp file=polygons/europe/germany/saarland.poly \ --wx saarland.osm.bz2 \ --bp file=polygons/europe/germany/sachsen-anhalt.poly \ --wx sachsen-anhalt.osm.bz2 \ --bp

file=polygons/europe/germany/sachsen.poly \ --wx sachsen.osm.bz2 \ --bp file=polygons/europe/germany/schleswig-holstein.poly \ --wx schleswig-

holstein.osm.bz2 \ --bp file=polygons/europe/germany/thueringen.poly \ --wx

thueringen.osm.bz2 13

(14)

MySQL – Datenbeschaffung

OSMOSIS - Beispiele

• Extrahiert komplett Karlsruhe aus Europadaten:

osmosis --read-xml enableDateParsing=no file=europe-latest.osm --bounding-box top=49.1310 left=8.2013 bottom=48.9008 right=8.6177 --write-xml file=karlsruhe.osm

• Extrahiert aus den Daten von Karlsruhe Haltestellen und Bahnlinien osmosis --read-xml karlsruhe.osm --way-key-value

keyValueList="railway.tram,railway.tram_stop" --used-node --write-xml karlsruhe_tram.osm

• Extrahiert alle Restaurants in Karlsruhe:

osmosis --read-xml karlsruhe.osm --way-key-value keyValueList=„amenity.restaurant"

--used-node --write-xml karlsruhe_tram.osm

• Verwendung eines Packprogramms

bzcat downloaded.osm.bz2 | osmosis --read-xml enableDateParsing=no file=- -- bounding-box top=49.5138 left=10.9351 bottom=49.3866 right=11.201 --write-xml file=- | bzip2 > extracted.osm.bz2

(15)

MySQL – Datenbeschaffung

OSMOSIS

• Der einfachste Weg, um die Koordinaten für einen Rechteck-Ausschnitt zu

bestimmen, ist der "Export"-Reiter auf www.openstreetmap.org - dort kann man ein Rechteck markieren, dabei werden die Koordinaten angezeigt.

15

(16)

MySQL – Datenbeschaffung

OSMOSIS - Datengröße

• Europe-latest.osm  196 GB (als bz2 nur 18GB)

• Karlsruhe  245 MB

• Karlsruhe_tram  7,6 MB

• Koordinaten Bruchsal

49,2389 8,3898

49,0093 8,8062

(17)

Datenbeschaffung

2. Möglichkeit – overpass-turbo.eu

• Tool um die enormen Datenmengen von OpenStreeMap zu durchsuchen.

• Overpass-turbo benutzt eine eigene Abfragesprache.

• Funktionsweise:

 Der overpass-Client setzt direkt Abfragen an die OSM-Karten.

Zurück kommen dann die Daten, die durch die Abfrage abgesprochen wurden.

• Welche Abfragen sind z.B. möglich?

 Abfrage nach Timestamps (z.B. letzte Änderungen)

 Abfrage nach User

 Suche in einem Polygon

 …

(18)

Datenbeschaffung

2. Möglichkeit – overpass-turbo.eu

(19)

- OVERPASS-TURBO Beispiele für Bruchsal - Ärzte: Amenity = doctors

- Haltestellen: Highway = bus_stop - Kirchen= Amenity = place_of_worship - Zugstrecken: Railway = rail

- OVERPASS-TURBO Beispiele für Karlruhe - Amenity = bar

- Amenity = biergarten - Amenity = cafe

- Amenity = drinking_water - Amenity = foot_court - Amenity = ice_cream - Amenity = pub - Amenity = restaurant - Amenity = college - Amenity = kindergarten - Amenity = library - Amenity = school - Amenity = university - Amenity = bicycle_parking

- Route = train Zuroute

- Route = tram Straßenbahnlinie

- Railway = tram_stop Straßenbahnhaltestelle - Railway = subway_entrance U-Bahn Eingang

- Railway = platform Bahnsteig

- Railway = halt Haltepunkt (kein Bahnhof)

19

(20)

Datenbeschaffung

2. Möglichkeit – overpass-turbo.eu

• Seitenaufrufe/Benutzer

(21)

Datenbeschaffung

2. Möglichkeit – overpass-turbo.eu

• Exportdatenformate von Overpass-Turbo

 GEOJSON

 GPX

 KML

 Rohdaten

 GIST

21

(22)

GIS-Formate

GeoJSON

• Das GeoJSON – Format (GEO JavaScript Object Notation)

• einfaches kompaktes Datenformat in einer einfach lesbaren Textform zum Zweck des Datenaustauschs zwischen

Anwendungen.

• Jedes gültige JOSN-Dokument soll ein gültiges Javascript sein und per „eval()“ interpretiert werden können.

• Die Funktionen umfassen:

 Punkte (z.B.: Adressen, Standorte)

 Linienzüge (z.B.: Straßen, Autobahnen, Grenzen)

 Poilygone (z.B.: Länder, Provinzen, Landflächen)

• Unterschied zu XML: XML ist eine Auszeichnungssprache dadurch vielseitiger einsetzbar als JOSN (Nur Datenaustauschformat).

JOSN hat XML aufgrund seiner Einfachheit zurückgedrängt.

• Sinnvoll für direkte JavaScript-Nutzung.

(23)

GIS-Formate

GeoJSON

23 Im Vergleich ist in diesem Beispiel die XML-Datei 29% größer als die JOSN-Datei

JSON XML

(24)

GIS-Formate

GPX

• GPX – GPS Exchange Format: Datenformat zur Speicherung von Geodaten (ursprüngliche für GPS-Daten).

• Entwickelt von der Firma TopoGrafix.

• Basiert auf dem allgemeinen XML-Standard. Ein XML-Schema beschreibt die Elemente und den Aufbau des GPS Exchange Formats.

• Das Daten-Schema ist erweiterbar. Normalerweise nur Wegpunkte, Routen und Tracks. Erweiterbar um z.B.: Autor, Beschreibung etc.

• XML-Daten lassen sich nicht gut in Standard-Datenbanken

speichern und verarbeiten.

(25)

GIS-Formate

KML

• KML ist eine Auszeichnungssprache zur Beschreibung von Geodaten.

• Bekannt wurde sie durch die Anwendung im GoogleEarth- Programm.

• KML befolgt die XML-Syntax, liegt in der Version 2.2 vor und ist ein Standard des Open Geospatial Consortium.

• KML-Dokumente können Geodaten sowohl in Vektor- wie auch in Rasterform beinhalten.

• Vektorobjekte wie Punkte, Linien, lineare Ringe, Polygone oder COLLADA-Modelle werden als Placemark-Elemente und Luft- und Satellitenbilder als GroundOverlay-Elemente modelliert.

• Da es sich um XML-Daten handelt, lassen sich auch nur schlecht mit herkömmlichen Datenbanken verarbeiten.

25

(26)

GIS-Formate

Rohdaten

• Beim Rohdatenformat handelt es sich um das OSM-Format  XML- Daten.

• Da es sich auch hier um XML-Daten handelt, lassen sich auch nur

schlecht mit herkömmlichen Datenbanken verarbeiten.

(27)

GIS-Formate

Umwandeln der XML-Geodaten

• Da es sich bei den Overpass-Turbo-Formaten fast ausschließlich um XML-Daten handelt, müssen diese eine Tabellenähnliche

Struktur umgewandelt werden.

• Als Werkzeug dient z.B. Excel und ein Texteditor, der reguläre Ausdrücke verarbeiten kann.

27

(28)

Aufbereiten der Daten mittels Excel

1. Aus OVERPASS-TURBO heraus XML-Daten (OSM-Rohdaten) exportieren.

ACHTUNG: Die Datei muss die Endung XML besitzen.

2. Excel hat aus den XML-Daten eine Tabelle erstellt. Die nicht

notwendigen Spalten eliminieren.

(29)

Aufbereiten der Daten mittels Excel

• Die importierten Daten liegen in Excel als Schlüssel-Werte-Paare in einzelnen Zeilen vor.

29

• Diese Daten müssen aber in Spalten organisiert werden.

Schlüssel_Arzt1_name = Dr. Müller Schlüssel_Arzt1_strasse = Hauptstraße Schlüssel_Arzt1_Hausnummer = 3

Schlüssel_Arzt1_xKoordinate = 49.1317258 Schlüssel_Arzt1_yKoordinate = 8.6039202 Schlüssel_Arzt2_name = Dr. Meier

Schlüssel_Arzt2_strasse = Bahnhofstraße Schlüssel_Arzt2_Hausnummer = 5

Schlüssel_Arzt2_xKoordinate = 49.1248508 Schlüssel_Arzt2_yKoordinate = 8.5925145

Arztname Strasse Hausnummer xKoord yKoord

Dr. Müller Hauptstraße 3 49.1317258 8.6039202

Dr. Meier Bahnhofstraße 5 49.1248508 8.5925145

(30)

MySQL – Datenbeschaffung

Aufbereiten der Daten mittels Tabellenkalkulationsprogramme (z.B. Excel) XML-Daten aufbereiten:

(31)

MySQL – Datenbeschaffung

• 4. Ladedatei für MySQL mit den Daten aus OSM erstellen.

31

/* create table "aerzte_bruchsal" */

DROP TABLE IF EXISTS aerzte_bruchsal;

SHOW WARNINGS;

CREATE TABLE IF NOT EXISTS aerzte_bruchsal(

id INT(2),

Stadt VARCHAR(20), Land VARCHAR(5),

Hausnummer VARCHAR(10), Postleitzahl VARCHAR(5), Straße VARCHAR(30,) Arztart VARCHAR(30), Name VARCHAR(20), Rollstuhl VARCHAR(10), lat Float(10),

lon Float(10), PRIMARY KEY (id) );

SHOW WARNINGS;

/*Insert values into table*/

INSERT INTO aerzte_bruchsal (id, Stadt, Land, Hausnummer, Postleitzahl, Straße, Arztart, Name, Rollstuhl, lat, lon) VALUES (1,'Dettenheim', 'DE','137a','76706','Hauptstraße','Doktor','Dr. Wächter','limited',49.1655136,8.4151351),

(2,'Bad Schönborn-Mingolsheim','DE','3a','76669','Hebelstraße','Doktor','Dr. Zimmermann','limited',49.2237108, 8.6387469), 49,2237108 8,6387469 name Dr. Wittek

49,2200384 8,6368942 amenity doctors

49,2200384 8,6368942 name Dr. D. Baierl und Dr. A. Grahmann-Baierl 49,2200384 8,6368942 wheelchair no

49,1248666 8,5929033 amenity doctors 49,1248666 8,5929033 healthcare centre

49,1248666 8,5929033 name Ärztehaus Postcenter 49,1248666 8,5929033 wheelchair yes

(32)

MySQL – Datenbeschaffung

• Vorgehensweise:

1. Exportieren der Exceltabellen in CSV-Dateien (Comma-separated- Value)

2. CSV-Dateien in einem Texteditor bearbeiten, so dass für den

INSERT-Befehl eine fast korrekte Syntax entsteht. Am besten mit

Hilfe von Regulären Ausdrücken.

(33)

MySQL – Datenbeschaffung

Verwendung Regulärer Ausdrücke

• Suche in allen Textstellen in einer Markierung, die Groß- und Kleinbuchstaben enthalten und diese dann in Hochkomma setzen.

Suche nach: ([A-Z a-z]+) Ersetze durch: ´\1´

• Ein Komma statt einem Leerzeichen einfügen Suche nach: ([A-Z]+) ([A-Z]+) ([A-Z]+) ([0-9]+ (.*) Ersetze durch: \1,\2,\3,\4,\5

• Beginn des INSERT´s. Erstes Zeichen in einer Zeile wird ´(

Suche nach: (^) Ersetze durch: \(´\1

• Ende des INSERT´s. Letzte Zeichen in einer Zeile wird ), Suche nach: ($)

Ersetze durch: ´\), \1

33

Egal was noch kommt, Es ist eine Gruppe

(34)

MySQL – Datenbeschaffung

Verwendung Regulärer Ausdrücke

• Suche in allen Textstellen in einer Markierung, die Groß- und Kleinbuchstaben und Leerzeichen enthalten. Diese dann in Hochkomma setzt.

Suche nach: ([A-Z a-z]+\s+[A-Z a-z]+) Ersetze durch: ´\1´

(35)

JOSM –

OSM-Daten visualisieren

35

(36)

MySQL – Datenbeschaffung

Linksammlung

• OSMOSIS Download-Seite (enthält auch einige Beispiele) http://wiki.openstreetmap.org/wiki/Osmosis

• Liste aller möglichen Parameter von OSMOSIS:

http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage

• OpenStreetmaps Datenextrakte http://download.geofabrik.de/

• OpenStreeMap-FAQ

http://www.openstreetmap.de/faq.html

• Overpass-turbo

http://overpass-turbo.eu/

• JOSM

https://josm.openstreetmap.de/

(37)

MySQL – Datenbeschaffung

Die Ladedatei

001 /* Data are taken from Dortmund but reduced and 002 changed to suit different aspects. */

003 DROPDATABASE IF EXISTSsample;

004 SHOW WARNINGS;

005 CREATEDATABASE IF NOT EXISTSsample 006 DEFAULT CHARACTER SETutf8 COLLATE utf8_unicode_ci;

007 SHOW WARNINGS;

008 USEsample;

009 SHOW WARNINGS;

010 DROP TABLE IF EXISTSroutes;

011 SHOW WARNINGS;

012 CREATE TABLE IF NOT EXISTSroutes ( 013 id INT2,

014 name VARCHAR(10) BINARY, 015 colorVARCHAR(10) BINARY, 016 colorhexVARCHAR(10) BINARY, 017 PRIMARY KEY (id)

018 );

019 SHOW WARNINGS;

37

* Erstellen einer MySQL-Ladedatei

(38)

MySQL – Datenbeschaffung

Die Ladedatei - Constraints

Löschen einer Tabelle und Kreieren mit Schlüssel und Constraints (Sql/sample.sql)

003 DROPDATABASE IF EXISTS sample;

005 CREATE DATABASE IF NOT EXISTS sample

006 DEFAULT CHARACTER SETutf8 COLLATE utf8_unicode_ci;

008 USEsample;

056 DROP TABLE IF EXISTS s_r;

058 CREATE TABLE IF NOT EXISTS s_r( 059 s_id INT2,

060 r_id INT2,

061 seq INT2 UNSIGNED DEFAULT NULL, 062 spa INT4 DEFAULT NULL,

063 durTIME DEFAULT NULL, 064 PRIMARY KEY (s_id,r_id),

065 FOREIGN KEY (s_id) REFERENCESstops(id), 066 FOREIGN KEY (r_id) REFERENCESroutes(id) 067 );

Auflistung möglicher Contraints:

• NOT NULL:

der Skalar darf nicht Null sein.

• PRIMARY KEY:

der Skalar muss einzigartig und nicht NULL sein.

• Foreign Key:

der Skalar muss auf referentielle Integrität geprüft sein.

• UNIQUE:

der Skalar muss innerhalb eines Attributes einzigartig sein.

• CHECK():

explizite Überprüfungsanweisung

(39)

MySQL – Datenbeschaffung

Übung 1

39

• Aufgabe:

1. Erstellen Sie ein ER-Modell ihrer Studienarbeit 2. Extrahieren sie die Daten aus OpenStreetMap

3. Erzeugen sie analog zu den MySQL-Tabellen Excel-Tabellen und befüllen Sie diese mit Daten.

4. Erstellen Sie die Datenbank mittels SQL-Script in MySQL.

5. Füllen Sie eine Tabelle davon mit dem SQL-Kommando INSERT.