Webhafen - Projekte und Bastelkram

Daten ergänzen

Nach dem wir im vorherigen Artikel die empfangenen Flugzeuge in eine Datenbank geschrieben haben, wird jeder, der die Datenbank mit z.B. DB Browser for SQLite öffnet, feststellen, dass ausser der hex-ID alle Felder leer sind. Soll das so? Sicher nicht. Wir müssen nun die Daten irgend wo her bekommen.

Wer sich die Datenbank genauer ansieht, wird sehen, dass ein kleiner Teil der hex-Ids mit einer Tilde anfängt. Also nicht 12abd4 sondern ~12abd4. Die stört uns hier und muss weg. Doch warum ist die da? Der Receiver stellt eine Tidle vor den hex wenn der Flugzeugtransponder selbst keine hex-id übermittelt. Dann wird die hex entweder vom Receiver zugewiesen oder die Fluglotsen haben dem Flugzeug einen hex-Wert zugewiesen, der hier irgendwie über den Transponder im Flugzeug übertragen wurde. Meistens ist es ein Fake-hex, manchmal aber auch der richtige des Flugzeugs, allerdings von einer anderen Quelle. Ich habe mich entschieden, diese Einträge erst mal zu behalten. Weg können sie später immer noch, wenn ich keine Quellen für weitere Daten finde.

Auch dabei hilft mir ein Python-Script. Wenn auch noch nicht ganz zuverlässig. Was soll es nun machen?

  • Die Datenbank öffnen
  • Nachsehen, ob es Einträge gibt, die zwar eine hex haben, aber keine Registrierung
  • Auf die Tilde vor dem hex prüfen und diese entfernen
  • Wenn es keine Registrierung gibt, dann diese abfragen und ergänzen
  • Wenn es eine Registrierung abfragen kann, dass die restlichen Daten ergänzen
  • Und wenn nicht, dass n/a in das Feld eintragen

Im Prinzip also nichts großes. Ich hatte allerdings große Probleme, überhaupt eine nutzbare Quelle zu finden, die mit meinen noch laienhaften Kenntnissen abzufragen ist. Nach langem wühlen durch Foren fand ich jemanden, der eine kostenlose API zur Verfügung stellt, um Anhand der hex-ID die Registrierung abzufragen. Von der gleichen Quelle kommen dann auf einem zweite Weg Hersteller, Typ und Airline.

Meinem Script habe ich noch ein mitloggen des Zugriffs gegeben, damit ich in einer Logdatei sehen kann, wenn die Quelle irgend wann versiegt.

Ich lasse das hier nicht im 30-Sekunden-Takt laufen, da es sich bei der API auch nur um ein Privatprijekt handelt, das nur jemand nebenbei mitlaufen lässt. Ich möchte da den Server nicht so oft mit Anfragen bombardieren. Deswegen ist es nicht in das Hauptscript integriert und läuft zwei mal am Tag per Cronjob.

Der Anfang

Wie schon im ersten Teil müssen wir ein paar Vorbereitungen treffen. Wir importieren die benötigten Module für Python.

import sqlite3
from urllib.request import urlopen, URLError
from contextlib import closing
import json
import logging
import sys

Dann setzen wir unsere Variablen und konfigurieren, wie die Ausgabe im Logfile aussehen soll.

logfile="/home/dietpi/flights.log"
logging.basicConfig(format="%(asctime)s %(message)s", filename=logfile, level=logging.DEBUG)
db="/home/dietpi/flights.db"
tb="flights"

Also wie schon gehabt. 

Die Funktionen

Nun definieren wir wieder Funktionen, die uns später helfen, dass ganze übersichtlicher zu gestalten.

Wir öffenen die Datenbank, wie schon gehabt, allerdings dieses mal um die Log-Fähigkeit ergänzt.

def db_conn(db_file):
    con = None
    try:
        con=sqlite3.connect(db_file)
        logging.info("Datenbank geöffnet")
        return con
    except Error as e:
        logging.error("Datenbankfehler: " + str(e))

