Erneuerung der Luftdatenpumpe

Entwicklung / Development Maschinenraum / Machine room
1

Einleitung

Wir importieren bereits seit März 2017 Daten des https://luftdaten.info Projekts ins System nach https://luftdaten.hiveeyes.org/, weitere Details darüber kann man unter luftdaten.info — Kotori 0.22.7 documentation nachlesen.

Dank gemeinsamem Troubleshooting bei Apparent data-loss for two luftdaten.info sensor nodes und folgenden Diskussionen rund um die Importtechnik konnten wir der luftdatenpumpe.py eine Runderneuerung spendieren. Danke an dieser Stelle an @roh, @wtf, @einsiedlerkrebs, @weef und @clemens!

Die Verbesserungen betreffen zwei Bereiche: Funktionalität und Leistungsfähigkeit.

Funktionalität: Filterung

Das Programm "luftdatenpumpe" versteht nun die Optionen "--sensor=" sowie "--location=", denen derzeit jeweils eine oder mehrere kommaseparierte numerische IDs übergeben werden können, um die Ausgabe auf diese Stationen bzw. Sensoren zu beschränken.

Funktionalität: Stationsliste

Die bei der Filterung akzeptierten Werte für die genannten Optionen können über die Stationsliste herausgefunden werden, in der einfachsten Form ist man davon nur durch einen Aufruf von

luftdatenpumpe stations

entfernt.

Zusammen mit reverse geocoding dauert es zwar länger, man erlangt dadurch jedoch auch einen besseren Überblick. Durch die neuen Filtermöglichkeiten kann der Vorgang nun individuell eingeschränkt werden, um angenehmere Laufzeiten zu erzielen, beispielsweise per:

luftdatenpumpe stations --reverse-geocode --station=28,1071

Leistung

Die Luftdatenpumpe kann von nun an statische JSON Dateien à la luftdaten-stations-grafana.json für die Environmental Metadata Library erzeugen, die im Zuge von Map Grafana template variable identifiers to text labels using HTTP requests zum Einsatz kommen und im neuen Feinstaub Verlauf Berlin Dashboard bereits verwendet werden.

In Folge dessen können die Meßdaten selbst nun ohne textuelle Metadaten in der InfluxDB Datenbank auskommen, so dass wir beim regulären Aufruf zum Datenimport nun auf die teure --reverse-geocode Option verzichten können. Der Importvorgang wird zukünftig also deutlich flotter über die Bühne gehen:

luftdatenpumpe readings --target=mqtt://localhost/testdrive/info/earth/77/data.json

Total: 6s

Die Erzeugung der statischen Stationsliste dauert dementsprechend weiterhin lange, kann zukünftig jedoch viel seltener laufen, etwa einmal pro Tag:

luftdatenpumpe stations --reverse-geocode --target=json.grafana+stream://sys.stdout

Total: 22m

Rückblick

Bisher benötigte der Import gute 20(!) Minuten, obwohl(!) die Antworten des reverse geocodings über die Nominatim API lokal gecached werden.

Die Liste der Stationsnamen wurde über folgende Anfrage an die Datenbank aus den Meßdaten selbst berechnet:

SHOW TAG VALUES FROM earth_43_sensors WITH key="location_name"
1 Like
2

Zum…

Importintervall

[…] deshalb konnten wir bisher nur alle 30 Minuten importieren.

Häufigerer Import

Können und sollten wir die Daten von nun an alle fünf Minuten importieren?

Zugegeben: Nach den sechs Sekunden, die benötigt werden, um ca. 6000 Datensätze von luftdaten.info zu beziehen, leicht anzureichern und auf den MQTT Bus zu publizieren, werden also noch weitere anderthalb Minuten benötigt, um die Daten in die Datenbank zu schreiben.

Beim Importvorgang gerade eben um 17:30 Uhr fielen dazu folgende Telemetriedaten an:

2018-11-05T17:29:22+0100 [kotori.daq.services.mig] INFO: [luftdaten] transactions: 0.00 tps
2018-11-05T17:30:23+0100 [kotori.daq.services.mig] INFO: [luftdaten] transactions: 150.93 tps
2018-11-05T17:31:22+0100 [kotori.daq.services.mig] INFO: [luftdaten] transactions: 243.79 tps
2018-11-05T17:32:22+0100 [kotori.daq.services.mig] INFO: [luftdaten] transactions: 0.00 tps

