Semantic knowledge base - title

Semantic knowledge base

Pesquisa na Base de Conhecimento

Apresentação de Base de Conhecimento

About the use of Authority tables

We have here a guest article from our colleague WASIK Zbigniew from DG GROW. We think his very practical approach can be an inspiration for other teams in engaging Authority Tables and the other Controlled Vocabularies published by the Publications Office. The original article was updated to stay in sync with the evolution of the Authority tables architecture.

Authority tables consists of harmonised and standardised codes and associated labels managed by the Publications Office for inter-institutional data exchange and consumption by various systems

Those are basically controlled EU Vocabularies for the use within EC systems. Briefly those tables include codes like, countries, currency, file type, languages.

The list of all such code lists (like Country codes) and vocabularies can be see in the dedicated Authority Tables page (and different subpages like https://op.europa.eu/en/web/eu-vocabularies/e-procurement/tables ).

Publication Offices web pages also describe the provided reference data and in most casses also offer the possibility to download manually the respective file in different formats.

Below, you can find a brief description on how to use the such data within your developed system.
All data from authority tables are provided from CELLAR database. The documentation about how to retrieve the data from Cellar are already published and available on the site of the Publication Office: the link refers to documentation how to use Cellar.

It contains many useful examples. Then the implementation will depend on the programming language you are using. It is very important to know how the required datasets are structured. In addition the description the Authorithy tables (Country, Currency, and so on ), Eurovoc and other datasets available on the EU Vocabulary Web site, the SKOS-AP-EU format (link) is followed, so it is advisable to become familiar with the SKOS structure and the various other properties available in the SKOS-AP-EU distribution.

However, for simplicity regarding CELLAR, there is provided SparQL endpoint that can be used with REST commands: below CURL is used for demonstrating how it works. In your systems you need to use REST within your programming language to execute SPARQL queries. If needed, you can see the description of SparQL language. However, in case of accessing the reference data maintained in Authority tables, Publication Office offer support to create SparQL queries made especially for the user needs. It means you can write email to OP-EU-VOCABULARIES@publications.europa.eu in order to get a dedicated SPARQL for your needs.

I will try to explain bellow how these SparQL queries works (to get country codes / names or also for currencies).

Publication office has prepared REST endpoint to get list of required codes and its details given in RDF/XML format, e.g. for country it is:

http://publications.europa.eu/resource/authority/country - it will provide a list of codes for countries and link for each country to get detailed information on given countries, for example below it is for Beligum
http://publications.europa.eu/resource/authority/country/BEL - it is an RDF/XML response with detailed information on country with code "BEL" (Belgium)

Just go to list of authority tables, choose data that you are interested with, e.g below are examples for countries and currencies:

You can see the description of the data set and also manually browse the content. It is advisable to browse the content in order to become familiar with the datasets and its structure.

While seeing the description at the bottom you can see the ID, dates when the data was released / updated, and links for manual download of the data in various format.

For the REST clients the ID is important. You can get RDF/XML response when performing REST GET access to obtain response from http://publications.europa.eu/resource/authority/country or http://publications.europa.eu/resource/authority/currency

Please remember, this database, as all database provided by Publication Office, is available also for public (it means also to Member States and Outsourcing entities that also sometimes develop system for EC).
As this reference data is publicly accessible, it means that from EU Network (where most of GROW systems exist) you need to access Authority tables via Proxy.

With CURL it is just an option --proxy http://usersername:password@your-proxy-ip:port or add export to the shell environment, like in .bashrc in linux machine: export http_proxy=http://usersername:password@your-proxy-ip:port before executing the curl command. The username:password is the one you use for accessing the internet. Check what proxy should be used, for example ps-lux-usr.cec.eu.int:8012 in Luxembourg or ps-bxl-usr.cec.eu.int:8012 in Brussels.

When you get response from authority table, e.g. for country (http://publications.europa.eu/resource/authority/country) it will be a list of countries, then inside you can see an url for each listed countries to get the country details, for example http://publications.europa.eu/resource/authority/country/BEL for Belgium.

All results are given in an RDF/XML serialisation using RDFs that describe each field.

On the back end there is a CELLAR SparQL endpoint that produces these responses. Using SparQL endpoint is recommended as to select appropriate result content (only get what is needed) in order to minimize data bandwidth.

Cellar SparQL endpoint is accessible only using publications.europa.eu and this is currently the one which should be used in case of requesting data from cellar database. In Cellar case, the Publication Office is responsible for different data that is provided as reference data. The SparQL endpoint http://publications.europa.eu/webapi/rdf/sparql is in general accessible from everywhere, however from EC network you have to use/extend REST client command using proxy configuration.

The full description how to use Cellar SparQL endpoint is provided in this documentation.

Briefly, the REST client needs to send a POST command with the "application/sparql-results+json" content type, meaning it has to properly encode data in the right format. The data given by post is a command (or query) to describe what data should be resulted. The query itself should be like

        
          
              
                    prefix cdm: <http:
                    //publications.europa.eu/ontology/cdm#> 

                    prefix skos: <http:
                    //www.w3.org/2004/02/skos/core#>

                    select distinct * where {

                    ?country skos:inScheme <http:
                    //publications.europa.eu/resource/authority/country>.

                    ?country skos:prefLabel
                      ?label .

                    filter (lang(?label)=
                    'en'
                    ).

                    } 

For our examples we put this query to a file "country.rq"

The above query provides list of countries, where the below provides list of currencies

        
          
              
                    prefix cdm: <http:
                    //publications.europa.eu/ontology/cdm#> 

                    prefix skos: <http:
                    //www.w3.org/2004/02/skos/core#>

                    select distinct * where {

                    ?currency skos:inScheme <http:
                    //publications.europa.eu/resource/authority/currency>.

                    ?currency skos:prefLabel
                      ?label .

                    filter (lang(?label)=
                    'en'
                    ).

                    }

For our examples we put this query to a file "currencies.rq"

Please notice that these two queries shown above has to different authority tables mentioned by their ID (Country ID: http://publications.europa.eu/resource/authority/country and Currency ID: http://publications.europa.eu/resource/authority/currency )

You can also do the same for any other Authority tables, for examples like languages.

The above will make the following response, for example as given for country: (response for a curl command: curl -H "Accept: application/sparql-results+json" --data-urlencode "query@country.rq" http://publications.europa.eu/webapi/rdf/sparql is shown below)

If you remove the filter in query then you will get list of all countries and given in all languages. But remember that the response time depends on the size of the dataset you select.

If you want to have a country in a particular language, you need to specify filter (above it is given a filter for one language - in english as shown in example)

If you want to have few selected languages, please make a filter like (to show countries in german and in english):

        
          
              
                    prefix cdm: <http:
                    //publications.europa.eu/ontology/cdm#>

                    prefix skos: <http:
                    //www.w3.org/2004/02/skos/core#>

                    select distinct ?country
                      ?label where {

                    values ?lng {
                    'en'
                    'de'
                    }

                    ?country skos:inScheme <http:
                    //publications.europa.eu/resource/authority/country>.

                    ?country skos:prefLabel
                      ?label .

                    filter (lang(?label)=?lng).

                    }

The reponse for such country.rq file is (curl command: curl -H "Accept: application/sparql-results+json" --data-urlencode "query@country.rq" http://publications.europa.eu/webapi/rdf/sparql)

As you see, this query, given in country.rq, will shows all countries with labels in english and german. If you want to create a different one using more languages just extend the list of languages “values ?lng {'en' 'de'}” with a valid ISO-639-1 code as language code. The usage of “inScheme” is already a preselection that only data is selected regarding country table. The one for currency, or any other authority table works only by replacing country with another table name.

If you want to see all properties for one specific country like Belgium. Just select it following way:

                    prefix cdm: <http:
                    //publications.europa.eu/ontology/cdm#>

                    prefix skos: <http:
                    //www.w3.org/2004/02/skos/core#>

                    select distinct * where {

                    values ?country {<http:
                    //publications.europa.eu/resource/authority/country/BEL>}.
 

                    ?country ?predicate
                      ?object .

                    }

Just see details in the response (it will be quite long JSON file)

If you are only interested in labels in all languages for one country:

        
                    prefix cdm: <http:
                    //publications.europa.eu/ontology/cdm#>

                    prefix skos: <http:
                    //www.w3.org/2004/02/skos/core#>

                    select distinct * where {

                    values ?country {<http:
                    //publications.europa.eu/resource/authority/country/BEL>}.

                    ?country skos:prefLabel
                      ?label .

                    }

If you want to see the name in let say Polish for a country France, then the query should look like:

        
          
              
                    prefix cdm: <http:
                    //publications.europa.eu/ontology/cdm#>

                    prefix skos: <http:
                    //www.w3.org/2004/02/skos/core#>

                    select distinct * where {

                    values ?country {<http:
                    //publications.europa.eu/resource/authority/country/FRA>} .

                    ?country skos:prefLabel
                      ?label .

                    filter (lang(?label)=
                    'pl'
                    ).

                    }

The response will be like

Please make some trials to learn how it works. Also please see documentation on making different type of queries.

As example, below it is
1) a SparQl query that provides the list of ISO-3166-1 ALPHA 2 codes: (if needed it is possible to get ISO-3166-1 ALPHA 3 code, or codes approved by EC council – if you provide data to ESTAT you should use EC Council country codes. )

SparQL to get list of ISO codes for all countries

PREFIX cdm: <http://publications.europa.eu/ontology/cdm#> PREFIX euvoc: <http://publications.europa.eu/ontology/euvoc#> PREFIX skos:<http://www.w3.org/2004/02/skos/core#> PREFIX skosxl: <http://www.w3.org/2008/05/skos-xl#> PREFIX dct: <http://purl.org/dc/terms/> PREFIX lbl: <http://publications.europa.eu/resource/authority/label-type/> select ?concept as ?uri ?label ?code as ?iso

from <http://publications.europa.eu/resource/authority/country> WHERE { values (?scheme ) { (<http://publications.europa.eu/resource/authority/country>) }

# code type values (?notation_type){ (<http://publications.europa.eu/resource/authority/notation-type/ISO_3166_1_ALPHA_2>) }

?concept skos:inScheme ?scheme . ?concept skos:prefLabel ?label . FILTER (lang(?label)= "en" ) optional { ?concept euvoc:xlNotation ?notation . ?notation dct:type ?notation_type . ?notation rdf:value ?code. }

filter(?concept != <http://publications.europa.eu/resource/authority/country/OP_DATPRO>) } order by ?label

2) a SparQL query that provides for example the list of all the country codes associated with a particular concept for a given country ( example for Greece)

SparQL to get all codes for a specified country

PREFIX cdm: <http://publications.europa.eu/ontology/cdm#> PREFIX skos:<http://www.w3.org/2004/02/skos/core#> PREFIX skosxl: <http://www.w3.org/2008/05/skos-xl#> PREFIX dct: <http://purl.org/dc/terms/> PREFIX lbl: <http://publications.europa.eu/resource/authority/label-type/> PREFIX euvoc: <http://publications.europa.eu/ontology/euvoc#> select ?code str(?notation_type_label) as ?type where { values ?c { <http://publications.europa.eu/resource/authority/country/GRC> } ?c euvoc:xlNotation ?notation . ?notation rdf:value ?code.

?notation dct:type ?notation_type . ?notation_type skos:prefLabel ?notation_type_label . filter (lang(?notation_type_label)= "en" ) }

As I mentioned the best is to use the Cellar SparQL endpoint to try the query , like:

and select the Result Format, like here to JSON data format, and click on "Run Query" button to see the response:

to get the ISO codes, names and the link that can be used in other SparQL query to get more details on the country.

However, as I mentioned, you can request via email to OP-EU-VOCABULARIES@publications.europa.eu to get a dedicated SparQL query to obtain data in one call (if possible of course ) - like in the same time to get country codes and languages. Jut write email to them.