Wir suchen alle Einträge, die eine Tilde vor der hex-Id haben.

def remove_tilde():
    statement="SELECT * FROM " + tb + " WHERE icaohex LIKE '~%'"
    erg=c.execute(statement).fetchall()
    for i in erg:
        oldhex=i[1]
        newhex=oldhex[1:]
        statement="UPDATE " + tb + " SET icaohex='" + newhex + "' WHERE icaohex='" + oldhex + "'"
        c.execute(statement)
        c.commit()

Eine Datenbankabfrage sucht hier alle Einträge in der Tabelle, die in der Spalte icaohex mit einer Tilde (WHERE icaohex LIKE '~%') beginnen und schreibt sie in eine Variable, die ich erg genannt habe. In einer Schleife wird dann für jede Tabellenzeile, in der das vorkommt, der zweite Eintrag der Zeile genommen und in die Variable oldhex geschrieben. Warum der zweite Eintrag wenn in den spitzen Klammern eine 1 steht? Python fängt bei 0 an zu zählen. Die Spalte id in unserer Tabelle ist die erste, also für Python 0, dann die zweite eben die 1.

Dann nehmen wir mit oldhex[1:] den Eintrag und fangen ab den zweiten Zeichen an. Hier haben wir das gleiche. Python beginnt bei 0, dass wäre dann das erste Zeichen, damit ist 1 das zweite und so weiter. Anschliessend updaten wir die Datenbank indem wir mit UPDATE ... SET ... die Variable newhex, die ja jetzt gekürzt ist, nehmen und über die alte drüberschreiben.

Dann sehen wir in der Datenbank nach und fragen alle Einträge ab, die ein leeres Feld bei der Registrierung haben.

def getemptyreg():
    statement="SELECT * FROM " + tb + " WHERE registration=''"
    erg=c.execute(statement).fetchall()
    return erg

Die nächste Funktion fragt bei der API nach der Registrierung. Dazu übermittelt sie die hex-ID an einen Server und der antwortet mit einer Website, die nichts anderes enthält, als die Registrierung.

def getreg(hex):
    try:
        url="https://api.joshdouch.me/hex-reg.php?hex=" + hex
        with urlopen(url) as webpage:
            registration = webpage.read().decode()
        return registration
    except Exception as e:
        logging.error("API-Fehler: " + str(e) +"\nURL: " + url)
        c.close()
        logging.info("Datenbank geschlossen")
        sys.exit()

Das sieht man auf den Screenshot hier gut:

Registrierung abfragen

Und auch im Quelltext:

Registrierung abfragen

Hat die API keine Daten zur dieser hex, dann meldet sie ein n/a zurück.

Registrierung abfragen

Im Falle eines Fehlers, also wenn die API nicht erreichbar ist, schreibt der except-Teil einen Eintrag in das Logfile.

Die letzte Funktion ruft nun die weiteren Daten des Flugzeugs ab. Auch das hätte ich kombinieren können. Doch wenn die API das Flugzeug nicht kennt, also ein n/a zurückliefert, dann muss ich auch die zweite Abfrage nicht durchführen. Deswegen habe ich hier eine getrennte Funktion.

def getplanedata(hex):
    try:
        url="https://api.joshdouch.me/api/aircraft/" + hex
        with closing(urlopen(url, None, 5.0)) as aircraft_file:
            planedata = json.load(aircraft_file)
        return planedata
    except Exception as e:
        logging.error("API-Fehler: " + str(e) +"\nURL: " + url)
        c.close()
        logging.info("Datenbank geschlossen")
        sys.exit()

Sie funktioniert genau wie die davor, doch das Ergebnis ist dieses mal keine einfache Website. Die Daten liegen jetzt im json-Format vor.

Flugzeugdaten abfragen

Der Quelltext sieht dann entsprechend gleich aus.

Das eigentlich Script

Der Rest ist dann eigentlich recht banal und besteht aus Funktionsaufrufen, in denen jeweils Daten hin und her geschoben werden. 

Wir öffnen die Datenbank.

