API Guidance
Introduction
This page provides further details on the Data Portal’s Application Programming Interface (API), for users who wish to write code to access information from the Data Portal site (rather than downloading through the user interface)
The intention of this guide is to provide an overview of the functionality and not to serve as a tutorial for using an API. To fully utilise the API functionality, general programming skills and knowledge of SQL are needed.
List of supported API calls
The base URL for the calls is: https://data.nationalgrideso.com/api/3/action/
It is worth noting that the naming conventions in the API differ from those in the Data Portal, the table below shows how the terms relate:
| API | Data Portal |
| Organization | Data Group |
| Package | Dataset |
| Resource | Data File |
The Data Portal has been built using the CKAN platform, the following subset of CKAN endpoints are supported on the Data Portal:
| End Point | Example | Description |
| organization_list | https://data.nationalgrideso.com/api/3/action/organization_list | Displays list of Data Groups on the Data Portal |
| package_list | https://data.nationalgrideso.com/api/3/action/package_list | Display list of datasets on the Data Portal |
| tag_list | https://data.nationalgrideso.com/api/3/action/tag_list | Display a list of tags on the Data Portal |
| package_search?q={query} | https://data.nationalgrideso.com/api/3/action/package_search?q=BSUOS | Displays datasets matching a criteria |
| resource_search?q={query} | https://data.nationalgrideso.com/api/3/action/resource_search?query=name:BSUOS | Displays data files matching a criteria |
| resource_show?q={query} | https://data.nationalgrideso.com/api/3/action/resource_show?id=a16abb3c-36bd-448f-9263-b25d90ca08a0 | Displays the metadata of a resource. |
| package_show?id={dataset_id} | https://data.nationalgrideso.com/api/3/action/package_show?id=embedded-wind-and-solar-forecasts | Displays dataset details |
| datastore_search | https://data.nationalgrideso.com//api/3/action/datastore_search?resource_id=db6c038f-98af-4570-ab60-24d71ebd0ae5&limit=5 Parameters here | Displays search data in a tabular file |
| datastore_search_sql?sql={sql} | Parameters here | Search data in a tabular file with SQL: Examples are provided in the section below. |
Querying data via SQL
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.
Example:
/datastore_search_sql? sql=SELECT”field id” FROM “resource id”
Field ID
Field IDs must be in double quotes and can be found by previewing the table view on the explore section of any resource. For example:
Resource ID
The resource ID can be obtained by accessing the “API” button from any resource page and will be in the “Querying” section under the “Query example (via SQL statement)” or from the bottom of the dataset page under “Integrate this dataset using cURL” section. The resource ID must also be represented in double quotes.
Examples:
| Example | End Point | Description |
| Return records matching field value | http://data.nationalgrideso.com/api/3/action/datastore_search_sql?sql=SELECT * FROM “aec5601a-7f3e-4c4c-bf56-d8e4184d3c5b” WHERE “CARDINALPOINT” = ‘1B’ | The resource ID “aec5601a-7f3e-4c4c-bf56-d8e4184d3c5b” is the day ahead demand forecast data file, and the results are being filtered by cardinal point “1B” which is the lowest demand of the day. |
| Return an aggregated value based on search parameters | http://data.nationalgrideso.com/api/3/action/datastore_search_sql?sql=SELECT SUM(“Constraints”) from “7bcd8e25-c148-4cdb-b46f-394f88b92db5” WHERE “SETT_PERIOD” BETWEEN ‘7’ AND ’14’ AND “SETT_DATE” = ‘2020-04-01T00:00:00’ | This query returns the constraints spend for EFA block 2 on 01/04/2020 from the resource ID “7bcd8e25-c148-4cdb-b46f-394f88b92db5” which is the daily costs table 2019-2020. |