Hier gibt es weitere Optimierungsmöglichkeiten, etwa in dem man die Daten direkt in die Datenbank speichert und die Buskommunikation nur sekundär aufstellt.

Fazit

Wir importieren die Daten von nun an alle 15 Minuten, das ist eine Verdoppelung der bisherigen Rate. Falls wir noch häufiger importieren sollen und das auch tatsächlich sinnvoll ist, lasst es uns wissen.

Aktueller Zeitplan

Folgendermaßen sieht das per /etc/cron.d/wetterdaten in der Praxis aus:

# --------------------
# luftdaten.info (LDI)
# --------------------
#
# Forward data from luftdaten.info JSON API to MQTT and finally store into InfluxDB
#
luftdatenpumpe="/home/workbench/kotori/.venv27/bin/luftdatenpumpe"
stations_file="/var/lib/grafana-metadata-api/luftdaten-stations-grafana.json"
mqtt_uri="mqtt://localhost/luftdaten/info/earth/01/data.json"

# Generate station-list once a day at 00:00
0      0       * * *           workbench       pflock ${luftdatenpumpe} stations --reverse-geocode --target=json.grafana+stream://sys.stdout | safewrite ${stations_file}

# Run data import each 15 minutes
*/15   *       * * *           workbench       pflock ${luftdatenpumpe} readings --target=${mqtt_uri}
3

Noch ein paar Samples und Trivia zur Stationsliste von luftdaten.info, die über das neue subcommand "luftdatenpumpe stations" produziert werden kann.

Kardinalität

luftdaten.info publiziert derzeit Daten von über 6000 Meßstationen:

cat /var/lib/grafana-metadata-api/luftdaten-stations-grafana.json | jq length
6074

Samples

Ausführliche Ausgabe

Der kanonische Aufruf:

luftdatenpumpe stations --reverse-geocode --station=28,1071

führt zu folgendem Ergebnis:

Ausgabe für Stationen 28 und 1071 
[
    {
        "station_id": 28,
        "name": "Ulmer Stra\u00dfe, Wangen, Stuttgart, Baden-W\u00fcrttemberg, DE",
        "position": {
            "latitude": 48.778,
            "longitude": 9.236,
            "altitude": 223.7,
            "country": "DE",
            "geohash": "u0wt6pv2qqhz"
        },
        "location": {
            "place_id": "101135850",
            "licence": "Data \u00a9 OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
            "osm_type": "way",
            "osm_id": "115374184",
            "lat": "48.777845",
            "lon": "9.23582396156841",
            "display_name": "Kulturhaus ARENA, 241, Ulmer Stra\u00dfe, Wangen, Stuttgart, Regierungsbezirk Stuttgart, Baden-W\u00fcrttemberg, 70327, Deutschland",
            "boundingbox": [
                "48.7775199",
                "48.778185",
                "9.2353783",
                "9.236272"
            ],
            "address": {
                "country_code": "DE",
                "country": "Deutschland",
                "state": "Baden-W\u00fcrttemberg",
                "state_district": "Regierungsbezirk Stuttgart",
                "county": "Stuttgart",
                "postcode": "70327",
                "city": "Stuttgart",
                "city_district": "Wangen",
                "suburb": "Wangen",
                "road": "Ulmer Stra\u00dfe",
                "house_number": "241",
                "neighbourhood": "Wangen"
            },
            "address_more": {
                "building": "Kulturhaus ARENA"
            }
        },
        "sensors": [
            {
                "sensor_id": 658,
                "sensor_type": "SDS011"
            },
            {
                "sensor_id": 657,
                "sensor_type": "DHT22"
            }
        ]
    },
    {
        "station_id": 1071,
        "name": "Gerichtstra\u00dfe, Gesundbrunnen, Mitte, Berlin, DE",
        "position": {
            "latitude": 52.544,
            "longitude": 13.374,
            "altitude": 38.7,
            "country": "DE",
            "geohash": "u33dbm6duz90"
        },
        "location": {
            "place_id": "81152066",
            "licence": "Data \u00a9 OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
            "osm_type": "way",
            "osm_id": "25686473",
            "lat": "52.54394525",
            "lon": "13.3738370010676",
            "display_name": "17, Gerichtstra\u00dfe, Gesundbrunnen, Mitte, Berlin, 13347, Deutschland",
            "boundingbox": [
                "52.5438614",
                "52.5440293",
                "13.3736783",
                "13.3739954"
            ],
            "address": {
                "country_code": "DE",
                "country": "Deutschland",
                "postcode": "13347",
                "city": "Berlin",
                "city_district": "Mitte",
                "suburb": "Gesundbrunnen",
                "road": "Gerichtstra\u00dfe",
                "house_number": "17",
                "state": "Berlin"
            }
        },
        "sensors": [
            {
                "sensor_id": 2129,
                "sensor_type": "SDS011"
            },
            {
                "sensor_id": 2130,
                "sensor_type": "DHT22"
            }
        ]
    }
]

