OpenStreetMap-Projekt: Teil1 – Openstreetmap-Daten in MySQL-Datenbank einlesen
Schon vor über einem Jahr habe ich mich etwas ausführlicher mit dem Projekt OpenStreetMap beschäftigt. Damals ging es um das Einlesen der Daten in eine eigene Datenbank und es gab schonmal ein HowTo, dessen Datenbankschema sich aber recht schnell als unnötig groß herausgestellt hat. Wirklich ordentlich beschrieben habe ich die dann folgende Lösung bis dato allerdings nicht, deshalb startet heute eine kleine Artikel-Serie zum Thema.
Datenbankschema für OpenStreetMap-Daten
Im ersten Teil soll es darum gehen die Openstreetmap-Daten in eine MySQL-Datenbank einzulesen. Später wird eine Möglichkeit gezeigt, aus den Daten einen gerichteten Graphen z.B. für eine Wegsuche zu erstellen, danach wird eine Wegsuche realisiert.
Aber zunächst müssen die Daten eingelesen werden. Benötigt wird:
- Eine funktionierende Installation eines MySQL-Servers (> Version 5.0) mit ordentlich Speicherplatz (Ich rede hier mindestens von Gigabyte-Zahlen für Baden-Württemberg, ganz Deutschland dürfte im Bereich um die 10GB liegen) und Administrations-Umgebung
- Eine funktionierende Java-Installation. Das von mir für den Import geschriebene Skript gibt eine SQL-Befehlskette auf der Standardausgabe aus, mit der Bash (Linux-Kommandozeile) lässt sich das direkt in eine MySQL-Verbindung schicken.
Als erstes wollen wir einen Blick auf die Openstreetmap-Daten werfen. Sie liegen im XML-Format vor und können auch auszugsweise z.B. bei Geofabrik heruntergeladen werden. Ich habe mir für das Projekt die Daten von Baden-Württemberg (baden-wuerttemberg.osm) heruntergeladen.
Wenn man die knapp 80MB große bz2-Datei entpackt hat, liegen ca.1 GB Daten auf der Festplatte. In die kann man mal reinschauen (aber bitte mit einem Tool, das Zeilenweise liest, z.B. “less”). Eine Analyse dieser Datei und ein Blick auf diese zwar veraltete, aber für diesen Zweck ausreichende Wikiseite führte mich zum Datenbankschema, wie in der Grafik.
Hier ist die zugehörige MySQL-Import-Datei.
Nachdem man eine Datenbank angelegt hat kann das oben gezeigte Schema erzeugt werden. Jetzt fehlt natürlich noch der Datenimport. Dafür entstand nach mehreren Versuchen ein Java-Programm, das aus einer *.osm-Datei einen entsprechenden Import herstellt.
Der Quellcode des Java-Programms lässt sich hier herunterladen. Seine Benutzung ist einfach. Das Programm erwartet einen Parameter mit dem Pfad/Dateinamen einer *.osm-Datei und erzeugt daraus einen MySQL-Input auf dem Std-Out. Nach dem Kompilieren von osm2sql sollte sich (zumindest auf der Linux-Bash) mit einem entsprechend angepassten Befehl der Art
java osm2sql baden-wuerttemberg.osm | mysql --host=localhost --database=osm --user=osm --password=abc
die Datenbank füllen lassen. Jetzt kann man normalerweise erst mal einen Tee kochen gehen. Ein wenig Zeit sollte man dem Datenbankserver schon gönnen.
Kommentare und Hinweise zum Thema sind hier nicht nur erlaubt, sondern ausdrücklich erwünscht.
Kommentare
25 Kommentare zu “OpenStreetMap-Projekt: Teil1 – Openstreetmap-Daten in MySQL-Datenbank einlesen”
Schreibe einen Kommentar
More?
Der Autor ist auch auf anderen Kanälen zu finden:
-
Wetter in Karlsruhe
-
Klar
-10°C
-
Mi.
-2/-13
-
Do.
-2/-10
-
Fr.
-4/-13
-
Klar
-10°C
[...] 2. Teil des Projektes soll es darum gehen, einen gerichteten Graphen aus den in Teil 1 in die Datenbank eingelesenen OpenStreetMap-Daten zu erstellen, mit dem später z.B. eine Wegsuche [...]
Hi,
erstmal Danke für deine tolle Arbeit. Ich bin schon seit einigen Stunden damit beschäftigt gewesen, eine osm-Datei in meine MySQL-DB zu pflanzen. Habe schon osmosis, anderes java, rails und alles was dazu gehört installiert und kurz vorm Ziel (der vorletzte Befehl laut wiki) dann ein Fehler (*klatschvordiestirn*).
Habe dann per Zufall dein Blog entdeckt und das hier mal versucht. Was soll ich sagen: Es läuft… zumindest kann ich im phpmyadmin entdecken, dass schon eine Menge Datensätze drin sind während auf der console nur Stille herrscht. Da es aber mehr werden, vermute ich, dass es funzt.
Übrigens wollte mein debian das java-script nicht kompilieren. Keine Ahnung warum. Ich habs dann einfach auf dem Win-System kompiliert und rüberkopiert.
Ich werde das hier interessiert weiterverfolgen. ;-D
Gruß
Uli
Hi nochmal,
ein kleiner Bericht, wie es bei mir mit dem Import (zunächst nicht) funktioniert hat:
Ich hatte das zunächst auf meinem Webserver (debian lenny) versucht zu importieren. Allerdings schlug der Import fehl, da auf der Platte “nur” 2,5 GB frei waren. Das osm-File lag auf einer anderen Partition. Resultat war, dass die Platte irgendwann voll war. Die Suche nach der Datei, die diese 2,5 Gig belegten verlief teilweise erfolglos. Ein Teil des Speicherplatzes wurde von der innodb-Datei belegt, der andere Teil wurde aber von einer – vermutlich temporären – Swapdatei belegt, die ich auch leider nicht löschen konnte (erst ein reboot half).
Ich habe dann den Versuch, die Datenbank auf meinem Webserver zu importieren (ist ein Live-System!) erstmal abgebrochen und dann das Ganze unter Windows mit xampp erfolgreich hinbekommen.
Um die Datenbank in ein Windows-System einzulesen geht man wie folgt vor:
1. java-Skript ausführen
> java osm2sql nordrhein-westfalen.osm > ausgabe.dump
Dauerte einige Minuten, also Geduld! Die Datei für NRW wird bei mir 1,6 GB groß.
2. mysql-console starten
> mysql –host=localhost –database=osm –user=osm –password=abc
3. dump-file einlesen
> use osm
> \. /pfad-zur-dump-datei/ausgabe.dump
Das dauert wieder recht lange. Und noch ein Tipp: Das Konsolenfenster minimieren, dann gehts 3 mal so schnell.
Gut zu wissen, dass das auch auf Windows funktioniert.
Meine Größenschätzung ist wohl etwas zu optimistisch. Hier gilt tatsächlich mal wieder: Mehr ist besser.
Werde das ganze Spielchen wohl morgen mal ausprobieren aber ich fürchte, dass ich nicht umhin kommen werde, meine TB Festplatte einzubauen, da ich den kompletten Worldfile in MySQL importieren will… und der ist alleine im Download (bzip2 komprimiert) knappe 6,8 GB groß *shiver*
Mal abwarten wenn der Download durch ist – halte euch gerne auf dem laufenden *g
Funktioniert nicht:
# java osm2sql.java
Exception in thread “main” java.lang.NoClassDefFoundError: osm2sql/java
Caused by: java.lang.ClassNotFoundException: osm2sql.java
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:307)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:248)
Could not find the main class: osm2sql.java. Program will exit.
@nurich
Vorher muss osm2sql.java natürlich kompiliert werden. Z.B. mit:
javac osm2sql.javadanach ist der Befehl, wie oben möglich.
Achsooo
# javac osm2sql.java
———-
1. WARNING in osm2sql.java (at line 10)
import javax.xml.parsers.ParserConfigurationException;
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
The import javax.xml.parsers.ParserConfigurationException is never used
———-
2. WARNING in osm2sql.java (at line 14)
import org.xml.sax.SAXException;
^^^^^^^^^^^^^^^^^^^^^^^^
The import org.xml.sax.SAXException is never used
———-
2 problems (2 warnings)
Hoffentlich nichts schlimmes. Nun hab ich aber wenigstens ne .class
Ne, nix schlimmes.. Sind zwei Warnungen, weil ich für verschiedene Exceptions spezielle Handler eingebunden habe, aber am Ende doch nicht benutzt.
zugegebenermaßen ein bisschen unsauber, aber seinen Zweck sollte das Tool trotzdem erfüllen.
Perfekt. Script funktioniert nun erstklassig. Hatte schon mit PHP angefangen aber heulend aufgegeben, nachdem auch der magicparser versagt hatte.
Ich probier es auch mal mit dem Planet dump. Trotzdem lüft das Script meines Erachtens für 2x4x2.66 GhZ u. 12 GB RAM recht langsam… Eventuell ist das aber auch nur mein Eindruck. Spitzenleistung!
Hab nur leichte Probleme mit den Umlauten, kommt das von dem Script?
4217293 name Rua Joana Ang?lica
Müsste eigentlich ein é sein. Parst du denn im Script die deutschen korrekt (ä, ü, ö…) oder liegt das an der Datenbankstruktur, die hab ich auch übernommen von Dir? Felder umsetzen auf utf8?
Mist: root@srv16 ~ # mysql -u root -p osm_de < germany.sql
Enter password:
ERROR 3 (HY000) at line 11071485: Error writing file '/tmp/MLMIbEMN' (Errcode: 27)
Mal sehen, ob ich rausbekomme was man da machen kann, Errcode 27 bedeutet "File too large".
Problem oben wurde gelöst indem ich auf der Festplatte einen ordner /temp angelegt habe und diesen in der my.cnf angegeben habe, das bestehende Temp Verzeichnis war auf die shm geroutet, das war wohl etwas zuviel für den armen. Nun läuft der Import. Mal sehen wie lange (9,9 GB SQL-Dump).
@11: Passt auch auf die deutsche OSM DB:
Bearbeiten Löschen 104 name M?nzmeisterstra?e
Grundsätzlich ist das ja “nur” ein Fehler im Encoding.
Ich habe zuletzt Ende 2009 damit gearbeitet und damals hatte ich den Fehler nicht. Die Datenbank, die ich jetzt noch habe, hat korrektes Encoding.
Hat sich evtl. das Encoding der planet.osm geändert? Das wäre jetzt mein Ansatz zum Debuggen. Gegebenenfalls mal eine kleine Demo-Datei mit Umlauten erzeugen und ausprobieren. Man muss ja nicht gleich mit dem riesigen Datenberg anfangen.
Cool, dass du dir die Mühe gemacht hast, werde ich nachher mal probieren und hoffen, dass alles glatt geht. Werd’s erstmal mit einem PLZ-Bereich probieren, wenn’s klappt kommt dann NRW dran. Hatte auch erst einen Parser in PHP geschrieben, der macht aber schon bei allem was größer als die Düsseldorfer Innenstadt ist schlapp.
8Lokal bei mir. Auf dem Uni-Rechner packt er nicht mal das.)
Super, nach vielen tests u.a. mit Osmosis und vielen Problemen habe ich auch dieses Tutorial gefunden. Beim ersten Durchlauf ist meine FP zugelaufen, aber mit einem Symlink auf die externe FP laeuft’s jetzt erstmal. Die Umlaute werden bei mir auch nicht richtig angezeigt, aber das ist mir gerade egal
Vielen Dank fuer die Vorarbeit!
root@ads /osm # javac osm2sql.java
-bash: javac: Kommando nicht gefunden.
Ideen? Welches apt-get Paket fehlt hier?
sun-java6-jdk hat gefehlt.
Kann es sein, dass das Script keine Umlaute korrekt darstellt? In der Datenbank sehe ich nur ein Fragezeichen wo der Umlaut sein sollte…
Jemand eine Idee? Ich denke, dass sich das Datenformat hier stark geändert hat aufgrund der “neuen” OSM API 0.6 (und der Dump ist mittlerweile ja auch schon 1 GB gross)… gepackt.
Liegt es u.U. an dem MySQL 5 Format? Für welche Version ist das denn geschrieben?
Wie bereits weiter oben schonmal beantwortet handelt es sich um ein Encoding-Problem. Vermutlich wurde das Encoding der osm-Daten umgestellt. Vielleicht liegt es auch an einer anderen Encoding-Einstellung in MySQL.
XML, Java und MySQL sind Unicode-fähig, der Fehler sollte sich also mit annehmbarem Aufwand beheben lassen.
Scheint an der Struktur zu liegen, die läuft auf ISO statt UTF-8. Ich probiere es nachher mal aus, wenn der planet-latest durchgelaufen ist (237 GB)
Hallo Leute.
Ich fand das Schema sehr brauchbar und habe ganz einfach mal ein plugin für osmosis geschrieben, welches die Daten direkt in ein sql-file schreibt (ähnlich, wie der osm2sql-konverter).
Wer mag, kann das gerne mal ausprobieren:
http://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage_0.39#–mysql-simple-dump_.28–wmsd.29
Schön, wenn ein fast schon vergessenes Projekt nach so langer Zeit nocheinmal aufgegriffen wird. Hat sich offensichtlich noch für den ein oder anderen gelohnt die Spielerei damals zu veröffentlichen.