Introduction

This page intended audience is all users of Energy Data Service that wants to dig a little deeper than just downloading data through the GUI.

Energy 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 Energy 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: /package_show?id={id}
  • Dataset extract: /datastore_search?resource_id={resource_id}
  • Dataset extract with SQL: /datastore_search_sql?sql={sql}
  • Datasets list: /package_list
  • Group details: /group_show?id={id}
  • Group list: /group_list
  • Help: /help_show?name={name}
  • Organization details : /organization_show?id={id}
  • Organizations list: /organization_list
  • Tag details: /tag_show?id={id}
  • Tag list: /tag_list
Some examples

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:

https://api.energidataservice.dk/datastore_search_sql?sql=SELECT"field 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.

Table id

This can be found in the examples shown when the DATA API button for the Dataset is clicked – see Query example (via SQL Statement).

Field id

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

Alias

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.

Filters

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" > date '2017-03-26'

If a specific time is needed then use this:

WHERE "HourUTC" > timestamp'2017-03-26 04:00'

If it’s relative to current time: `

WHERE "HourUTC" > ((current_timestamp at time zone 'UTC') - INTERVAL '2 days')

Sort order

The sort order of the output is not guaranteed if the sort order is not specified – ex.:

ORDER BY "HourUTC" ASC

Other examples

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 + with %2B like this %2B INTERVAL '6 hours'