Grafana-kompatible Ausgabe

Für die Kompatibilität mit einer Grafana Datenquelle à la Map Grafana template variable identifiers to text labels using HTTP requests ist die Option "--format=grafana" zuständig.

Ein entsprechender Aufruf:

luftdatenpumpe stations --reverse-geocode --station=28,1071 --target=json.grafana+stream://sys.stdout

erzeugt das gewünschte Format [1]:

Ausgabe für Stationen 28 und 1071 
[
    {
        "value": 28,
        "text": "Ulmer Stra\u00dfe, Wangen, Stuttgart, Baden-W\u00fcrttemberg, DE"
    },
    {
        "value": 1071,
        "text": "Gerichtstra\u00dfe, Gesundbrunnen, Mitte, Berlin, DE"
    }
]

[1] Erzeugt aus der effektiven Zieldatei per cat /var/lib/grafana-metadata-api/luftdaten-stations-grafana.json | jq .[:3].

5

… und nun noch die vorhandenen Daten anpassen.

Ausgangspunkt

Die Datenbank ist seit dem Beginn der vorhandenen Aufzeichnungen am August 30, 2017 auf gute 7 Gigabyte angewachsen:

du -sch /var/lib/influxdb/data/luftdaten_info

7.6G total

Datenmigration

Bei der Migration in eine neue Datenkollektion wollen wir die Daten reduzieren. Bisher wurden noch die Textfelder "location_name" sowie "sensor_type" mitgespeichert, das wollen wir zukünftig nicht mehr tun.

Derzeit läuft die Datenmigration, die sich momentan noch problematisch gestaltet, siehe Umschreiben großer InfluxDB Datenbanken.

Zwischenergebnis

Der gesamte Prozess hat 90 Minuten gedauert, ist aber erfolgreich durchgelaufen. Allerdings lässt das Ergebnis noch zu wünschen übrig:

du -sch /var/lib/influxdb/data/ldi02
6.9G total

Nach der ersten Runde TSM compaction wuchs die Datenmenge sogar an:

du -sch /var/lib/influxdb/data/ldi02
7.4G total

Fazit

Im Vergleich zur unoptimierten Datenbankversion haben wir bislang doch nicht so viele Daten eingespart wie erhofft (~ 700 MB). Die vermutlich gleichermaßen hohen Kardinalitäten für die Tag Felder "geohash" sowie zweitrangig "sensor_type" fallen wohl weiterhin stark ins Gewicht.

1 Like
6

Update: Weil wir die Datenbank beim ersten (erfolgreichen) Lauf falsch benannt hatten, konnten wir den Datenfeed nicht umschwenken. Wir reiten vorerst also weiterhin auf den alten Daten weiter und überlegen, wie man das Umschreiben effizienter gestalten könnte, damit man das nächste Mal nicht wieder die komplette Datenbank offline nehmen muss. Das mussten wir, weil InfluxDB beim INSERT ... INTO ... extrem sensibel auf weitere konkurrierende Anfragen reagiert. Alles in allem nicht gerade berauschend, wir werden Euch unter Umschreiben großer InfluxDB Datenbanken auf dem Laufenden halten, was dieses Thema betrifft.

7

