Difference between revisions of "ENTSOG data analysis"

From embargo
Jump to navigation Jump to search
 
(3 intermediate revisions by 2 users not shown)
Line 4: Line 4:
 
(Experimental, physical flow data from 01 Jan 2021 to 05 Aug 2022, where flow value is not null)
 
(Experimental, physical flow data from 01 Jan 2021 to 05 Aug 2022, where flow value is not null)
  
Postgres access: <code> psql -h embargo.energy -U embargo entsog </code>; password: 9mTc_9hPjk}&D27/F,VHC8qG
+
Postgres access: <code> psql -h embargo.energy -U embargo gas </code>; password: 9mTc_9hPjk}&D27/F,VHC8qG
  
 
Some fields:
 
Some fields:
Line 54: Line 54:
 
== Some tentative examples ==
 
== Some tentative examples ==
  
 +
[[File:Screen-2022-08-16-20-46-24.png|thumb| DE/DK flows ]]
 
Emulate German THE data for Denmark exports 16 July 2022 https://www.tradinghub.eu/en-gb/Publications/Transparency/Market-area-monitor  
 
Emulate German THE data for Denmark exports 16 July 2022 https://www.tradinghub.eu/en-gb/Publications/Transparency/Market-area-monitor  
 +
 
<code>
 
<code>
 
select '====', operator.operatorlabel, value, flows.directionkey, ispipeinpipe, * from flows left outer join operator on flows.pointkey = operator.pointkey and flows.directionkey = operator.directionkey and flows.operatorkey = operator.operatorkey where tsocountry = 'DE' and adjacentcountry = 'DK' and date(periodfrom)='2022-07-16' and value > 0 order by operator.operatorlabel;
 
select '====', operator.operatorlabel, value, flows.directionkey, ispipeinpipe, * from flows left outer join operator on flows.pointkey = operator.pointkey and flows.directionkey = operator.directionkey and flows.operatorkey = operator.operatorkey where tsocountry = 'DE' and adjacentcountry = 'DK' and date(periodfrom)='2022-07-16' and value > 0 order by operator.operatorlabel;
Line 84: Line 86:
 
<code>
 
<code>
 
select left(operatorkey,2), date_trunc('month', periodto), round(sum(value)) from flows where pointkey in (select pointKey from operator where pointtype like 'Cross-Border Transmission IP%' and tsoCountry = 'DE' and isPipeInPipe = 'False') group by left(operatorkey,2), date_trunc('month', periodto) order by left(operatorkey,2), date_trunc('month', periodto);
 
select left(operatorkey,2), date_trunc('month', periodto), round(sum(value)) from flows where pointkey in (select pointKey from operator where pointtype like 'Cross-Border Transmission IP%' and tsoCountry = 'DE' and isPipeInPipe = 'False') group by left(operatorkey,2), date_trunc('month', periodto) order by left(operatorkey,2), date_trunc('month', periodto);
 +
</code>
 +
 +
Czech border transmissions
 +
<code>
 +
as=# copy (select * from entsog_flows left outer join entsog_operator on entsog_flows.pointkey = entsog_operator.pointkey where entsog_operator.pointkey in (select pointkey from entsog_operator where adjacentcountry = 'DE' and tsocountry = 'CZ' union select entsog_operator.pointkey from entsog_operator where adjacentcountry = 'CZ' and tsocountry = 'DE') order by periodfrom desc) to '/tmp/data.csv' with CSV DELIMITER ',' HEADER;
 
</code>
 
</code>
  

Latest revision as of 06:39, 17 October 2022

ENTSOG data as relational database[edit | edit source]

(Experimental, physical flow data from 01 Jan 2021 to 05 Aug 2022, where flow value is not null)

Postgres access: psql -h embargo.energy -U embargo gas ; password: 9mTc_9hPjk}&D27/F,VHC8qG

Some fields:

