Covid-19 und DIVI Bettenbelegung Dashboard

Für ein Dashboard habe ich die Covid-19 7-Tages-Inzidenzen und die Bettenbelegung der Intensivstationen beispielsweise in Stadt und Landkreis Würzburg visualisiert.

Die Zahlen kommen tagesaktuell vom Esri COVID-19 Datenhub und vom DIVI Intensivregister. https://npgeo-corona-npgeo-de.hub.arcgis.com https://www.intensivregister.de/#/aktuelle-lage/downloads

Bereitgestellt werden die Zahlen mit node-red-dashboard. https://user-images.githubusercontent.com/72078441/139655406-82004418-4fa6-4a3e-a306-81839fc83b92.jpg Die DIVI Zahlen kommen auch nochmal als Tabelle unter http://HOST:1880/covid

Für andere Landkreise kann die OBJECTID und der Gemeindeschlüssel geändert werden.

OBJECTID Landkarte: https://npgeo-corona-npgeo-de.hub.arcgis.com/datasets/917fc37a709542548cc3be077a786c17_0/explore?location=51.159939%2C10.714458%2C6.72

Gemeindeschlüssel: https://www.destatis.de/DE/Themen/Laender-Regionen/Regionales/Gemeindeverzeichnis/_inhalt.html

Copy[{"id":"25da5a46.655ac6","type":"tab","label":"COVID-19","disabled":false,"info":""},{"id":"de6144b8.125578","type":"http request","z":"25da5a46.655ac6","name":"Covid-19 LK Würzburg","method":"GET","ret":"obj","paytoqs":false,"url":"https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_Landkreisdaten/FeatureServer/0/query?where=OBJECTID=304&outFields=*&outSR=4326&f=json","tls":"","persist":false,"proxy":"","authType":"basic","x":320,"y":200,"wires":[["7dfbb3a.11c044c","140a4944.025cd7","aafe9537.35bb88"]]},{"id":"71d1019c.6448a","type":"comment","z":"25da5a46.655ac6","name":"covid-19 Live-Ticker für Würzburg","info":"Quelle\nhttps://npgeo-corona-npgeo-de.hub.arcgis.com/datasets/917fc37a709542548cc3be077a786c17_0\n\ncases7_per_100k 7 Tage Inzidenz \ncases7_bl_per_100k 7 Tage Inzidenz im Bundesland\n\nOBJECTID 295 = Stat Würzburg\nOBJECTID 304 = Stat Würzburg\n\nBsp. Aufruf für 304\nhttps://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_Landkreisdaten/FeatureServer/0/query?where=OBJECTID=304&outFields=*&outSR=4326&f=json","x":260,"y":80,"wires":[]},{"id":"7dfbb3a.11c044c","type":"debug","z":"25da5a46.655ac6","name":"LK","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":690,"y":200,"wires":[]},{"id":"140a4944.025cd7","type":"function","z":"25da5a46.655ac6","name":"LK Wue","func":"var LK = {};\nLK.payload = Math.round( msg.payload.features[0].attributes.cases7_per_100k );\nreturn LK;\n\n","outputs":1,"noerr":0,"x":610,"y":120,"wires":[["1cb9aadc.488285"]]},{"id":"6d1605ca.47937c","type":"http request","z":"25da5a46.655ac6","name":"Covid-19 SK Würzburg","method":"GET","ret":"obj","paytoqs":false,"url":"https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_Landkreisdaten/FeatureServer/0/query?where=OBJECTID=295&outFields=*&outSR=4326&f=json","tls":"","persist":false,"proxy":"","authType":"basic","x":320,"y":380,"wires":[["8d18c791.691d88","94d08487.6b2458"]]},{"id":"8d18c791.691d88","type":"function","z":"25da5a46.655ac6","name":"SK Wue","func":"var SK = {};\nSK.payload = Math.round( msg.payload.features[0].attributes.cases7_per_100k );\nreturn SK;\n\n","outputs":1,"noerr":0,"x":600,"y":480,"wires":[["6180d40b.63b39c"]]},{"id":"94d08487.6b2458","type":"debug","z":"25da5a46.655ac6","name":"SK","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":680,"y":380,"wires":[]},{"id":"1cb9aadc.488285","type":"ui_gauge","z":"25da5a46.655ac6","name":"LK","group":"c3788ad.efe3a78","order":3,"width":2,"height":2,"gtype":"gage","title":"<font color=#999999>LK","label":"","format":"{{value}}","min":0,"max":"300","colors":["#00b500","#e6e600","#ca3838"],"seg1":"49","seg2":"99","x":830,"y":80,"wires":[]},{"id":"6180d40b.63b39c","type":"ui_gauge","z":"25da5a46.655ac6","name":"SK","group":"c3788ad.efe3a78","order":1,"width":2,"height":2,"gtype":"gage","title":"<font color=#999999>WÜ","label":"","format":"{{value}}","min":0,"max":"300","colors":["#00b500","#e6e600","#ca3838"],"seg1":"49","seg2":"99","x":830,"y":480,"wires":[]},{"id":"6909ea64.927084","type":"inject","z":"25da5a46.655ac6","name":"5:00 Uhr","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"00 05 * * *","once":false,"onceDelay":0.1,"x":100,"y":280,"wires":[["6d1605ca.47937c","de6144b8.125578"]]},{"id":"cfb58c8d.618d5","type":"interval","z":"25da5a46.655ac6","name":"6 h","interval":"6","onstart":false,"msg":"ping","showstatus":true,"unit":"hours","statusformat":"YYYY-MM-D HH:mm:ss","x":80,"y":400,"wires":[["6d1605ca.47937c","de6144b8.125578"]]},{"id":"cc202235.e60a9","type":"ui_text","z":"25da5a46.655ac6","group":"c3788ad.efe3a78","order":2,"width":2,"height":1,"name":"Text","label":"","format":"<font size=0.1 color=#999999>{{msg.payload.datum}}","layout":"col-center","x":790,"y":260,"wires":[]},{"id":"cf498734.a74ee8","type":"inject","z":"25da5a46.655ac6","name":"2h","topic":"","payload":"","payloadType":"date","repeat":"7200","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":680,"wires":[["39a8610b.116ade"]]},{"id":"39a8610b.116ade","type":"http request","z":"25da5a46.655ac6","name":"","method":"GET","ret":"txt","paytoqs":false,"url":"https://diviexchange.blob.core.windows.net/%24web/DIVI_Intensivregister_Auszug_pro_Landkreis.csv","tls":"","persist":false,"proxy":"","authType":"","x":330,"y":680,"wires":[["98e884dc.794aa8"]]},{"id":"98e884dc.794aa8","type":"csv","z":"25da5a46.655ac6","name":"","sep":",","hdrin":true,"hdrout":"","multi":"mult","ret":"\\n","temp":"","skip":"0","strings":true,"x":530,"y":680,"wires":[["4edfa7d5.d6dde8"]]},{"id":"1fbda1b4.a091ae","type":"comment","z":"25da5a46.655ac6","name":"DIVI Bettenbelegung","info":"DIVI Intensivregister\n\nhttps://www.intensivregister.de/#/aktuelle-lage/reports\n\npayload[290] = WÜ\nGemeindeschlüssel 9-9663\npayload[299] = LK WÜ\nGemeindeschlüssel 9-9679","x":150,"y":580,"wires":[]},{"id":"48315413.2b385c","type":"http response","z":"25da5a46.655ac6","name":"","x":690,"y":1020,"wires":[]},{"id":"2c02faf7.f04606","type":"template","z":"25da5a46.655ac6","name":"HTML","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<html>\n  <head>\n    <title>Intensivbetten</title>\n    <meta name=\"covid\" content=\"initial-scale=1.0, user-scalable=yes\">\n    <meta charset=\"utf-8\">\n    <style>\n        table, th, td {\n        border: 1px solid black;\n        padding: 5px;\n        }\n\n</style>\n  </head>\n  <body style=\"background-color:#363636; font-family: verdana; font-size: 20\"\n\n<div align = \"center\">\n<font color=\"#03fc35\" size = \"50\">DIVI Bettenregister</font>\n</div>\n<p></p>\n<div>\n\n<table border = \"1\">\n  <tr align= \"center\">\n    <th></th>\n    <th><font color=\"#a1a3e3\" size = 20\">Covid</th>\n    <th><font color=\"#a1a3e3\" size = \"20\">beatmet</th>\n    <th><font color=\"#a1a3e3\" size = \"20\">belegt</th>\n    <th><font color=\"#a1a3e3\" size = \"20\">frei</th>\n</font>\n  </tr>\n  <tr align= \"center\">\n    <td><font color=\"#a1a3e3\" size = \"40\">WÜ</td>\n    <td><font color=\"#03fc35\" size = \"40\"> {{ payload.faelle_w }}</td>\n    <td><font color=\"#03fc35\" size = \"40\"> {{ payload.beatmet_w }}</td>\n    <td><font color=\"#03fc35\" size = \"40\"> {{ payload.bettenbelegt_w }}</td>\n    <td><font color=\"#03fc35\" size = \"40\"> {{ payload.bettenfrei_w }}</td>\n  </tr>\n  <tr align= \"center\">\n    <td><font color=\"#a1a3e3\" size = \"40\">LK</td>\n    <td><font color=\"#03fc35\" size = \"40\"> {{ payload.faelle_lk }}</td>\n    <td><font color=\"#03fc35\" size = \"40\"> {{ payload.beatmet_lk }}</td>\n    <td><font color=\"#03fc35\" size = \"40\"> {{ payload.bettenbelegt_lk }}</td>\n    <td><font color=\"#03fc35\" size = \"40\"> {{ payload.bettenfrei_lk }}</td>\n  </tr>\n  <tr align= \"center\">\n    <td><font color=\"#a1a3e3\" size = \"40\">&sum;</td>\n    <td><font color=\"#03fc35\" size = \"40\"> {{ payload.faelle_g }}</td>\n    <td><font color=\"#03fc35\" size = \"40\"> {{ payload.beatmet_g }}</td>\n    <td><font color=\"#03fc35\" size = \"40\"> {{ payload.bettenbelegt_g }}</td>\n    <td><font color=\"#03fc35\" size = \"40\"> {{ payload.bettenfrei_g }}</td>\n  </tr>\n</table>\n\n</div>\n\n<h5> <font color = \"#a1a3e3\" size = \"10\">\n {{ payload.time }}</h5>\n</body>\n</html>","x":490,"y":1020,"wires":[["48315413.2b385c"]]},{"id":"6b2bee1e.c5b9e","type":"http in","z":"25da5a46.655ac6","name":"","url":"/covid","method":"get","upload":false,"swaggerDoc":"","x":140,"y":1020,"wires":[["b3f7f243.6b5bc"]]},{"id":"cf66c510.d07e18","type":"mqtt out","z":"25da5a46.655ac6","name":"Covid","topic":"covid","qos":"1","retain":"","broker":"1e290fbd.f0fb1","x":970,"y":680,"wires":[]},{"id":"4edfa7d5.d6dde8","type":"function","z":"25da5a46.655ac6","name":"Bettenbelegung","func":"var newmsg = {};\n\nvar faelle = msg.payload[290].faelle_covid_aktuell + msg.payload[299].faelle_covid_aktuell;\nvar beatmet = msg.payload[290].faelle_covid_aktuell_invasiv_beatmet + msg.payload[299].faelle_covid_aktuell_invasiv_beatmet;\nvar bettenfrei = msg.payload[290].betten_frei_nur_erwachsen + msg.payload[299].betten_frei_nur_erwachsen;\nvar bettenbelegt = msg.payload[290].betten_belegt_nur_erwachsen + msg.payload[299].betten_belegt_nur_erwachsen;\n\nfaelle = faelle.toString();\nbeatmet = beatmet.toString();\nbettenfrei = bettenfrei.toString();\nbettenbelegt = bettenbelegt.toString();\n\n\nnewmsg.payload = {faelle_w: msg.payload[290].faelle_covid_aktuell, \n                  beatmet_w: msg.payload[290].faelle_covid_aktuell_invasiv_beatmet,\n                  bettenfrei_w: msg.payload[290].betten_frei_nur_erwachsen, \n                  bettenbelegt_w: msg.payload[290].betten_belegt_nur_erwachsen,\n                  \n                  time: msg.payload[290].daten_stand,\n                 \n                  faelle_lk: msg.payload[299].faelle_covid_aktuell, \n                  beatmet_lk: msg.payload[299].faelle_covid_aktuell_invasiv_beatmet,\n                  bettenfrei_lk: msg.payload[299].betten_frei_nur_erwachsen, \n                  bettenbelegt_lk: msg.payload[299].betten_belegt_nur_erwachsen,\n                  \n                  faelle_g: (msg.payload[290].faelle_covid_aktuell + msg.payload[299].faelle_covid_aktuell), \n                  beatmet_g: (msg.payload[290].faelle_covid_aktuell_invasiv_beatmet + msg.payload[299].faelle_covid_aktuell_invasiv_beatmet),\n                  bettenfrei_g: (msg.payload[290].betten_frei_nur_erwachsen + msg.payload[299].betten_frei_nur_erwachsen), \n                  bettenbelegt_g: (msg.payload[290].betten_belegt_nur_erwachsen + msg.payload[299].betten_belegt_nur_erwachsen),\n                  \n                  gesamt: (faelle + \"/\" + beatmet + \"/\" + bettenbelegt + \"/\" + bettenfrei),\n                  \n};\n\n\n\nreturn newmsg;","outputs":1,"noerr":0,"x":760,"y":680,"wires":[["cf66c510.d07e18"]]},{"id":"f6b578ee.220048","type":"mqtt in","z":"25da5a46.655ac6","name":"Covid","topic":"covid","qos":"2","datatype":"json","broker":"1e290fbd.f0fb1","x":130,"y":860,"wires":[["c06fe4f0.4a5f98","2294d629.7a0dfa"]]},{"id":"c06fe4f0.4a5f98","type":"debug","z":"25da5a46.655ac6","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":310,"y":800,"wires":[]},{"id":"2294d629.7a0dfa","type":"change","z":"25da5a46.655ac6","name":"Store covid","rules":[{"t":"set","p":"covid","pt":"flow","to":"payload","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":310,"y":860,"wires":[["b0ecc4a4.7e2148","24d5a548.4049ea"]]},{"id":"b0ecc4a4.7e2148","type":"debug","z":"25da5a46.655ac6","name":"msg.covid","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":500,"y":860,"wires":[]},{"id":"b3f7f243.6b5bc","type":"change","z":"25da5a46.655ac6","name":"Copy covid","rules":[{"t":"set","p":"payload","pt":"msg","to":"covid","tot":"flow"}],"action":"","property":"","from":"","to":"","reg":false,"x":330,"y":1020,"wires":[["2c02faf7.f04606"]]},{"id":"24d5a548.4049ea","type":"ui_text","z":"25da5a46.655ac6","group":"c3788ad.efe3a78","order":4,"width":2,"height":1,"name":"DIVI","label":"","format":"<font size=0.5 color=#e6e600>{{msg.payload.gesamt}}","layout":"row-left","x":590,"y":920,"wires":[]},{"id":"aafe9537.35bb88","type":"function","z":"25da5a46.655ac6","name":"Datum kürzen","func":"var newmsg = {};\n\nvar lang = msg.payload.features[0].attributes.last_update;\nvar kurz = lang.substring(0,10);\n\nnewmsg.payload = {datum: kurz };\nreturn newmsg;","outputs":1,"noerr":0,"x":580,"y":260,"wires":[["cc202235.e60a9"]]},{"id":"c3788ad.efe3a78","type":"ui_group","z":"","name":"Covid-19","tab":"6dd6fbb7.f2a4b4","order":1,"disp":false,"width":"6","collapse":false},{"id":"1e290fbd.f0fb1","type":"mqtt-broker","z":"","name":"Raspi","broker":"raspberrypi.fritz.box","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"6dd6fbb7.f2a4b4","type":"ui_tab","z":"","name":"Tardis","icon":"dashboard","order":1,"disabled":false,"hidden":false}]