aggregate function in assignment_storage

[IlkaCu]

Executing the adjusted script 'ego_dp_powerflow_assignment_storage.sql' creates the following error:

ProgrammingError: (psycopg2.ProgrammingError) aggregate functions are not allowed in GROUP BY
LINE 540:   sum(A.nominal_value[1]) AS p_nom,

@lukasoldi, @wolfbunke: It seems to be a combination of the changes you made, which causes the problem.

[wolfbunke]

strange as a single command it is working. I take a look and change it.

[wolfbunke]

Is this error maybe caused by using python for the sql script? As fare I can remember we had a similar problem in the beginning or? @IlkaCu

[wolfbunke]

It looks that the problem is the change of input tables of the eGo100 scenario of storages. calc_renpass_gis.renpass_gis_storage is here model_draft.ego_grid_pf_hv_storage_batteries. See https://github.com/openego/data_processing/blob/release/v0.4.1/dataprocessing/sql_snippets/ego_dp_powerflow_assignment_storage.sql#L557

[wolfbunke]

Is this error maybe caused by using python for the sql script? As fare I can remember we had a similar problem in the beginning or? @IlkaCu

this is not the reason. I change the query for ego100.

[wolfbunke]

Ok my suggestion is to change the script a bit:

To delete the new code here:

CREATE TABLE model_draft.ego_grid_pf_hv_storage_batteries AS
SELECT * FROM calc_renpass_gis.renpass_gis_storage WHERE substring(calc_renpass_gis.renpass_gis_storage.target,12,21) = 'redox_flow' UNION
SELECT * FROM calc_renpass_gis.renpass_gis_storage WHERE substring(calc_renpass_gis.renpass_gis_storage.target,12,22) = 'lithium_ion';

And use a temp table with only needed and already totalized data like:

CREATE TABLE model_draft.ego_grid_pf_hv_storage_batteries_temp AS
SELECT
 sub. scenario_id ,
 sub.source ,
 sum( sub.p_nom) as p_nom,
 sub.cntr_id
FROM 
(
SELECT
  scenario_id ,
  'battery'::text as source ,
  nominal_value[1] AS p_nom,
  substring(source, 1, 2)::text as  cntr_id
FROM
  calc_renpass_gis.renpass_gis_storage
WHERE substring(source, 1, 2) <> 'DE'
AND nominal_value IS not NULL
AND nominal_value[1] > 0.001
AND source not LIKE '%%powerline%%'
AND scenario_id = 40
Group by scenario_id, source,nominal_value
) as sub
Group by sub.scenario_id, sub.source,sub.cntr_id

and use this table in order to add the other parameters for https://github.com/openego/data_processing/blob/release/v0.4.1/dataprocessing/sql_snippets/ego_dp_powerflow_assignment_storage.sql#L511-L532 and the join it with https://github.com/openego/data_processing/blob/release/v0.4.1/dataprocessing/sql_snippets/ego_dp_powerflow_assignment_storage.sql#L559-L570 like before? @lukasoldi

[lukasoldi]

Well, if this works it's a nice workaround. Have you tested it? @wolfbunke

[ulfmueller]

Yes he did! :+1:

[lukasoldi]

Nice, then let's go for a PR. Do you want to implement it @wolfbunke?

[wolfbunke]

I thought you would adapt it.

[wolfbunke]

@lukasoldi as you sad I forgot to add the filter for the types of storages. Here my new script for testing:

-- battery

CREATE TABLE model_draft.ego_grid_pf_hv_storage_batteries_temp AS
SELECT
 sub. scenario_id ,
 sub.source ,
 sum( sub.p_nom) as p_nom,
 sub.cntr_id
FROM 
(
SELECT
  scenario_id ,
  'battery'::text as source ,
  nominal_value[1] AS p_nom,
  substring(source, 1, 2)::text as  cntr_id
FROM
  calc_renpass_gis.renpass_gis_storage
WHERE substring(source, 1, 2) <> 'DE'
AND (substring(target,12,22) = 'lithium_ion'
Or substring(target,12,21) = 'redox_flow')

AND nominal_value IS not NULL
AND nominal_value[1] > 0.001
AND source not LIKE '%%powerline%%'
AND scenario_id = 40
Group by scenario_id, source,nominal_value
) as sub
Group by sub.scenario_id, sub.source,sub.cntr_id;

----
INSERT into model_draft.ego_grid_pf_hv_storage (
  scn_name,
  storage_id,
  bus,
  dispatch,
  control,
  p_nom,
  p_nom_extendable,
  p_nom_min,
  p_min_pu_fixed,
  p_max_pu_fixed,
  sign,
  source,
  marginal_cost,
  capital_cost,
  efficiency,
  soc_initial,
  soc_cyclic,
  max_hours,
  efficiency_store,
  efficiency_dispatch,
  standing_loss
)
SELECT
  'eGo 100' as scn_name,
 nextval('model_draft.ego_grid_pf_hv_storage_neighbouring') as storage_id, -- set as comment for testing 
  B.bus_id as bus,
  'flexible' AS dispatch,
  'PV' AS control,
  A.p_nom,
  FALSE as p_nom_extendable,
  0 as p_nom_min,
  -1 as p_min_pu_fixed,
  1 as p_max_pu_fixed,
  1 as sign,
  19 as source,
  0 as marginal_cost,
  0 as capital_cost,
  1 as efficiency,
  0 as soc_inital,
  true as soc_cyclic,
  6 as max_hours,
  0.9487 as efficiency_store, -- efficiency_store according to Acatech2015 
  0.9487 as efficiency_dispatch, -- efficiency_dispatch according to Acatech2015 
  0.00417 as standing_loss -- standing_loss according to Acatech2015

        FROM  model_draft.ego_grid_pf_hv_storage_batteries_temp A join
        (
        SELECT
        *
        FROM
            (SELECT *,
            max(v_nom) over (partition by cntr_id) AS max_v_nom
            FROM
            model_draft.ego_grid_hv_electrical_neighbours_bus
            where central_bus = True
            ) SQ
        WHERE SQ.v_nom = SQ.max_v_nom
        ) B
        ON A.cntr_id = B.cntr_id
    WHERE substring(A.source, 1, 2) <> 'DE'
    AND A.p_nom > 0.001
Group by bus, p_nom;

-- DROP TABLE model_draft.ego_grid_pf_hv_storage_batteries_temp; 

For testing the storage_id has to be set as comment.

[wolfbunke]

@lukasoldi it would be nice if you could test it and implement it for the other types of storages.

[lukasoldi]

Generally working, BUT there are still no storages for AT and LU. Any clues, @wolfbunke?

[wolfbunke]

This is coursed by the "Kupferplatten" logic of the market of Germany by feeding in DE_bus_el . I was not sure if those storages has to be added? By adding AT_storage_phs, LU_storage_phs to the filter function it will work:

....

AND (substring(target,12,22) = 'lithium_ion'
Or substring(target,12,21) = 'redox_flow'
Or source = 'AT_storage_phs'
Or source = 'LU_storage_phs' )

This needs to be added for all three scenarios.