Table operator:[edit | edit source]

  • adjacentcountry: non-TSO country (includes non-EU countries such as RU, MK, NO)
  • crossborderpointtype, examples: 'In-country EU', 'Cross-Border EU|EU', 'Cross-Border EU|Non-EU', 'Cross-Border Non-EU|Non-EU', 'In-country Non-EU'
  • directionkey, examples: 'exit', 'entry'
  • eurelationship, examples: 'within EU', 'between EU and Non-EU', 'Non-EU'
  • hasdata, examples: 'True', 'False'
  • id, examples: '5DE-TSO-0014VTP-00045exitDE-TSO-0010', '5DE-TSO-0002VTP-00012exitDE-TSO-0007'
  • isdoublereporting, examples: 'None', 'False', 'True'
  • ispipeinpipe, examples: 'False', 'True'
  • isvirtualizedcommercially, examples: 'False', 'True'
  • isvirtualizedoperationally, examples: 'False', 'True'
  • lastupdatedatetime, examples: '2022-07-05T19:07:06+02:00'
  • operatorkey, examples: 'UK-TSO-0001', 'NL-TSO-0001', 'DE-TSO-0005'
  • operatorlabel, examples: 'National Grid Gas', 'GTS', 'Gasunie Deutschland '
  • pointkey, examples: 'VTP-00045', 'VTP-00011', 'VTP-00012'
  • pointlabel, examples: 'THE VTP (DE)', 'VHP GASPOOL', 'VHP NetConnectGermany', 'Wallbach'
  • pointtype, examples: 'Trading Point', 'Cross-Border Transmission IP within EU', 'Storage point', 'Distribution Point', 'Cross-Border Transmission IP between EU and Non-EU (import)', 'Cross-Border Transmission IP between EU and Non-EU'
  • tsobalancingzone, examples: 'DE THE BZ', '', 'UK', 'Netherlands', 'TRF',
  • tsocountry: country of TSO (includes non-EU countries such as RU, MK, NO)
  • virtualreverseflow, examples: 'No', 'Yes'

Table: flows[edit | edit source]

  • bookingplatformkey, examples: , 'PRISMA', 'RBP', 'GSA', 'None'
  • directionkey, examples: 'exit', 'entry'
  • flowstatus, examples: 'Provisional', 'Confirmed'
  • id, examples: '1Physical Flowday2020-12-312021-01-01CZ-TSO-0001ITP-00535exitkWh/d', '1Physical Flowday2020-12-312021-01-01CZ-TSO-0001ITP-00537entrykWh/d', '1Physical Flowday2020-12-312021-01-01CZ-TSO-0001ITP-00537exitkWh/d'
  • idpointtype, examples: '0', '6', '17', '1', '18', '5', '15', '14', '3', '2'
  • indicator, examples: 'Physical Flow'
  • itemremarks, examples: 'None', 'Physical Flow Remark', 'No comment', 'According to Annex 1 Chapter 3 of Regulation CE n. 715/2009, physical flow data are published in aggregate. Capacity information per point are available at www.snamretegas.it at the following path: Business & Services/Thermal year/Transportation capacity.'
  • lastupdatedatetime, examples: '2021-12-24T15:35:23+01:00', '2022-04-28T02:05:53+02:00', '2021-08-24T07:55:09+02:00', '2022-05-25T08:59:09+02:00', '2022-03-17T07:54:09+01:00', '2021-03-31T07:53:09+02:00', '2022-01-26T10:47:08+01:00'
  • operatorkey, examples: 'UK-TSO-0001', 'DE-TSO-0009', 'NL-TSO-0001', 'GR-TSO-0001', 'DE-TSO-0005', 'BE-TSO-0001', 'PL-TSO-0002', 'DE-TSO-0001', 'CZ-TSO-0001', 'IT-TSO-0001'
  • operatorlabel, examples: 'National Grid Gas', 'Open Grid Europe', 'GTS', 'DESFA', 'Gasunie Deutschland ', 'Fluxys Belgium', 'GAZ-SYSTEM', 'GASCADE Gastransport', 'NET4GAS', 'Snam Rete Gas'
  • periodfrom, examples: '2021-04-20 06:00:00', '2021-09-23 06:00:00', '2021-09-24 06:00:00', '2021-09-25 06:00:00', '2021-09-26 06:00:00', '2021-09-27 06:00:00', '2021-09-28 06:00:00', '2021-09-29 06:00:00', '2021-04-08 06:00:00'
  • periodto, examples: '2021-04-21 06:00:00', '2021-09-24 06:00:00', '2021-09-25 06:00:00', '2021-09-26 06:00:00', '2021-09-27 06:00:00', '2021-09-28 06:00:00', '2021-09-29 06:00:00', '2021-09-30 06:00:00', '2021-04-09 06:00:00'
  • periodtype, examples: 'day'
  • pointkey, examples: 'ITP-00535', 'ITP-00019', 'ITP-00537', 'ITP-00538', 'ITP-00015', 'ITP-00027', 'ITP-00049', 'ITP-00051', 'ITP-00061', 'ITP-00076'
  • pointlabel, examples: 'Deutschneudorf EUGAL Brandov ', 'Überackern ABG (AT) / Überackern (DE)', 'Balassagyarmat (HU) / Velké Zlievce (SK)', 'Bunde (DE) / Oude Statenzijl (H) (NL) (GASCADE)', 'Cieszyn (PL) / Český Těšín (CZ)'
  • pointtype, examples: 'Cross-Border Transmission IP within EU', 'Storage point', 'Distribution Point', 'Cross-Border Transmission IP between EU and Non-EU (import)', 'Aggregated Point - Final Consumers'
  • tsoeiccode, examples: '21X-GB-A-A0A0A-7', '21X-DE-C-A0A0A-T', '21X-NL-A-A0A0A-Z', '21X-GR-A-A0A0A-G', '21X-DE-D-A0A0A-K', '21X-BE-A-A0A0A-Y', '21X-PL-A-A0A0A-B', '21X-DE-H-A0A0A-L', '21X000000001304L', '21X-IT-A-A0A0A-7'
  • tsoitemidentifier, examples: '21Z0000000004839', '21Z000000000002E', '21Z0000000004863', '21Z000000000489Y', '21Z0000000000074', '21Z0000000000228', '21Z000000000044Z', '21Z000000000056S', '21Z000000000061Z', '21Z000000000074Q'
  • unit, examples: 'kWh/d'
  • value, examples: '0', '9048000', '1', '168000', '1000', '7', '2', '6', '39730478', '278'

Some tentative examples[edit | edit source]

DE/DK flows

Emulate German THE data for Denmark exports 16 July 2022 https://www.tradinghub.eu/en-gb/Publications/Transparency/Market-area-monitor

select '====', operator.operatorlabel, value, flows.directionkey, ispipeinpipe, * from flows left outer join operator on flows.pointkey = operator.pointkey and flows.directionkey = operator.directionkey and flows.operatorkey = operator.operatorkey where tsocountry = 'DE' and adjacentcountry = 'DK' and date(periodfrom)='2022-07-16' and value > 0 order by operator.operatorlabel; Output still needs to be cleaned for a duplicated directions and tsoitemidentifier (tptsoitemlabel = VIP DK-THE).

Confirm no flow through Nordstream 1 on 15 July 2022: select '======================', ispipeinpipe, operator.operatorlabel, * from flows left join operator on flows.pointkey = operator.pointkey where tsocountry = 'DE' and adjacentcountry = 'RU' and date(periodto)='2022-07-15' order by operator.operatorlabel;


Challenges[edit | edit source]

Data quality:

> 1000 GWh flow (probably erroneous data)

Give out consumption (apparently underestimation)

select left(operatorkey,2), date_trunc('month', periodto), round(sum(value)) from flows where pointkey in (select pointKey from operator where pointtype = 'Aggregated Point - Final Consumers') and flowstatus = 'Confirmed' group by left(operatorkey,2), date_trunc('month', periodto) order by left(operatorkey,2), date_trunc('month', periodto);

Sort by distribution points select pointlabel, operatorlabel, sum(value) from flows where pointkey in (select pointKey from operator where pointtype = 'Aggregated Point - Final Consumers') and periodto > '2022-07-01' and periodto <= '2022-08-01' and left(operatorkey,2) = 'DE' group by operatorlabel, pointlabel order by operatorlabel ;

German cross-border transmissions select left(operatorkey,2), date_trunc('month', periodto), round(sum(value)) from flows where pointkey in (select pointKey from operator where pointtype like 'Cross-Border Transmission IP%' and tsoCountry = 'DE' and isPipeInPipe = 'False') group by left(operatorkey,2), date_trunc('month', periodto) order by left(operatorkey,2), date_trunc('month', periodto);

Czech border transmissions as=# copy (select * from entsog_flows left outer join entsog_operator on entsog_flows.pointkey = entsog_operator.pointkey where entsog_operator.pointkey in (select pointkey from entsog_operator where adjacentcountry = 'DE' and tsocountry = 'CZ' union select entsog_operator.pointkey from entsog_operator where adjacentcountry = 'CZ' and tsocountry = 'DE') order by periodfrom desc) to '/tmp/data.csv' with CSV DELIMITER ',' HEADER;

Python scripts: git clone https://git.embargo.energy/entsog.git

Comments spotted: "Please note that the German dual system in VIP implementation leads to a Co-existence of Virtual Interconnection Points and Interconnection Points and may result in publication of the same physical flow at several points"