Adjuster

[peterdudfield]

Detailed Description

We have now calculated the ME for time of day and forecast horizon. We want to now use this to adjust the national forecast

Context

Forecast is often constitnely out, so we want to adjuts by lookin at mean error over the last week

Possible Implementation

1. When saving the forecast adjust the values. This will help. but then the after a few days the ME will decrease and then the forecast wont be adjusted.
2. Add column to forecast_value tables - adjuster_mw amount, then when showing to the API / UI, we adjust the forecast accordingly. This means the original value is saved, but we easily know how much to adjust the forecast by

[peterdudfield]

We will need a read function that does something like this

select distinct on (time_of_day) * from (
select distinct on (time_of_day, forecast_horizon_minutes) time_of_day, forecast_horizon_minutes, value, 
    EXTRACT(MINUTE FROM TIMESTAMP '2023-01-01 12:04') as minute_now, 
    EXTRACT(Hour FROM TIMESTAMP '2023-01-01 12:04') as hour_now ,
    EXTRACT(Hour FROM time_of_day) as hour_time_of_day,
    EXTRACT(MINUTE FROM time_of_day) as minute_time_of_day 
    from metric_value
join metric on metric_value.metric_id = metric.id
where metric.id = 8
order by time_of_day, forecast_horizon_minutes, created_utc desc
        ) as A
where 60*hour_time_of_day + minute_time_of_day > 60*hour_now + minute_now + forecast_horizon_minutes
order by time_of_day, forecast_horizon_minutes desc

[peterdudfield]

or

select distinct on (time_of_day, forecast_horizon_minutes) time_of_day, forecast_horizon_minutes, value  
    from metric_value
join metric on metric_value.metric_id = metric.id
where metric.id = 8
order by time_of_day, forecast_horizon_minutes, created_utc desc

and join in python accordliny

[peterdudfield]

To deploy need to

• run migration on datbase
• upgrade forecast to 1.2.43
• check adjust_mw is being written to the database in ForevastVlaue table
• upgrade api to 0.2.53

Checked by hitting /v0/solar/GB/national/forecast and comparing to database values using

select distinct on (forecast_value_latest.target_time) * from forecast_value_latest
join forecast on forecast.id = forecast_value_latest.forecast_id
join location on forecast.location_id = location.id
join model on forecast.model_id = model.id
where location.gsp_id=0
and forecast_value_latest.target_time>'2023-03-09 12:00'
order by forecast_value_latest.target_time, forecast_value_latest.created_utc desc
limit 50

had to use

UPDATE forecast_value_latest SET adjust_mw=0 WHERE adjust_mw IS NULL;

which took some time 2 mins

Done on development

[peterdudfield]

Need https://github.com/openclimatefix/nowcasting_api/pull/195, so API version >=1.2.2

[peterdudfield]

SELECT avg(abs(forecast_value_last_seven_days.expected_power_generation_megawatts - forecast_value_last_seven_days.adjust_mw - gsp_yield.solar_generation_kw / 1000 )) AS avg_1, count(forecast_value_last_seven_days.expected_power_generation_megawatts) AS count_1 
FROM forecast_value_last_seven_days, gsp_yield 
WHERE forecast_value_last_seven_days.uuid IN (
    SELECT anon_1.uuid FROM (
        SELECT DISTINCT ON (forecast_value_last_seven_days.target_time) forecast_value_last_seven_days.uuid AS uuid 
        FROM forecast_value_last_seven_days 
        JOIN forecast ON forecast.id = forecast_value_last_seven_days.forecast_id 
        JOIN location ON location.id = forecast.location_id 
        JOIN model ON model.id = forecast.model_id 
        WHERE location.gsp_id = 0
        AND forecast_value_last_seven_days.target_time - forecast_value_last_seven_days.created_utc >= interval '60 minute' 
        AND forecast_value_last_seven_days.target_time > '2023-03-10'
        AND forecast_value_last_seven_days.target_time <= '2023-04-10'
        AND model.name = 'cnn'
        ORDER BY forecast_value_last_seven_days.target_time, forecast_value_last_seven_days.created_utc DESC) AS anon_1) 
AND gsp_yield.id IN (
    SELECT anon_2.id FROM (
        SELECT gsp_yield.id AS id 
        FROM gsp_yield 
        JOIN location ON location.id = gsp_yield.location_id 
        WHERE location.gsp_id = 0
        AND gsp_yield.datetime_utc > '2023-03-10'
        AND gsp_yield.datetime_utc <= '2023-04-10'
        AND gsp_yield.regime = 'day-after') AS anon_2) 
    AND gsp_yield.datetime_utc = forecast_value_last_seven_days.target_time

We can look at the MAE from 03-10 onwards, for different forecast horizons

Forecast Horizon	Reference	With adjuster
0	180	153
60	208	180
240	336	260
480	714	360

[peterdudfield]

 SELECT avg(abs(anon_1.solar_generation_kw / 1000 - anon_2.solar_generation_kw / 1000 )) AS avg_1, count(anon_1.datetime_utc) AS count_1 
FROM (
    SELECT gsp_yield.id AS id, gsp_yield.datetime_utc AS datetime_utc, gsp_yield.solar_generation_kw AS solar_generation_kw, gsp_yield.regime AS regime, gsp_yield.location_id AS location_id, gsp_yield.created_utc AS created_utc 
    FROM gsp_yield 
    JOIN location ON location.id = gsp_yield.location_id 
    WHERE gsp_yield.datetime_utc >= '2023-03-10'
    AND gsp_yield.datetime_utc < '2023-04-10' 
    AND gsp_yield.regime = 'in-day' 
    AND location.gsp_id = 0) AS anon_1 
JOIN (
    SELECT gsp_yield.id AS id, gsp_yield.datetime_utc AS datetime_utc, gsp_yield.solar_generation_kw AS solar_generation_kw, gsp_yield.regime AS regime, gsp_yield.location_id AS location_id, gsp_yield.created_utc AS created_utc 
    FROM gsp_yield 
    JOIN location ON location.id = gsp_yield.location_id 
    WHERE gsp_yield.datetime_utc >= '2023-03-10'
    AND gsp_yield.datetime_utc < '2023-04-10' 
    AND gsp_yield.regime = 'day-after' 
    AND location.gsp_id = 0) AS anon_2 
ON anon_1.datetime_utc = anon_2.datetime_utc

PVlive was MAE 120,

Need to break this down into day by day

[peterdudfield]

[peterdudfield]

End of March results

[peterdudfield]

For 2023-03-10 to 2023-04-02

The average MAE goes down from in MW	Horizon	Ref	With adjuster
0	161	131
60	187	164
240	372	300
480	926	477

[peterdudfield]

@dantravers

[dantravers]

Good difference at 0-4 hours ahead. Huge improvement at 8 hours ahead!