Local [Germany/Cologne] Coronavirus/Covid19-Data: Parse RESTful JSON Data from arcgis.com

blackmesa · January 26, 2021, 11:20pm

Hello all,

I spent the afternoon finding new data sources for my coronavirus card. I liked to display more local numbers in the process (for the anticipation of the day when life starts again :D).

I’m not reinventing the wheel here, but use some sources that don’t show up here in the forum yet, so I’m posting this as a new thread.
(Inspiration can also be found here)

Example-Dataset:

API-Query Form: Here
Output: Here

Helpful Tools:

JSONPath Online Evaluator: https://jsonpath.com/
JSON-Selector: http://jsonselector.com/

Parse RESTful JSON-Data:

sensor:

##############################################################
##
##          Fallzahlen Deutschland
##
##############################################################

   - platform: rest
    name: "Germany Coronavirus BRD Risklayer"
    resource: https://services.arcgis.com/ObdAEOfl1Z5LP2D0/ArcGIS/rest/services/Corona_Risklayer_Kreisebene/FeatureServer/0/query?where=&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&resultType=none&distance=0.0&units=esriSRUnit_Meter&returnGeodetic=false&outFields=&returnGeometry=true&returnCentroid=false&featureEncoding=esriDefault&multipatchOption=xyFootprint&maxAllowableOffset=&geometryPrecision=&outSR=&datumTransformation=&applyVCSProjection=false&returnIdsOnly=false&returnUniqueIdsOnly=false&returnCountOnly=false&returnExtentOnly=false&returnQueryGeometry=false&returnDistinctValues=false&cacheHint=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=%5B%7B%22statisticType%22%3A%22sum%22%2C%22onStatisticField%22%3A%22cases%22%2C%22outStatisticFieldName%22%3A%22cases%22%7D%2C%0D%0A%7B%22statisticType%22%3A%22sum%22%2C%22onStatisticField%22%3A%22cases_current%22%2C%22outStatisticFieldName%22%3A%22cases_current%22%7D%2C%0D%0A%7B%22statisticType%22%3A%22sum%22%2C%22onStatisticField%22%3A%22recovered%22%2C%22outStatisticFieldName%22%3A%22recovered%22%7D%2C%0D%0A%7B%22statisticType%22%3A%22sum%22%2C%22onStatisticField%22%3A%22death%22%2C%22outStatisticFieldName%22%3A%22death%22%7D%2C%0D%0A%7B%22statisticType%22%3A%22sum%22%2C%22onStatisticField%22%3A%22cases_new_7d%22%2C%22outStatisticFieldName%22%3A%22cases_new_7d%22%7D%2C%0D%0A%7B%22statisticType%22%3A%22sum%22%2C%22onStatisticField%22%3A%22EWZ%22%2C%22outStatisticFieldName%22%3A%22EWZ%22%7D%5D&having=&resultOffset=&resultRecordCount=&returnZ=false&returnM=false&returnExceededLimitFeatures=true&quantizationParameters=&sqlFormat=none&f=pjson&token=
    json_attributes_path: $.features[0].attributes
    json_attributes:
      - cases
      - recovered
      - cases_current
      - death
      - cases_new_7d
      - EWZ
    value_template: 'OK'

  - platform: rest
    name: "Germany Coronavirus BRD Risklayer timestamp"
    resource: https://services.arcgis.com/ObdAEOfl1Z5LP2D0/ArcGIS/rest/services/Corona_Risklayer_Kreisebene/FeatureServer/0/query?where=1%3D1&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&resultType=none&distance=0.0&units=esriSRUnit_Meter&returnGeodetic=false&outFields=last_update&returnGeometry=false&returnCentroid=false&featureEncoding=esriDefault&multipatchOption=xyFootprint&maxAllowableOffset=&geometryPrecision=&outSR=&datumTransformation=&applyVCSProjection=false&returnIdsOnly=false&returnUniqueIdsOnly=false&returnCountOnly=false&returnExtentOnly=false&returnQueryGeometry=false&returnDistinctValues=false&cacheHint=false&orderByFields=last_update&groupByFieldsForStatistics=&outStatistics=&having=&resultOffset=&resultRecordCount=&returnZ=false&returnM=false&returnExceededLimitFeatures=true&quantizationParameters=&sqlFormat=none&f=pjson&token=
    value_template: '{{ (value_json.features[-1].attributes.last_update | int / 1000) | timestamp_local }}'

  - platform: template
    sensors:
      germany_coronavirus_brd_risklayer_cases7_per_100k:
        value_template: "{{ ((state_attr('sensor.germany_coronavirus_brd_risklayer', 'cases_new_7d') | int) / (state_attr('sensor.germany_coronavirus_brd_risklayer', 'EWZ') | int / 100000)) | round(0) }}"

##############################################################
##
##          Fallzahlen Köln
##
##############################################################

  - platform: rest
    name: "Germany Coronavirus Cologne Risklayer"
    resource: https://services.arcgis.com/ObdAEOfl1Z5LP2D0/arcgis/rest/services/Corona_Risklayer_Kreisebene/FeatureServer/0/query?where=ags=05315&returnGeometry=false&f=json&outFields=%22*%22
    json_attributes_path: $.features[0].attributes
    json_attributes:
      - cases
      - recovered
      - cases_current
      - death
      - cases_new_7d_per_100k
    value_template: '{{ (value_json.features[0].attributes.last_update | int / 1000) | timestamp_local }}'
    device_class: timestamp

  - platform: rest
    name: "Germany Coronavirus Cologne DIVI"
    resource: https://services7.arcgis.com/mOBPykOjAyBO2ZKk/ArcGIS/rest/services/DIVI_Intensivregister_Landkreise/FeatureServer/0/query?where=AGS=05315&f=json&returnGeometry=false&outFields=%22*%22
    json_attributes_path: $.features[0].attributes
    json_attributes:
      - betten_frei
      - betten_belegt
      - betten_gesamt
      - Anteil_betten_frei
      - faelle_covid_aktuell
      - faelle_covid_aktuell_beatmet
      - Anteil_covid_beatmet
      - Anteil_COVID_betten
      - daten_stand
    value_template: '{{ as_timestamp(strptime(value_json.features[0].attributes.daten_stand,"%d.%m.%Y %M:%H Uhr")) | timestamp_local }}'
    device_class: timestamp

  - platform: rest
    name: "Germany Coronavirus Cologne RKI"
    resource: https://services7.arcgis.com/mOBPykOjAyBO2ZKk/arcgis/rest/services/RKI_Landkreisdaten/FeatureServer/0/query?where=AGS=05315&f=json&returnGeometry=false&outFields=%22*%22
    json_attributes_path: $.features[0].attributes
    json_attributes:
      - cases
      - death
      - cases7_per_100k
    value_template: '{{ as_timestamp(strptime(value_json.features[0].attributes.last_update,"%d.%m.%Y, %M:%H Uhr")) | timestamp_local }}'
    device_class: timestamp

Generating some Sensors from the data:

sensor:
  - platform: template
    sensors: 
      cologne_coronavirus_risklayer_cases:
        value_template: "{{ state_attr('sensor.germany_coronavirus_cologne_risklayer', 'cases') }}"
        friendly_name: 'Bestätigt'
        unit_of_measurement: 'Fälle'

  - platform: template
    sensors: 
      cologne_coronavirus_risklayer_current:
        value_template: "{{ state_attr('sensor.germany_coronavirus_cologne_risklayer', 'cases_current') }}"
        friendly_name: 'Aktuell'
        unit_of_measurement: 'Fälle'

  - platform: template
    sensors: 
      cologne_coronavirus_risklayer_death:
        value_template: "{{ state_attr('sensor.germany_coronavirus_cologne_risklayer', 'death') }}"
        friendly_name: 'Tote'
        unit_of_measurement: 'Fälle'

  - platform: template
    sensors: 
      cologne_coronavirus_risklayer_cases7_per_100k:
        value_template: "{{ state_attr('sensor.germany_coronavirus_cologne_risklayer', 'cases7_per_100k') }}"
        friendly_name: '7-Tage-Inzidenz'
        unit_of_measurement: 'Fälle/100k'

  - platform: template
    sensors: 
      cologne_coronavirus_divi_beds_sum:
        value_template: "{{ state_attr('sensor.germany_coronavirus_cologne_divi', 'betten_gesamt') }}"
        friendly_name: 'Intenisivbetten'
        unit_of_measurement: 'Fälle'

  - platform: template
    sensors: 
      cologne_coronavirus_divi_beds_free:
        value_template: "{{ state_attr('sensor.germany_coronavirus_cologne_divi', 'betten_frei') }}"
        friendly_name: 'Intenisivbetten'
        unit_of_measurement: 'Fälle'

  - platform: template
    sensors: 
      cologne_coronavirus_divi_beds_covid:
        value_template: "{{ state_attr('sensor.germany_coronavirus_cologne_divi', 'faelle_covid_aktuell') }}"
        friendly_name: 'Stationär'
        unit_of_measurement: 'Fälle'

  - platform: template
    sensors: 
      cologne_coronavirus_rki_cases7d:
        value_template: "{{ state_attr('sensor.germany_coronavirus_cologne_rki', 'cases7_per_100k') | round(0) }}"
        friendly_name: '7-Tage-Inzidenz'
        unit_of_measurement: 'Fälle'

