Erneuerung der Luftdatenpumpe

luftdaten

#23

Stationslistengalerie Demo #2

Hier geht es um die Implementierung von mehreren kaskadierten Auswahlfeldern. Als Ergebnis werden entsprechend gefilterte Stationslisten angezeigt.

LDI Stations #2 » Cascaded » Stations

Details

Auch hier findet Ihr die im Dashboard verwendeten Variablen inkl. SQL Statements.

$ldi_station_countrycode

SELECT 
    country_code AS __value, 
    concat(country, ' (', country_code, ')') AS __text 
FROM 
    ldi_osmdata 
ORDER BY 
    country_code

$ldi_station_state

SELECT 
    state AS __value, 
    concat(concat_ws(', ', state, country), ' (', country_code, ')') AS __text 
FROM 
    ldi_osmdata 
WHERE 
    country_code IN ($ldi_station_countrycode) 
ORDER BY 
    country_code, state

$ldi_station_city

SELECT 
    city AS __value, 
    concat(concat_ws(', ', city, state, country), ' (', country_code, ')') AS __text 
FROM 
    ldi_osmdata 
WHERE 
    country_code IN ($ldi_station_countrycode) AND 
    state IN ($ldi_station_state) AND 
    city != '' 
ORDER BY 
    country_code, state, city

$ldi_station_road

SELECT 
    road AS __value, 
    concat(concat_ws(', ', road, city), ' (', country_code, ')') AS __text 
FROM 
    ldi_osmdata 
WHERE 
    country_code IN ($ldi_station_countrycode) AND 
    state IN ($ldi_station_state) AND 
    city IN ($ldi_station_city) AND 
    road != '' 
ORDER BY 
    country_code, city, road

#24

Stationslistengalerie Demo #3

Auch hier geht es um die Implementierung von mehreren kaskadierten Auswahlfeldern. Als Ergebnis werden jedoch entsprechend gefilterte Meßwerte angezeigt.

LDI Stations #3 » Cascaded » Measurements

Um einen Filter auf die Meßwerte anwenden zu können, müssen die Daten in der InfluxDB nach station_id aka. location_id abgefragt werden. Da wir naturgemäß keine datenbankübergreifenden JOIN Operationen zwischen InfluxDB and PostgreSQL machen können (Hello TimescaleDB!), müssen wir das Abfragekriterium für das station_id Feld zuerst in der PostgreSQL Domäne berechnen.

Neben den für die Kaskadierung notwendigen Grafana Variablen aus Demo #2 benötigen wir also noch eine weitere Variable $ldi_station_id, die über die Anwendung aller vier Abfragekomponenten auf die ldi_osmdata Tabelle berechnet werden kann:

$ldi_station_id

SELECT 
    station_id 
FROM 
    ldi_osmdata 
WHERE 
    country_code IN ($ldi_station_countrycode) AND 
    state IN ($ldi_station_state) AND 
    city IN ($ldi_station_city) AND 
    road IN ($ldi_station_road) 
ORDER BY 
    country_code, state, city, road

Da diese Variable nicht für den Benutzer zur interaktiven Auswahl gedacht ist, definieren wir sie als versteckt:

image.

Folgendermaßen kann die neue Variable schließlich zur Filterung der Meßdaten verwendet werden, sie steckt hier im Kriterium "WHERE location_id = $ldi_station_id":

image


#25

Stationslistengalerie Demo #4

@wtf wünschte sich noch die Berücksichtigung des Sensortyps bei der Auswahl der Meßstationen.

LDI Stations #4 » Select by sensor type

image

$ldi_station_sensortype

SELECT 
    DISTINCT(sensor_type) 
FROM 
    ldi_sensors 
ORDER BY 
    sensor_type

Datenbankabfrage nach Ort und Sensortyp

Die Filterauswahl wird folgendermaßen auf die Datenbank umgesetzt:

SELECT 
    ldi_osmdata.country_code, 
    /* Some runtime formatting fancyness for "state_and_city" and "name_and_id" */
    concat(ldi_osmdata.state, ' » ', ldi_osmdata.city) AS state_and_city, 
    concat(ldi_stations.name, ' (#', CAST(ldi_stations.id AS text), ')') AS name_and_id, 
    ldi_sensors.sensor_type 

