Navigation

    Logo
    • Register
    • Login
    • Search
    • Recent
    • Tags
    • Unread
    • Categories
    • Unreplied
    • Popular
    • GitHub
    • Docu
    • Hilfe
    1. Home
    2. Deutsch
    3. Skripten / Logik
    4. JavaScript
    5. Corona-Daten nach MySQL importieren

    NEWS

    • Neuer Blog: Fotos und Eindrücke aus Solingen

    • ioBroker@Smart Living Forum Solingen, 14.06. - Agenda added

    • ioBroker goes Matter ... Matter Adapter in Stable

    Corona-Daten nach MySQL importieren

    This topic has been deleted. Only users with topic management privileges can see it.
    • a200
      a200 @sissiwup last edited by

      @sissiwup coole Sache. bei mir bekomme ich ich aber Probleme bei der Abfrage

      SELECT sum(R_Fall) as fall, sum(R_Tote) as Tote, R_Landkreis FROM `cor_view` where R_Fall>0 group by ID_L order by fall DESC
      
      #1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cor_view.R_Landkreis' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
      
      sissiwup 1 Reply Last reply Reply Quote 0
      • sissiwup
        sissiwup @a200 last edited by

        @a200 sagte in Corona-Daten nach MySQL importieren:

        @sissiwup coole Sache. bei mir bekomme ich ich aber Probleme bei der Abfrage

        SELECT sum(R_Fall) as fall, sum(R_Tote) as Tote, R_Landkreis FROM `cor_view` where R_Fall>0 group by ID_L order by fall DESC
        
        #1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cor_view.R_Landkreis' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
        

        Hallo,

        dann probier bitte mal group by R_Landkreis

        haselchen 1 Reply Last reply Reply Quote 0
        • haselchen
          haselchen Most Active @sissiwup last edited by

          @sissiwup

          Welche Gemeinde in der Region Hannover? 😉

          sissiwup 1 Reply Last reply Reply Quote 0
          • sissiwup
            sissiwup @haselchen last edited by

            @haselchen sagte in Corona-Daten nach MySQL importieren:

            @sissiwup

            Welche Gemeinde in der Region Hannover? 😉

            Dicht dabei, Schaumburg.

            haselchen 1 Reply Last reply Reply Quote 0
            • haselchen
              haselchen Most Active @sissiwup last edited by

              @sissiwup

              Dachte Du wärst ein bisschen näher 🙂
              Trotzdem ein Gruß aus der Herzogstadt 92km entfernt 🙂

              1 Reply Last reply Reply Quote 0
              • a200
                a200 last edited by a200

                @sissiwup kannst du deine grafana dashboards zur Verfügung stellen? Es wäre einfacher die dann zu editieren als sie erstmal abtippen zu müssen. Großer Dank für deine Arbeit.

                und wo bekomme ich die Kreise vollständig? In der kreise.sql Datei sind lediglich 25 Kreise vorhanden.

                sissiwup 1 Reply Last reply Reply Quote 0
                • sissiwup
                  sissiwup @a200 last edited by

                  @a200
                  Anbei die vollständige SQL:
                  kreise.sql

                  Und das Dashboard:
                  (die Variablen müssen vermutlich angepaßt werden)
                  ioBroker Corona-1585949802227.json

                  sissiwup 1 Reply Last reply Reply Quote 0
                  • sissiwup
                    sissiwup @sissiwup last edited by

                    Berechnung von geheilten (wie RKI vorschlägt) 14 Tage nach Meldung

                    SELECT
                      unix_timestamp(R_MeldeDatum) as time_sec,
                      (select sum(R_FALL) from cor_view i 
                       where unix_timestamp(i.R_MeldeDatum)<=unix_timestamp(v.R_MeldeDatum)-1209600 and K_SKreis = "Schaumburg") as val,
                      "Genesen" as metric
                    FROM cor_view v
                    WHERE $__unixEpochFrom()<unix_timestamp(R_MeldeDatum) and $__unixEpochTo()>unix_timestamp(R_MeldeDatum)
                    and K_Skreis = "Schaumburg"
                    group by R_MeldeDatum  
                    ORDER BY R_MeldeDatum ASC 
                    

                    1209600 = 14 * 24 * 60 * 60

                    sissiwup 1 Reply Last reply Reply Quote 0
                    • sissiwup
                      sissiwup @sissiwup last edited by sissiwup

                      Hallo,

                      wenn man anstelle eines View eine Tabelle verwenden möchte (ist etwas schneller), dann hilft folgendes:

                      (Update 4.4. Delete Zahlen<0)

                      createZiel.txt

                      DROP TABLE IF EXISTS cor_view;
                      
                      CREATE TABLE cor_view AS
                      SELECT
                      r.IDBundesLand as ID_B, r.IDLandkreis as ID_L,SUBSTRING(r.MeldeDatum,1,10) as R_MeldeDatum, r.ObjectID as ID_R,
                      r.Bundesland as R_Bundesland, r.Landkreis as R_Landkreis, r.Altersgruppe as R_Alter, r.Geschlecht as R_Geschl, r.AnzahlFall as R_Fall, r.AnzahlTodesfall as R_Tote, r.Datenstand as
                       R_Datenstand, r.NeuerFall as R_Neuerfall, r.NeuerTodesFall as NeuerTodesFall,
                      b.LAN_ew_EWZ as B_Einwohner,b.FallZahl as B_Fallzahl,b.Death as B_Tote,
                      l.EWZ as L_Einwohner,l.KFL as L_Flaeche, l.death_rate as L_TodesRate, l.cases as L_Faelle, l.deaths as L_Tote, l.cases_per_100k as L_Faelle_pro_100000,l.cases_per_population as L_
                      Faelle_pro_Bevoelkerung,
                      k.skreis as K_SKreis, k.bevoelkerung as K_Bevoelkerung, k.maenner as K_Maenner,k.frauen as K_Frauen, k.dichte as K_Dichte
                      FROM
                      cor_rki r,cor_bundesland b,cor_landkreise l, kreise k
                      where r.IdLandkreis=l.RS and r.IdBundesland=b.id and r.IdLandkreis=k.id
                      and r.AnzahlFall>0
                      order by ID_B,ID_L,R_MeldeDatum;
                      
                      update cor_view set R_Tote=0 where R_Tote<0;
                      
                      ALTER TABLE `cor_view`
                        ADD UNIQUE KEY `PRIME` (`ID_B`,`ID_L`,`R_MeldeDatum`,`ID_R`) USING BTREE,
                        ADD KEY `I1` (`R_MeldeDatum`,`R_Bundesland`,`R_Fall`,`R_Tote`),
                        ADD KEY `I2` (`R_MeldeDatum`,`R_Landkreis`,`R_Fall`,`R_Tote`),
                        ADD KEY `I3` (`R_MeldeDatum`,`K_SKreis`,`R_Fall`,`R_Tote`);
                      COMMIT;
                      

                      Das Batch Skript sieht dann so aus:

                      #!/bin/bash
                      NOW=`date +"%d.%m.%g %H:%M.%S"`
                      NOWDAT=`date +"%d_%m_%g"`
                      USER=DBUSER
                      PASS=DBPASSWORD
                      
                      rm /var/skripte/data/*.csv
                      wget -O /var/skripte/data/cor_rki.csv https://opendata.arcgis.com/datasets/dd4580c810204019a7b8eb3e0b329dd6_0.csv
                      wget -O /var/skripte/data/cor_landkreise.csv https://opendata.arcgis.com/datasets/917fc37a709542548cc3be077a786c17_0.csv
                      wget -O /var/skripte/data/cor_bundesland.csv https://opendata.arcgis.com/datasets/ef4b445a53c1406892257fe63129a8ea_0.csv
                      
                      cp /var/skripte/data/cor_rki.csv /var/skripte/data/rki_$NOWDAT.csv.backup
                      cp /var/skripte/data/cor_landkreise.csv /var/skripte/data/landkreise_$NOWDAT.csv.backup
                      cp /var/skripte/data/cor_bundesland.csv /var/skripte/data/bundesland_$NOWDAT.csv.backup
                      
                      #mysql -u $USER -p$PASS iobroker < /var/skripte/data/createTable.txt
                      
                      mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_rki.csv
                      mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_landkreise.csv
                      mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_bundesland.csv
                      
                      mysql -u $USER -p$PASS iobroker < /var/skripte/data/createZiel.txt
                      
                      1 Reply Last reply Reply Quote 1
                      • a200
                        a200 last edited by a200

                        @sissiwup cool, das sieht schon besser aus. Jetzt meckert grafana über eine Tabelle datapoints, die sie nicht finden kann. Kannst du auch hier helfen?
                        Erledigt. Das lag daran, dass ich in der DB nur deine Daten habe und die aus iobroker nicht.

                        sissiwup 1 Reply Last reply Reply Quote 0
                        • sissiwup
                          sissiwup @a200 last edited by sissiwup

                          Die csv-Datei für rki ist nicht mehr komplett ladbar (vermutlich ein Fehler auf der Seite)

                          PS: Datenstand in der cor_rki Tabelle muss 22 Zeichen lang sein...

                          Hier eine kleine Umgehungslösung:
                          rkijson.py

                          import csv, json
                          import datetime as dt
                          import requests
                          
                          outfile = r'/var/skripte/data/cor_rki.csv'
                          
                          count = "https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_COVID19/FeatureServer/0/query?where=1%3D1&outFields=*&returnCountOnly=true&f=pjson"
                          anz = requests.get(count)
                          anz_json = json.loads(anz.text)
                          anzahl = anz_json['count']
                          print("Zeilen:" + str(anzahl))
                          
                          ofile = open(outfile, 'w+')
                          output = csv.writer(ofile)
                          
                          search1 = "https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_COVID19/FeatureServer/0/query?where=1%3D1&outFields=*&resultOffset="
                          search2 = "&resultRecordCount=1000&f=pjson"
                          i = 0
                          while i < anzahl:
                              print("Readfile:" + str((int)(i / 1000)) + " von " + str((int)(int(anzahl) / 1000)))
                              s_str = search1 + str(i) + search2
                              x = requests.get(s_str)
                              data = json.loads(x.text)
                          
                              daten = data['features']
                              for row in daten:
                                  zeit = row['attributes']['Meldedatum'] / 1000
                                  row['attributes']['Meldedatum'] = dt.datetime.utcfromtimestamp(zeit).strftime(
                                      "%Y-%m-%dT%H:%M:%S.000Z")
                              if i == 0:
                                  output.writerow(daten[0]['attributes'].keys())
                              for row in daten:
                                  output.writerow(row['attributes'].values())
                              i = i + 1000
                          
                          

                          Das Downloadskript sieht dann wie folgt aus:

                          #!/bin/bash
                          NOW=`date +"%d.%m.%g %H:%M.%S"`
                          NOWDAT=`date +"%d_%m_%g"`
                          USER=DBUSER
                          PASS=DBPASSWORT
                          
                          rm /var/skripte/data/cor*.csv
                          #wget -O /var/skripte/data/cor_rki.csv https://opendata.arcgis.com/datasets/dd4580c810204019a7b8eb3e0b329dd6_0.csv
                          python3 -u /var/skripte/rkijson.py
                          wget -O /var/skripte/data/cor_landkreise.csv https://opendata.arcgis.com/datasets/917fc37a709542548cc3be077a786c17_0.csv
                          wget -O /var/skripte/data/cor_bundesland.csv https://opendata.arcgis.com/datasets/ef4b445a53c1406892257fe63129a8ea_0.csv
                          
                          cp /var/skripte/data/cor_rki.csv /var/skripte/data/rki_$NOWDAT.csv.backup
                          cp /var/skripte/data/cor_landkreise.csv /var/skripte/data/landkreise_$NOWDAT.csv.backup
                          cp /var/skripte/data/cor_bundesland.csv /var/skripte/data/bundesland_$NOWDAT.csv.backup
                          
                          #mysql -u $USER -p$PASS iobroker < /var/skripte/data/createTable.txt
                          
                          mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_rki.csv
                          mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_landkreise.csv
                          mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_bundesland.csv
                          
                          mysql -u $USER -p$PASS iobroker < /var/skripte/data/createZiel.txt
                          
                          sissiwup 1 Reply Last reply Reply Quote 0
                          • sissiwup
                            sissiwup @sissiwup last edited by sissiwup

                            Hier noch die JHU Daten:

                            Skript:

                            #!/bin/bash
                            NOW=`date +"%d.%m.%g %H:%M.%S"`
                            NOWDAT=`date +"%d_%m_%g"`
                            USER=DBUSER
                            PASS=DBPASSWORT
                            
                            rm /var/skripte/data/jhu*.csv
                            wget -O /var/skripte/data/jhu_fall.csv https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_
                            global.csv
                            wget -O /var/skripte/data/jhu_tote.csv https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_glo
                            bal.csv
                            wget -O /var/skripte/data/jhu_genesen.csv https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recover
                            ed_global.csv
                            
                            cp /var/skripte/data/jhu_fall.csv /var/skripte/data/fall_$NOWDAT.csv.backup
                            cp /var/skripte/data/jhu_tote.csv /var/skripte/data/tote_$NOWDAT.csv.backup
                            cp /var/skripte/data/jhu_genesen.csv /var/skripte/data/genesen_$NOWDAT.csv.backup
                            
                            python3 -u /var/skripte/convertJHU.py
                            
                            mysql -u $USER -p$PASS iobroker < /var/skripte/data/createJHU.txt
                            
                            mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_jhu.csv
                            
                            mysql -u $USER -p$PASS iobroker < /var/skripte/data/updateJHU.txt
                            

                            und das convertJHU.py

                            import csv
                            import datetime as dt
                            
                            outfile = r'/var/skripte/data/cor_jhu.csv'
                            infile1 = r'/var/skripte/data/jhu_fall.csv'
                            infile2 = r'/var/skripte/data/jhu_genesen.csv'
                            infile3 = r'/var/skripte/data/jhu_tote.csv'
                            
                            now = dt.date.today()
                            
                            
                            def conv_date(ind):
                                val = ind.split("/")
                                txt = "20" + val[2] + "-" + ("0" + val[0])[-2:] + "-" + ("0" + val[1])[-2:]
                                return txt
                            
                            
                            def read_files(csv_reader, fall_art):
                                for row in csv_reader:
                                    keys = list(row.keys())
                                    for subkey in keys[4:]:
                                        if row["Country/Region"] == "Canada":
                                            row["Province/State"] = ""
                                        nice_key = conv_date(subkey)
                                        key = nice_key + ":" + row["Country/Region"] + ":" + row["Province/State"]
                                        # print("Key=" + key)
                                        if key not in countrys.keys():
                                            lists = {"Meldedatum": nice_key, "Land": row["Country/Region"],
                                                     "Bundesland": row["Province/State"], "Import": now, fall_art: row[subkey]}
                                            # print("List=" + str(lists))
                                            countrys[key] = lists
                                            # print("C=" + str(countrys))
                                        else:
                                            lists = countrys[key]
                                            if fall_art not in lists.keys():
                                                lists[fall_art] = row[subkey]
                                            else:
                                                lists[fall_art] = row[subkey] + lists[fall_art]
                                            countrys[key] = lists
                            
                            
                            countrys = {}
                            
                            in_f = open(infile1)
                            csv_reader = csv.DictReader(in_f)
                            read_files(csv_reader, "fall")
                            
                            in_f = open(infile2)
                            csv_reader = csv.DictReader(in_f)
                            read_files(csv_reader, "genesen")
                            
                            in_f = open(infile3)
                            csv_reader = csv.DictReader(in_f)
                            read_files(csv_reader, "tote")
                            
                            print(len(countrys))
                            # print(countrys)
                            
                            # for row in list(countrys.values())[:10]:
                            #    print(row)
                            
                            out_f = open(outfile, "w+")
                            fields = ["Meldedatum", "Land", "Bundesland", "fall", "genesen", "tote", "fall_tag" ,"genesen_tag" ,"tote_tag" ,"Import"]
                            csv_writer = csv.DictWriter(out_f, fieldnames=fields)
                            csv_writer.writerow(dict((fn, fn) for fn in fields))
                            for row in list(countrys.values())[:]:
                                csv_writer.writerow(row)
                            

                            Canada wird gesondert
                            behandelt, da genesene nur für Gesamtcanada vorhanden sind.

                            Und die Tabelle createJHU.txt:

                            CREATE TABLE IF NOT EXISTS cor_jhu(
                              Meldedatum    varchar(10) NOT NULL,
                              Land          varchar(50) NOT NULL,
                              Bundesland    varchar(50) DEFAULT NULL,
                              fall          int(11) NOT NULL,
                              genesen       int(11) NOT NULL,
                              tote          int(11) NOT NULL,  
                              fall_tag      int(11) NOT NULL,
                              genesen_tag   int(11) NOT NULL,
                              tote_tag      int(11) NOT NULL,
                              Import        varchar(10) NOT NULL
                            ) DEFAULT CHARACTER SET = UTF8;
                            
                            ALTER TABLE `cor_jhu`
                              ADD UNIQUE KEY `U1` (`Meldedatum`,`Land`,`Bundesland`),
                              ADD UNIQUE KEY `U2` (`Land`,`Bundesland`,`Meldedatum`),
                              ADD KEY `I1` (`Land`,`Meldedatum`,`fall`),
                              ADD KEY `I2` (`Land`,`Meldedatum`,`genesen`),
                              ADD KEY `I3` (`Land`,`Meldedatum`,`tote`);
                            COMMIT;
                            
                            truncate cor_jhu;
                            

                            Und die SQL-Korrektur updateJHU.txt:

                            update cor_jhu set land="Korea",bundesland="South" where land like "%Korea%" and Bundesland like "%South%";
                            
                            update cor_jhu j1,cor_jhu j2 set j1.fall_tag=j1.fall-j2.fall,j1.tote_tag=j1.tote-j2.tote,j1.genesen_tag=j1.genesen-j2.genesen WHERE
                            j1.land=j2.land and j1.bundesland=j2.bundesland and date_sub(j1.meldedatum,interval 1 day)=j2.meldedatum and j1.meldedatum>date("2020-01-22");
                            
                            COMMIT
                            
                            sissiwup 1 Reply Last reply Reply Quote 0
                            • sissiwup
                              sissiwup @sissiwup last edited by

                              Hier mal ein Vergleich JHU und RKI:

                              Bildschirmfoto 2020-04-05 um 18.18.46.png

                              sissiwup 1 Reply Last reply Reply Quote 0
                              • sissiwup
                                sissiwup @sissiwup last edited by

                                Hallo,

                                und hier mal ein paar Auswertungen auf den JHU_daten:

                                Bildschirmfoto 2020-04-06 um 01.08.42.png

                                Bildschirmfoto 2020-04-06 um 01.09.50.png

                                iobroker Corona Welt-1586128248813.json

                                1 Reply Last reply Reply Quote 0
                                • frankjoke
                                  frankjoke @sissiwup last edited by

                                  @sissiwup und Kollegen!

                                  Bin jetzt 2 in Quarantäne gewesen und seit dem Wochenende zu Hause.

                                  Hatte nur ein altes Chrome-book mit mir welches keine linux-Befehle erlaubte und einen neuen 4GB Raspi den ich mir vorher noch gekauft hatte. Ich habe wegen meiner fehlenden Testmöglichkeiten kein iobroker-adapter geschrieben aber eine kleine web-app mit der die Krankheitsverlaufkurve von Staaten angezeigt und verglöichen werden kann.

                                  Ihr könnt die App direkt auf git anschauen: https://frankjoke.github.io/coronafj/
                                  Das repo dazu ist https://github.com/frankjoke/coronafj

                                  Es gibt einen chart tab und einen list tab, sonst sollte es selbsterklärend sein.
                                  Da ich das für meine Kollegen aus allen Ländern gemacht habe ist's halt in Englisch, aber man kann sehr schön die Unterschiedliche Entwicklung der Länder sehen!

                                  Übrigens, bin ab jetzt in Pension (oder Rente wie ihr sagen würdet) und hoffe mich bald wieder meinen iobroker-Adaptern widmen zu können, wir dürfen sowieso nicht wirklich rausgehen hier in A!

                                  p.s.: verwende eine api verwendet die von da https://gisanddata.maps.arcgis.com/apps/opsdashboard/index.html#/bda7594740fd40299423467b48e9ecf6 gespeist wird.

                                  sigi234 2 Replies Last reply Reply Quote 1
                                  • sigi234
                                    sigi234 Forum Testing Most Active @frankjoke last edited by

                                    @frankjoke sagte in Corona-Daten nach MySQL importieren:

                                    Übrigens, bin ab jetzt in Pension

                                    Gratuliere Dir. 😀

                                    1 Reply Last reply Reply Quote 0
                                    • sigi234
                                      sigi234 Forum Testing Most Active @frankjoke last edited by

                                      @frankjoke

                                      Cool, App läuft einwandfrei! 👍

                                      sissiwup 1 Reply Last reply Reply Quote 0
                                      • sissiwup
                                        sissiwup @sigi234 last edited by

                                        Hallo,

                                        habe bei JHU die Tabelle verbreitert, damit auch die Tageswerte da sind und habe Süd-Korea repariert (da hier ein Fehler in den Daten ist)

                                        sissiwup 1 Reply Last reply Reply Quote 0
                                        • sissiwup
                                          sissiwup @sissiwup last edited by sissiwup

                                          RKI hat neue Werte hinzugefügt. RefDatum (da wo die Erkrankung aufgetreten ist) und Zahlen für genesen:

                                          getCorona.sh:

                                          NOW=`date +"%d.%m.%g %H:%M.%S"`
                                          NOWDAT=`date +"%d_%m_%g"`
                                          USER=DBUSER
                                          PASS=DBPASSWORD
                                          
                                          rm /var/skripte/data/cor*.csv
                                          #wget -O /var/skripte/data/cor_rki.csv https://opendata.arcgis.com/datasets/dd4580c810204019a7b8eb3e0b329dd6_0.csv
                                          python3 -u /var/skripte/rkijson.py
                                          wget -O /var/skripte/data/cor_landkreise.csv https://opendata.arcgis.com/datasets/917fc37a709542548cc3be077a786c17_0.csv
                                          wget -O /var/skripte/data/cor_bundesland.csv https://opendata.arcgis.com/datasets/ef4b445a53c1406892257fe63129a8ea_0.csv
                                          
                                          cp /var/skripte/data/cor_rki.csv /var/skripte/data/rki_$NOWDAT.csv.backup
                                          cp /var/skripte/data/cor_landkreise.csv /var/skripte/data/landkreise_$NOWDAT.csv.backup
                                          cp /var/skripte/data/cor_bundesland.csv /var/skripte/data/bundesland_$NOWDAT.csv.backup
                                          
                                          #mysql -u $USER -p$PASS iobroker < /var/skripte/data/createTable.txt
                                          
                                          mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_rki.csv
                                          mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_landkreise.csv
                                          mysqlimport --fields-terminated-by=, --ignore-lines=1 --verbose --delete --local -u $USER -p$PASS iobroker /var/skripte/data/cor_bundesland.csv
                                          
                                          mysql -u $USER -p$PASS iobroker < /var/skripte/data/createZiel.txt
                                          

                                          rkijson.py:

                                          import csv, json
                                          import datetime as dt
                                          import requests
                                          
                                          outfile = r'/var/skripte/data/cor_rki.csv'
                                          
                                          count = "https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_COVID19/FeatureServer/0/query?where=1%3D1&outFields=*&returnCountOnly=true&f=pjson"
                                          anz = requests.get(count)
                                          anz_json = json.loads(anz.text)
                                          anzahl = anz_json['count']
                                          print("Zeilen:" + str(anzahl))
                                          
                                          ofile = open(outfile, 'w+')
                                          output = csv.writer(ofile)
                                          
                                          search1 = "https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_COVID19/FeatureServer/0/query?where=1%3D1&outFields=*&resultOffset="
                                          search2 = "&resultRecordCount=1000&f=pjson"
                                          i = 0
                                          while i < anzahl:
                                              print("Readfile:" + str((int)(i / 1000)) + " von " + str((int)(int(anzahl) / 1000)))
                                              s_str = search1 + str(i) + search2
                                              x = requests.get(s_str)
                                              data = json.loads(x.text)
                                          
                                              daten = data['features']
                                              for row in daten:
                                                  zeit = row['attributes']['Meldedatum'] / 1000
                                                  row['attributes']['Meldedatum'] = dt.datetime.utcfromtimestamp(zeit).strftime(
                                                      "%Y-%m-%dT%H:%M:%S.000Z")
                                                  meld = row['attributes']['Refdatum'] / 1000
                                                  row['attributes']['Refdatum'] = dt.datetime.utcfromtimestamp(meld).strftime(
                                                      "%Y-%m-%dT%H:%M:%S.000Z")
                                                  dat = row['attributes']['Datenstand']
                                                  row['attributes']['Datenstand'] = dat.replace(",",":")
                                              if i == 0:
                                                  output.writerow(daten[0]['attributes'].keys())
                                              for row in data['features']:
                                                  output.writerow(row['attributes'].values())
                                              i = i + 1000
                                          

                                          createTable.txt:

                                          CREATE TABLE IF NOT EXISTS cor_rki(
                                             IdBundesland    INTEGER  NOT NULL
                                            ,Bundesland      VARCHAR(44) NOT NULL
                                            ,Landkreis       VARCHAR(44) NOT NULL
                                            ,Altersgruppe    VARCHAR(9) NOT NULL
                                            ,Geschlecht      VARCHAR(9) NOT NULL
                                            ,AnzahlFall      INTEGER  NOT NULL
                                            ,AnzahlTodesfall INTEGER  NOT NULL
                                            ,ObjectId        INTEGER  NOT NULL PRIMARY KEY
                                            ,Meldedatum      VARCHAR(24) NOT NULL
                                            ,IdLandkreis     VARCHAR(5) NOT NULL
                                            ,Datenstand      VARCHAR(22) NOT NULL
                                            ,NeuerFall       INTEGER  NOT NULL
                                            ,NeuerTodesfall  INTEGER  NOT NULL
                                            ,Refdatum        VARCHAR(24) NOT NULL
                                            ,NeuGenesen      INTEGER  NOT NULL
                                            ,AnzahlGenesen   INTEGER  NOT NULL
                                          ) DEFAULT CHARACTER SET = UTF8;
                                          
                                          ALTER TABLE `cor_rki`
                                            ADD KEY `Meldedatum` (`Meldedatum`),
                                            ADD KEY `IdLandkreis` (`IdLandkreis`),
                                            ADD KEY `Datenstand` (`Datenstand`),
                                            ADD KEY `Refdatum` (`Refdatum`);
                                          COMMIT;
                                          
                                          CREATE TABLE IF NOT EXISTS cor_landkreise(
                                             OBJECTID             INTEGER  NOT NULL PRIMARY KEY
                                            ,ADE                  INTEGER
                                            ,GF                   INTEGER
                                            ,BSG                  BIT
                                            ,RS                   VARCHAR(5) NOT NULL
                                            ,AGS                  VARCHAR(5)
                                            ,SDV_RS               VARCHAR(11)
                                            ,GEN                  VARCHAR(44) NOT NULL
                                            ,BEZ                  VARCHAR(44) NOT NULL
                                            ,IBZ                  INTEGER
                                            ,BEM                  VARCHAR(13)
                                            ,NBD                  VARCHAR(4)
                                            ,SN_L                 INTEGER
                                            ,SN_R                 INTEGER
                                            ,SN_K                 INTEGER
                                            ,SN_V1                INTEGER
                                            ,SN_V2                INTEGER
                                            ,SN_G                 INTEGER
                                            ,FK_S3                VARCHAR(1)
                                            ,NUTS                 VARCHAR(5)
                                            ,RS_0                 INTEGER
                                            ,AGS_0                INTEGER
                                            ,WSK                  VARCHAR(23)
                                            ,EWZ                  INTEGER  NOT NULL
                                            ,KFL                  NUMERIC(7,2)
                                            ,DEBKG_ID             VARCHAR(16)
                                            ,Shape_Area           NUMERIC(17,7) NOT NULL
                                            ,Shape_Length         NUMERIC(17,10) NOT NULL
                                            ,death_rate           NUMERIC(17,15) NOT NULL
                                            ,cases                INTEGER  NOT NULL
                                            ,deaths               INTEGER  NOT NULL
                                            ,cases_per_100k       NUMERIC(17,14) NOT NULL
                                            ,cases_per_population NUMERIC(19,17) NOT NULL
                                            ,BL                   VARCHAR(22) NOT NULL
                                            ,BL_ID                INTEGER  NOT NULL
                                            ,county               VARCHAR(36) NOT NULL
                                            ,last_update          VARCHAR(16) NOT NULL
                                          ) DEFAULT CHARACTER SET = UTF8;
                                          
                                          ALTER TABLE `cor_landkreise`
                                            ADD KEY `RS` (`RS`);
                                          COMMIT;
                                          
                                          CREATE TABLE IF NOT EXISTS cor_bundesland(
                                             ID               INTEGER  NOT NULL PRIMARY KEY
                                            ,LAN_ew_AGS       INTEGER  NOT NULL
                                            ,LAN_ew_GEN       VARCHAR(44) NOT NULL
                                            ,LAN_ew_BEZ       VARCHAR(44) NOT NULL
                                            ,LAN_ew_EWZ       INTEGER  NOT NULL
                                            ,OBJECTID         INTEGER  NOT NULL
                                            ,Fallzahl         INTEGER  NOT NULL
                                            ,Aktualisierung   VARCHAR(24) NOT NULL
                                            ,AGS_TXT          INTEGER  NOT NULL
                                            ,GlobalID         VARCHAR(36) NOT NULL
                                            ,faelle_100000_EW NUMERIC(16,13) NOT NULL
                                            ,Shape_Area       NUMERIC(17,5) NOT NULL
                                            ,Shape_Length     NUMERIC(16,9) NOT NULL
                                            ,Death            INTEGER  NOT NULL
                                          ) DEFAULT CHARACTER SET = UTF8;
                                          
                                          truncate cor_rki;
                                          truncate cor_bundesland;
                                          truncate cor_landkreise;
                                          

                                          createZiel.txt:
                                          Achtung falls ihr die Tabelle cor_datum nicht verwendet, dann die Bezüge hier löschen (Tabelle siehe nächste Post)

                                          DROP TABLE IF EXISTS cor_view;
                                          
                                          CREATE TABLE cor_view AS
                                          SELECT
                                          r.IDBundesLand as ID_B, r.IDLandkreis as ID_L,SUBSTRING(r.MeldeDatum,1,10) as R_MeldeDatum, r.ObjectID as ID_R,
                                          r.Bundesland as R_Bundesland, r.Landkreis as R_Landkreis, r.Altersgruppe as R_Alter, r.Geschlecht as R_Geschl, r.AnzahlFall as R_Fall, r.AnzahlTodesfall as R_Tote, r.Datenstand as
                                           R_Datenstand, r.NeuerFall as R_Neuerfall, r.NeuerTodesFall as R_NeuerTodesFall,
                                          SUBSTRING(r.Refdatum,1,10) as R_Refdatum, r.NeuGenesen as R_NeuGenesen, r.AnzahlGenesen as R_AnzahlGenesen,
                                          b.LAN_ew_EWZ as B_Einwohner,b.FallZahl as B_Fallzahl,b.Death as B_Tote,
                                          l.EWZ as L_Einwohner,l.KFL as L_Flaeche, l.death_rate as L_TodesRate, l.cases as L_Faelle, l.deaths as L_Tote, l.cases_per_100k as L_Faelle_pro_100000,l.cases_per_population as L_
                                          Faelle_pro_Bevoelkerung,
                                          k.skreis as K_SKreis, k.bevoelkerung as K_Bevoelkerung, k.maenner as K_Maenner,k.frauen as K_Frauen, k.dichte as K_Dichte
                                          FROM
                                          cor_rki r,cor_bundesland b,cor_landkreise l, kreise k
                                          where r.IdLandkreis=l.RS and r.IdBundesland=b.id and r.IdLandkreis=k.id
                                          and r.AnzahlFall>0
                                          order by ID_B,ID_L,R_MeldeDatum;
                                          
                                          update cor_view set R_Tote=0 where R_Tote<0;
                                          update cor_view set R_AnzahlGenesen=0 where R_AnzahlGenesen<0;
                                          
                                          update cor_datum set rki=false;
                                          update cor_datum set rki=true where d_datum<=(select max(R_meldedatum) from cor_view) and d_datum>=(select min(R_meldedatum) from cor_view);
                                          
                                          ALTER TABLE `cor_view`
                                            ADD UNIQUE KEY `PRIME` (`ID_B`,`ID_L`,`R_MeldeDatum`,`ID_R`) USING BTREE,
                                            ADD KEY `I1` (`R_MeldeDatum`,`R_Bundesland`,`R_Fall`,`R_Tote`),
                                            ADD KEY `I2` (`R_MeldeDatum`,`R_Landkreis`,`R_Fall`,`R_Tote`),
                                            ADD KEY `I3` (`R_MeldeDatum`,`K_SKreis`,`R_Fall`,`R_Tote`);
                                          COMMIT;
                                          
                                          sissiwup 1 Reply Last reply Reply Quote 0
                                          • sissiwup
                                            sissiwup @sissiwup last edited by

                                            Um einfacher Abfragen zu können habe ich eine Tabelle:

                                            cor_datum angelegt, hier sind die vorhandenen Datumswerte markiert:

                                            cor_datum.sql

                                            grafana:

                                            ioBroker Corona-1586463967124.json

                                            Wenn ihr im json R_meldedatum durch R_refdatum ersetz, dann bekommt ihr die Kurven nach Erkrankungsdatum und nicht nach Meldedatum.

                                            1 Reply Last reply Reply Quote 0
                                            • First post
                                              Last post

                                            Support us

                                            ioBroker
                                            Community Adapters
                                            Donate

                                            818
                                            Online

                                            31.8k
                                            Users

                                            80.0k
                                            Topics

                                            1.3m
                                            Posts

                                            communication
                                            5
                                            22
                                            2217
                                            Loading More Posts
                                            • Oldest to Newest
                                            • Newest to Oldest
                                            • Most Votes
                                            Reply
                                            • Reply as topic
                                            Log in to reply
                                            Community
                                            Impressum | Datenschutz-Bestimmungen | Nutzungsbedingungen
                                            The ioBroker Community 2014-2023
                                            logo