These are my Lovelace-Cards:

type: vertical-stack
cards:
  - entities:
      - entities:
          - attribute: cases
            name: Bestätigt
          - attribute: death
            name: Tote
          - entity: sensor.germany_coronavirus_brd_risklayer_cases7_per_100k
            name: 7T.-Inz
        entity: sensor.germany_coronavirus_brd_risklayer
        icon: 'mdi:virus'
        name: Deutschland
        secondary_info:
          entity: sensor.germany_coronavirus_brd_risklayer_timestamp
          format: relative
          name: false
        show_state: false
        type: 'custom:multiple-entity-row'
      - entities:
          - entity: sensor.cologne_coronavirus_risklayer_cases
            name: Bestätigt
            unit: false
          - entity: sensor.cologne_coronavirus_risklayer_current
            name: Aktuell
            unit: false
          - entity: sensor.cologne_coronavirus_risklayer_death
            name: Tote
            unit: false
          - entity: sensor.cologne_coronavirus_risklayer_cases7_per_100k
            name: 7T.-Inz.
            unit: false
        icon: 'mdi:virus'
        name: Köln
        entity: sensor.germany_coronavirus_cologne_risklayer
        secondary_info:
          entity: sensor.germany_coronavirus_cologne_risklayer
          format: relative
          name: false
        show_state: false
        type: 'custom:multiple-entity-row'
    type: entities
    header:
      type: picture
      image: local/img/header_coronavirus.png
  - entities:
      - color: red
        entity: sensor.cologne_coronavirus_risklayer_current
        name: Aktuell
      - color: black
        entity: sensor.cologne_coronavirus_risklayer_death
        name: Tote
    hour24: true
    name: Fallzahlen in Köln
    unit: Fälle
    show:
      labels: true
    type: 'custom:mini-graph-card'
    hours_to_show: 168
    points_per_hour: 0.25
    icon: 'mdi:clipboard-account'
  - entities:
      - color: orange
        entity: sensor.cologne_coronavirus_divi_beds_covid
        name: Covid19
      - color: purple
        entity: sensor.cologne_coronavirus_divi_beds_sum
        name: Gesamt
      - color: green
        entity: sensor.cologne_coronavirus_divi_beds_free
        name: Frei
    hour24: true
    name: Intensivbetten in Köln
    unit: Betten
    show:
      labels: true
    type: 'custom:mini-graph-card'
    hours_to_show: 168
    points_per_hour: 0.25
    icon: 'mdi:bed'

I hope that I could facilitate the search for appropriate data or the preparation for some.

Have fun with it!

Flipso · January 27, 2021, 7:59am

hey very nice work, would you mind to share your lovelace code for the first card, that really looks amazing
Grüße

newkey · January 27, 2021, 8:02am

I would appreciate if you could share your lovelace code too. Greetings from Cologne & stay healthy !

blackmesa · January 27, 2021, 3:42pm

@Flipso @newkey
I just updated my post.

In Ergänzung zu meinem Post von gestern habe ich nun auch Daten zu den Impfungen implementiert:

RESTful sensor and templating for data on vaccination:

##############################################################
##
##          IMPFUNGEN
##
##############################################################
sensor:
  - platform: rest
    name: "Germany Coronavirus Impfungen RKI BRD"
    resource: https://rki-vaccination-data.vercel.app/api
    json_attributes:
      - vaccinated
      - quote
      - difference_to_the_previous_day
    value_template: '{{ as_timestamp(strptime(value_json.lastUpdate,"")) | timestamp_local }}'
    device_class: timestamp

  - platform: rest
    name: "Germany Coronavirus Impfungen RKI NRW"
    resource: https://rki-vaccination-data.vercel.app/api
    json_attributes_path: $.states.Nordrhein-Westfalen
    json_attributes:
      - vaccinated
      - quote
      - difference_to_the_previous_day
    value_template: '{{ as_timestamp(strptime(value_json.lastUpdate,"")) | timestamp_local }}'
    device_class: timestamp

  - platform: template
    sensors: 
      coronavirus_vaccination_brd_vaccinated:
        value_template: "{{ state_attr('sensor.germany_coronavirus_impfungen_rki_brd', 'vaccinated') }}"
        friendly_name: 'Geimpfte'
        unit_of_measurement: 'Personen'

  - platform: template
    sensors: 
      coronavirus_vaccination_brd_quote:
        value_template: "{{ state_attr('sensor.germany_coronavirus_impfungen_rki_brd', 'quote') }}"
        friendly_name: 'Impfquote'

  - platform: template
    sensors: 
      coronavirus_vaccination_brd_difference:
        value_template: "{{ state_attr('sensor.germany_coronavirus_impfungen_rki_brd', 'difference_to_the_previous_day') }}"
        friendly_name: 'Neu-Geimpfte'
        unit_of_measurement: 'Personen'

  - platform: template
    sensors: 
      coronavirus_vaccination_nrw_vaccinated:
        value_template: "{{ state_attr('sensor.germany_coronavirus_impfungen_rki_nrw', 'vaccinated') }}"
        friendly_name: 'Geimpfte'
        unit_of_measurement: 'Personen'

  - platform: template
    sensors: 
      coronavirus_vaccination_nrw_quote:
        value_template: "{{ state_attr('sensor.germany_coronavirus_impfungen_rki_nrw', 'quote') }}"
        friendly_name: 'Impfquote'

  - platform: template
    sensors: 
      coronavirus_vaccination_nrw_difference:
        value_template: "{{ state_attr('sensor.germany_coronavirus_impfungen_rki_nrw', 'difference_to_the_previous_day') }}"
        friendly_name: 'Neu-Geimpfte'
        unit_of_measurement: 'Personen'

