This page intended audience is all users of Energi Data Service that wants to dig a little deeper than just downloading data through the GUI.
Energi Data Service exposes an API interface that can be used in a number of contexts, e.g. from Python, from Visual Basic for Application or many other programming tools. Some working knowledge of SQL and general programming skills are required. This is not a tutorial about using an API but is a list of the specifics of the API exposed by Energi Data Service which is relevant to know when using the API service.
Some programming skills required
List of supported API calls
All API calls are derivatives of the full ckan API call specification. The following subset of ckan API calls are supported by EDS
Base URL for API calls: https://api.energidataservice.dk
Full list of supported API calls through the base URL:
- Dataset details:
- Dataset extract:
- Dataset extract with SQL:
- Datasets list:
- Group details:
- Group list:
- Organization details :
- Organizations list:
- Tag details:
- Tag list:
- Generic datastore call:
/datastore_search_sql?sql=SELECT ... from ...
How to search data using the API
The datastore_search_sql action allows a user to search data in a resource or connect multiple resources with join expressions. The underlying SQL engine is the PostgreSQL engine.
Data from the REST API can be extracted using an URL in the format:
id" FROM "table id"
This can easily be tested using Chrome. It is a good idea to install one of the many extension to Chrome that supports formating JSON.
Other tools such as Postman can, of course, also be used.
This can be found in the examples shown when the DATA API button for the Dataset is clicked – see Query example (via SQL Statement).
The list of fields available for a Dataset is shown under the preview of data. Find the value of the property Field for a given attribute. Field Id’s must be enclosed by double quotes
An alias for the table name must be used when the query reads from more than one table to avoid ambiguity between fields from the different tables. Ex.:
https://api.energidataservice.dk/datastore_search_sql?sql=SELECT res."HourDK", res."ResidualConsumption", sp."SpotPriceDKK" FROM "fixedresidualconsumption" as res INNER JOIN "elspotprices" AS sp ON res."HourUTC" = sp."HourUTC" WHERE res."GridCompany" = '860' AND res."HourUTC" > ((current_timestamp at time zone 'UTC') - INTERVAL '2 days')
Join on more tables
To include values from more tables, just add a copy of the ‘inner join’ line and replace the table name and it’s alias.
To filter the output add a WHERE clause like this:
WHERE "GridCompany" = '860'
String values are enclosed in single quotes.
Filters on time
For performance reasons it is recommended to filter on the UTC time field – ex. HourUTC – since it is part of the primary index. To filter on a specific dates are constructed like this:
WHERE "HourUTC" >
If a specific time is needed then use this:
WHERE "HourUTC" >
If it’s relative to current time: `
((current_timestamp at time zone 'UTC') - INTERVAL '2 days')
The sort order of the output is not guaranteed if the sort order is not specified – ex.:
ORDER BY "HourUTC" ASC
Number of records per day for the last 30 days – FCR DK1
https://api.energidataservice.dk/datastore_search_sql?sql= SELECT COUNT(*) AS cnt, to_char(date_trunc('day', "HourUTC"), 'yyyy-mm-dd') AS dc FROM "fcrreservesdk1" WHERE "HourUTC" > ((current_timestamp at time zone 'UTC')-INTERVAL '30 days') GROUP BY date_trunc('day', "HourUTC") ORDER BY date_trunc('day', "HourUTC")
CO2 emission prognosis for the next 6 hours.
Used on the front page of energidataservice.dk – makes use of Common Table Expression.
https://api.energidataservice.dk/datastore_search_sql?sql= WITH b AS ( SELECT "Minutes5UTC", date_trunc('hour', "Minutes5UTC") as hourutc, date_trunc('hour', "Minutes5DK") AS hourdk, "PriceArea", "CO2Emission" FROM "co2emisprog" WHERE "PriceArea" = 'DK1' AND "Minutes5UTC" >= (current_timestamp at time zone 'UTC') AND "Minutes5UTC" < ((current_timestamp at time zone 'UTC') %2B INTERVAL '6 hours') ), a AS ( SELECT hourutc, CAST(AVG("CO2Emission") as INTEGER) AS CO2 FROM b GROUP BY hourutc ORDER BY hourutc ASC LIMIT 6 ) SELECT distinct to_char(b.hourDK, 'HH24:MI') AS "Minutes5DK", b."PriceArea", b.hourDK, a.CO2 as "CO2Emission" FROM a INNER JOIN b ON a.hourutc = b.hourutc ORDER BY b.hourDK ASC LIMIT 6
One thing to notice here is that some characters are being interpreted when the URL is being parsed by the
browser. Plus (+) – is one of them. This is fixed by replacing
%2B like this
%2B INTERVAL ‘6 hours’