Blog Post

Brieftauben Leistungsdaten (riro.de)

16. Mai 2022 Brieftauben, Data, Data Engineering, Technologie by Patrick Hirschi

Zusammenfassung

Jedes Jahr werden in der Schweiz mit den Brieftauben 12 Wettflüge ausgetragen. Über die Flugzeit und den Distanzen vom Auflassort zu den einzelnen Heimatschlägen der Brieftauben kann die durchschnittliche Geschwindigkeit für jede Taube berechnet werden. Ein Drittel der eingesetzten Tauben wird jeweils gelistet (“Preise”). Die Resultate (inkl. Geschwindigkeiten, Punkte, etc.) dieser Wettflüge werden auf RIRO (http://www.bas-riro.de/) bereitgestellt.

Mit Hilfe von Python-Skripten und Regular Expressions (GitHub) können die Leistungsdaten der Brieftauben in eine Datenbank (hier mySQL) eingelesen, aggregiert und in der Folge als HTML formatiert bereitgestellt werden. So kann für einen Züchter eine praktische Übersicht der Ergebnisse der eigenen Tauben bereitgestellt werden.

Umsetzung

Datenbank

Das Datenmodell soll so simpel wie möglich gehalten werden. Natürlich könnte man die Daten hochnormalisiert und typisiert ablegen, allerdings wäre dies für das ultimative Ziel einer HTML5 / Javascript / CSS Seite mit den Resultaten schon ein Overkill. Deshalb wurden in diesem Fall nur zwei Tabellen in einer mySQL Datenbank erstellt:

2022_Flugsaison
- Die Resultate der Tauben (Ränge, As-Punkte, Geschwindigkeit, …) pro Flug, zusammen mit aggregierten Informationen (Anzahl Züchter, Anzahl Tauben, …).
2022_Eingesetzte_Tauben
- Hier soll pro Flug ersichtlich sein, welche Tauben noch/wieder eingesetzt wurden.

Mit den folgenden Statements können die Tabellen erstellt werden:

CREATE TABLE `2022_Flugsaison` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `taubennr` varchar(255) NOT NULL,
  `mmin` varchar(255) NOT NULL,
  `prs_fg` varchar(255) DEFAULT NULL,
  `prs_rv` varchar(255) DEFAULT NULL,
  `aspkt_fg` varchar(255) DEFAULT NULL,
  `aspkt_rv` varchar(255) DEFAULT NULL,
  `insert_dttm` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `km` varchar(255) NOT NULL,
  `prs` varchar(255) NOT NULL,
  `auflassort` varchar(255) NOT NULL,
  `prs_zt` varchar(255) NOT NULL,
  `flugnummer` varchar(255) NOT NULL,
  `prs_tb` varchar(255) DEFAULT NULL,
  `flugdatum` date DEFAULT NULL,
  `anz_ztr_fg` varchar(255) DEFAULT NULL,
  `anz_ztr_rv` varchar(255) DEFAULT NULL,
  `anz_tbn_fg` varchar(255) DEFAULT NULL,
  `anz_tbn_rv` varchar(255) DEFAULT NULL,
  `prs_rg` varchar(255) DEFAULT NULL,
  `anz_tbn_rg` varchar(255) DEFAULT NULL,
  `anz_ztr_rg` varchar(255) DEFAULT NULL,
  `aspkt_rg` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=917 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `2022_Eingesetzte_Tauben` (
  `id` int NOT NULL AUTO_INCREMENT,
  `taubennr` varchar(255) NOT NULL,
  `flugnummer` varchar(255) NOT NULL,
  `insert_dttm` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=297 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Datenquellen

Für dieses Vorhaben können zwei Quellen interessant sein:

Ranglistendaten von http://www.bas-riro.de/
Daten vom Tipes Registriergerät https://tipes.de/

Leider sind die Ranglistendaten sehr heterogen, je nach Reisevereinigung, Land, und teilweise sind sie sogar von Flug zu Flug anders strukturiert. Dies kann zum Beispiel auftreten, wenn gewisse Flüge mit anderen Reisevereinigungen zusammen durchgeführt werden, und am Schluss eine Gesamtrangliste erstellt wird.

Die Daten vom Tipes Gerät beinhalten die registrierten Ankunftszeiten pro Taube und sind die Basis für die Erstellung der Gesamtranglisten. Diese sind glücklicherweise immer gleich formatiert.

Datenprozessierung

Methodik

Um die teils heterogenen Text-Daten konsistent und generisch einlesen zu können, wird mit Regular Expressions gearbeitet. Durch die Definition einer Reihe von syntaktischer Grundregeln, die man in den Daten erkennt und aus ihnen auslesen möchte, können die einzelnen Textbausteine nach bestimmten Trennzeichen (zum Beispiel Leerzeichen) ausgelesen werden. Da gewisse Textbausteine nicht immer vorhanden sind, können diese in den syntaktischen Regeln auch als “optional” deklariert werden.

Auf der Website https://regex101.com/ kann man die Regeln erstellen, prüfen, validieren und testen:

Beispiel für das Testen einer Regular Expression für das Auslesen einer Riro-Resultatzeile

Die einzelnen farbigen Bausteine können in der Folge bequem in praktisch jeder Programmiersprache verwendet werden.

Einlesen der Tipes-Daten

Die Daten der Tipes Geräte beinhalten nicht nur die Ankunftszeiten, sondern listen jede Taube, die am Flug eingesetzt war. Somit bieten sie eine gute Basis, um in den Statistiken auch ersichtlich zu machen, ob die Taube denn überhaupt eingesetzt war am jeweiligen Flug. Nachfolgend ein Beispiel:

Tipes Beispiel-Daten (Flugdatum in grün, Ankunftszeit in gelb und Taubennummer in rot)

Für dieses spezielle Format hat sich die folgende Regular Expression als hilfreich erwiesen:

^([0-9][0-9][0-9][0-9])([1-9][0-9])([A-Z][A-Z]?)\s*(0*)([1-9][0-9]*w?)\s*([0-9]*)\s*([0-9A-Z]*)(\*)([0-9]*;)([A-Z][A-Z]?)([0-9]*).([1-9][0-9]).([0-9]*w?);([a-zA-Z0-9.]*)$

Die detaillierten Anweisungen bei der Prozessierung können dem öffentlichen Github Repository entnommen werden.

Einlesen der Riro Ranglistendaten

Für dieses Format hat sich die folgende Regular Expression als hilfreich erwiesen:

^(\s*)([1-9][0-9]*[a-z]?)(\s*)([1-9][0-9]*[a-z]?)?(\s*)([1-9][0-9]*[a-z]?)?(\s*)([0-9][A-Z][0-9]\/)?(\s*)([0-1][0-9]*)?(\s*)([0-9]*)?(\s*)([0-9]*)?(\s*)([a-zA-Z]*)?(\s*)([0-9]*)(\s*)([0-9]*)?(\s*)([0-9]*W?\s)(\s*)([a-zA-Zäöüéèß&]*[.,]?\s*[,]?\s*[a-zA-Zäöüéèß&]*\s*[,+]?[a-zA-Zäöüéèß&]*\s*[+-.\/]*\s*[a-zA-Zäöüéèß&]*.?)(\s*)([0-9][0-9])(\s*)([0-9]*\/\s*[0-9]*)?(\s*)([0-9][0-9]?\s)?(\s*)([0-9][0-9]?\s)?(\s*)([0-9]*.[0-9]*.[0-9]*)(\s*)(-?[0-9]\s)?(\s*)([0-9]*,[0-9]*)(\s*)([0-9]*,[0-9]*)?(\s*)([0-9]*,[0-9]*)?(\s*)([0-9]*,[0-9]*)?(\s*)([A-Z])?(\s*)([0-9]*\/\s*[0-9]*)(\s*)([0-9]*)(\s*)([0-9]*,?[0-9]*)(\s*)([0-9]*)(\s*)([0-9]*)(\s*)$

Dies ermöglicht das strukturierte Auslesen von:

Taubennummer (inkl. Nationalität, Geschlecht und Jahrgang)
Ränge (Regional, Gruppe, etc.)
As-Punkte (Regional, Gruppe, etc.)
Geschwindigkeit
Züchtername
…

Die detaillierten Anweisungen bei der Prozessierung können dem öffentlichen Github Repository entnommen werden.

Datenaggregation

Jetzt wo alle nötigen Daten in die mySQL Datenbank eingelesen werden konnten, möchte man diese natürlich in geeigneter Form visualisieren. Die Abfrage der Resultate pro Taube und Flug aggregiert in einer Tabelle könnte man z.B. so erreichen:

SELECT   EING.taubennr,
         CAST(EING.flugnummer AS UNSIGNED) AS flugnummer,
         RESULTATE.prs_fg, 
         RESULTATE.prs_rv, 
         RESULTATE.prs_zt, 
         IF(RESULTATE.aspkt_fg IS NULL OR RESULTATE.aspkt_fg='',NULL,CAST(REPLACE(RESULTATE.aspkt_fg,',','.') AS DECIMAL(4,2))) AS aspkt_fg, 
         IF(RESULTATE.aspkt_rv IS NULL OR RESULTATE.aspkt_rv='',NULL,CAST(REPLACE(RESULTATE.aspkt_rv,',','.') AS DECIMAL(4,2))) AS aspkt_rv 
 FROM brieftauben.`2022_Eingesetzte_Tauben` AS EING 
 LEFT JOIN brieftauben.`2022_Flugsaison`	AS RESULTATE ON RESULTATE.taubennr = EING.taubennr AND RESULTATE.flugnummer = EING.flugnummer

Datenbereitstellung

Die Resultate der SQL Abfrage kann man nach Belieben weiter transformieren und aggregieren, und z.B. als HTML / Javascript / CSS Webseite ausgeben.

Beispielwebseite mit den aggregierten Resultaten (das Endprodukt sozusagen)

Damit man dies auch sinnvoll ausdrucken kann, habe ich noch einen “PDF Export” Button implementiert. Der Output ist nicht wunderschön, aber zweckmässig:

Nun sind alle Schritte komplett und man kann nach jedem Flug die entsprechenden Daten mit wenigen Klicks in mySQL einlesen, prozessieren und schlussendlich die Webseite aktualisieren. Beispiele für diese Webseiten sind zu finden unter: https://patrick-hirschi.ch/brieftauben/

Tags: brieftaube data data engineering data science

Write a comment