Lovelace-Configuration

type: vertical-stack
cards:
  - entities:
      - entities:
          - entity: sensor.coronavirus_vaccination_brd_difference
            name: Deutschland
            unit: false
          - entity: sensor.coronavirus_vaccination_nrw_difference
            name: NRW
            unit: false
        entity: sensor.germany_coronavirus_impfungen_rki_nrw
        icon: 'mdi:needle'
        name: Geimpfte gestern
        show_state: false
        type: 'custom:multiple-entity-row'
    type: entities
    header:
      type: picture
      image: local/img/header_impfung.png
      tap_action:
        action: none
      hold_action:
        action: none
  - type: horizontal-stack
    cards:
      - entities:
          - entity: sensor.coronavirus_vaccination_brd_vaccinated
            name: Deutschland
          - entity: sensor.coronavirus_vaccination_nrw_vaccinated
            color: green
            name: NRW
        hour24: true
        icon: 'mdi:needle'
        name: Bisher geimpft
        type: 'custom:mini-graph-card'
        unit: P.
      - entities:
          - entity: sensor.coronavirus_vaccination_brd_quote
            name: Deutschland
          - entity: sensor.coronavirus_vaccination_nrw_quote
            color: green
            name: NRW
        hour24: true
        icon: 'mdi:needle'
        name: Impfquote
        type: 'custom:mini-graph-card'
        unit: /100

An image of the corresponding lovelace-card you can find in my first posting

Have fun with that!

Flipso · January 27, 2021, 7:53pm

Thanks a lot for this update on your post.
Und liebe Grüsse in den Nord-Westen

kiromatis · January 29, 2021, 7:39am

Thank you for this
Can you do me a favour and update your first post with your “Impfung”-Lovelace? (I think this is missing)
Lieben Dank und Grüße aus BaWü

blackmesa · January 29, 2021, 9:47am

Sorry, forgot about that. I just added the lovelace-code below the corresponding restful sensor

(btw: Ich habe mich heute mal wieder über die schlechte Qualität der RKI-Daten aufgeregt. Laut Pressemeldung liegt deren berechnete 7-Tages-Inzidenz heute erstmalig im Bundes-Durchschnitt wieder unter 100. Aber der Datensatz ergibt 103. Daher update ich gerade den BRD-Sensor ebenfalls auf die Daten von Risklayer. Die sind einfach zuverlässiger.)

Update:
I just updated the code in the initial post. From now on, I only use the data from the Robert Koch Institute (RKI) for the visualization of vaccinations. Otherwise, I consider the data from Risklayer to be more up-to-date and accurate.

kiromatis · January 29, 2021, 10:06am

Thanks!

… und vielen Dank!

newkey · January 29, 2021, 11:04am

Thanks a lot! Keep up the good work and have a nice weekend!

Kölle Alaaf

bebru · March 27, 2021, 5:10pm

Hello, thank you very much for your great job!
I used your informations, but at one place I found a mistake.
In this row:
value_template: “{{ state_attr(‘sensor.germany_coronavirus_cologne_risklayer’, ‘cases7_per_100k’) }}”`
it must be not “'cases7_per_100k” rather then “cases_new_7d_per_100k”.
I changed it for my usement and then it works.

TillTaucher · June 9, 2022, 8:35pm

Hi. Anyone using this still? I noticed that the https://rki-vaccination-data.vercel.app is no more there. So how and where do I still get that data to fill up my numbers again?

Thanks. Danke. Gruß/ regards Till

DS_DV · July 2, 2022, 12:23am

this API looks nice.
tho i have no clue how to adapt the script / integration

here is also a good tutorial:

maybe we can put an update together

also the “original api” seems to be crashed which is referenced in this issue: