e-PRTR Support
The central QA-database of the Pollutant Release and Transfer Register (Thru.de) was extended and completely redesigned in autumn 2012
New is an SQLite database which includes all historical company data which can be downloaded for a more comprehensive data analysis. ENDA is supplying the database with the company operational information. We support the Federal Environment Agency at various points of the e-PRTR reporting process.
In Detail
We support the Federal Environmental Agency, in the acquisition of operating data from the federal states, with the central QA-database for reports in line with the EU PRTR Regulation 166/2006 / EC.
After successful quality assurance the company data is exported in the (XÖV-standard) XML format, to the EU commision. There it is imported into the reporting tools of the Commission, where it is checked and then the report made.
After this the company data is prepared for the presentation module of Thru.de and then the database from which the presentation module obtains its company data is passed on to the BLE (Federal Institute for Agriculture and Food), which operates the website.
There, the public is provided with the respective data for the latest available period.
Currently, two delivery campaigns are conducted annually in which ENDA performs the following tasks:
- Consideration and possible procurement and updating of variable lists (eg. authorities, municipalities)
- Expansion of the QS database by adding new attributes
- Adaptation of the data import to the respective current XML Schema
- Implementation of data imports and initial check of the Federal States data sets
- Data preparation for deployment in the EU, ie. Conversion between geographic coordinates and Universal Transverse Mercator (UTM) coordinates. Filling of blank form fields according to Coordinate and attribute-specifics
- Manual execution of numerous varying data corrections, depending on the campaign type and scope, in close coordination with the Federal Environmental Agency, and in the process create a detailed classification and structured documentation of all changes and altered records.
- Adaptation of EU export modules to changing requirements of the Commission
- Implementation of the EU XML-export and testing of the result with the tools of the Commission
- Carrying out further iterations of data conversion and corrections for a successful EU XML-export
- Analysis of PRTR data and preparation of statistical information for the Federal Environment Agency
- Creation of HTML pages (since 12.12.2012 the new design) with the detailed facility information (release reports) and transmission of the database to the BLE for updating the website
In addition to these campaigns, the following activities are carried out to support PRTR:
- Creation of a SQLite3 database with the data of all reporting years for download by anyone interested in a more comprehensive evaluation
- Maintenance of the e-PRTR web application for quality assurance
- Maintenance of import and export modules
- Adaptation of the HTML representation for the web presence
- Maintaining XML schema and documentation for PRTR data exchange (XML-1, XML-2)
- Support to the Federal Environmental Agency (UBA) in the area of the PRTR
At the 5th workshop for DV-supported applications in soil and contaminated areas, held in Dessau, ENDA presented the structure, applications, data streams and interfaces as well as the Open Source strategy and quality assurance measures of PRTR, in a joint lecture with the representatives of the UBA Department II 2.2 .
In addition, ENDA regularly assists the Environmental Protection Agency in informing international delegations about the organizational and technical structure and operation of the PRTR.
e-PRTR reporting
The central QA-database of the Pollutant Release and Transfer Register was extended in autumn 2012 and completely redesigned. The following changes were implemented
- Transfer of quality inspections carried out so far and the automated corrections into the QA-database
- Chronological reproduction of inspections for the purpose of comparative testing of data from previous reporting years
- Query for confidential company data
- Query by federal state
- Query for secondary activities
- Query by evaluations of pollutants and their accumulated loads
e-PRTR Process migration to the Federal Environmental Agency
ENDA has handed over the e-PRTR delivery process to the Environmental Protection Agency (UBA). For this, the QS database, which was optimized especially for compatibility with the Debian-Squeeze production environment of the presentation module (Thru.de) of the Federal Institute for Agriculture and Nutrition (BLE), was transferred to a virtual machine together with all previously used program modules and put into operation.
The responsible staff of the UBA were instructed in the use of the applications and database and familiarized with the current process descriptions from ENDA's QM system. Subsequently, the forthcoming data delivery spring 2014 was accompanied to ensure a smooth takeover.
With this measure, the extensive know-how for the manual implementation of PRTR delivery campaigns no longer has to be kept in several places, which reduces the costs. In the past, ENDA has developed several automated delivery processes for the PRTR campaigns, the descriptions of which will be consolidated in the near future.