c=db_conn(db)
c.cursor()

Dann entfernen wir die Tilde und suchen uns alle Einträge ohne Registrierung.

remove_tilde()
noreg=getemptyreg()

Eine Schleife nimmt jeden Eintrag ohne eine Registrierung und fragt anhand der hex-ID die API ab.

for i in noreg:
    reg=getreg(i[1])

Liefert die API kein n/a (reg != "n/a"), dann wird die zweite Funktion aufgerufen mit getplanedata() und in die Klammern kommt die Variable, welche die hex-ID enthält. Wie oben schon erklärt, die zweite Spalte pro Zeile in der Datenbank. i[0] = erste Spalte, i[1] = zweite Spalte usw.

    if reg != "n/a":
        plane_data = getplanedata(i[1])
        manufacturer = plane_data['Manufacturer']
        ptype = plane_data['Type']
        airline = plane_data['RegisteredOwners']

Liefert die API allerdings das n/a, dann bleiben einefach alle Felder leer.

    else:
        manufacturer=""
        ptype=""
        airline=""

Dann aktualisieren wir wieder den Eintrag in der Datenbank. Dabei wird dann entweder die Registrierung und die Daten des Flugzeugs oder eben ein n/a bei Registrierung eingetragen.

    statement="UPDATE " + tb + " SET registration='" + reg + "', manufacturer='" + manufacturer + "', 
               type='" + ptype + "', airline='" + airline + "' WHERE icaohex='" + i[1] + "'"
    c.execute(statement)
    c.commit()

Das kann dann auch mal so aussehen:

Datenbank

Und wir schliessen die Datenbank wieder.

c.close()
logging.info("Datenbank geschlossen")

Da ich noch keine weiteren Quellen habe, muss ich nun damit leben, dass fast alle hex-IDs, die nicht direkt von Transponder aus den Flugzeug kamen, keine weiteren Werte haben. Wie ich die dann herausbekomme, darüber muss ich mir noch Gedanken machen und getrennt berichten.

Das fertige Script

Hier nun das fertige Script, am Ende der Seite auch als Download. Da es für mich funktioniert, wie es soll, allerdings noch nicht für alle gesehenen Flugzeuge Daten findet, bekommt es auch noch keine 1.0 als Versionsnummer. Ich nehme da einfach mal eine 0.5.

import sqlite3
from urllib.request import urlopen, URLError
from contextlib import closing
import json
import logging
import sys

logfile="/home/dietpi/flights.log"

logging.basicConfig(format="%(asctime)s %(message)s", filename=logfile, level=logging.DEBUG)

db="/home/dietpi/flights.db"
tb="flights"        #table name in database

def db_conn(db_file):
    con = None
    try:
        con=sqlite3.connect(db_file)
        logging.info("Datenbank geöffnet")
        return con
    except Error as e:
        logging.error("Datenbankfehler: " + str(e))

def remove_tilde():
    statement="SELECT * FROM " + tb + " WHERE icaohex LIKE '~%'"
    erg=c.execute(statement).fetchall()
    for i in erg:
        oldhex=i[1]
        newhex=oldhex[1:]
        statement="UPDATE " + tb + " SET icaohex='" + newhex + "' WHERE icaohex='" + oldhex + "'"
        c.execute(statement)
        c.commit()

def getemptyreg():
    statement="SELECT * FROM " + tb + " WHERE registration=''"
    erg=c.execute(statement).fetchall()
    return erg

def getreg(hex):
    try:
        url="https://api.joshdouch.me/hex-reg.php?hex=" + hex
        with urlopen(url) as webpage:
            registration = webpage.read().decode()
        return registration
    except Exception as e:
        logging.error("API-Fehler: " + str(e) +"\nURL: " + url)
        c.close()
        logging.info("Datenbank geschlossen")
        sys.exit()