Weil Ihr fragtet, @wtf und @roh: Es sind doch ~6000 Meßstationen, nicht nur die Sensoren, die ich mit der Zahl meinte. In anderen Worten: Der jede Viertelstunde gezogene Batch enthält 6074 Einträge mit eindeutiger location_id.

Zur Reproduktion gibt’s die Datei unter luftdaten-stations-grafana.json, sie wird jede Nacht um 00:00 Uhr Systemzeit neu generiert, das Ganze dauert eine knappe Stunde (Date: Sat, 10 Nov 2018 00:51:06 GMT). Ggf. produziert ein Aufruf von "luftdatenpumpe stations" lokal das gleiche Ergebnis, weitere Beispiele dazu findet Ihr hier weiter oben im Thread.

Wir können diese Summe gerne zukünftig als Metrik erheben, wenn Ihr mögt. Ein kleines Shellscript, das das um 02:00 Uhr so wie oben gezeigt durchführt, sollte dafür doch ausreichen?

8

Wir müssen auch die Stationsliste noch weiter verbessern. Das mit dem statischen JSON war nett gemeint, reicht aber nicht aus, wenn wir dynamische Abfragen auf die Stationsliste machen wollen, um geospatiale Filterungen auf den Meßdaten veranstalten zu können.

9

Hilfe, der Feed enthält auf einmal über 20.000(!) Meßstationen:

http 'https://api.luftdaten.info/static/v1/data.json' | jq length
25065

Das ist teuer und unhandlich, sobald es um das reverse geocoding geht. Es dauert bereits zehn Stunden und benötigt noch weitere viereinhalb:

28%|███████████▋                     | 6696/23907 [10:06:59<4:33:39,  1.05it/s]
10

Ich habe mir die Daten nun noch einmal näher angesehen. Anhand des Beispiels location_id=1071 hier einmal ein Auszug aus einer Datenlieferung:

data.json, filtered by location_id=1071 
[
  {
    "id": 2418110339,
    "sampling_rate": null,
    "timestamp": "2018-12-02 13:33:39",
    "location": {
      "id": 1071,
      "latitude": "52.5440",
      "longitude": "13.3740",
      "altitude": "38.7",
      "country": "DE"
    },
    "sensor": {
      "id": 2129,
      "pin": "1",
      "sensor_type": {
        "id": 14,
        "name": "SDS011",
        "manufacturer": "Nova Fitness"
      }
    },
    "sensordatavalues": [
      {
        "id": 5143554675,
        "value": "8.03",
        "value_type": "P1"
      },
      {
        "id": 5143554677,
        "value": "7.23",
        "value_type": "P2"
      }
    ]
  },
  {
    "id": 2418110373,
    "sampling_rate": null,
    "timestamp": "2018-12-02 13:33:40",
    "location": {
      "id": 1071,
      "latitude": "52.5440",
      "longitude": "13.3740",
      "altitude": "38.7",
      "country": "DE"
    },
    "sensor": {
      "id": 2130,
      "pin": "7",
      "sensor_type": {
        "id": 9,
        "name": "DHT22",
        "manufacturer": "various"
      }
    },
    "sensordatavalues": [
      {
        "id": 5143554750,
        "value": "84.10",
        "value_type": "humidity"
      },
      {
        "id": 5143554749,
        "value": "10.30",
        "value_type": "temperature"
      }
    ]
  },
  {
    "id": 2418122085,
    "sampling_rate": null,
    "timestamp": "2018-12-02 13:36:06",
    "location": {
      "id": 1071,
      "latitude": "52.5440",
      "longitude": "13.3740",
      "altitude": "38.7",
      "country": "DE"
    },
    "sensor": {
      "id": 2129,
      "pin": "1",
      "sensor_type": {
        "id": 14,
        "name": "SDS011",
        "manufacturer": "Nova Fitness"
      }
    },
    "sensordatavalues": [
      {
        "id": 5143579362,
        "value": "11.30",
        "value_type": "P1"
      },
      {
        "id": 5143579363,
        "value": "10.03",
        "value_type": "P2"
      }
    ]
  },
  {
    "id": 2418122122,
    "sampling_rate": null,
    "timestamp": "2018-12-02 13:36:07",
    "location": {
      "id": 1071,
      "latitude": "52.5440",
      "longitude": "13.3740",
      "altitude": "38.7",
      "country": "DE"
    },
    "sensor": {
      "id": 2130,
      "pin": "7",
      "sensor_type": {
        "id": 9,
        "name": "DHT22",
        "manufacturer": "various"
      }
    },
    "sensordatavalues": [
      {
        "id": 5143579440,
        "value": "84.20",
        "value_type": "humidity"
      },
      {
        "id": 5143579438,
        "value": "10.30",
        "value_type": "temperature"
      }
    ]
  },
]

