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

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:

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

32 Kommentare zu “OpenStreetMap-Projekt: Teil1 – Openstreetmap-Daten in MySQL-Datenbank einlesen”

  1. OpenStreetMap-Projekt: Teil2 – Von den Rohdaten zum gerichteten Graphen | goblor grübelt am October 17th, 2009 14:05

    […] 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 […]

  2. Uli am October 21st, 2009 16:17

    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

  3. Uli am October 26th, 2009 12:52

    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.

  4. goblor am October 26th, 2009 17:32

    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.

  5. Dominik Habichtsberg am May 27th, 2010 01:06

    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

  6. nurich am October 12th, 2010 19:11

    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.

  7. goblor am October 12th, 2010 23:51

    @nurich

    Vorher muss osm2sql.java natürlich kompiliert werden. Z.B. mit:
    javac osm2sql.java
    danach ist der Befehl, wie oben möglich.

  8. nurich am October 21st, 2010 23:34

    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 :)

  9. goblor am October 21st, 2010 23:42

    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.

  10. nurich am October 21st, 2010 23:43

    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!

  11. nurich am October 22nd, 2010 01:03

    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?

  12. nurich am October 22nd, 2010 14:00

    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".

  13. nurich am October 22nd, 2010 14:20

    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).

  14. nurich am October 22nd, 2010 18:20

    @11: Passt auch auf die deutsche OSM DB:
    Bearbeiten Löschen 104 name M?nzmeisterstra?e

  15. goblor am October 22nd, 2010 19:15

    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.

  16. David am November 24th, 2010 11:57

    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.)

  17. Rob am January 7th, 2011 18:52

    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!

  18. Christian am March 9th, 2011 03:43

    root@ads /osm # javac osm2sql.java
    -bash: javac: Kommando nicht gefunden.

    Ideen? Welches apt-get Paket fehlt hier?

  19. Christian am March 9th, 2011 03:46

    sun-java6-jdk hat gefehlt. :)

  20. Christian am March 18th, 2011 18:50

    Kann es sein, dass das Script keine Umlaute korrekt darstellt? In der Datenbank sehe ich nur ein Fragezeichen wo der Umlaut sein sollte…

  21. Christian am April 8th, 2011 18:57

    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?

  22. goblor am April 8th, 2011 19:03

    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.

  23. Christian am June 13th, 2011 12:21

    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)

  24. Christoph am June 18th, 2011 13:56

    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

  25. goblor am June 18th, 2011 15:11

    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.

  26. Friedrich am March 5th, 2013 23:08

    Hallo!
    Ich bin heute auf das Skript gestossen, beim laufenlassen bekomme ich den Fehler:

    ERROR 1366 (HY000) at line 13: Incorrect string value: ‘\x9Fhw\x9Frm…’ for column ‘user’ at row 1
    Danach braucht java 100%CPU, läuft eine Zeitlang und beendet sich. Importiert wurde nichts.
    Datenbank und der dump sind in UTF-8, wo kann ich da weitersuchen?

  27. Friedrich am March 5th, 2013 23:24

    Kommando zurück. Der Fehler kam von mysql. Die Datei war in Western (Mac OS Roman) codiert. Kann man das in Java irgendwie umstellen? Arbeite auf dem Mac.

  28. Oliver am September 12th, 2013 09:24

    Die Node-IDs in den OSM-Daten sind teilweise größer als es ein MySQL int erlaubt. Ich habe alle IDs im Datenmodell auf “unsigned int” umgestellt. Dann ging’s.

  29. The_Wizzkid am November 29th, 2013 10:36

    Gutes Tool und Beschreibung! Ganz Deutschland osm.bz2 ist nun entpackt > 32 GB!
    Habe auch ERROR 1366 (HY000)… gehabt. Habe bei mir am Anfang des Java Scripts noch “SET NAMES latin1;” hinzugefügt, nun bekomme ich diesen Fehler nicht mehr. (DB ist auf UTF-8 – general_ci). Nun bin ich gespannt ob ich in ein paar Stunden Daten in der DB habe…

  30. The_Wizzkid am November 29th, 2013 11:04

    Wegen ” ERROR 1366 (HY000)… ” , “SET NAMES latin1” gab dann doch einen Fehler als ich abgebrochen habe. Richtig für das Javascript ist:
    System.out.println(“set character set latin1;”);
    anstelle von UTF-8.

  31. Showtek am June 13th, 2014 09:55

    Hallo,
    Gibt’s da noch jemanden der Support geben könnte mit windows ?

  32. Alexander am April 6th, 2016 10:50

    Wenn jemand diese Meldung bekommt:
    ERROR 1062 (23000) Duplicate entry ‘2147483647’ for key ‘PRIMARY’

    Schema von INT(11) auf BIGINT(18) stellen.

Schreibe einen Kommentar