ENTSOG data analysis

From embargo
Jump to navigation Jump to search

ENTSOG data as relational database

(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 entsog ; password: 9mTc_9hPjk}&D27/F,VHC8qG

Some fields:

Table operator:

  • 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

  • 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

> 1000 GWh flow (probably erroneous data)

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

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);

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"

Retrieved from "https://embargo.energy/index.php?title=ENTSOG_data_analysis&oldid=1245"