Man erkennt vier Einträge, die zwei Messungen repräsentieren. Die erste Messung fand um 2018-12-02 13:33:39/40 statt, die zweite um 2018-12-02 13:36:06/07.

Eine Messung wiederum enthält zwei Einträge, da eine einzige Messung nur einen einzigen Sensor repräsentieren kann. Es gibt jedoch zwei Sensoren: Einmal den SDS011 für die Feinstaubpartikel (sensor_id=2129) und daneben noch den DHT22, der für Temperatur und Luftfeuchtigkeit zuständig ist (sensor_id=2130).

Um die absolute Zahl der Meßstationen zu erhalten, muss man also ca. durch vier teilen, wenn die Datenlieferung so beschaffen ist wie derzeit und daher zwei Meßzeitpunkte enthält. Präziser wäre eine Aggregation über alle Daten, um count(unique(station_id)) zu berechnen.

11

So macht das Ganze wieder mehr Sinn:

Unixy

http 'https://api.luftdaten.info/static/v1/data.json' | jq '.[].location.id' | sort | uniq | wc -l
6294

jq native

http 'https://api.luftdaten.info/static/v1/data.json' | jq 'unique_by(.location.id) | length'
6294

– jq manual: Builtin operators and functions

12

5. November 2018

2. Dezember 2018

Zuwachs

Seit der letzten Bestandsaufnahme vor 27 Tagen sind also 220 Stationen hinzugekommen, wenn die Zahlen stimmen.

1 Like
13

Im Sinne von »Daten ohne Datenbank um Rat fragen« geht “Was ist die Station mit der kleinsten ID?” genauso schön mit jq:

http 'https://api.luftdaten.info/static/v1/data.json' | jq 'min_by(.location.id)'
1 Like
14

Volle Stationsliste

Die neue Version der Luftdatenpumpe wird der LDI Stationsliste in spe folgende Informationen zur Verfügung stellen:

luftdatenpumpe stations --station=28,297 --reverse-geocode
Ausgabe für Stationen 28 und 297 
[
    {
        "station_id": 28,
        "name": "Ulmer Stra\u00dfe, Wangen, Stuttgart, Baden-W\u00fcrttemberg, DE",
        "position": {
            "latitude": 48.778,
            "longitude": 9.236,
            "altitude": 223.7,
            "country": "DE",
            "geohash": "u0wt6pv2qqhz"
        },
        "location": {
            "place_id": "101135850",
            "licence": "Data \u00a9 OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
            "osm_type": "way",
            "osm_id": "115374184",
            "lat": "48.777845",
            "lon": "9.23582396156841",
            "display_name": "Kulturhaus ARENA, 241, Ulmer Stra\u00dfe, Wangen, Stuttgart, Regierungsbezirk Stuttgart, Baden-W\u00fcrttemberg, 70327, Deutschland",
            "boundingbox": [
                "48.7775199",
                "48.778185",
                "9.2353783",
                "9.236272"
            ],
            "address": {
                "country_code": "DE",
                "country": "Deutschland",
                "state": "Baden-W\u00fcrttemberg",
                "state_district": "Regierungsbezirk Stuttgart",
                "county": "Stuttgart",
                "postcode": "70327",
                "city": "Stuttgart",
                "city_district": "Wangen",
                "suburb": "Wangen",
                "road": "Ulmer Stra\u00dfe",
                "house_number": "241",
                "neighbourhood": "Wangen"
            },
            "address_more": {
                "building": "Kulturhaus ARENA"
            }
        },
        "sensors": [
            {
                "sensor_id": 658,
                "sensor_type": "SDS011"
            },
            {
                "sensor_id": 657,
                "sensor_type": "DHT22"
            }
        ]
    },
    {
        "station_id": 297,
        "name": "Hochschulstra\u00dfe, S\u00fcdvorstadt, Plauen, Dresden, Sachsen, DE",
        "position": {
            "latitude": 51.032,
            "longitude": 13.732,
            "altitude": 129.8,
            "country": "DE",
            "geohash": "u31f26p6fy33"
        },
        "location": {
            "place_id": "129637081",
            "licence": "Data \u00a9 OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
            "osm_type": "way",
            "osm_id": "226231020",
            "lat": "51.0317258",
            "lon": "13.732546611565",
            "display_name": "Kindergarten der Ev.-Luth. Lukaskirchgemeinde Dresden, 41, Hochschulstra\u00dfe, S\u00fcdvorstadt-Ost, S\u00fcdvorstadt, Plauen, Dresden, Sachsen, 01069, Deutschland",
            "boundingbox": [
                "51.0314671",
                "51.031926",
                "13.7319996",
                "13.7330698"
            ],
            "address": {
                "country_code": "DE",
                "country": "Deutschland",
                "state": "Sachsen",
                "postcode": "01069",
                "city": "Dresden",
                "city_district": "Plauen",
                "suburb": "S\u00fcdvorstadt",
                "road": "Hochschulstra\u00dfe",
                "house_number": "41",
                "neighbourhood": "S\u00fcdvorstadt-Ost"
            },
            "address_more": {
                "kindergarten": "Kindergarten der Ev.-Luth. Lukaskirchgemeinde Dresden"
            }
        },
        "sensors": [
            {
                "sensor_id": 622,
                "sensor_type": "SDS011"
            },
            {
                "sensor_id": 623,
                "sensor_type": "DHT22"
            },
            {
                "sensor_id": 624,
                "sensor_type": "BMP180"
            }
        ]
    }
]
15

Grafana Stationsauswahl

Für die Darstellung der vom Dashboard Feinstaub Verlauf Berlin bekannten Stationsauswahl im Grafana

image
image.png651×531 53.7 KB

per

luftdatenpumpe stations --station=28,297 --reverse-geocode --target=json.grafana+stream://sys.stdout

werden diese Informationen weiterhin kompakt zusammengefasst:

Grafana Datenmodell für Stationen 28 und 297 
[
    {
        "value": 28,
        "text": "Ulmer Stra\u00dfe, Wangen, Stuttgart, Baden-W\u00fcrttemberg, DE"
    },
    {
        "value": 297,
        "text": "Hochschulstra\u00dfe, S\u00fcdvorstadt, Plauen, Dresden, Sachsen, DE"
    }
]
16

Noch mehr Details

Für die Berechnung des Felds location_name haben wir gemeinsam mit @einsiedlerkrebs folgende format_address Routine entwickelt, die auf Basis der Nominatim Daten einen kompakteren Anzeigenamen berechnet als das bereits vom OSM Upstream kommende display_name. Sie muss dafür einige Anomalien ausgleichen und geht dazu heuristisch vor.

Ergebnis

"he_location_name": "Gerichtstraße, Gesundbrunnen, Mitte, Berlin, DE"
"osm_display_name": "17, Gerichtstraße, Gesundbrunnen, Mitte, Berlin, 13347, Deutschland"

"he_location_name": "Ulmer Straße, Wangen, Stuttgart, Baden-Württemberg, DE"
"osm_display_name": "Kulturhaus ARENA, 241, Ulmer Straße, Wangen, Stuttgart, Regierungsbezirk Stuttgart, Baden-Württemberg, 70327, Deutschland"

Na und?

Wir nehmen hier gerne Vorschläge zur Verbesserung entgegen, da wir diesen Namen zukünftig vermutlich auch über LDI hinaus einsetzen werden. Wie die berechneten Namen derzeit in der Praxis aussehen, sieht man in der LDI Stationslistengalerie.

1 Like
17

Update: Volle Stationsliste

RDBMS Adapter

Mit dem neuen RDBMS Adapter werden folgende Tabellen bestückt

Ausgabe für Stationen 28 und 1071

Table name: ldi_stations

{
    "id": 28,
    "name": "Ulmer Stra\u00dfe, Wangen, Stuttgart, Baden-W\u00fcrttemberg, DE",
    "latitude": 48.778,
    "longitude": 9.236,
    "altitude": 223.7,
    "country": "DE",
    "geohash": "u0wt6pv2qqhz"
}

