This article today is from data scientist and Delphi developer Max Kleiner. Max is the author of the MaXbox which Max’s site describes as “a precompiled object based scripting tool”. Max describes his use of RAD Studio with Delphi to develop Windows software and how he uses JSON objects from HTTP requests to visualize aggregated gas storage inventory data into graphs and charts.
Table of Contents
Data representation of gas in storage as a timeline AGSI dataset
“There are many kinds of data scientists: a) Those who dive in data are the best.“
This data science tutorial explains the so called AGSI data storage and his visualization of the timeline. AGSI is the Aggregated Gas Storage Inventory and offers you the possibility to be kept up to date whenever a new service announcement or update from one of our data providers is posted on the website.
The Gas Infrastructure Europe (GIE) is also providing related data such as the Storage Map and Storage Investment Database at https://www.gie.eu/publications/maps/
What will the result of the data chart look like?
The result of the data will be the chart below.
What components do you use to download and chart the data?
We use WinHttp.WinHttpRequest, JSONObjects and TEECharts library with loading and testing the plot. Also an API-key is needed, get the key first at: https://agsi.gie.eu/account
What does the data represent?
The data represents gas in storage at the end of the previous gas day. Data is updated every day at 19:30 CET and a second time at 23:00. Before we dive into code this is the main part of the script:
1 2 3 4 5 |
plotform:= getForm2(1400, 600, clsilver, 'Sciplot4maXbox'); plotform.icon.loadfromresourcename(hinstance,'ZHISTOGRAM'); HttpResponse:= getEnergyStreamJSON2(URL_AGSIAPI2,'DE,2022-01-03,150',AGSI_APIKEY); JSON2Plot(plotform, letGenerateJSON2(HttpResponse)); |
What does the code do?
The main part generates a form, invokes the API and plots the data.
GIE is providing an API (Application Programming Interface) service on its AGSI and ALSI transparency publication platforms.
Using API access, consumer can bypass the AGSI and ALSI website and get hold of the data directly and continuously. It enables to extract, filter, aggregate the data and create any subset as required, without having to download each dataset separately from the website. The API export format is JSON. For example a subset of 150 days:
What is in the data you are charting?
The published datasets are based on the EIC code mapping table provided to ACER. Storage data is aggregated by company, and country.
With the call I pass country, start-date and number of days:
1 |
getEnergyStreamJSON2(URL_AGSIAPI2,'DE,2022-01-03,150',AGSI_APIKEY); |
All available datasets can be downloaded also in Excel, CSV and JSON format. The data in this report shows an aggregated view – individual datasets by company and storage facility are also accessible.
How to call the API to get some data
So, let’s start with the API call:
1 |
HttpResponse:= getEnergyStreamJSON2(URL_AGSIAPI2,'DE,2022-01-03,150',AGSI_APIKEY); |
This command and script runs WinHttp.WinHttpRequest
. When you fail you get a bunch of exceptions like the following:
1 2 3 4 5 6 7 8 |
Exception: WinHttp.WinHttpRequest: The data necessary to complete this operation is not yet available; or you missed a valid key: AGSIPost: Failed at getting response:403{ "error": { "code": 403, "message": "The request is missing a valid API key.", "status": "PERMISSION_DENIED" }} |
The funny thing is the JSON formatted exception. Be careful, also, to expose your key as I get from Git: GitGuardian has detected the following Google API Key exposed within your GitHub account.
How to get JSON data from the API
Next is the formatting of the get-call with a valid API-key request in the function energyStream()
1 2 3 4 5 6 7 8 |
function getEnergyStreamJSON2(AURL, feedstream, aApikey:string): string; ... encodURL:= Format(AURL,[HTTPEncode(asp[0]),(asp[1]),asp[2]]); writeln(encodurl) //debug hr:= httpRq.Open('GET', encodURL, false); httpRq.setRequestheader('user-agent',USERAGENTE); httpRq.setRequestheader('x-key',aAPIkey); ... |
And where is the fabulous content-type? As far as I understood there are only two places in a web-request where to set a content-type:
- The client sets a content type for the body he is sending to the server (e.g. for get and post).
- The server sets a content type for the response.
Making sure there is a Content-Type available
A sender that generates a message containing a payload body has to generate a Content-Type
header field in that message unless the intended media type of the enclosed representation is unknown to the sender; otherwise, we failed at getting response: 503503 - Service Unavailable
.
1 2 |
('headers={"Content-Type":"application/json"}') httpRq.setRequestheader('Content-Type',application/json); |
It means that the content-type HTTP header should be set only for PUT and POST requests. GET requests can have “Accept” headers, which say which types of content the client understands. The server can then use that to decide which content type to send back.
As an option you can also use TALWinInetHttpClient
. It is a easy to use WinInet-based protocol and supports HTTPs. HTTP client component which allows to post and get any data from the Web via HTTP protocol.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
function TALHTTPClient_Get(aUrl: AnsiString; feedstream, aApikey: string): string; Var LHttpClient: TALWininetHttpClient; asp: TStringArray; begin LHttpClient:= TALWininetHttpClient.create; asp:= splitStr(feedstream,','); LHttpClient.url:= Format(AURL,[HTTPEncode(asp[0]),(asp[1]),asp[2]]); LHttpClient.RequestMethod:= HTTPmt_Get; //HTTPrm_Post; LHttpClient.RequestHeader.UserAgent:=USERAGENTE; //LHttpClient.RequestHeader.CustomHeaders:= LHttpClient.RequestHeader.RawHeaderText:='x-key:'+aAPIkey; try result:= LHttpClient.Get1(LHttpClient.url); //overload; finally LHttpClient.Free; end; end; |
How do we convert a JSON response into usable data for the chart?
Any missing or incomplete data is also visible on AGSI. Next, we step to the conversion of our JSON response for the plot with TJSONObject
:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
function letGenerateJSON2(HttpRqresponseText: string): TJSONArray; var jo: TJSONObject; begin jo:= TJSONObject.Create4(HttpRqresponseText); try //writeln(jo.getstring('data')); writeln(itoa(jo.getjsonarray('data').getjsonobject(0).length)) writeln(itoa(jo.getjsonarray('data').length)) result:= jo.getjsonarray('data'); //write out to check for it:= 0 to result.length-1 do writeln(result.getjsonobject(it).getstring('gasDayStart')+':'+ result.getjsonobject(it).getstring('injection')); except writeln('EJson: '+ExceptiontoString(exceptiontype, exceptionparam)); end; end; |
And this JSON Array as above function returns, we pass to the next plot:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
procedure JSON2Plot(form1: TForm; jar: TJSONArray); var chart1: TChart; cnt: integer; sumup,tmp2,tmp: double; gday: string; begin form1.onclose:= @Form_CloseClick; chart1:= ChartInjector(form1); sumup:=0; tmp2:=0; tmp:=0; try for cnt:= 0 to jar.length-1 do begin //writeln(locate.getjsonobject(it).getstring('gasDayStart')+':'+ tmp:= jar.getjsonobject(jar.length-1-cnt).getdouble('injection'); tmp2:= jar.getjsonobject(jar.length-1-cnt).getdouble('full'); sumup:= sumup+tmp; gday:= jar.getjsonobject(jar.length-1-cnt).getstring('gasDayStart'); chart1.Series[0].Addxy(cnt,tmp,gday,clgreen); chart1.Series[1].Addxy(cnt,tmp2,'',clred); chart1.Series[2].Addxy(cnt,jar.getjsonobject(jar.length-1-cnt).getdouble('withdrawal'),'',clyellow); end; except writeln('EPlot: '+ExceptiontoString(exceptiontype, exceptionparam)); end; PrintF('Landrange %d: Injection sum: %.2f',[jar.length-1,sumup]); end; |
What is in the chart series data?
As we can see we have 4 series to plot (including timeline):
- Injection (Injection during gas day)
- Full (Storage / WGV (in%))
- Withdrawal (Withdrawal during gas day (2 digits accuracy)).
- GasDayStart (The start of the gas day reported)
The time series is a result of the gas day and a trend is available.
“Gas day” means the period from 5:00 to 5:00 UTC the following day for Wintertime and from 4:00 to 4:00 UTC the following day when daylight saving is applied. Gas day is to be interpreted as UTC+1 for CET or UTC+2 in Summertime for CEST. (Definition: see CAM Network Code specifications).
How do we access the API for the data?
API access is provided in a REST-like interface (Representational State Transfer) exposing database resources in a JSON format with content-type in the mentioned Response Header.
Where can other developers access this data?
The code of the data science vision contains example usage, and runs under Python3, Delphi, Jupyter-Notebook, object Pascal and maXbox4. Note that the API service is made available to the public free of charge. ONLY the data as currently available on the platforms is made available.
Tip: To extract data direct from the system, you can click on one of these links in a browser (web traffic versus API traffic) AGSI+ https://agsi.gie.eu/api?type=eu
What have we learned about the GIE data API?
GIE is providing an API (Application Programming Interface) service on its AGSI and AGSI+ storage data. The API documentation is on progress and provides examples and guidance on how to use the service and is available after registration to get an API-key. Below zoom of the past half year:
Where can people find your example scripts and code?
The scripts and images can be found at https://github.com/maxkleiner/agsi-data
Do you have an example showing the download code?
Yes, here’s a WinAPIDownload class from maXbox4 integration.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
TWinApiDownload = class(TObject) private fEventWorkStart: TEventWorkStart; fEventWork: TEventWork; fEventWorkEnd: TEventWorkEnd; fEventError: TEventError; fURL: string; fUserAgent: string; fStop: Boolean; fActive: Boolean; fCachingEnabled: Boolean; fProgressUpdateInterval: Cardinal; function GetIsActive: Boolean; public constructor Create; destructor Destroy; override; function CheckURL(aURL: string): Integer; function Download(Stream: TStream): Integer; overload; function Download(var res: string): Integer; overload; function ErrorCodeToMessageString(aErrorCode: Integer): string; procedure Stop; procedure Clear; property UserAgent: string read fUserAgent write fUserAgent; property URL: string read fURL write fURL; property DownloadActive:Boolean read GetIsActive; property CachingEnabled:Boolean read fCachingEnabled write fCachingEnabled; property UpdateInterval:Cardinal read fProgressUpdateInterval write fProgressUpdateInterval; property OnWorkStart: TEventWorkStart read fEventWorkStart write fEventWorkStart; property OnWork: TEventWork read fEventWork write fEventWork; property OnWorkEnd: TEventWorkEnd read fEventWorkEnd write fEventWorkEnd; property OnError: TEventError read fEventError write fEventError; end; |
References and additional links
- https://agsi.gie.eu/api
- https://www.gie.eu/transparency-platform/GIE_API_documentation_v006.pdf
- https://svn.code.sf.net/p/alcinoe/code/demos/ALWinInetHTTPClient/_source/Unit1.pas
- https://docwiki.embarcadero.com/Libraries/Sydney/en/System.Net.HttpClient.THTTPClient.Post
- Doc and Tool: https://maxbox4.wordpress.com (Script Ref: 1154_energy_api_agsi_plot14.txt)
This article was submitted as part of our Enterprise Article Showcase. If you have a success story to tell about a project which makes use of RAD Studio with Delphi or C++ Builder, or any of our other great enterprise products please get in touch, you could be featured too and win a prize! Read all about it here: Enterprise Article Showcase
Design. Code. Compile. Deploy.
Start Free Trial Upgrade Today
Free Delphi Community Edition Free C++Builder Community Edition
Very useful report, thanks, that brings me to the question if there is an open source (or commercial) and/or free statistics package or library for Delphi as a standard?
There are a lot of packages around, but many are commercial (paid) rather than open source. I haven’t tried this one myself, but many people recommend the FastMath library: https://github.com/neslib/FastMath – the source is available under the BSD license which may be suitable for you. There is a quite comprehensive help file here: https://neslib.github.io/FastMath/
Let me tell you why I, a mathematician, chose Delphi for a powerful real-world real-time Stereo Visualization since 2004 (see the Reference). Delphi graphics happened to be an ideal tool for implementing the 3D stereo anaglyph visualization of real-time processes because of the following reason.
1. Plotting on Canvas of Form or of Paintbox is a fast method suitable for real-time plotting and real-time animation, which is the unique feature of the Taylor solver called the Taylor Center.
2. The assortment of graphic routines with various copy modes, allowing to plot pixels and lines in Red or Blue not affecting each other, as though having three independent overlapping displays (in Red, Blue, and Green). These copy modes were absolutely crucial for plotting two images of stereo pairs in Red and Blue, doing it fast enough for real-time animation. And they allowed also the implementation of 3D stereo tubular graphics.
All the above is a huge achievement of Delphi, a high-level language, which allowed to develop all that so comfortably in terms of device-independent graphics, smoothly working with all graphic cards! Amazing!
References
Delphi Informant Magazine:
Do-It-Yourself 3D, Aug. 2004, Vol 10, #8
3D Delphi: Stereo Vision on Your PC, Jan. 2004, Vol 10, #1
Also here: Examples of applications and articles in scientific Delphi
http://taylorcenter.org/Gofen/Teaching/
http://taylorcenter.org/Gofen/TaylorMethod.htm
The more we call such things like an OpenAPI, the more we need also a kind of specification. An OpenAPI document that conforms to the OpenAPI specification is itself a JSON object, which may be represented either in JSON or YAML format.
We find out that a seasonal pattern over 5 years can be found, see image link below:
https://softwareschule.code.blog/wp-content/uploads/2022/10/2024-06-12-1.png?w=1024