Lake Level analysis - pulling data from external source via API key - REST JSON

[kenwiens]

I am trying to pull hydrometric data in a near real-time fashion. The data is maintained by Environment Canada and made available via an api key and calls to a web site.

I use the following url to request data:

https://vps267042.vps.ovh.ca/scrapi/station/08LE070/primarylevel/?startDate=2023-05-30&endDate=2023-05-30&resultType=history&key={my secret key}

If you want to try this, you can get a free key at Water Data API | Open Government, Government of Canada

when I submit this request in a browser, I receive data that looks like the following:

{“code”:200,“details”:“Water Level and Flow - Environment Canada 00:00:00”,“value”:“348.448”},{“date”:“2023-05-30 00:05:00”,“value”:“348.448”},{“date”:“2023-05-30 00:10:00”,“value”:“348.450”},{“date”:“2023-05-30 00:15:00”,“value”:“348.450”},{“date”:“2023-05-30 00:20:00”,“value”:“348.450”},{“date”:“2023-05-30 00:25:00”,“value”:“348.449”},{“date”:“2023-05-30 00:30:00”,“value”:“348.449”},{“date”:“2023-05-30 00:35:00”,“value”:“348.448”},

• lots of records omitted -

{“date”:“2023-05-30 20:15:00”,“value”:“348.441”},{“date”:“2023-05-30 20:20:00”,“value”:“348.438”}]}}

Every 5 minutes another record is added.

The value is the number of metres above sea level for the Shuswap lake in Sicamous BC (Canada). It might change by 3-5 metres some years. From very low (so I get a lot of beach) to flood conditions (so I might need to take some corrective actions). This lake (and my cabin) are about a 6 hour drive from home - so I want to monitor it remotely. Fortunately we have a government agency that provides very accurate measurements (every 5 minutes) - and provides it as shown above.

The api limits me to 200 calls per day, and given that lake levels don’t change that fast, I might pull the data once an hour - so 200 is more than enough.

I will want to get the last record provided (the “date” and the “value”) with each call

From the URL, you will see that I need to enter a start and end date (so I’ll need to create the URL programmatically) - and all I know is that I need to extract the last record in the file each time.

This is all different than everything I’ve done in HA so far. My initial questions are:

1. How do I pull down the data (REST?) - I’ve never done this and it looks a bit complex to me.
2. how do I extract just the last record - I assume into a sensor?
3. Should I be using the json parsing capability? (and how)
4. How do I best manage some history (I know HA automatically will keep history of entities - should I use that or should I manage some history on my own?)

My objective is to take these lake levels, and coupled with other historical data, calculate the amount of beach in front of my cabin. I will want some history as well - as I would also present information like - change in last 24 hours, change in last week, to indicate if the lake going up or down (and how fast) , has it peaked, etc.

Any thoughts on how best to approach this?

thanks,
Ken

[vingerha]

This provides both date and value in the state
It is not (!) possible to use templates for attributes so if you want state/value separate then you 'd need either to pre-parse json with e.g. jq and put them in attributes…or create two sensors
For storing long term…check Statistics - Home Assistant (home-assistant.io)

  - platform: rest
    name: testing_lake
    scan_interval: 360000
    resource_template: "https://vps267042.vps.ovh.ca/scrapi/station/08LE070/primarylevel/?startDate=2023-05-30&endDate=2023-05-30&resultType=history&key=[YOURKEY]"
    value_template: >
        {{ (value_json.message.history | last).value }} - {{ (value_json.message.history | last).date }}

[kenwiens]

Thank-you for the code
Should this go in the sensor section of the config.yaml file? I’ve tried a few places, but it always generates errors in the subsequent lines in the config.yaml file.

Thanks
Ken

[vingerha]

yes, sensor
RESTful Sensor - Home Assistant (home-assistant.io)

[kenwiens]

Thank-you for the suggested approach, it looks much simpler than I was expecting. I’m not sure why I get the errors in my config.Yaml - but I’ll have time to more thoroughly experiment later this week. I’ll post my progress!

[vingerha]

post your config?

[kenwiens]

I got distracted (by other stuff in HA - primarily power monitoring), which did result in pushing me up the learning curve a little. As a result I got past my initial issue with yaml config errors (it was just indentation) - so am feeling proud I did learn a little!

Firstly - than you for giving me the solution - that was so helpful and elegant compared to the path I was heading down.

Question 1: I chose the path of using two sensors. The only down side I see to this is that I can’t be sure that the second value is retrieved immediately after the first - but given that lake levels change very slowly - I can live with this ) I think I’d now like to put both values into a sensor (make the date the state and the depth an attribute) and then use the statistics function you referenced to achieve long term data recording. Does that make sense? I thought keeping the values together would also help to keep the date and the depth reading pair aligned. (yes / no? ). Also, I am using the depth value to calculate “exposed beach” which I would also store as an attribute.