{
    "id": 1071,
    "name": "Gerichtstra\u00dfe, Gesundbrunnen, Berlin, DE",
    "latitude": 52.544,
    "longitude": 13.374,
    "altitude": 38.7,
    "country": "DE",
    "geohash": "u33dbm6duz90"
}

Table name: ldi_sensors

{
    "sensor_id": 658,
    "station_id": 28,
    "sensor_type": "SDS011"
}

{
    "sensor_id": 657,
    "station_id": 28,
    "sensor_type": "DHT22"
}

{
    "sensor_id": 2129,
    "station_id": 1071,
    "sensor_type": "SDS011"
}

{
    "sensor_id": 2130,
    "station_id": 1071,
    "sensor_type": "DHT22"
}

Table name: ldi_osmdata

{
    "station_id": 28,
    "place_id": "101135850",
    "licence": "Data \u00a9 OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
    "osm_type": "way",
    "osm_id": "115374184",
    "lat": "48.777845",
    "lon": "9.23582396156841",
    "display_name": "Kulturhaus ARENA, 241, Ulmer Stra\u00dfe, Wangen, Stuttgart, Regierungsbezirk Stuttgart, Baden-W\u00fcrttemberg, 70327, Deutschland",
    "building": "Kulturhaus ARENA",
    "house_number": "241",
    "road": "Ulmer Stra\u00dfe",
    "neighbourhood": "Wangen",
    "suburb": "Wangen",
    "city_district": "Wangen",
    "city": "Stuttgart",
    "county": "Stuttgart",
    "state_district": "Regierungsbezirk Stuttgart",
    "state": "Baden-W\u00fcrttemberg",
    "postcode": "70327",
    "country": "Deutschland",
    "country_code": "DE"
}

{
    "station_id": 1071,
    "place_id": "81152066",
    "licence": "Data \u00a9 OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
    "osm_type": "way",
    "osm_id": "25686473",
    "lat": "52.54394525",
    "lon": "13.3738370010676",
    "display_name": "17, Gerichtstra\u00dfe, Gesundbrunnen, Mitte, Berlin, 13347, Deutschland",
    "building": null,
    "house_number": "17",
    "road": "Gerichtstra\u00dfe",
    "neighbourhood": null,
    "suburb": "Gesundbrunnen",
    "city_district": "Mitte",
    "city": "Berlin",
    "county": null,
    "state_district": null,
    "state": null,
    "postcode": "13347",
    "country": "Deutschland",
    "country_code": "DE"
}

Joins FTW

damit endlich normale Datenbankjoins möglich werden:

SELECT * 
  FROM ldi_stations, ldi_osmdata 
  WHERE ldi_stations.id = ldi_osmdata.station_id
Ausgabe für Stationen 28 und 1071 
{
    "id": 28,
    "name": "Ulmer Stra\u00dfe, Wangen, Stuttgart, Baden-W\u00fcrttemberg, DE",
    "latitude": 48.778,
    "longitude": 9.236,
    "altitude": 223.7,
    "country": "Deutschland",
    "geohash": "u0wt6pv2qqhz",
    "station_id": 28,
    "place_id": "101135850",
    "licence": "Data \u00a9 OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
    "osm_type": "way",
    "osm_id": "115374184",
    "lat": "48.777845",
    "lon": "9.23582396156841",
    "display_name": "Kulturhaus ARENA, 241, Ulmer Stra\u00dfe, Wangen, Stuttgart, Regierungsbezirk Stuttgart, Baden-W\u00fcrttemberg, 70327, Deutschland",
    "building": "Kulturhaus ARENA",
    "house_number": "241",
    "road": "Ulmer Stra\u00dfe",
    "neighbourhood": "Wangen",
    "suburb": "Wangen",
    "city_district": "Wangen",
    "city": "Stuttgart",
    "county": "Stuttgart",
    "state_district": "Regierungsbezirk Stuttgart",
    "state": "Baden-W\u00fcrttemberg",
    "postcode": "70327",
    "country_code": "DE"
}

