Experience from the data collection exercise for update of the WFD priority substances 2011

1. Experience from the data collection exercise for update of the WFD priority substances 2011 D.PREUX and B.FRIBOURG-BLANC, IOW

2. International Office for Water • Non profit making association (NGO with 150 members), State approved • Created in 1991 (20 years!) • Missions : • international networking: INBO • services in the water domain (Training centre, Information and data Centre) • European and international cooperation: twinning, CIS-SPI, 6th WWF Marseille 2012 • http://www.oieau.org/

3. International Office for Water • A Motto: “Capacity building for better water management!” • THREE DEPARTMENTS Information management Vocational training Institutional cooperation

4. WFD priority substances: the project assistance to DGENV on WFD priority substances One main activity: monitoring based prioritisation Prioritisation methodology (INERIS) + data collection (IOW) EU relevant, scientifically based and pragmatic From universe of substances to a manageable list (30-50 substances) Minimum data requirements Target: pollution of surface water compartments (Water, sediment and biota) (Adapted from V.Bonnomet, INERIS)

5. WFD PS: the data collection steps 1.Define the needs: minimum mandatory and optional fields, voluntary basis 2. Create the End user tool (MS Access) for data collection 3. Data collection + helpdesk 4. Gather data, analyse, define the corrections and quality checks, test and implement 5. Import data in a central database 6. Treat data for prioritisation

6. Tools to support the collection • The Microsoft Access End user tool (+template) • The How to use guide A Web page www.oieau.fr/WISE-end-user-tool and a helpdesk

7. WFD PS: Data collection 2008 and 2009 • A strictly defined template with 22 mandatory fields • Flexibility introduced on 8 fields(longitude/latitude, LoD/LoQ, type of station, date of sampling, biota species, laboratory name) • A flexible data collection tool, and detailed documentation for non experts, to: • minimise reporting burden: except on some validity checks, any fraction, any unit, any substance even with missing mandatory fields is accepted • allow decentralised distribution of the tool (experienced by RO) • maximise quantity of data collected • Interaction between data provider and consultant for • Quality checking before selection of relevant dataset(s) • correction of missing fields and coherence

8. WFD PS: Preliminary data treatment • Only datasets for surface water • River, Lake, Transitional, Coastal, Marine • Elimination of : • data measured before year 2000 • non relevant parameters (e.g. P(tot), Nitrates, etc.) • datasets for which neither LOD nor LOQ were provided • Insufficient precision of matrix or fraction (all ”other”) • Correction • station location if missing • substance name (in national language) or CAS code if detected wrong • unit (in national language) • fraction when it can be gathered with existing fractions • date of sampling or date of analysis format or when one is missing • Harmonisation of the measurement units (130 units on the 1130 substances provided and 102 units on 21 Tin compounds) • Water: µg/l, Sediment: µg/kg dw, Biota: µg/kg ww

9. XML file Internal checks and validation export Discarded (cat. 1 incomplete) End User Tool : one for each data provider import Use for prioritisation Central database WFD PS: Central Database creation • A central database similar to the End User tool (Station, Sampling, Analysis) • structured database (PostGre/PostGIS, SQL Language) • on-line management tools for import of datasets (SQL based interface) • Maintenance tools for storing and saving (SQL calculation modules) • All individual files imported (more than 50 files, up to 40 GBytes)

10. WFD PS: Summary situation Source: GIS layer : Official WFD Districts • Data 2000-2008 • Surface water • 26 Member States + CH and NO • almost 20 000 stations • 4 water body types • 547 000 sampling • 14,6 million analyses • 1 150 substances or groups (PCB and Tin compounds)

11. WFD PS: Apportionment of analyses by year by water category

12. WFD PS: Apportionment of analyses by matrix along analytical result

13. WFD PS: The data quality aspects • Example: performance criteria set in Directive 2009/90/EC: LoQ < 0,3 x EQS

14. WFD PS: Conclusion • The first data collection of regular chemical monitoring data under WFD and a huge database • A collaborative exercise involving EU and MS authorities • A complete report on quality assessments(restricted, accessible to CIS expert)concluded future data collection will require: • Adaptation of the data collection template and tool • additional collection rules (more mandatory fields, checks…) • A checking mechanism on the data content (station location, …) • more quality information (LoD and LoQ, accreditation…) • Use of WISE and INSPIRE reference lists • Possible development could include: • Use of a shared EU wide structured water language (Reportnet) • Use of shared reference lists (Reportnet Data Dictionary, ROD…) • Use of shared scenarios •  for example the French system: SANDRE and EDILABO

15. SANDRE, French data and metadata for waterA cornerstone of French water information system Water Agencies I do not understand the data sent I can not read the format Ministry of Environment RBD authorities Municipalities Laboratories Industries SANDRE I need to develop a specific mapping for each data collection He used codes I do not know • Data interchange in an evolving context: WFD implementation, progress of scientific knowledge… Numerous actors with different needs and tools

16. SANDRE, a collaborative systemfor water professionals • From natural water cycle to anthropogenic water cycle: a number of themes • Drinking water • Surface water • Transitional and coastal water • Waste water • Rainwater • Groundwater From professional language to database interoperability 1 2 3 Experts needs Conceptual model Database

17. SANDRE, the approach 1st STEP: A COMMON SEMANTIC DATA DICTIONARIES FOR EACH THEME CODE LISTS, REFERENCE MAP LAYERS 2nd STEP: COMMON REFERENCES DATA EXCHANGE BY SCENARIOS AND FORMATS 3rd STEP: SCENARIOS XML-XSD All the SANDRE results are freely available www.eaufrance.fr Scenario documentation

18. Extract of data model

19. SANDRE: code list management 1107 PARAMETER UNIT µg/L 133 132 T 90-121 METHOD 3 WATER MATRIX raw water 23 FRACTION ANALYSED The principle: evolving code lists freely available to water community to ease data interchange. http://www.sandre.eaufrance.fr

20. EDILABO, a scenario for water analysis Asks for analyses Analyses results Samples in situ information in situ information Asks for samplings 3 main actors, electronic exchange of sectoral data. Client – public administration or private operator Laboratory Sampler

21. EDILABO: secured data exchange in water field • Started 2001, based on EDIFACT standard and SANDRE, • Target water physico-chemical and microbiological analyses, • Example: <Analysis> <Parameter>1107</Parameter> <Matrix>3</Matrix> <Fraction>23</Fraction> <Result>0.72</Result> <Unit>133</Unit> <Method>132</Method> </Analysis>

22. Main conclusions • Based on WFD PS first experience: • the first voluntary exercise proved feasibility of collecting big datasets • Tool known: can be used for future • Quality of data expected to improve: • QA/QC Directive entered into force Aug. 09 and transposed Aug. 11 (art 4 minimum performance, Art 5 calculation of mean, Art 6 ISO 17025) • EQS Directive • a need to improve robustness and reliability of data collections • More mandatory fields • a need for more automatic data interchanges • French SANDRE proves the feasibility of automating the process • A collaborative work: crucial for the success !!!

23. Thanks for your attention… d.preux@oieau.fr