[influxdb] Use semantic model for structuring InfluxDB storage #9556
Closed
Description
To make easier to build powerful graphic respresentations of measurements for huge amount of sensors the kind of storing measurement points into influxdb should be changed.
Making it easy to query for example all temperature measurements of groundfloor. Or all battery curves of first floor.
In my opinion this can be achieved very easily by using the "new" OH3 semantic model.
First there should be no measurement per item but by property type. Second there should be a tag with item name and one with item label. Third there should be tags representing the location tree of this item.
This would give in influxdb series like this:
Using database openhab
> show series from temperature
key
---
temperature,building=Schuppen,item=tempSchuppenAqara,label=Schuppen\ Aqara\ Sensor
temperature,floor=Aussen,item=tempAussen,label=Aussen,room=Terrasse
temperature,floor=Aussen,item=tempAussenGefuehlt,label=Gefühlte\ Temperatur,room=Terrasse
temperature,floor=Dachgeschoss,item=tempGaestezimmer,label=Danis\ Zimmer,room=Danis\ Zimmer
temperature,floor=Dachgeschoss,item=tempMusikzimmer,label=Musikzimmer,room=Musikzimmer
temperature,floor=Erdgeschoss,item=tempEsszimmer,label=Esszimmer,room=Esszimmer
temperature,floor=Erdgeschoss,item=tempFlurEG,label=Flur,room=Flur
temperature,floor=Erdgeschoss,item=tempHauptbadAqara,label=Hauptbad\ Aqara\ Sensor,room=Hauptbad
temperature,floor=Erdgeschoss,item=tempHauptbadGross,label=Hauptbad\ groß,room=Hauptbad
temperature,floor=Erdgeschoss,item=tempHauptbadKlein,label=Hauptbad\ klein,room=Hauptbad
temperature,floor=Erdgeschoss,item=tempWohnzimmerGross,label=Wohnzimmer\ groß,room=Wohnzimmer
temperature,floor=Erdgeschoss,item=tempWohnzimmerKlein,label=Wohnzimmer\ klein,room=Wohnzimmer
temperature,floor=Erdgeschoss,item=tempWohnzimmerNetatmo,label=Wohnzimmer\ Thermometer,room=Wohnzimmer
temperature,floor=Obergeschoss,item=tempArbeitszimmer,label=Arbeitszimmer,room=Arbeitszimmer
temperature,floor=Obergeschoss,item=tempDuschbadAqara,label=Duschbad\ Aqara\ Sensor,room=Duschbad
temperature,floor=Obergeschoss,item=tempDuschbadHeizung,label=Duschbad\ Heizung,room=Duschbad
temperature,floor=Obergeschoss,item=tempKinderzimmerAqara,label=Kinderzimmer\ Aqara\ Sensor,room=Kinderzimmer
temperature,floor=Obergeschoss,item=tempKinderzimmerHeizung,label=Kinderzimmer\ Heizung,room=Kinderzimmer
temperature,floor=Obergeschoss,item=tempSchlafzimmerGross,label=Schlafzimmer\ groß,room=Schlafzimmer
temperature,floor=Obergeschoss,item=tempSchlafzimmerKlein,label=Schlafzimmer\ klein,room=Schlafzimmer
temperature,floor=Obergeschoss,item=tempSchlafzimmerNetatmo,label=Schlafzimmer\ Thermometer,room=Schlafzimmer
And an single select to get all temperatures of a room or floor for example all of groundfloor:
SELECT moving_average(mean("value"), 5) FROM "temperature" WHERE ("floor" = 'Erdgeschoss') AND $timeFilter GROUP BY time($__interval), "label" fill(none)
Alias by $tag_label