Issuu

00_162_2 FHR reports

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 Development of a methodology for updating the water demand of the water allocation model

www.flandershydraulicsresearch.be

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Huysentruyt, S.; Michielsen, S.; Nossent, J.; Pereira, F.; Mostaert, F.

Cover figure © The Government of Flanders, Department of Mobility and Public Works, Flanders Hydraulics Research Legal notice Flanders Hydraulics Research is of the opinion that the information and positions in this report are substantiated by the available data and knowledge at the time of writing. The positions taken in this report are those of Flanders Hydraulics Research and do not reflect necessarily the opinion of the Government of Flanders or any of its institutions. Flanders Hydraulics Research nor any person or company acting on behalf of Flanders Hydraulics Research is responsible for any loss or damage arising from the use of the information in this report. Copyright and citation © The Government of Flanders, Department of Mobility and Public Works, Flanders Hydraulics Research 2021 D/2021/3241/129 This publication should be cited as follows: Huysentruyt, S.; Michielsen, S.; Nossent, J.; Pereira, F.; Mostaert, F. (2021). Modelling water availability and water allocation strategies in the Scheldt basin: Sub report 2 – Development of a methodology for updating the water demand of the water allocation model. Version 2.0. FHR Reports, 00_162_2. Flanders Hydraulics Research: Antwerp Reproduction of and reference to this publication is authorised provided the source is acknowledged correctly. Document identification Customer: Keywords (3-5): Knowledge domains

Flanders Hydraulics Research Water demand, inventory, Water balance,

Text (p.): Confidentiality:

35 ‫ ܈‬No

Author(s):

Huysentruyt, S; Michielsen, S.

Ref.:

WL2021R00_162_2

Water management > Water quantity> Water balance - Water availability> Numerical modelling Appendices (p.): ‫ ܈‬Available online

Control Name Reviser(s):

Project leader:

Signature

Getekend door:Jiri Nossent (Signature) Getekend op:2021-07-08 10:26:16 +02:0 Reden:Ik keur dit document goed

Nossent, J.

Pereira, F

