«NILE BASIN INITIATIVE Initiative du Bassin du Nil Information Products for Nile Basin Water Resources Management The ...»
Data Retrieval, Processing and
Final Storage into the Nile Basin Database
Entebbe October 2001
NILE BASIN INITIATIVE
Initiative du Bassin du Nil
Information Products for Nile Basin Water Resources Management
The designations employed and the presentation of material
throughout this book do not imply the expression of any opinion
whatsoever on the part of the Food and Agriculture Organization (FAO) concerning the legal or development status of any country, territory, city, or area or of its authorities, or concerning the delimitations of its frontiers or boundaries.
The authors are responsible for the choice and the presentation of the facts contained in this book and for the opinions expressed therein, which are not necessarily those of FAO and do not commit the Organization.
© FAO 2011 Table of Contents Table of Contents 1 Introduction 9
1.1 General 9
1.2 Overview of the Contents of the Manual 9 2 Detailed Outline of the Data Trajectory 11
2.1 General 11
2.2 Hardware 11
2.3 Software 12
2.4 Comprehensive Data Trajectory 13 3 Installation of the Remaining Hardware and Software 15
3.1 Installation of SC532-A Interface 15
3.2 Installation of PC208W Datalogger Support Software 15 4 Recommended Folder (Directory) Structure 16
4.1 General 16
4.2 PC208W Files 16
4.3 MS Access Database Files 17 5 PC208W Datalogger Support Software 19
5.1 Introduction 19
5.2 StgModule Component 19 5.2.1 Introduction 5.2.2 Connecting Storage Module to PC 5.2.3 Retrieving Raw Data Files from Storage Module 5.2.4 Pointers 5.2.5 Erasing Data on the Storage Module 5.2.6 Trouble Shooting
5.3 Report Utility of the PC208W 3.3 Software 26 5.3.1 Introduction Manual 3 Table of Contents
6.3 Importing Output ASCII Text Files Generated by Report into MS Access 41
6.4 Processing of Imported Data in MS Access and Final Transfer into the NBD 47
Annexes Annex 1: Consecutive Steps in Connecting Storage Module SM4M/SM192 to PC.
Annex 2: Consecutive Steps in Retrieving Raw Data Files form SM4M/SM192 Storage Module to PC.
Annex 3: Consecutive Steps in Processing Raw Data Files in Report.
Annex 4: Consecutive Steps in Pre-Processing of Report Output Files in MS Access and Appending Processed New Data Sets into NBD.
Annex 5: General Design of the Append Queries for Pre-Processing of the Report Output Files in MS Access.
Annex 6: Design of the ID Converter Table.
FAO regional project GCP/INT/752/ITA – Nile Basin Water Resources is committed in establishing a limited water resources monitoring network in the Nile Basin region. The adopted monitoring network consists of 21 meteorological and 14 hydrological stations to be either rehabilitated or newly implemented at key points in the Nile Basin. These stations will be supplied with conventional and/or fully automatic equipment depending upon the priorities of the hydrometeorological departments in the respective beneficiary countries. This network is a continuation of the efforts made by the previously concluded FAO Lake Victoria Water Resources Project, which has already established, fully automatic 14 hydrological and 4 meteorological stations in the Lake Victoria region of the Nile Basin. The network is no doubt inadequate as compared to the identified needs and water resources development priorities in the region.
The beneficiary countries have placed, with slight variation an emphasis and scope, a high priority on sustainable and environmentally sound water resources development in the Nile Basin region to satisfy rapidly increasing water demand for irrigation to increase food production, hydropower production and domestic and industrial water supply.
For this reason, substantial additional efforts and resources will be necessary to address the remaining gaps in the environmental and water resources data and information by expanding further the monitoring network and georeferenced database system established by the FAO Projects in the region.
Recognizing the importance of the established nucleus monitoring network in this context, the project is making every effort to secure its sustainable and technically sound operation and maintenance, as well as collection of data from the network, data quality control, processing and final storage into the Nile Basin database. To this effect most of the stations in the adopted network are being furnished with state of the art data loggers and hydrometeorological measuring sensors which require minimum efforts and material for operation and maintenance. It is important to mention that the experience gained from the automatic equipment installed by the concluded Project have been proved, now for years, to be successful. Furthermore the project is developing a series of technical manuals and conducting comprehensive on-the-job training seminars and workshops in the region on all aspects of equipment installation, station operation and maintenance and data processing.
The Manual on Data Retrieval, Processing and Final Storage into the Nile Basin Databases is the latest in the series. Its first version was written in January 99 during the previously concluded FAO GCP/RAF/304/JPN and has now been revised due to the rapidly changing technology in its hardware and software installed in the Nile Basin. The manual, at present, has also been subsequently used for training of national professionals in Rwanda, Kenya and Uganda and is expected to be used for training of national professionals in other countries. The experience gained during these practical training sessions was used to substantially simplify and improve many of the procedures in this revised version of the manual. The project is thankful to all the trainees in the region for their enthusiastic participation in the training sessions conducted so far and the extraordinary efforts in mastering the technology.
Based on evaluation of skills and know-how already in place in 4 countries of the region in this field we are confident that the revised Manual presented hereby will further contribute towards the achievement of the major goal: the sustainable and technically sound operation and maintenance of the monitoring network established by the Project in the region, including the collection, quality control and processing of data generated in the network.
Dr. Mohammad Chebane Chief Technical Advisor October 2001, Entebbe, Uganda
1.1 General The adopted Monitoring Network in the Nile Basin comprises of Automatic Hydrological and Meteorological Stations equipped with electronic instruments for data acquisition and storage. Measurements are initially pre-processed and stored on site in a digital storage and control module, commonly referred to as datalogger.
Periodically, the accumulated raw data are transferred from datalogger to a computer at the office of the hydrometeorological service. This is accomplished with the help of a portable data retrieval unit, which can serve several stations during a single inspection tour. In order to enhance sustainability of the network, a visiting frequency of 1 month is proposed. Consequently, data should arrive at the office of the hydro-meteorological service with a delay of maximum one month.
After transfer of the collected hydro-meteorological information from retrieval unit to PC, the newly imported data should be processed, checked on consistency and possible measurement errors, and stored in a dedicated table in the Nile Basin Database (NBD), regarded as their final destination.
The first segment of the data trajectory, i.e. transfer of measurements form logger to retrieval unit, is covered in the manual “Manual for Installation, Operation and Maintenance of Automatic Meteorological Stations”.
The second part of the data trajectory is dealt with in this manual. It describes in detail all steps involved in transferring the accumulated climatological measurements from the storage module to PC, processing of data downloaded onto PC and final storage of the processed time series to the Nile Basin Database [NBD] in MS Access.
More specifically, this manual deals with the procedures for:
• installation and setup of necessary hardware components;
• transfer of raw data from storage module to PC in the initial data and file formats;
• separation of individual measured variables from initial data file to comma separated ASCII file, and first visual data quality check;
• importing the pre-processed ASCII data file into MS Access;rearrangement of newly imported data to final table structure using pre-defined database queries and second computer operated quality check;
• appending and final storage of new data into the Nile Basin Database.
This manual deals with data originating from Campbell type Automatic Weather Stations (AWS). A substantial number of steps involved in the data trajectory are included in the manual for processing actions.
1.2 Overview of the Contents of the Manual This manual describes in detail all steps and operations involved in transferring the accumulated raw data from retrieval unit to PC and primary processing – by using a Campbell Scientific software package, called PC208W, which has been specifically tailored for the purpose; and secondary processing of pre-processed data and their subsequent final storage into the NBD – by using MS Access database package.
In order to provide the user with the complete picture of the information flow from sensor to database, chapter 2 presents a detailed outline of the comprehensive data trajectory, as well as all software and hardware components involved, and their role.
Chapter 3 concentrates on the instructions for installing the necessary hardware and software components that are to be set up in appropriate manner.
Chapter 4 is discussing a recommended or required directory structure aimed at achieving uniformity in the
location of the concerned files on the PC’s hard disk drive.
Chapter 5 deals in detail with retrieving the raw data from the storage module to PC, and the initial processing and storing this new information into a file in a comma separated ASCII format for each strictly defined group of data.
Chapter 6 presents the instructions for importing the pre-processed new data set into MS Access, performing a final processing round and storing the new data records into their final destination in the NBD.
Finally, chapter 7 describes the procedures for creating an MS Access “transfer” database file, extraction of needed data records from any main NBD file/table and their storage into the ‘transfer’ file; this will enable the users to disseminate the selected data records from NBD to other users, within the country of in the Nile Basin region, in an easy manner and without violating the integrity of either new or historical data stored in the main NBD.
Detailed Outline of Data Trajectory
2.1 General This chapter gives an overview of the complete data trajectory, starting from values measured by the station’s sensor all the way till the final storage of data into the Nile Basin Database (NBD) on PC at the FPI. It presents the various hardware and software components involved in this process, together with their interconnection. Furthermore, it lists sequentially all user actions that play a role in the data trajectory.
2.2 Hardware Figure 1 presents all hardware components involved in the route from sensor to PC in the hydro-meteorological data acquisition process
Figure 1: Hardware components involved in data acquisition process.
The various climatological parameters are measured using electronic sensors. Depending on the nature of a parameter, its value is represented by either a voltage or a sum of pulses.
The datalogger measures the voltages and/or counts the pulses, and transfers them to intelligible figures in standard units, like for example degrees Celsius for temperature or mm of rainfall for precipitation. Other tasks of the datalogger are to activate the various sensors at pre-defined intervals, and store the measurements in its memory.
The CR10KD keyboard display is used to communicate with the datalogger on site. For instance, in case when a user wants to invoke manual data transfer or check on operation of individual sensors, this is accomplished by connecting the CR10KD keyboard to storage module and logger, and by typing in on the keyboard the proper commands.
Also, when the user wants to check the performance of sensors and datalogger while onsite, this is achieved by connecting the keyboard only to the datalogger and keying in the respective commands.
The SM4M/SM192 storage module is used to download the accumulated raw data from datalogger on site and
thereafter to transfer/upload the raw data to the Personal Computer at the office. Information is stored in the module in a specific format, which cannot be read directly by IBM compatible PCs.
The role of the SC532A or SC932 Interface is, therefore, to convert this specific storage module format into a RS232 compatible information communication standard, which is ‘understood’ by IBM compatible PCs.
Data can also be downloaded directly from the meteorological station to the PC using short haul modems without using keyboard and storage module.
Data pre-processing, processing final check and storage in the NBD is accomplished on PC.
2.3 Software Figure 2 presents the various software packages and programs, as well as the concerned file formats, which are used in the process of data acquisition, pre-processing, processing and final storage.
Data processing, rearrangement and final storage in the LVBD is accomplished on PC.