Spiel und Spaß mit den Benzinpreisen in Deutschland

Seit Ende 2013 gibt es in Deutschland die „Markttransparenzstelle für Kraftstoffe“ an die jede Tankstelle jederzeit aktuell ihre Preise melden muss. Nur bis auf unzählige Spritspar-Apps hat man als Nerd davon noch nicht viel gesehen.
Zum Glück gibt es aber schon seit einiger Zeit Tankerkönig!
Eine Website die sich die Mühe gemacht hat sich offiziell bei der MTS-K als Verbraucher Informationsdienst anzumelden (wäre auch zu einfach wenn jeder die Daten einfach so bekommen würde!) und sie unter einer CC-Lizenz für jeden weiterzugeben…

Was gibt es hier nun für Möglichkeiten?
1. Es gibt eine API! Mit einem simplen HTTP-Request der Sorte

https://creativecommons.tankerkoenig.de/json/list.php?lat=48.9586&lng=10.5949&rad=10&sort=price&type=e5&apikey=xxyyzz

kann man z.B. die E5 Preise aller Tankstellen im Radius von 10km von meinem Zuhause (lat 48.9586, lng 10.5949) abrufen.
Den API-Key gibt es dabei schnell und unkompliziert wenn man seine Mailadresse bei Tankerkönig angibt.

Die Bedingungen sind:
tankerkoenig.de muss erwähnt werden (CC BY 4.0), man darf keine vom User nicht gewünschte Vorfilterung der Daten vornehmen, der API-Key muss geheimgehalten werden und die API-Anfragen sollten nicht öfter als alle 5 Minuten kommen. Wer also mit der API die nächste Killer-App programmieren will darf das sicher, er sollte aber vorher mit tankerkoenig.de sprechen und das abklären!

2. Es gibt einen Dump aller Daten seit dem 08.06.2014 – und genau diesen schauen wir uns jetzt hier mal etwas genauer an – denn eine API ansprechen kann ja jeder!
Wir klicken also auf der Website auf „Historische Preisdaten“ und bekommen zwei Postgres-Dumps mit 1.6 und 2.9 GB die sich zu 5.4 GB und 9.4 GB entpacken lassen.

Aber von vorne: Fangen wir also damit an die Datenbank Postgres und die dazugehörigen Postgis Erweiterungen zu installieren:

sudo apt-get install postgresql postgis

Nun erzeugen wir den Datenbankuser ‚benzin‘ (der heißt genauso wie unser Linux-User damit können wir uns ohne Passwort an der Datenbank anmelden!):

sudo -u postgres createuser -P -d benzin

Nun bekommt der User ‚benzin‘ noch eine Datenbank ‚benzinpreis‘ die ihm gehört:

sudo -u postgres createdb -O benzin benzinpreis

Jetzt laden wir die Dateien runter und entpacken sie:

wget https://creativecommons.tankerkoenig.de/history/history.dump.gz
wget https://creativecommons.tankerkoenig.de/history/history.dump.1.gz
gunzip history.dump.gz
gunzip history.dump.1.gz

-> Nicht wundern, manchmal meldet gunzip beim Entpacken „unexpected end of file“, dann ist die Datei futsch. Sie wird jeden morgen (ich glaube so gegen 4 Uhr) neu erzeugt und wenn man dem Betreiber von tankerkoenig.de unter info@tankerkoenig.de eine Mail schreibt dann erzeugt er sie schnell neu!

Nun sind wir im Linux als User ‚benzin‘ angemeldet und laden die entpackten Daten in die Datenbank. Zuerst das neuere Dump weil das ein paar Änderungen zum alten hat. Andersrum klappt es nicht:

psql benzinpreis < history.dump
psql benzinpreis < history.dump.1

Auch hier nicht wundern, Fehler wegen doppelten Keys oder Tabellen sind normal denn das zweite Dump hat nochmal alle Table-Create Kommandos mit drin!

Momentan sind die Fehler:

ERROR: relation "gas_station" already exists
ERROR: relation "gas_station_information_history" already exists
ERROR: relation "gas_station_information_history_id_seq" already exists
ERROR: duplicate key value violates unique constraint "gas_station_pkey"
ERROR: multiple primary keys for table "gas_station" are not allowed
ERROR: relation "idx_brand" already exists
ERROR: relation "idx_gas_station_post_code" already exists
ERROR: relation "idx_lat" already exists
ERROR: relation "idx_lng" already exists

Wir können nun die Dumps wegwerfen:

rm history.dump
rm history.dump.1

Und dann aktivieren wir (als Superuser) die Postgis Erweiterungen für unsere Datenbank. Diese benötigen wir für alle Funktionen die Entfernungen berechnen!

sudo -u postgres psql benzinpreis
> create extension postgis;
> \q

So, nun wieder als User ‚benzin‘ hupfen wir mal in die Datenbank rein und schauen uns mal die Tabellenstruktur der beiden existieren Tabellen an:

psql benzinpreis
> \d+ gas_station
> \d+ gas_station_information_history

Und so sehen wir die Details einer Tankstelle:

select * from gas_station
where id = '7b029caf-172e-4e50-6300-f2991c068432';

Und so die historischen Benzinpreisdaten einer Tankstelle:

select * from gas_station_information_history 
where stid = '7b029caf-172e-4e50-6300-f2991c068432';

Preise sind in Zehntel-Cent – Changed ist eine Art Bitfeld:

          DIESEL E5 E10
new       3-12-48
removed   2-8-32
changed   1-4-16

Heißt also:

changed == 21:
21 == 10101
Es wurde also Bit 1, 4 und 16 gesetzt. 
Somit hat sich Diesel, E5 und E10 geändert.