{
    "id": 1071,
    "name": "Gerichtstra\u00dfe, Gesundbrunnen, Berlin, DE",
    "latitude": 52.544,
    "longitude": 13.374,
    "altitude": 38.7,
    "country": "Deutschland",
    "geohash": "u33dbm6duz90",
    "station_id": 1071,
    "place_id": "81152066",
    "licence": "Data \u00a9 OpenStreetMap contributors, ODbL 1.0. https://osm.org/copyright",
    "osm_type": "way",
    "osm_id": "25686473",
    "lat": "52.54394525",
    "lon": "13.3738370010676",
    "display_name": "17, Gerichtstra\u00dfe, Gesundbrunnen, Mitte, Berlin, 13347, Deutschland",
    "building": null,
    "house_number": "17",
    "road": "Gerichtstra\u00dfe",
    "neighbourhood": null,
    "suburb": "Gesundbrunnen",
    "city_district": "Mitte",
    "city": "Berlin",
    "county": null,
    "state_district": null,
    "state": null,
    "postcode": "13347",
    "country_code": "DE"
}
18

Preview: Grafana Stationslistengalerie für LDI

https://weather.hiveeyes.org/grafana/d/yDbjQ7Piz/stationslistengalerie-fur-ldi

1 Like
20

Ein weiterer Beitrag aus der Reihe »Daten ohne Datenbank um Rat fragen«:

Stationen mit mehr als vier Sensoren, absteigend nach Anzahl der Sensoren sortiert

luftdatenpumpe stations --reverse-geocode --progress | \
    jq 'map(select(.sensors | length >= 4)) | sort_by(.sensors | length) | reverse'

Stationen mit PPD42NS Sensor

luftdatenpumpe stations --reverse-geocode --progress | \
    jq 'map(select(.sensors | .[].sensor_type == "PPD42NS"))'

Liste verwendeter Sensortypen

luftdatenpumpe stations --progress | \
    jq '[ .[].sensors | .[].sensor_type ] | unique'

– ./jq

21 
[
  "BME280",
  "BMP180",
  "BMP280",
  "DHT11",
  "DHT22",
  "DS18B20",
  "DS18S20",
  "HPM",
  "HTU21D",
  "PMS3003",
  "PMS5003",
  "PMS7003",
  "PPD42NS",
  "SDS011",
  "SDS021"
]
1 Like
22

Stationslistengalerie

Auf Basis der Stationsdaten in der neuen PostgreSQL Datenbank lassen sich wie erhofft einige schöne Dinge im Grafana zaubern. Da man nun alle Einzelkomponenten der Adressinformation diskret zur Verfügung hat, kann man sie schön flexibel zur Laufzeit wieder zusammenstecken, um nach Belieben ausführlichere oder kompaktere Labels für Stationslisten und entsprechende Auswahlfelder zu erzeugen.

Im folgenden findet Ihr einige Dashboards zu Anschauungszwecken, zusammen mit den entsprechenden Variablen $ldi_station_* und den entsprechenden SQL Abfragen, mit denen Inhalte aus den Tabellen ldi_stations sowie ldi_osmdata der neuen PostgresSQL Datenbank eingespeist werden.

Demo #1: Überblick

LDI Stations #1 » Select by name, country and state

image
image.png1141×800 170 KB

Demo #1: Details

Hier haben wir die verwendeten Grafana Variablen und deren SQL Statement Definitionen zusammengeschrieben, damit man ein wenig besser unter die Haube schauen kann.

$ldi_station_canonical

SELECT 
    id AS __value, name AS __text 
FROM 
    ldi_stations 
ORDER BY 
    name

image

$ldi_station_countrycode

SELECT 
    DISTINCT(country) 
FROM 
    ldi_stations 
ORDER BY 
    country

image

$ldi_station_countryname

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

image

$ldi_station_reversed

SELECT 
    ldi_stations.id AS __value, 
    concat_ws('  »  ', ldi_osmdata.country_code, ldi_osmdata.state, ldi_osmdata.county, ldi_osmdata.city, ldi_osmdata.city_district, ldi_osmdata.suburb, ldi_osmdata.road) AS __text 
FROM 
    ldi_stations, ldi_osmdata 
WHERE 
    ldi_stations.id = ldi_osmdata.station_id 
ORDER BY 
    __text

image

$ldi_station_state

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

image

1 Like