EUROPEAN COMMISSION - DG ENVIRONMENT
TECHNICAL SUPPORT IN RELATION TO THE IMPLEMENTATION OF THE WATER FRAMEWORK DIRECTIVE (2000/60/EC) – summary report of Wise testing
Report Ref: REP2005/1
NOVEMBER 2005
TECHNICAL SUPPORT IN RELATION TO THE IMPLEMENTATION OF THE WATER FRAMEWORK DIRECTIVE (2000/60/EC) – summary report of Wise testing
Report Ref.: REP2005/1
November 2005
Authors: J Cima, D Mills, D Shepherd, Y Walker
The contents of this document are subject to copyright and all rights are reserved. No part of this document may be reproduced, stored in a retrieval system or transmitted, in any form or by any means electronic, mechanical, photocopying, recording or otherwise, without the prior written consent of the copyright owner.
This document has been produced by WRc plc.
Document History
1st Draft / 3 November 2005 / DS/JC/YW (WRc)
Support Contract – Summary Report of WISE Testing
CONTENTS
1. WISE TesT: SUMMARY OF ACTions 3
2. General comments (Appendix A) 4
3. Access tool (Appendix B) 5
3.1 Bugs 5
3.2 Design and functionality changes 5
3.3 Unresolved issue 5
3.4 User guide 6
4. Format issues (Appendix C, E) 7
4.1 Mandatory fields 7
4.2 Memo fields 7
4.3 Decimal separators 7
4.4 Scale format 8
4.5 Volumes 8
4.6 Year format 8
4.7 Fundamental design of the schemas 8
5. TYPE DEFINITIONS (Appendix C, E) 9
6. WISE (Appendix D) 10
7. Possible future modifications 11
8. Schemas – Lesson learned 12
8.1 Reporting Guidance Document 12
8.2 Schema Design 12
8.3 User Review Group 12
8.4 Reference Schemas 13
8.5 Mandatory elements 13
8.6 Units of measurement 13
8.7 Stylesheets 14
8.8 Effect of change 14
APPENDICES
appendix a general comments 15
appendix b access tool 30
appendix c xml 97
appendix d wise 102
appendix e access tool – schema design issues 112
Support Contract – Summary Report of WISE Testing
1. WISE TesT: SUMMARY OF ACTions
All comments received from testers were collated into five general categories. Reports of each of these categories are appended:
· Appendix A – General comments
· Appendix B – Access tool
· Appendix C – XML
· Appendix D – WISE
· Appendix E – Access tool schema issues
These reports include, where appropriate, notes on any actions taken. The sections below summarise the main points arising from each general category.
Appendix E is a sub-set of issues collated under the Access tool category (Appendix B) but which refer to aspects of schema design.
The schema was amended based on comments and has been circulated to the GIS Working Group Activity 1 team for information.
2. General comments (Appendix A)
The WISE website was considered easy to use.
Testers considered the Access tool to be useful for data entry and for generating XML files. It was considered to be easy or moderately easy to use.
3. Access tool (Appendix B)
3.1 Bugs
When an RBD is deleted, the corresponding records in ALL tables are now deleted. A related bug which, under certain circumstances, caused duplicate records to be created in table SWPI3_SWPI4_GWPI3_GWPI4, has been fixed.
Invalid default values for some fields in tables GroundWaterBodies and SurfaceWaterBodies have been removed.
A duplicated label on form GWPI1 has been corrected.
An error in the control level validation for one textbox on form GWPI5 has been corrected.
3.2 Design and functionality changes
A form has now been provided which enables direct entry data (SurfaceWaterBodies, GroundWaterBodies, and ProtectedAreas tables) to be viewed as datasheets. This form includes form-level validation and, both separately and as part of the validation, a function that checks, and if necessary attempts to modify, the format of imported LAT and LON values.
The Validation Errors form has been modified to include more information to help identify the control (textbox or drop-down list) on the form bound to the field causing the error. The problem value (if there is one) is now shown. The way in which form-level validation error messages are created has been changed to tailor the error message to the actual error.
The River Basin District and XML File Creation Record forms have been modified to allow them to be closed without having to enter certain values.
The schema location paths specified on the XML File Creation Record form can now be changed, to allow, if required, XML file creation from local copies of the schemas when working off-line. The default paths can easily be reinstated.
The form-level and point-of-entry validation of text values now makes allowance for any XML tokens when testing against maximum permissible length.
3.3 Unresolved issue
As originally designed, the RBD stylesheet would only run if the XML validation was successful. It was suggested that it would be useful to be able to run the stylesheet without having to ensure the data is valid, as a means of visually inspecting the data. This was attempted but could not be made to work. The original restriction therefore remains.
3.4 User guide
The user guide has been updated in accordance with the changes made to the tool.
Appendices have been added to:
· show the elements and element codes of code lists
· describe the system fields that are included in some tables
4. Format issues (Appendix C, E)
4.1 Mandatory fields
New elements have been added to some code lists, to allow meaningful entry where a field restricted to elements in a code list is mandatory but the information required cannot be supplied. This includes values for ‘unknown’, ‘not applicable’ and ‘yet to be measured/determined’ as appropriate.
All numeric fields have been modified to permit the entry of negative integer code values when the actual value required cannot be supplied:
· -9999 = unknown
· -8888 = yet to be measured
· -7777 = not applicable
Three fields that previously were not mandatory are now mandatory for consistency:
· GWPI5: VOL_ABSTRACTION
· SWPI5: VOL_ABSTRACTION
· GWPI6: VOL_RECHARGE
The schema definition and the Access tool have been adapted to reflect these changes.
4.2 Memo fields
The maximum 2000 character length of memo fields has not been changed. This is a limitation imposed by the Oracle database design.
4.3 Decimal separators
The database can now be used with whichever decimal separator (point or comma) is defined by the host PC’s Regional Settings. The XML schema still requires a point separator for decimal numbers, but this has been addressed by including a routine that, if necessary, temporarily changes the defined decimal separator to point (if currently comma) for the duration of the XML creation process, and then changes it back again.
4.4 Scale format
The required format of Scale on form SWB2 has not been changed. However a function has been added to the Access tool that checks the validity of the format before proceeding with form-level validation.
4.5 Volumes
Volumes on forms SWPI5, GWPI5 and GWPI6 can now be entered in exponential format.
4.6 Year format
STATUS_YR for SurfaceWaterBodies and GroundWaterBodies must now be in yyyy format.
4.7 Fundamental design of the schemas
Suggestions were made regarding the design of the schemas. An overall schema re-design was not undertaken as the original design based on the objects being reported had been agreed by the Activity 1 Team. Lessons learned in the development of the schemas are summarised in section 8.
5. TYPE DEFINITIONS (Appendix C, E)
Some changes to type definitions have been made; these are listed in Table 1.
Table 1 Changes to type definitions
Reporting sheet / Field / ChangeGWPI5 / VOL_ABSTRACTION / Type changed from long integer to decimal.
Was optional, now mandatory.
GWPI5 / GWB_CONTENT / Type changed from decimal to percentage.
SWPI5 / VOL_ABSTRACTION / Type changed from long integer to decimal.
Was optional, now mandatory.
SWPI5 / VOL_AGRI / Type changed from long integer to decimal.
SWPI5 / VOL_PWS / Type changed from long integer to decimal.
SWPI5 / VOL_MANU / Type changed from long integer to decimal.
SWPI5 / VOL_ELEC / Type changed from long integer to decimal.
SWPI5 / VOL_FISH / Type changed from long integer to decimal.
SWPI5 / VOL_HYDRO / Type changed from long integer to decimal.
SWPI5 / VOL_QUARRY / Type changed from long integer to decimal.
SWPI5 / VOL_NAVIG / Type changed from long integer to decimal.
SWPI5 / VOL_OTHER / Type changed from long integer to decimal.
GWPI6 / VOL_RECHARGE / Type changed from long integer to decimal.
Was optional, now mandatory.
SWB, SegmentStructure / NAME / Type defined as 250 character string.
ProtArea / AREA / Did have fractionDigits=”2” restriction. This has been removed.
ProtArea / LENGTH / Type declaration was missing. Declared as type decimal.
6. WISE (Appendix D)
Several testers had problems uploading files. Modifications have been made to WISE to resolve these problems.
Problems were encountered by some testers when trying to open uploaded zip files. A problem specific to the WRc test server was identified as the cause of these problems and resolved.
7. Possible future modifications
The following modifications will not be implemented within the scope of the current project, but are suggested for future development.
1. If the 2000 character limit on memo fields is considered too restrictive, there are, in principle, ways in which it can be overcome. Modifications to the schema design, Access tool and Oracle database will however be needed. The requirement for this change and the impact will therefore need to be determined before proceeding.
2. Stylesheets for SurfaceWaterBodies, GroundWaterBodies and ProtectedAreas can, in principle, be supplied. It is recommended that the appropriate resources are made available.
3. The unresolved issue with Access tool should be addressed to allow the validation reporting stylesheet to be activated when data is invalid.
4. The overall schema design changes are not recommended. Such a change will require the Access tool and schema to be modified as well as changes to both the front-end and back-end databases. If the change is required it must be undertaken before general roll out to users.
8. Schemas – Lesson learned
8.1 Reporting Guidance Document
The reporting guidance document provided the main point of reference for developing the schemas. In moving towards electronic reporting, there is a need to consider how to develop such guidance documents so that, where possible, codes or absolute values are provided rather than summary text which needs to be interpreted. This also minimises translation issues.
It is therefore strongly recommended that these issues are considered when developing such guidance
8.2 Schema Design
The schema were developed based on the requirements outlined in the reporting guidance document. The data required could be seen to split into 3 distinct categories, water body detail, summary data at the RBD level and spatial/GIS data. The schema designs followed this logic by splitting the schemas into the summary data (RBD.xsd) and the water body detail data (SWB.xsd, GWB.xsd, ProtArea.xsd).
Many comments were received on the design of the schemas. Many users felt that, for example, all surface water body data – both summary and detail - should be provided in a single file. However, as most of the data for the waterbody and protected area schemas can be generated from GIS, the design of these schemas is very flat, to enable the data to be easily automatically generated. Also these detail files have the potential to be extremely large, as they define each and every waterbody in the RBD. The summary data requirements require significant text input and are split by reporting sheets as this helped to tie-up the schema requirements with the guidance documents. In addition, with the development of the access tool to help collate the data and generate the XML, that the schema designs were suitable.
Many testers also queried why totals (e.g. number of SWBs in RBD) was required by the RBD schema when this could be determined by calculating the total number provided by the SWB file. The reasons for this are two-fold:
· It provided a cross-reference check (i.e. the numbers of each category should be the same in both the river basin district and the surface water body XML files)
· An XSL stylesheet has been created to convert the complex river basin district XML file to a HTML format report for easy review of the contents. Having all of the relevant summary data in one file facilitates this.
8.3 User Review Group
The GIS Activity 1 group undertook the role to review the schema designs and were involved from the start of the design phase. Such a group provides a valuable assistance in the development of the schemas as the members of the group provided a deeper level of understanding of how the requirements in the guidance document translated to the data held by the Member States.
The review group also provided invaluable assistance in design clarification and in proof-reading and error-checking of the resultant schemas.
It is recommended that such a group be set up for any future schema development and for any future schema review.
8.4 Reference Schemas
In total, 5 schemas have been created to support article 5 schemas. The WFDCommon reference schema provides common type definitions that are used by the other schemas. This allows, for example, a code list to be defined once but used many times in different schemas.
It is possible to define common elements and to reference these, an example would be the Member State 2 character code MS_CD. However, in many cases, it is the Type rather than the element which is common (e.g. DecimalType which only allows a positive decimal value with options for exceptions). Therefore the decision was taken to maintain WFDCommon.xsd as a type-based reference schema.
8.5 Mandatory elements
A perennial issue with schemas is that of mandatory elements. The schemas developed for Article 5 reporting have a number of mandatory elements, usually requiring the user to either select form a code list or to enter a numerical value. This led to many problems during the test phase where data was either not known, yet to be measured or just not applicable.
In some schema designs, a mandatory element can be passed blank or empty. It is then up to the receiving system to determine if this black element implies No data, or Not Applicable etc, or whether the user just forgot to put in a value. The decision was taken to amend all mandatory elements to allow ‘exception’ values as appropriate, as this requires a positive action by the user, and also provides more information to the system (e.g. can distinguish where there is no data as opposed to the requirement not being applicable)