So bekommen wir raus wann unsere Datenbasis beginnt, endet und wieviele Preisänderungen wir haben:

select min(date) from gas_station_information_history;
select max(date) from gas_station_information_history;
select count(1) from gas_station_information_history;

Und jetzt kommen wir -zu guter letzt- zu den richtig schönen Datenbankabfragen!

Welche Tankstellen existieren in einem 10km Umkreis um meine Heimatkoordinaten (lat 48.9586, lng 10.5949):

select id, name, street, house_number, place, lat, lng,
round(cast(ST_DistanceSphere(ST_MakePoint(10.5949, 48.9586), ST_MakePoint(lng, lat)) / 1000 as numeric), 2) as "dist_in_km",
round(cast(degrees(ST_Azimuth(ST_MakePoint(10.5949, 48.9586), ST_MakePoint(lng, lat))) as numeric), 0) as "direction_in_degree"
from gas_station
where ST_DistanceSphere(ST_MakePoint(10.5949, 48.9586), ST_MakePoint(lng, lat)) / 1000 < 10
order by dist_in_km asc;

Man muss zuerst longitude und dann latitude an die ST_GeomFromText()-Funktion geben.
Das ist genau umgekehrt wie die Angabe in der Wikipedia. Da wird latitude (Breite) und danach longitude (Länge) angegeben.

Hier nochmal mit dem letzten Preis für E5 Benzin:
(Davor zusätzlichen Index erzeugen: „create index idx_date on gas_station_information_history (stid, date);“ )

select 
gas_station.id, 
gas_station.name, 
gas_station.street, 
gas_station.house_number, 
gas_station.place, 
gas_station.lat, 
gas_station.lng,
round(cast(ST_DistanceSphere(ST_MakePoint(10.5949, 48.9586), ST_MakePoint(gas_station.lng, gas_station.lat)) / 1000.0 as numeric), 2) as "dist_in_km",
round(cast(degrees(ST_Azimuth(ST_MakePoint(10.5949, 48.9586), ST_MakePoint(gas_station.lng, gas_station.lat))) as numeric), 0) as "direction_in_degree",
round(cast(gas_station_information_history.e5 / 1000.0 as numeric), 3) as "price_super_e5",
round(cast(gas_station_information_history.e10 / 1000.0 as numeric), 3) as "price_super_e10",
round(cast(gas_station_information_history.diesel / 1000.0 as numeric), 3) as "price_diesel",
to_char(gas_station_information_history.date, 'DD.MM.YYYY HH:MI:SS') as "price_time"
from gas_station
left join
gas_station_information_history on 
    stid = gas_station.id
    and gas_station_information_history.date = (select max(date) from gas_station_information_history where stid = gas_station.id)
where 
ST_DistanceSphere(ST_MakePoint(10.5949, 48.9586), ST_MakePoint(gas_station.lng, gas_station.lat)) / 1000 < 10
order by dist_in_km asc;

ACHTUNG: Damit der in einer sinnvollen Laufzeit fertig ist sollte man einen Index für die Tabelle gas_station_information_history für die Felder stid und date erzeugen:

create index idx_date on gas_station_information_history (stid, date);

-> Ein Primary Key geht nicht wegen doppelten Werten – übrigens auch in der Spalte ‚id‘!
–> Hier hilft die Funktion „explain analyze“ um genau rauszufinden was so lange an einem Query braucht damit man ihn optimieren kann!

Hier also der günstigste Preis von E5 im Umkreis von 100km. Ungültige Preise („<= 0“) und zu alte Preise (älter als 24 Stunden) werden ausgenommen!
(Davor nicht vergessen den Index zu erzeugen: „create index idx_date on gas_station_information_history (stid, date);“ )

select 
gas_station.id, 
gas_station.name, 
gas_station.street, 
gas_station.house_number, 
gas_station.place, 
gas_station.lat, 
gas_station.lng,
round(cast(ST_DistanceSphere(ST_MakePoint(10.5949, 48.9586), ST_MakePoint(gas_station.lng, gas_station.lat)) / 1000.0 as numeric), 2) as "dist_in_km",
round(cast(degrees(ST_Azimuth(ST_MakePoint(10.5949, 48.9586), ST_MakePoint(gas_station.lng, gas_station.lat))) as numeric), 0) as "direction_in_degree",
round(cast(gas_station_information_history.e5 / 1000.0 as numeric), 3) as "price_super_e5",
round(cast(gas_station_information_history.e10 / 1000.0 as numeric), 3) as "price_super_e10",
round(cast(gas_station_information_history.diesel / 1000.0 as numeric), 3) as "price_diesel",
to_char(gas_station_information_history.date, 'DD.MM.YYYY HH:MI:SS') as "price_time"
from gas_station
left join
gas_station_information_history on 
    stid = gas_station.id
    and gas_station_information_history.date = (select max(date) from gas_station_information_history where stid = gas_station.id)
where 
ST_DistanceSphere(ST_MakePoint(10.5949, 48.9586), ST_MakePoint(gas_station.lng, gas_station.lat)) / 1000 < 100
and gas_station_information_history.e5 > 0
and gas_station_information_history.date > (now() - interval '24 hours')
order by price_super_e5 asc;

-> TIPP: Mit „\x on“ bekommt man das Ergebnis in einzelnen Records angezeigt!

Übrigens habe ich schonmal etwas mit Postgres und Koordinaten gemacht, damals ging es um die existentielle Frage: Wie weit ist der nächste Kentucky Fried Chicken entfernt?

Übrigens gibt es jetzt einen Teil2 bei dem auch eine Grafik zum Benzinpreis gezeichnet wird.