Question 2 - In the resource_template you can see there is a start date and end date required. I could set these to today and an extremely large end date to always get the most recent data - but it seems that eventually the data stream would be too large and I’d probably run into some limit. Can I put a date calculation into the resource template (maybe a start date of now()-1 and an end date of now()+1. If so, how would I format that?

Here is my current configuration:

###  SHUSWAP LAKE LEVEL ANALYSIS
#
- platform: rest
  name: shuswap_lake_crescent_bay_depth
  scan_interval: 3600
  resource_template: "https://vps267042.vps.ovh.ca/scrapi/station/08LE070/primarylevel/?startDate=2023-01-31&endDate=2023-12-31&resultType=history&key=[mykey]"
  value_template: >
        {{ (value_json.message.history | last).value }}
- platform: rest        
  name: shuswap_lake_crescent_bay_reading_date
  scan_interval: 3600
  resource_template: "https://vps267042.vps.ovh.ca/scrapi/station/08LE070/primarylevel/?startDate=2023-01-31&endDate=2023-12-31&resultType=history&key=[mykey]"
  value_template: >
        {{ (value_json.message.history | last).date }}
#
##

My attempt to load the values into a template is also problematic. I don’t believe I should need the time trigger but I put it in to force the refresh just in case.

###   SENSOR FOR DATA FOR LAKE LEVEL CALCULATIONS
#
  - trigger:
      - platform: time_pattern
        minutes: "/1"
    sensor:
      - name: Shuswap Lake and Beach Data
        unit_of_measurement: "m"
        state: "{{states('sensor.shuswap_lake_crescent_bay_reading_date')}}"
        attributes:
          salmon_arm_lake_depth: "{{states('sensor.shuswap_lake_crescent_bay_depth')}}"
          shuswap_lake_crescent_bay_beach: "lots"

I results I see are:

My two sensors appear correct, but I’m having trouble getting them into the new template sensor.

---

Further experimentation shows the issue is related to putting a date in the state. Not sure if that is the specific issue or if this is a string issue? If I make the state hold the depth value, then the rest of the template populates properly. Can someone offer some guidance on putting the date string into the state please?

[vingerha]

@kenwiens

Question 1:

Unless you chose to change the update frequency the 2nd one will update when the first does. There may be some issues if this is high update frequency (multiple per second) but for99.9% this is fine

@kenwiens

Question 2

Yes you can use templates for the date. Someting alike

...rylevel/?startDate={{ (now() - timedelta(days=1)).strftime("%Y-%m-%d") }}&endDate={{ (now() + timedelta(days=100)).strftime("%Y-%m-%d") }}&resu....

on time trigger, see above, it should update when the source / rest updates

[kenwiens]

@vingerha

{{ (now() - timedelta(days=1)).strftime("%Y-%m-%d") }}

Thank you for the advice!

I tried this, iteratively, changing only the start date in the first sensor, and can’t get past an indentation error:

#
###  SHUSWAP LAKE LEVEL ANALYSIS
#
- platform: rest
  name: shuswap_lake_crescent_bay_depth
  scan_interval: 1200
  resource_template: "https://vps267042.vps.ovh.ca/scrapi/station/08LE070/primarylevel/?startDate={{ (now() - timedelta(days=1)).strftime("%Y-%m-%d") }}&endDate=2023-12-31&resultType=history&key=-NVvTtPODmHSv4lai4_I"
  value_template: >
        {{ (value_json.message.history | last).value }}
- platform: rest        
  name: shuswap_lake_crescent_bay_reading_date
  scan_interval: 1200
  resource_template: "https://vps267042.vps.ovh.ca/scrapi/station/08LE070/primarylevel/?startDate=2023-08-28&endDate=2023-12-31&resultType=history&key=-NVvTtPODmHSv4lai4_I"
  value_template: >
        {{ (value_json.message.history | last).date }}
#
##
#

The line 65 referred to is the resource template.

When I plug the date calculation you provided into the template editor it gives me the correct date string. I can’t see why I get an indentation error. I also tried doing this in studio code server and it gave me an undefined platform error. Can you see what I am doing wrong?

Thanks again,
Ken

[vingerha]

Possibly the quotes, try single quotes on strftime or as the value template:

resource_template: >
    https://vps267042.vps.ovh.ca/scrapi/station/08LE070/primarylevel/?startDate={{ (now() - timedelta(days=1)).strftime("%Y-%m-%d") }}&endDate=2023-12-31&resultType=history&key=-NVvTtPODmHSv4lai4_I

[kenwiens]

@vingerha

{{ (now() + timedelta(days=100)).strftime("%Y-%m-%d") }}

Thank you!!

Putting single quotes on strftime fixed it.

Here is the final product: