ENTSOG data analysis - Revision history

77.182.187.230 at 06:39, 17 October 2022

2022-10-17T06:39:36Z

77.189.24.88 at 13:20, 28 August 2022

2022-08-28T13:20:17Z

HolgerB: /* Some tentative examples */

2022-08-16T22:00:29Z

Some tentative examples

HolgerB: /* Some tentative examples */

2022-08-16T22:00:07Z

Some tentative examples

HolgerB at 19:02, 16 August 2022

2022-08-16T19:02:05Z

HolgerB at 18:59, 16 August 2022

2022-08-16T18:59:55Z

HolgerB at 18:58, 16 August 2022

2022-08-16T18:58:29Z

HolgerB: Created page with ""

2022-08-16T18:57:22Z

Created page with "<img src="Screen.png">"

New page

@@ Line 86: / Line 86: @@
 <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);
 </code>

@@ Line 4: / Line 4: @@
 (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:

@@ Line 54: / Line 54: @@
 == Some tentative examples ==
-[[File:Screen-2022-08-16-20-46-24.png|thumb|> 1000 GWh flow (probably erroneous data)]]
+[[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

@@ Line 54: / Line 54: @@
 == Some tentative examples ==
 Emulate German THE data for Denmark exports 16 July 2022 https://www.tradinghub.eu/en-gb/Publications/Transparency/Market-area-monitor
 <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;

@@ Line 1: / Line 1: @@
-=== ENTSOG data as relational database  ===
+= ENTSOG data as relational database  =
 (Experimental, physical flow data from 01 Jan 2021 to 05 Aug 2022, where flow value is not null)
@@ Line 8: / Line 8: @@
 Some fields:
-====== Table operator: ======
+== Table operator: ==
 * adjacentcountry: non-TSO country (includes non-EU countries such as RU, MK, NO)
@@ Line 30: / Line 30: @@
 * virtualreverseflow, examples: 'No', 'Yes'
-===== Table: flows =====
+== Table: flows ==
 * bookingplatformkey, examples: '', 'PRISMA', 'RBP', 'GSA', 'None'
 * directionkey, examples: 'exit', 'entry'
@@ Line 52: / Line 52: @@
 * value, examples: '0', '9048000', '1', '168000', '1000', '7', '2', '6', '39730478', '278'
-===== Some tentative examples =====
+== Some tentative examples ==
 Emulate German THE data for Denmark exports 16 July 2022 https://www.tradinghub.eu/en-gb/Publications/Transparency/Market-area-monitor
@@ Line 65: / Line 65: @@
 </code>
 Give out consumption (apparently underestimation)
@@ Line 85: / Line 89: @@
 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"

← Older revision		Revision as of 18:59, 16 August 2022
Line 1:		Line 1:
		+
		+	=== 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: <code> psql -h embargo.energy -U embargo entsog </code>; 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', <nowiki>''</nowiki>, '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 =====
		+
		+	Emulate German THE data for Denmark exports 16 July 2022 https://www.tradinghub.eu/en-gb/Publications/Transparency/Market-area-monitor
		+	<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;
		+	</code>
		+	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:
		+	<code>
		+	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;
		+	</code>
		+
		+
		+	Give out consumption (apparently underestimation)
		+
		+	<code>
		+	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);
		+	</code>
		+
		+	Sort by distribution points
		+	<code>
		+	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 ;
		+	</code>
		+
		+	German cross-border transmissions
		+	<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);
		+	</code>
		+
		+	Python scripts: <code>git clone https://git.embargo.energy/entsog.git</code>
		+
		+	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"
		+
	[[File:Screen.png\|thumb\|> 1000 GWh flow (probably erroneous data)]]		[[File:Screen.png\|thumb\|> 1000 GWh flow (probably erroneous data)]]
	<img src="Screen.png">		<img src="Screen.png">

← Older revision		Revision as of 18:58, 16 August 2022
Line 1:		Line 1:
		+	[[File:Screen.png\|thumb\|> 1000 GWh flow (probably erroneous data)]]
	<img src="Screen.png">		<img src="Screen.png">