DISCODATA

Discodata is a public tool and service where anybody can access data published by the European Environment Agency (EEA). It offers two functionalities:

A web application to inspect and query databases using SQL.
An API to get data from databases, using SQL queries, as JSON information.

Web application

Database explorer

It is on the left hand side and it shows a list of available databases that be expanded to access tables and views of them. The information on a database is classified in versions and revisions:

‘v1r1’ means version 1 and revision 1 and ‘v1r4’ means version 1 and revision 4.
‘v1’ means version 1 and is linked to the last revision of version 1. In the example, querying ‘v1’ or ‘v1r4’ is the same.
‘latest’ means the latest version and revision of the database. In the example, querying ‘latest’ or ‘v1r4’ is the same. If there were a ‘v2’ and ‘v2r1’, ‘latest’ would be similar to ‘v2r1’.

Within each version/revision is the list of tables/views available for querying the database.

Query source

It is a text field to introduce the database query to execute. The database server behind Discodata is Microsoft SQL Server. The SQL language to use is, then, Transact-SQL. It is pretty close to Standard SQL, but there are some differences. Other important considerations:

Data Query Language statements are allowed.
Data Definition Language statements are not allowed.
Not all Data Query Language statements are available. For example, ‘WITH’ (Common Table Expressions), is not permitted.
‘*’ can be used as a list of table columns to select.
Calculated or aggregate columns must have a name. For example: select count(*) from [EUNIS].[v1].[Site_Species] generates an error because ‘count(*)’ has no name. This query is correct: select count(*) as number_sites_species from [EUNIS].[v1].[Site_Species]. This is because the result of the query must be translated to JSON format and key-value pairs use the name of a column as key.

After click ‘Submit’ button, Discodata executes the query. The ‘Table view’ area shows the records selected by the query or an error.

API call

The URL to call Discodata’s API an get the result of the query on ‘Query source’ is shown bellow the ‘Submit’ button. The URL can be copied clicking on the ‘Copy’ button or selected the URL with the mouse a copying it. There is more information about the API on the section “Discodata’s API” on this document.

Table view

It shows a table with the records selected from the database using the query on ‘Query source’.The records are paginated. The default page size is 100 records.

Raw response

It shows the records that a query produces as text in JSON format. Discodata’s API provides information in this format.

API

Discodata provides a website that allows to query databases and to test SQL queries. That is an interface for people. The other goal of Discodata is to provide data for applications, websites or any other development that needs to gather information from the databases available at Discodata.

Discodata’s web site shows, or any query, the URL to use to get the data that a query returns. For example, this query:

SELECT *
FROM [EUNIS].[v1].[Site_Species]
WHERE code_site = 'AT1101112'

The URL shown on the web page is:

https://discodata.eea.europa.eu/sql?query=select%20*%0Afrom%20%20%20%5BEUNIS%5D.%5Bv1%5D.%5BSite_Species%5D%0Awhere%20%20%20code_site%20%3D%20'AT1101112'%0A&p=1&nrOfHits=100

We can see that it takes three parameters:

query is the SQL query to execute
p is the numeric value that indicates what page to show.Data Definition Language statements are not allowed.
nrOfHits is the number of records per page. The default value is 100.

It can return records page by page. If the page requested is beyond the last page available, the result of the query is empty.

The JSON text returned by the API contains an object of name “results” that contains an array of objects. Each object contains a record. For example:

{
    "results": [
        {
            "code_site": "AT1101112",
            "id_eunis": 154421,
            "code_2000": "1917",
            "species_name": "Artemisia pancicii",
            "species_group_name": "Flowering Plants",
            "picture_url": ""
        },
        {
            "code_site": "AT1101112",
            "id_eunis": 177070,
            "code_2000": null,
            "species_name": "Pulsatilla vulgaris subsp. grandis",
            "species_group_name": "Flowering Plants",
            "picture_url": "https://eunis.eea.europa.eu/images/species/177070/thumbnail.jpg"
        }
    ]
}

When an error occurs, the JSON text return by the API is:

			{
    "errors": [
        {
            "error": "Invalid object name 'EUNIS.v1.Site_Species2'.",
            "errorcode": 10003
        }
    ]
}

Comparing two database versions

Discodata has a public tool to compare two database versions in order to show the schema differences and help to understand them.

It takes four parameters:

dbName is the name of a database
schema1 is the name of schema 1
schema2 is the name of schema 2
result is the desired output format. "html" or "json" are the valid values.

Example call:

https://discodata.eea.europa.eu/SchemaDifferences.aspx?dbName=fise&schema1=latest&schema2=v1&result=html