FROM 
    ldi_stations, ldi_osmdata, ldi_sensors 

WHERE 

    /* JOIN constraints */
    ldi_stations.id = ldi_osmdata.station_id AND 
    ldi_stations.id = ldi_sensors.station_id AND 

    /* Query constraints */
    ldi_stations.country IN ($ldi_station_countrycode) AND 
    ldi_osmdata.state IN ($ldi_station_state) AND 
    ldi_osmdata.city IN ($ldi_station_city) AND 
    ldi_sensors.sensor_type IN ($ldi_station_sensortype) 

ORDER BY 
    country_code, state_and_city, name_and_id, sensor_type;

#26

Noch ein kleiner Nachtrag zu

Problem

Bei der Implementierung der Liste von Stations-IDs, die dann an InfluxDB übermittelt wird…

… hatten wir noch Probleme mit "Request-URI Too Large (414)" Fehlern à la

Ursache

Sobald man “zu wenige” Einschränkungen bei der Stationsauswahl definiert hat, explodieren naheliegenderweise die Einträge der Ergebnisliste mit station_id Einträgen. Da diese Liste per GET request an die InfluxDB Datenquelle von Grafana übertragen wird à la

GET /grafana/api/datasources/proxy/13/query?db=luftdaten_info&q=SELECT%20%22location_id%22%2C%20%22P1%22%2C%20%22P2%22%20FROM%20%22earth_43_sensors%22%20WHERE%20(%22location_id%22%20%3D~%20%2F%5E(728%7C6042%7C8430%7C8111%7C8822%7C7711%7C6221%7C6745
...

kommt es bei einer zu hohen Anzahl an Einträgen zu o.g. "Request-URI Too Large (414)" Fehlern.

Weitere Infos

Auch andere haben das Problem schon erkannt und schreiben darüber:

Lösung

Wir sind nun kurzerhand diesem Vorschlag von Torkel Ödegaard gefolgt:

In many cases you can specify a custom All value, like .* (regex wildcard) in the template variable options. This way Grafana will not use all values but the regex string you specified.

Request-URI Too Large · Issue #8109 · grafana/grafana · GitHub

image


#27

Problem

Diese Lösung klappt bei uns leider doch nicht. Da die Variable $ldi_station_id “versteckt” ist, kann man deren Inhalt nicht interaktiv auswählen und in Folge belegt sie Grafana dauerhaft mit “All” bzw. ".*". Außerdem haben wir weiterhin Probleme im Labor mit Abfragen wie

SELECT CAST(id AS text) AS id FROM ldi_stations WHERE country IN ('DE');

die weiterhin eine zu hohe Zahl an station_id Einträgen liefern. Danke, @wtf!

Lösung

Philipp Gortan schlägt vor:

For those running into this problem and using nginx: you can increase the large_client_header_buffers as a work-around.

Use POST Requests to query data from influxdb · Issue #10038 · grafana/grafana · GitHub

In der /etc/nginx/snippets/kotori-daq.conf hatten wir die large_client_header_buffers Einstellung bereits auf 16k erhöht, nun sind 64k konfiguriert:

# Performance parameters
# Relax "414 Request-URI Too Large"
large_client_header_buffers 6 64k;

#28

Stationslistengalerie Demo #5

Display luftdaten.info (LDI) measurements on Grafana Worldmap Panel.
Filter by synthesized address components and sensor type.


Map and table display.

image
Long name from OSM address and station id on overlay.

Grafana Worldmap Setup

Prerequisites

The OSM metadata is not stored in InfluxDB but will be acquired from a JSON file by the Grafana Worldmap Panel. Using luftdatenpumpe · PyPI, an appropriate file can be produced easily:

luftdatenpumpe stations --reverse-geocode --progress | \
    jq '[ .[] | {key: .station_id | tostring, name: .name} ]' | \
    safewrite /var/lib/grafana-metadata-api/jsonp/ldi-stations.json

However, as the data is stored in table format like

> select * from ldi_readings limit 10;

time                P1    P2   humidity sensor_id location_id temperature geohash
----                --    --   -------- --------- ----------- ----------- -------
1544581932000000000 4.05  3.75          2129      1071                    u1hfrxzj6e99
1544581933000000000            88.1     2130      1071        7.3         u1hfrxzj6e99
1544582001000000000 15.88 8.85          658       28                      u1j51p2sffb5
1544582002000000000            99.9     657       28          3.2         u1j51p2sffb5

these patches have been required to make things actually work:
https://github.com/grafana/worldmap-panel/pull/177

Panel configuration

image
Metrics configuration. Nothing special here but take care about all details.


Map configuration. The new “table+json” source makes it possible to acquire location information from the specified JSON file located on the same webserver at /json/ldi-stations.json. The record inside this dataset gets identified by using the value from the specified “Location Name Field” as a lookup key.


Grafana rendert nicht schnell genug
#29

Mit einer ähnlichen Abfrage bekommt man die höchste Sensor ID heraus:

http 'https://api.luftdaten.info/static/v1/data.json' | jq 'max_by(.sensor.id)'

Feinstaubsensor-Daten direkt an Kotori schicken?
#30

Trivia:

# Uncompressed CSV files
root@rem:~$ du -sch /datalarge/var/spool/archive.luftdaten.info/20*
176G	total
# Zip-compressed per-month/per-sensor archives
root@rem:~$ du -sch /datalarge/var/spool/archive.luftdaten.info/csv_per_month/
43G	total

These numbers are from a mirror of http://archive.luftdaten.info/ without http://archive.luftdaten.info/parquet/, from 2015-10-01 until 2019-01-02.


#31

Projecting Unicode to ASCII

Einleitung

Im Zuge der Verarbeitung von Adressinformationen von Stationen des lufdaten.info Meßnetzwerks entstehen durchaus schöne Geschichten wie diese hier:

Das Python Paket “Unidecode”

Um mit solchen Adressätzen besser im Raum der ASCII Zeichen umgehen zu können, hilft das Python Paket Unidecode · PyPI weiter. Es verspricht, den kompletten Unicode Raum in den Unicodeblock Basis-Lateinisch – Wikipedia projizieren zu können.

Am Beispiel

Einfachere Dinge wie die Transliteration – Wikipedia des Vietnamesischen “Đại Nài” in eine ASCII Zeichenkette erscheinen einigermaßen plausibel:

>>> from unidecode import unidecode
>>> unidecode("Đại Nài")
'Dai Nai'

Eine Transliteration des Chinesischen “朝阳区” erscheint da schon zauberhafter, obwohl alles natürlich auf den gleichen Mechanismen basiert:

>>> from unidecode import unidecode
>>> unidecode("朝阳区")
'Zhao Yang Qu '

– Abgekupfert von Projecting Unicode to ASCII

Weiterführende Informationen

Siehe auch

Fazit

Auf jeden Fall können wir damit pragmatisch Zeichenketten aus non-ASCII Glyphen erschließen und indizierbar/durchsuchbar machen, selbst wenn man nur lateinische Zeichen auf dem Keyboard tippen kann.


#33

Datenbankschema für Meßwert- und Stationsmetadaten

Wir haben nun endlich eine erste Version eines möglichen Datenbankschemas für die Aufbewahrung der Luftgütemeßdaten und der Stationsmetadaten anliegen. Auch wenn zwar gerade am Beispiel für LDI entwickelt, kann es als Blaupause für die Datenverarbeitung anderer Meßnetzwerke dienen.

Mehr Details dazu findet man unter LDI data plane v2.


Danke nochmals für die Impulse, gerade an @roh, das endlich besser zu machen als bisher. Und natürlich an @einsiedlerkrebs, der die allerersten Schritte in dieser Richtung unternommen hatte, dass wir die Daten überhaupt erst für uns erschließen konnten. Wir hoffen, @wtf ist mit den Ergebnissen als Basis für seine Wetterarbeiten ebenfalls zufrieden.


#34

Wir haben die neue Datenquelle jetzt in Betrieb, siehe auch LDI data plane v2.

image