def getplanedata(hex):
    try:
        url="https://api.joshdouch.me/api/aircraft/" + hex
        with closing(urlopen(url, None, 5.0)) as aircraft_file:
            planedata = json.load(aircraft_file)
        return planedata
    except Exception as e:
        logging.error("API-Fehler: " + str(e) +"\nURL: " + url)
        c.close()
        logging.info("Datenbank geschlossen")
        sys.exit()

c=db_conn(db)
c.cursor()
remove_tilde()
noreg=getemptyreg()
for i in noreg:
    reg=getreg(i[1])
    if reg != "n/a":
        plane_data = getplanedata(i[1])
        manufacturer = plane_data['Manufacturer']
        ptype = plane_data['Type']
        airline = plane_data['RegisteredOwners']
    else:
        manufacturer=""
        ptype=""
        airline=""
    statement="UPDATE " + tb + " SET registration='" + reg + "', manufacturer='" + manufacturer + "', 
               type='" + ptype + "', airline='" + airline + "' WHERE icaohex='" + i[1] + "'"
    c.execute(statement)
    c.commit()

c.close()
logging.info("Datenbank geschlossen")

Regelmässiges Ausführen

Das Script kann von der Kommandozeile manuell gestartet werden. Ich lasse es zwei mal am Tag, morgens und abends, per Cronjob auf dem Pi laufen.

Dazu loggen wir uns auf dem Pi per SSH ein. Mit

sudo crontab -e

öffnet sich ein Editor.

Crontab

in diesem wird eine Zeile ein- bzw. hinzugefügt.

01 7,18 * * * root /etc/cron.daily/flights

Mit Ctrl+S speichern und Ctrl+X beenden.

Der erste Eintrag bestimmt die Minute, nach dem Leerzeichen dann die Stunde. Hier sind zwei mit einem Komma getrennt, das wäre dann 07:01 und 18:01. Mit den drei Platzhaltern (*) können noch Tag, Monat und Wochentag definiert werden. Dann folgt der user, mit dessen rechten das Script ausgeführt wird und dann der Pfad und die Datei zu dem Script, welches zu der Zeit gestartet wird.

Sehen wir uns an, was da drin steht. Das geht mit

sudo nano /etc/cron.daily/flights

und öffnet einen Editor.

#!/bin/bash
/usr/bin/python3 /home/dietpi/getplanedata.py

Dei erste Zeile definiert, wer das Script ausführt, also hier die Shell bash (sowas wie die Kommandozeile) und die zweite Zeile was ausgeführt wird. Hier ist das Python in der Version 3 mit dem Script, das wir hier erstellt haben. Wichtig ist hier, dass immer der komplette Pfad angegeben ist. Auch hier mit Ctrl+S speichern und Ctrl+X beenden.

Jetzt könnte man natürlich einfach in der crontab schreiben:

01 7,18 * * * root /usr/bin/python3 /home/dietpi/getplanedata.py

Da ich aber noch mehr Ergänzungen habe und gleichzeitig noch eine Kopie der Datenbank auf ein NAS anlege, nehme ich lieber die längere Lösung und lasse in der /etc/cron.daily/flights mehrere Zeilen durchlaufen.

Fertig ist meine Eigenbau-Lösung, die meine Datenbank ergänzt, zumindest zum Teil. Solltet Ihr Anregungen oder Hinweise haben, bin ich da gerne offen für. Schreibt mir dann einfach eine Mail.

Get Plane Data Version 0.5

Get Plane Data Version 0.6

Ich habe eine neue Quelle gefunden. Bei Live Military Mode-S gibt es die Möglichkeit, über die Website die hex-ID, dort Mode-S-Code genannt, abzufragen. Das mache ich mir zunutze.

Um die Website, die als Ergebnis zurückgegeben wird, auszulesen, benutze ich BeautifulSoup, was allerdings per Hand in Python installiert werden muss. Dafür geben wir einfach auf der Kommandozeile des Pi

pip install beautifulsoup4

ein und warten.

Wenn BeautifulSoup installiert ist, kann es weitergehen. Diese kleine Erweiterung dient uns dazu, die Ausgabe, die von der Quelle kommt, schnell und einfach zu durchsuchen.