Getekend door:Fernando Pereira (Signat Getekend op:2021-07-01 16:44:19 +02:0 Reden:Ik keur dit document goed

Approval Head of Division:

F-WL-PP10-2 Version 7 Valid as from 3/01/2017

Mostaert, F.

Getekend door:Frank Mostaert (Signatur Getekend op:2021-07-07 16:19:53 +02:0 Reden:Ik keur dit document goed

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Abstract To be able to perform up to date simulations with the water allocation model of the Flemish navigable waterways, it is necessary to update its input regularly. Besides updating the network and possible new structures like new canal reaches, new locks, new pumping regimes, … also the water demand of the different water users should be updated. In contrary to the update of the network – which needs to be done manually – the update of the water demand can be done automatically by means of a new developed tool. This sub report describes the development of this tool.

Final version

WL2021R00_162_2

III

F-WL-PP10-2 Version 7 Valid as from 3/01/2017

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Contents Abstract ............................................................................................................................................................ III Contents ............................................................................................................................................................ V List of tables..................................................................................................................................................... VII List of figures .................................................................................................................................................. VIII 1

Introduction ............................................................................................................................................... 1 1.1

Web enabled updating tool ............................................................................................................... 1

1.2

Water demand updating tool ............................................................................................................ 1

Web enabled tool to update water demand (prototype) ......................................................................... 2 2.1

Analysis of the needs ......................................................................................................................... 2

2.2

Prototype ........................................................................................................................................... 2

2.2.1

Introduction ............................................................................................................................... 2

2.2.2

Design ........................................................................................................................................ 3

2.2.3

Implementation ......................................................................................................................... 3

2.3

2.3.1

Apache Tomcat installation ....................................................................................................... 8

2.3.2

PostGreSQL ................................................................................................................................ 8

2.3.3

Web application (WAR file) ....................................................................................................... 8

2.4 3

Installation ......................................................................................................................................... 8

Conclusion ......................................................................................................................................... 9

Water demand update tool ..................................................................................................................... 10 3.1

Analysis of the needs ....................................................................................................................... 10

3.2

Technical design .............................................................................................................................. 10

3.2.1

General .................................................................................................................................... 10

3.2.2

Important considerations ........................................................................................................ 12

3.2.3

Framework............................................................................................................................... 12

3.2.4

Object Oriented ....................................................................................................................... 12

3.2.5

Comparing algorithm ............................................................................................................... 13

3.2.6

Graphs ..................................................................................................................................... 13

3.2.7

Export module ......................................................................................................................... 14

3.2.8

Configuration file ..................................................................................................................... 14

3.2.9

Output ..................................................................................................................................... 15

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

3.3

Implementation ............................................................................................................................... 15

3.4

Testing ............................................................................................................................................. 16

3.5

Installation of the water demand update tool ................................................................................ 16

3.6

Use of water demand update tool .................................................................................................. 19

3.6.1

Prerequisites ............................................................................................................................ 19

3.6.2

Start up .................................................................................................................................... 20

3.6.3

Data information ..................................................................................................................... 22

3.6.4

Helpful tools ............................................................................................................................ 28

3.6.5

Linkage ..................................................................................................................................... 29

3.6.6

Graphs ..................................................................................................................................... 29

3.6.7

Export ...................................................................................................................................... 33

3.6.8

Configuration ........................................................................................................................... 33

3.6.9

Mails ........................................................................................................................................ 34

Conclusion ............................................................................................................................................... 35

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

List of tables Table 1 – File formats of the different data providers .................................................................................... 11 Table 2 – 3rd Party components ..................................................................................................................... 12

Final version

WL2021R00_162_2

VII

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

List of figures Figure 1 – Objective ........................................................................................................................................... 2 Figure 2 – Hierarchy........................................................................................................................................... 3 Figure 3 – Email ................................................................................................................................................. 3 Figure 4 – Logon screen webtool ...................................................................................................................... 4 Figure 5 – Main screen webtool ........................................................................................................................ 4 Figure 6 – Profile water user ............................................................................................................................. 5 Figure 7 – Editing information ........................................................................................................................... 5 Figure 8 – Profile of Water Administrator ......................................................................................................... 6 Figure 9 – Profile of water Administrator.......................................................................................................... 6 Figure 10 – Reporting graphs ............................................................................................................................ 7 Figure 11 – Validation of the data ..................................................................................................................... 7 Figure 12 – Web interface of tomcat for deploying WAR file ........................................................................... 8 Figure 13 – Excell file with information ........................................................................................................... 10 Figure 14 – Different file formats .................................................................................................................... 11 Figure 15 – New created classes ..................................................................................................................... 12 Figure 16 – Conceptual scheme ...................................................................................................................... 13 Figure 17 – example of generated graph ........................................................................................................ 14 Figure 18 – output directory structure ............................................................................................................ 15 Figure 19 – Object Oriented programmeming method .................................................................................. 16 Figure 20– Installation (screen 1) .................................................................................................................... 17 Figure 21 – Installation (screen 2) ................................................................................................................... 17 sFigure 22 – Installation (screen 3).................................................................................................................. 18 Figure 23 – Installation (screen 4) ................................................................................................................... 18 Figure 24 – Installation (screen 5) ................................................................................................................... 18 Figure 25 – Collected e-mail messages (screen 4)........................................................................................... 19 Figure 26 – Received data by administrators .................................................................................................. 20 Figure 27 – different locations for same administrator .................................................................................. 20 Figure 28 – Splash screen ................................................................................................................................ 20 Figure 29 – Main screen .................................................................................................................................. 21 Figure 30 – Collapsed input ............................................................................................................................. 22 Figure 31 – Data screen ................................................................................................................................... 22

VIII

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Figure 32 – Link file .......................................................................................................................................... 23 Figure 33 – Basin file........................................................................................................................................ 23 Figure 34 – Linkage screen .............................................................................................................................. 24 Figure 35 – Water Administrators ................................................................................................................... 25 Figure 36 – Configuration file ......................................................................................................................... 26 Figure 37 – Priorities ....................................................................................................................................... 26 Figure 38 – dfs0 file ........................................................................................................................................ 26 Figure 39 – dfs0 file creation .......................................................................................................................... 27 Figure 40 – Processing ..................................................................................................................................... 27 Figure 41 – Searching the data grid in columns .............................................................................................. 28 Figure 42 – Group by column to select specific data. ..................................................................................... 28 Figure 43 – Linkage screen .............................................................................................................................. 29 Figure 44 – Users screen.................................................................................................................................. 29 Figure 45 – All basins (screen 1) ...................................................................................................................... 30 Figure 46 – All basins (screen 2) ...................................................................................................................... 30 Figure 47 – Per basin screen ............................................................................................................................ 31 Figure 48 – All sectors (screen 1)..................................................................................................................... 31 Figure 49 – All sectors (screen 2)..................................................................................................................... 32 Figure 50 – Per sector screen .......................................................................................................................... 32 Figure 51 – Export screen ................................................................................................................................ 33 Figure 52– Configuration screen for new data ................................................................................................ 33 Figure 53 – Mails (Sent and Inbox) .................................................................................................................. 34

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

1 Introduction To be able to perform up to date simulations with the water allocation model of the Flemish navigable waterways it is necessary to update its input regularly. Besides updating the network and possible new structures like new canal reaches, new locks, new pumping regimes, … also the water demand of the different water users should be updated. In contrary to the update of the network – which needs te be done manually – the update of the water demand can be done automatically by means of a new developed tool.

1.1 Web enabled updating tool The Flemish water managers are responsible for granting permits to companies that want to withdraw more than 500 m³/y from waterways within their jurisdiction. Based on the net consumption (withdrawal minus discharge) the companies will pay an annual allowance according to the rate registered in the permit. To determine the annual volumes the water managers install flow meters. Each year the water managers collect these measurements to determine the retribution for the different companies. From interviews with water managers it became clear that each has its own registration and invoicing tools. It is this information that is needed for the yearly update of the water allocation model. Initially it was the intention to develop a multi functional web based application. It would enable the water managers to send the yearly reminder to register the annual volumes for withdrawal (and discharge), to calculate the retribution, to create and send the invoice to the companies, … with one application. Besides the formal/financial aspects it would also be possible to visualise the yearly trend of water consumption for each company individually or per sector or per river/canal reach. Because all the collected information on the tapped and discharged volumes would be registered in one tool, it would be easy to convert the data into the model file format so it would be ready to use for modelling. A prototype of this application was developed and presented to the water managers (Brussels 09-05-2014). Despite the advantages, that were acknowledged by the water managers, they preferred to stay with their own applications. They were not ready for the administrative transition to such a new system. Therefore it was decided to develop a new stand alone application that could be used by FHR to update the water demand. Chapter 2 contains more reading on the web based prototype.

1.2 Water demand updating tool This application has two main objectives: (1) to perform an annual update of the water demands and discharges from and into the Flemish navigable waterways and (2) transfer it into ready for use model input time series. It starts with the demand for data from the water managers which have agreed to deliver these data in their chosen format. These data are converted into a standardised format that can be used to evaluate the annual trend in water demand and consumption from the navigable waterways. From this format the data is converted into dfs0 file format which is the only format that can be read by the water allocation modelling software. Actually two types of time series are created after the update. One contains only the data collected from that year. With these file you could run simulations with the water demand of a specific year. The second time series is updated each year. The new data is attached to the previous years so it becomes a time series that contains all historical information (starting from first moment data was asked at the water managers). More (technical) details can be read in chapter 3.

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

2 Web enabled tool to update water demand (prototype) 2.1 Analysis of the needs The objective is to develop a web enabled portal that makes it possible for the “data providers” (companies, water managers) to upload their data. They can log on to the portal and provide their data. This information is uploaded into the web tool and will be used as input for the water allocation model. After the updated water allocation model has run, there is a need for the users and water managers to have a reporting tool. Figure 1 – Objective

2.2 Prototype 2.2.1

Introduction

The components that will be used for this prototype are built on open source technology. The framework that is implemented and used is called Geomajas 1. The web application will store its data into a PostgreSQL 2 database. The extension that will provide the support for geographical objects is called PostGIS 3. This prototype can be hosted at the offices of AnteaGroup for the time that is necessary and will be accessible through a public IP address.

Geomajas is an open source GIS framework for the web. It allows aggregation and transformation of GIS data sources and has components for Web mapping. It was written in Java building on the Spring Framework, JTS Topology Suite, GeoTools, Hibernate spatial, GWT. It includes various plug-ins so support different data formats, extra widgets, printing using (IText), Geocoding etc. 2 PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. It can handle workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users. 3 PostGIS is an open source software programme that adds support for geographic objects to the PostgreSQL objectrelational database. PostGIS follows the Simple Features for SQL specification from the Open Geospatial Consortium (OGC). 1

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

2.2.2

Design

The design of the web application is bound to some important requirements. Different user profiles should be provided. The hierarchy explained in Figure 2 will be implemented. We foresee 3 profiles. -

Administrator web application Water Administrator (or water manager) Water User (or company)

From these profiles multiple instances can be created. There also needs to be a link set on the profiles. For example: User A and User B are linked to Water Admin A. This defines the relationship of the Water administrator and the water user. Depending on the profile, the features and possibilities in the web application will be limited to the role of the user. Figure 2 – Hierarchy

2.2.3

Implementation

Emails When a user has been created by the Water Administrator, an email will be sent to the user asking him to change the provided password and to update his user information. Every year a new email will be sent to the water users, asking them to provide the necessary data. The mail contains a link to access the platform. Figure 3 – Email

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Logon When the user accesses the provided IP address, he or she will be asked to provide a User name and a Password. The user will also be able to edit personal information. Changing the password would also be recommended on first logon. Figure 4 – Logon screen webtool

Profiles 1. Water User When a user logs on under the profile of a water user, he or she will be redirected to the following webpage (Figure 5). On the left: all the information and the history of submitted data is presented. The user can also see a graph of the historical data. On the map the user can see the location(s) of his/her company which can, if necessary, be edited as well (Figure 7). Figure 5 – Main screen webtool

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model Figure 6 – Profile water user

Figure 7 – Editing information

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

2. Water Administrator When a user logs on under the profile of a water administrator, he or she will be redirected to the following webpage (Figure 8). On the left: all the information and history of submitted data of the water users linked to his/hers account. The water manager can also see a graph of the historical data and also has a geographical view of the locations of the linked water users including a legend depending the sector of the water users. Figure 8 – Profile of Water Administrator

Figure 9 – Profile of water Administrator

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Reporting Depending the role of the application user, one will also be able to report graphs of the provided information. As for the Water Administrator, it will be possible to make graphs of yearly water use, or water use by sector. Figure 10 – Reporting graphs

Validation Some validation rules are provided in the web application. These rules are committed on the uploaded data. The result of this validation rule will become visible in the dashboard of the user (Figure 11). This way the user can upload a different file. Figure 11 – Validation of the data

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

2.3 Installation To run the prototype as a webapplication, a server with specific software is needed. Following is a list of requirements that needs to be fulfilled to be able to deploy the webtool. 2.3.1

Apache Tomcat installation

Apache Tomcat, often referred to as Tomcat Server, is an open-source Java Servlet Container developed by the Apache Software Foundation (ASF). Tomcat implements several Java EE specifications including Java Servlet, JavaServer Pages (JSP), Java EL, and WebSocket, and provides a "pure Java" HTTP web server environment in which Java code can run. 2.3.2

PostGreSQL

PostGreSQL is the database that will be used for this prototype. The system administrator can download the latest version (must be compatible with 9.2). Install the software and also select the postgis 2.0 plugin from the stack. This will enable the geographic component on certain tables which is necessary for the visualization of the different locations on the map. The following steps are needed to make a connection from within the webtool to the PostGreSQL database: 2.3.3

create a new database called “allocatie” set the user rights correct for access to this database Web application (WAR file) Once the server is running you can connect to the server and deploy the war file. Figure 12 – Web interface of tomcat for deploying WAR file

If all went according to plan, the operator should be able to browse to the web application.

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

2.4 Conclusion The prototype was presented during a project meeting (brussels 09/05/2014) with all managers and representatives of different sectors and administrations. The pro’s and cons where discussed during. There was no unanimous decision on implementation, and this was one of the key requirements for going further with the developments. Some positive notes: -

A systematic update of the water consumption required for optimum operation of the update of the water allocation model. Above protocols should be established betweenFHR and data providers. An exchange platform seems to be the ideal instrument. It is easy to manage. It is simple to use. It can be customized (figures, layout, ...) The data are uniformly written to a database, which is coupled to the water allocation model. Model results can be placed on the platform. There is interaction between user and administrator FHR It works through a web server, so only the Internet and browser suffice.

Some negative notes: -

Water Administrators already have their tools for saving their data. So this would imply that they need to submit it twice into two different systems, because they don’t want to abandon their own system. This data is confidential? Who will stand in for the security?

Another solution had to be suggested in order to automate the update of the water allocation model. The solution will comprise a stand-alone desktop application that will interpret each format of data that will be submitted by each water administrator. The data will need to be collected each year.

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

3 Water demand update tool Since not all water managers were keen to introduce a new system to manage their permits, it was decided to build a stand-alone application for FHR in which FHR could introduce the information of all “data providers”. The data can be converted to the input format of the water allocation model and a basic analysis of the data can be performed as well.

3.1 Analysis of the needs The objective is to develop a tool that makes it possible to do an automatic update of the models water users input files. Water managers and other data providers will provide data of their clients water use in their own format. This application should import this data and transform it into the format (dfs0) that is readable by Mike Basin, the water allocation software. It is also important that the operator is able to generate reports with the aid of certain filters. This allows the operator to visualize the distribution of water consumption per company/per sector.

3.2 Technical design 3.2.1

General

An excel file was made to build up a list of users (Figure 13). This file with all the information can be used as the base for our programme. This excel file contains a lot of information we will need to make an update of the water users in the water allocation model: • • • • • • • •

Name Adress ZIP code Municipality Coördinates Sector (NACEBEL code) River/canal reach Other descriptions Figure 13 – Excell file with information

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Every data provider has a different method of storing and updating their data. This makes it complex since the tool must be able to parse all different kinds of data and their format. Therefore it was necessary to examine all the different kinds of formats. The different types of data we get are explained below: Table 1 – File formats of the different data providers

File type

Extensions

Excell 2003

*.xls

Excell 2010

*.xls

Excell 2010

*.xlsx

CSV

*.csv

ASCII

*.txt

PDF

*.pdf

Word

*.doc

Word

*.docx

As the first 5 types of data formats are the most common, the focus will be on these formats. Pdf and Word documents will need a manual conversion to a standard format. The more common formats will be converted to a standardized format. For each data provider a configuration file is created containing the way their format must be interpreted. First it is necessary to develop a standardized uniform file format in which the different data can be converted to. Figure 14 – Different file formats

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

3.2.2 • • • • • •

3.2.3

Important considerations Not all the data received can be linked to the base file automatically (need of an algorithm) The algorithm can also help the operator of the software to manually link the received data to the base file data. Assistance of a GIS component that locates the water users can help the operator to manually make the links. All companies in a certain river or canal reach are grouped per sector. Each sector is represented by the “water user” building block of Mike Basin. If the new user belongs to a sector that’s not yet operating along that reach, a new water user and corresponding dfs0 input file should be created. As a result of restrictions in Mike Basin it is impossible for a water user to withdraw and discharge water to the same reach. Therefore two separate water users should be introduced. Graphs: After the update it would be handy to report the generated results. These results will be reported by the use of graphs: o Per sector o Per water user o Per reach Framework

The application is written in VB.NET on framework 4.0 and is executable on any CPU architecture (32 or 64 bit). It is also clear, that taking the considerations into account, it incorporate some third party components: This is the list of 3rd party components that will be used in this software package: Table 2 – 3rd Party components

Company DHI Telerik Mapwing Zedgraph Antea Group 3.2.4

Component To address the dfs0 format: create and edit dfs0 files Objects that can be used in the Windows forms GIS component Graphical component Outlook Reader

Object Oriented

These classes should be created to hold all the information we will need to process the information: Figure 15 – New created classes

The conceptual interpretation would look like:

WL2021R00_162_2

Final version

3.2.5

Comparing algorithm

The first step is to automatically link the name of the water user (situated in the base file) with the exact same name of water user which was provided (in the data files) by the water managers and other data providers. This automatic coupling will cover almost 50 percent of the water users. The remaining percentage will have to be linked manually. For this, a 1 on 1 comparing algorithm is developed. This algorithm will compare the two strings and return a percentage of comparison (Based on the Levenshtein distance 4). This percentage can be changed in the settings. For instance, the operator can choose that the programme shows all the comparisons that are above 60 percent. These results will return a Boolean for that record, that will be set as a possible match. This module, combined with the GIS enabled feature that displays the geographical location of the water user will make it feasible to make the right decisions. The manually linked records are stored in a file and will always stay available. It is important to take into account that there will be a possibility of spelling errors, but also the way of interpreting and documenting the name of the water user can be different. This is why the automatization of the coupling will never be 100 percent. 3.2.6

Graphs

There is a need for the operator to visualize graphs after importing the new data. Every graph needs to be generated on the fly. It would also be useful to export the graphs. The component which will be used is the zedgraph component. This library makes it easy to produce graphs.

https://en.wikipedia.org/wiki/Levenshtein_distance Final version

WL2021R00_162_2

3.2.7

Export module

There should be an export module available to export information outside the application. This data can then be used for reporting or other post processing jobs. 3.2.8

Configuration file

For every different format of data it will be nessecary to provide a configuration file. This file will contain the information how the software application needs to interpret the data format which has been converted into the standard format. Some key components in this configuration: - naamfile => the name of the csv file - jaar => In which year this data was measured. - rijbedrijf => how many rows for one company is used. - offsetRow: if an offset is used, the operator can enter the amount of rows to be skipped. - offsetColumn: If an offset is used, the operator can enter the amount of rows to be skipped. - Bedrijf: The name of the company - captatie: The column where the value resides for the withdraw - terugstorting: The column where the value resides for the dischargee - functieCaptatie: If there is a function needed to calculate the withdraw, this field can be used. - functieTerugstorting: If there is a function needed to calculate the discharge, this field can be used. - handmatigbedrijf: When the company is not mentioned in the file, the operator can provide this manually.

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

3.2.9

Output

The output files are dfs0 files. These can be used directly as input for MikeHydro 2016 to rerun the model. In the output directory there will be three sub directories available. Important to know is that we mostly start from the historical data which was we have when the current model was created: Figure 18 – output directory structure

Append: The dfs0 files that are created in the append directory are the existing dfs0 files (which contain historic data) appended with all the data that was covered in the tool. Blank: The dfs0 files that are created in the Blank directory are dfs0 files created by the data delivered in a year. Thus the blank directory will contain subdirectories for every year the tool has data. The dfs0 file contains only data for this year and historic data is not applicable in this type of output. New: The dfs0 files that are created in the New directory are dfs0 files created by the data delivered in a year. Thus the New directory will contain subdirectories for every year the tool contains data. The dfs0 file encapsulates only data for this year. This dfs0 will only use the existing dfs0 and will only append the data of the year.

The name of the dfs0 file is also according the standards of the Water allocation model.

3.3 Implementation The implementation of the coding in VB.NET is completely object oriented. Using this method brings along some advantages: • • •

It provides a clear modular structure for programms which makes it good for defining abstract datatypes where implementation details are hidden and the unit has a clearly defined interface. It makes it easy to maintain and modify existing code as new objects can be created with small differences to existing ones. It provides a good framework for code libraries where supplied software components can be easily adapted and modified by the programmer. This is particularly useful for developing graphical user interfaces.

Final version

WL2021R00_162_2

3.4 Testing The software has been installed and tested on a testing machine. Performance tests, user tests and unit tests were conducted. At the time of writing the version of the tool is 2.13.

3.5 Installation of the water demand update tool For the installation of the application a setup has been created. This setup contains all the necessary libraries to start up the programme with all the used components. The list of requirements you can find below: • • • • • •

64 bit machine Installation of client profile .Net framework 4.0 Windows 7 or above CPU Speed: 2.2 GHz minimum or higher; Screen Resolution: 1024 x 768 recommended or higher at Normal size (96dpi) Disk Space: 500 MB of free space to accommodate a full setup installation and additional disk space.

A prerequisite is the installation of the Mike SDK. This is freely downloadable from the DHI website. The installer of the Mike SDK is also provided in the installation package. If Mike Hydro is already installed on the machine where the installation of the water demand would take place, this first step does not need to be carried out. Another prerequisite is the installation of the mapWinGIS component.The tool has been tested with version 4.9.2. To start the installation of the water demand tool, open the setup file and proceed by following the next steps:

WL2021R00_162_2

Final version

Figure 21 – Installation (screen 2)

Final version

WL2021R00_162_2

Figure 23 – Installation (screen 4)

Figure 24 – Installation (screen 5)

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

3.6 Use of water demand update tool 3.6.1

Prerequisites

Send E-mail to request data For the update of the model it is necessary to be in possession of the latest data. Each year an e-mail must be sent to the water administrators to collect the most recent data. These e-mails are stored (or can be stored) in the software application. This e-mail must be sent every year to all water administrators. The operator can recycle the e-mail from last year to send the new one. The directory location of these files is the following : -

Application directory => Mails => Inbox Application directory => Mails => Sent

The format in which these email are stored are *.msg. New messages can be copied to the correct directory. The software application has a message reader to interpret and visualize these files. Figure 25 – Collected e-mail messages (screen 4)

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Received data The received data can be added according to the respective water administrator. These folders already contain data that has been collected throughout the years. If the administrator has diverse locations, these can be placed in subdirectories. Figure 26 – Received data by administrators

For the administrator AMT we have two different locations: Figure 27 – different locations for same administrator

3.6.2

Start up

Double-click the icon actualisatietool.exe. This action will start the programme. First, the user will see a splash screen. This screen contains information about the tool. Figure 28 – Splash screen

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

This is the basic screen the user will see is: Figure 29 – Main screen

The composition of the screen consists of two components. On the left: the navigation. By clicking one of these buttons, the main screen on the right will enable the information chosen by the operator. The operator will always start at the default screen containing the data. One will then need to select two files and two directories. These configurations can be saved in Extra => Settings form. This way, the operator does not need to select these files/directories every time he (re)starts the programme. Once the directories are set, the operator can minimize these settings by pressing the little arrow on the screen pointing upwards. This will give the operator more space to examine the data (Figure 30). The map can be minimized by clicking on the small arrow above the map. The data grid on the left presents all the linked data. The blue records are the records that have been linked. The data grid on the right are the data that were received by the data poviders. The button export makes it possible to export all this data to a *.csv file.

Final version

WL2021R00_162_2

3.6.3

Data information

The software application requires some input files. The basic files will be provided with the initial installation. Aside from the files, there is also a need to define some directories for the software application. These directories will also be explained. The path to these files/directories must be selected in the user interface as seen below: Figure 31 – Data screen

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

The link file The most important file we need is the link file. This file was created manually for creating the initial model. This file contains all the information regarding the water users and it’s coordinates (address, related basin, related dfs0 file,…). On the other hand it is essential to connect the data from the link file to the data which the operator has received by e-mail from the water administrators. Figure 32 – Link file

Basin file The basin file links the available basins with the pands. The name of the dfs0 file has a specific structure: BOS-p13_U-Erwziin.dfs0 P13_U is the pand and this file links this dfs0 to a specific basin. The basin file must be selected. Below you can find the structure of this file: Figure 33 – Basin file

Afterwards, the operator can read these two files. It will import the existing information. This will also link the water user with each location, also linked with the river/canal reach.

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Manual linkage The operator can press the linkage button on the main screen on the left hand side to start the manual linkage. Figure 34 – Linkage screen

5 3

8 The screen above will become visible. More information about the different modules in the screen you can find below: 1: This datagrid contains all the user data provided by the link file. -

Green = Linked Red = Not linked

This also contains information if there is a match with the received data (according to the algorithm) and if this record is not applicable (NVT). 2: This datagrid contains all the received data. 3: This datagrid contains the selected row from datagrid 1. 4: If there is a match, or suggestion for this user, it will be displayed in this datagrid 5: The operator can press this button if a record from datagrid 3 must be coupled with datagrid 7 6: If the user is not applicable for the program, than this can be set to NVT (not applicable) by pressing this button. 7: This datagrid contains the selectd row for the received registrations. 8: This datagrid contains all the linked information.

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Data directory When the latest data has been collected, the user can set the location of this directory. Figure 35 – Water Administrators

Dfs0 Directory This directory will be the output directory were the dfs0 files will be created/appended for the actualization of the model Conversion When all data is complete, the operator can press the conversion button. This will convert all the possible files to the standard format that will be used throughout the programme. All tabpages in the excel files will be converted to different *.csv files. The original files will not be manipulated. This gives the possibility to return to the original data, always. After finalizing the conversion, a lot of *.csv files are created from different water users with the data all in different layouts. With the help of the configuration files it is possible to tell the programme which data it has to use from each *.csv file, and more important, how the programme should interpret this data. These configuration files make the programme generic. If a water manager would change the layout of its data while the application was build with hardcoded functions to read the data, the whole code should be rewritten. The use of the configuration file solves this problem by letting the operator decide how to read the different layouts. The configuration files format is presented below. The first line of the file will always be the header. In the update tool it is possible to manage these files on a user friendly way so the operator does not have to edit these files in notepad (Figure 36). For example:

D:\225523\Data\AMT\Gent\watercaptatie verbruik 2013.xlsx0.csv;2013;1;0;0;0;0;4;m³;5;m³;0;-9999;-9999;-9999 Naamfile Jaar Rijperbedrijf Tabblad 0 offsetRow offsetColom Bedrijf Captatie 4 Eenheidcaptatie m³ Terugstorting Eenheidterugstorting Tijdstip functieCaptatie functieTerugstorting HandmatigBedrijf

Final version

D:\225523\Data\AMT\Gent\watercaptatie verbruik 2013.xlsx0.csv 2013 1 0 0 0 5 m³ 0 -9999 -9999 -9999 WL2021R00_162_2

The configuration will also be equipped with a priority. This will be a level that will be indicated with an integer from 0 to 9. This integer will be placed as first character in the configuration filename. This priority level will be used in the case that the same companies are mentioned in different files (from different managers). This priority will only comprise the most important data. It will also avoid that the existing information being overwritten. For example: There are 2 providers of data whom provide data for the same company (Electrabel). The first provider gives information about 2011-2012-2013. The other provider has information about 2011-2012-2013-2014-2015. The first provider has Priority 0, the other provider has priority 7. The company will use the 2011, 2012 and 2013 from the first provider as he has the highest priority. However the company will also use the data from 2014 and 2015 as the first provider did not release this information. Figure 37 – Priorities

New data If data was delivered for a user that does not have a corresponding dfs0 file, this dfs0 file should be created. Preparing .dfs0 files for MIKE basin In order to add a Water User to the MIKE basin model a water demand time series must be read by the model in the form of a .dfs0 file. This file Is usually made out of five columns, including the Time step: Figure 38 – dfs0 file

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Water Demand is enough data if it is assumed that no groundwater is used. The time series may be as long as there is available data for or longer. This is defined by accessing the Edit menu and then Properties. A File Properties pop up menu appears where the number and type of time steps can be defined, as well as the amount of columns and units used. Figure 39 – dfs0 file creation

Output map When all the provided data is available in the configuration files, the processing of this information can begin. The following steps will be performed: 1. 2. 3. 4.

Reading configuration files with listed priorities Loading all files in configuration files Loop over the water users and group them together according the input file. Create the dfs0 files by calculating the new values Figure 40 – Processing

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Companies will be linked to the delivered data. As the automatic linkage will not cover 100 percent of the records, it will be necessary to do some manual coupling. 3.6.4

Helpful tools

Some tools which can be helpful to the operator are listed below: •

Searching the data grid Figure 41 – Searching the data grid in columns

•

Grouping by in the data grid Figure 42 – Group by column to select specific data.

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

3.6.5

Linkage

When the operator wants to use the manual linkage, he/she should press the linkage button. Figure 43 – Linkage screen

3.6.6

Graphs

Plots can be created on different levels: individual company, aggregated per basin or per sector. Users Figure 44 – Users screen

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

Basins Figure 45 – All basins (screen 1)

Figure 46 – All basins (screen 2)

WL2021R00_162_2

Final version

Sectors Figure 48 – All sectors (screen 1)

Final version

WL2021R00_162_2

Figure 50 – Per sector screen

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

3.6.7

Export

The operator can export the generated graphs. The year of export can be chosen. There is also an export tool which can convert *.dfs0 files to text files. Figure 51 – Export screen

3.6.8

Configuration

When the new data is collected, the operator must place the data in the directory of the provider. In most cases the data format will be the same as the previous received data. The operator should add the new addition in the configuration file of the provider. As you can see below, the latest entry is from 2015. This configuration file will give information to the program how to interpret the received data. Figure 52– Configuration screen for new data

Final version

WL2021R00_162_2

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

3.6.9

Mails

To keep the model up to date, the operator needs to send an email to the water administrators yearly. This part of the programme lets the operator see which mails were sent in the past, and which emails were received. It’s also a backup of the correspondence during the project. These mails were sent with outlook, but in the future this won’t be obligatory. The messages can be pasted in another email programme of choice. Figure 53 – Mails (Sent and Inbox)

WL2021R00_162_2

Final version

Modelling water availability and water allocation strategies in the Scheldt basin Sub report 2 – Development of a methodology for updating the water demand of the water allocation model

4 Conclusion The idea of a fully equipped web based platform where companies and other water users could enter their yearly water consumption, where water managers could fully manage their permitting administration and where the data is automatically converted into useful input files for the water allocation model, was abandoned by the water managers themselves. Despite the advantages, also acknowledged by the water managers, they didn’t want to abandon their own systems. Operating two parallel systems is of course useless. The prototype of this platform remains available and can be activated and further developed if necessary. Since the need to update the water demand of the water users in the water allocation model remains, it was decided to build a stand-alone desktop application. After receiving the data provided by the different water managers or individual companies each in its own format, the data needs to be coupled with the existing data that’s already in the system. To facilitate this an algorithm is used to do the coupling. However this algorithm won’t be able to couple al data. It will be necessary to do a manual coupling. To assist with this procedure some suggestions are proposed based on the recognized characteristics in for example the company’s name. Also a map is added which can help when the location of the company is known. When all the data is coupled to the right company/user in the application the data is converted into a standardized format. From these standardized files the data is converted to dfs0 files that can be used for simulations with the water allocation model. There are dfs0 files with only data of that specific year or files where the most recent data is attached to historical data. So it is possible to run simulations with the water demand of a specific year or try to reproduce the historical water allocation. The water demand update tool also makes it possible to plot all the gathered information per individual company, per sector or per river basin. It will give a quick overview of how the water demand is evolving in time on different scales. The application is developed in such a way that it is generic and can easily be adapted if future evolutions make it necessary to do so.

Final version

WL2021R00_162_2

DEPARTMENT MOBILITY & PUBLIC WORKS Flanders hydraulics Research Berchemlei 115, 2140 Antwerp T +32 (0)3 224 60 35 F +32 (0)3 224 60 36 waterbouwkundiglabo@vlaanderen.be www.flandershydraulicsresearch.be