In unser Script haben zwei Funktionen Einzug gehalten. Die erste sucht in der Datenbank alle Einträge, die "n/a" bei der Registrierung haben und gibt sie zurück.

def getna():
    statement="SELECT * FROM " + tb + " WHERE registration='n/a'"
    erg=c.execute(statement).fetchall()
    return erg

Der zweiten Funktion wird dann der dazugehörige Hex-Wert übergeben und diese versucht, aus der Quelle Daten dazu herauszubekommen.

def getreglmms(hex):
    try:
        url="https://www.live-military-mode-s.eu/military%20mode-s%20database/search/searchMilHex.php?Code=" + hex + "&submit4=Search"
        with urlopen(url) as webpage:
            registration = BeautifulSoup(webpage.read().decode())
        td=registration.find_all("td")
        zlist=[]
        for i in td:
            z=("".join(i.findAll(text=True)))
            zlist.append(z)
        return zlist
    except Exception as e:
        logging.error("API-Fehler: " + str(e) +"\nURL: " + url)
        c.close()
        logging.info("Datenbank geschlossen")
        sys.exit()

Hier dann noch der zusätzliche Code, der die Funktionen nutzt.

regna=getna()
for i in regna:
    nareg=getreglmms(i[1])
    if "Serial:" in nareg:
        reg=nareg[nareg.index("Serial:")+1]
        type=nareg[nareg.index("Type:")+1]
        operator=nareg[nareg.index("Operator")+1]
    else:
        reg="n/a2"
        type=""
        operator=""
    statement="UPDATE " + tb + " SET registration='" + reg + "', type='" + type + "', airline='" + operator + "' WHERE icaohex='" + i[1] + "'"
    c.execute(statement)
    c.commit()

Wir rufen uns eine Liste der "n/a"-Einträge ab. Für jeden Eintrag in der Liste rufen wir die Informationen ab. Dabei habe ich festgestellt, dass die Website einen Wert "Serial:" zurückgibt, wenn sie eine Registrierung zur hex-ID kennt. Wenn die hex-ID unbekannt ist, taucht auch "Serial:" nicht auf. Ist dies allerdings so, dann wird aus der Liste der Abfrage die Registrierung, der Typ und die Airline ausgelesen und per SQL in die Datenbank zum passenden Eintrag geschrieben. Und damit das Script nicht immer und immer wieder suchen muss, wenn es einen Eintrag schon mal nicht gefunden hat, ersetze ich die Registrierung durch "n/a2".

Live Military Mode-S gibt zwar auch nicht-militärische Maschinen aus, leider aber keinen Hersteller. Das macht aber erst mal nichts, darum können wir uns später noch kümmern. Auch beim Operator, der Airline, sind die Angaben teils unterschiedlich. Hierfür muss ich mir dann noch ein Script ausdenken, welches möglichst einfach die Datenbank bereinigt und auf einem besser durchsuchbaren Stand bringt.

Get Plane Data Version 0.6.1

Mein Logfile zahlt sich aus:

API-Fehler: 'utf-8' codec can't decode byte 0xe9 in position 2672: invalid continuation byte
URL: https://www.live-military-mode-s.eu/military%20mode-s%20database/search/searchMilHex.php?Code=a3e38d&submit4=Search

Da passierte es. Ein Fehler beim auslesen der Daten. Sonderzeichen waren im Ergebnis, die kein utf-8 waren.

Die Zeile

registration = BeautifulSoup(webpage.read().decode())

verändert sich in

registration = BeautifulSoup(webpage.read().decode('latin-1'))

Ob latin oder utf ist hier für das Ergebnis egal. Aufgeräumt wird später

reg=nareg[nareg.index("Serial:")+1]

habe ich auch ergänzt, da Registrierungen teilweise große, teilweise kleine Buchstaben enthielten.

reg=nareg[nareg.index("Serial:")+1].upper()

Das passiert nun auch nicht mehr. Alte Einträge, die noch kleine Buchstaben haben, werden später korrigiert.