Manual 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 www.fao.
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.
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.
Table of Contents 5.3.2 4 Processing Data from Met Station 5.3.2.1 General 5.3.2.2 Five-Minute Rainfall Data 5.3.2.3 Hourly Meteorological Time Series 5.3.2.4 Daily Meteorological Time Series 5.3.2.5 Meteorological Station Performance Information 5.3.2.6 Renaming Meteorological Output Files 6 Data Processing in MS Access and Final Storage into the NBD 39 6.1 General 39 6.2 MS Access Database “NBD NEW DATA” 39 6.
List of Figures List of Figures Figure 1: Figure 2: Figure 3: Figure 4: Figure 5: Hardware components involved in data acquisition process Software components and file formats involved in data acquisition and storage process Recommended directory structure for PC208W and related “work” files Required Folder (Directory) structure for all MS Access database files Final data processing steps in MS Access and subsequent removal and storage of raw files as backups 11 12 16 17 39 M
List of Tables List of Tables Table 1: Table 2: Table 3: Table 4: 6 Comprehensive Data Trajectory Various Files and their Function Used by Report in the Processing of Meteo Station Measurements Database Objects and their Function in the MS Access Database file ”NBD_Pre Processing.
Annexes Annexes Annex 1: Annex 2: Annex 3: Annex 4: Annex 5: Annex 6: Consecutive Steps in Connecting Storage Module SM4M/SM192 to PC. Consecutive Steps in Retrieving Raw Data Files form SM4M/SM192 Storage Module to PC. Consecutive Steps in Processing Raw Data Files in Report. Consecutive Steps in Pre-Processing of Report Output Files in MS Access and Appending Processed New Data Sets into NBD. General Design of the Append Queries for Pre-Processing of the Report Output Files in MS Access.
Foreword Foreword 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.
Introduction Introduction 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.
Introduction 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.
Detailed Outline of Data Trajectory Detailed Outline of Data Trajectory 2.1 General 2.2 Hardware 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.
Detailed Outline of Data Trajectory 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.
Detailed Outline of Data Trajectory The datalogger works under a specific Campbell Scientific operating system and is controlled by the METSTAT datalogger program for meteorological stations. These programs are specifically created by the project GCP/INT/752/ ITA – Nile Basin Water Resources for the sensor configuration and data requirements in the network established by the project.
Detailed Outline of Data Trajectory Table 1: Comprehensive Data Trajectory Step 1 Operation ~ Periodic automatic measurement of meteorological parameters ~ pre-processing and storage in the datalogger’s RAM ~ Transfer of accumulated measurements from logger’s RAM to portable SM4M/SM192 Storage Module; each single transfer exercise results in a separate data file Hardware Involved ~ Sensors ~ Datalogger Software Involved ~ Campbell Scientific Datalogger Operating System (OS) ~ METSTAT datalogger program
Hardware and Software Installation Installation of the remaining Hardware and Software 3.1 Installation of SC532A Interface Provided a PC has already been set up successfully, only one hardware item remains to be installed: the SC532A Interface.
Recommended Directory Structure Recommended Directory Structure 4.1 General 4.2 PC208W Files For standardization and instruction purposes, this chapter proposes a Recommended Folder (Directory) Structure. The user is free to modify this if another system would better suits his or her particular computer organization. However, the instructions presented in this manual are based on the proposed structure. Modification of it will require corresponding adjustment of commands concerning file locations.
Recommended Directory Structure *For the creation of the various folders, use the appropriate Windows Explorer commands. 4.3 MS Access Database Files Figure 4 illustrates the required Folder (Directory) Structure for further processing of data in MS Access and final storage of processed data files into the NBD in database standard format. This format can, later on, be changed once a final structure for the Nile Basin Database has been developed.
Recommended Directory Structure 18 Data Retrieval, Processing and Final Storage in the Nile Basin Database
PC208W Datalogger Support Software PC208W Datalogger Support Software 5.1 Introduction PC208W Datalogger Support Software is a Windows based software package specifically developed by Campbell Scientific for communication between datalogger and PC. It consists of a help module and 7 independent datalogger support components. Among them are also the utilities for setting up a network of PC connected stations and on-line datalogger communication.
PC208W Datalogger Support Software The following toolbar appears: Step A4: Click the StgModule tab. A window alike the one presented below shows up. The menu-bar contains five different items: File, Options, Data, Tools and Help. Just beneath this line 4 different tabs are found, named CSM1/MCR1, SM192/SM716, PCCard and SM4M/SM16M, each dealing with a different storage medium.
PC208W Datalogger Support Software Step A7: Select the COM Port settings corresponding to the serial port used by the “9 to 9 pin” computer cable connecting the SC532A Interface (see paragraph 3.1). In most cases this will be either COM1 or COM2. Step A8: Set Baud Rate at 19200. This is a measure of the communication speed between PC and Storage Module. In case frequent communication problems are experienced, lower the Baud Rate and see if this solves the problem.
PC208W Datalogger Support Software PC and Storage Module are now connected. The user can proceed with retrieving data files or other SM4M related operations. If the connection is not established successfully, a message will pop up as “Standard Prompt Not Detected”. Recheck the wiring connection and make sure that the Interface and the Storage Module are connected properly to the power source. Also recheck the COM PORT and then try to connect.
PC208W Datalogger Support Software Step B4: Mark the “Auto Increment Name” option in the Auto Name Control select box. Each retrieved data file will get a name according to the format “DataXXX.dat” in which XXX is a number which automatically increments when a new file is down loaded. Step B5: Click the file name in the File Naming Options location (in the current example this is Data031.dat).
PC208W Datalogger Support Software If the user has opted for the Recommended Directory Structure (see paragraph 4.2), data files captured from the SM4M should be stored in the following folder: C:\CAMPBELL\FILES\INPUT Step B6: Click the “Get New” button. This option collects all “new” data files from the SM192, i.e. all files not yet retrieved from the storage module in a previous down load operation. Each single SM4M data file is stored in an individual DAT file on the hard drive.
PC208W Datalogger Support Software Dump Pointer: The Dump Pointer is an internal pointer used for keeping track of the current ‘start-of-dump’ for module-to-module data dumps. It indicates the first location from where new data should be collected. It is a function of the software to move this pointer after each successful data collection.
PC208W Datalogger Support Software 5.2.6 Trouble Shooting Problem 1: SM4M does not respond by clicking the “Connect” button and is giving the message “Standard Prompt Not Detected”. Cause: 1: Cables are not properly connected 2. Power supply 3. SC532A interface is not properly connected 4. Problem with the COM Port or wrong SMS “Setup” Remedy 1.1: Check if the cables are connected firmly and if they are according to the following diagram Remedy 1.
PC208W Datalogger Support Software Due to the logger’s memory configuration, all four groups of data time series are written to the same storage area in the system’s RAM in the order of their occurrence (time of recording), and consequently downloaded from the datalogger directly via short haul modems or storage module to a PC as one single, rather unorganized raw data file.
PC208W Datalogger Support Software Step C2: Choose Open from the File menu to activate a pre-defined parameter file, as indicated in the following window.
PC208W Datalogger Support Software Step C3: Navigate to the location on hard drive, which contains the parameter files. This is “C:\CAMPBELL\FILES\ PARAMETR” if the Recommended Directory Structure is used. Highlight the file “metrain.par”, as shown on the window below. Click OK. The title bar on the Split window now indicates the name of the open parameter file. Step C4: Activate the Input File(s) sub-window and select the Input Data File.
PC208W Datalogger Support Software Step C5: Switch to the Output File sub-window by clicking its tab at the top-mid of the current Split screen. The below window pops up.
PC208W Datalogger Support Software Step C6: In the File Name text box of the Selected Output File sub-screen, type in the name of the appropriate output file. Apply the following naming format: “metrnXX.txt” Make sure that the XX - serial number of the output file – is the same as of the respective raw data file. For instance, give the name “metrn31.txt” to the output file if it is being extracted from “data031.dat” raw data file. Click OK.
PC208W Datalogger Support Software For each five-minute interval in which rainfall has been registered at the station, the following information is extracted from raw input data file: station datalogger-ID, year, date and time and cumulative five-minute rainfall values. The first four values (i.e. ID, year, date and time) guarantee that each array is uniquely identified. The associated output file is in comma separated ASCII format. This is shown below for the output file “metrn31.
PC208W Datalogger Support Software This concludes the user activities in Report with respect to the extraction of 5-minute rainfall time series data from the respective raw data input file downloaded from storage module. For ease of reference, the above-presented steps are listed in Annex 3. 5.3.2.3 Hourly Meteorological Time Series As in the previous, the procedure for extraction of hourly meteorological time series data is similar to the one described in paragraph 5.3.3.
PC208W Datalogger Support Software Step C7: Complete the routine by selecting Go from the Run menu.
PC208W Datalogger Support Software For each full hour the Met station has been operational, the following hourly meteorological data are extracted from raw input data file: station datalogger-ID, year, date and time, average hourly air temperature, vapor pressure, vapor pressure deficit and wind speed; and total hourly solar radiation. The first four values (i.e. ID, year, date and time) guarantee unique identification of each array. The associated output file is in comma separated ASCII format.
PC208W Datalogger Support Software Step C7: Complete the routine by selecting Go from the Run menu.
PC208W Datalogger Support Software For each day the Met station has been operational, the following daily meteorological data are extracted from the respective raw input data file: station datalogger-ID, year and date, average, maximum and minimum daily air temperature, average vapor pressure and vapor pressure deficit; total daily rainfall and solar radiation; average daily wind speed and direction and standard deviation of the wind direction. The first three indices (i.e.
PC208W Datalogger Support Software There is no need to archive the output file containing the station performance information. After assessment of the station performance indicators (which will lead to a decision to either take or not take an action) this information can be printed out for records and the respective file deleted thereafter. 5.3.2.
Data Processing in MS Access and Final Storage into the NBD Final Processing in MS Access and Final Storage into the NBD 6.1 General Final destination of the meteorological data obtained from the Automatic Weather Stations in The Nile Basin Monitoring Network is the NBD: The Nile Basin Database developed in MS Access.
Data Processing in MS Access and Final Storage into the NBD This database file (NBD_Pre Processing.mdb) contains several pre-defined objects including 4 tables and 3 process-append queries. The database tables and queries names are listed in table 4 together with a brief description of their function. As indicated by their numbering, the various database objects listed in table 4 are divided into three different groups.
Data Processing in MS Access and Final Storage into the NBD the actions for importing ASCII text file “metdayXX.txt” into MS Access are equivalent to those for “methrXX.txt” and “metrnXX.txt”, only table and file names differ. The same applies for running the various queries and adding the fully processed data to the NBD. It is because of this fact, and the wish to avoid redundancy, that this manual presents the detailed user instructions for only one member of each data type.
Data Processing in MS Access and Final Storage into the NBD In the above screen, the Meteo_Hourly table still contains data from a previous transfer exercise. Although primary key setting in the final NBD tables do not allow for data duplication, it is good policy to delete all records from the auxiliary tables, in this case Meteo_Hourly, prior to a processing new data set. To this end, continue with step 3.
Data Processing in MS Access and Final Storage into the NBD Step D5: Close Meteo_Hourly table. The auxiliary Meteo_Hourly table is now empty. No data duplication will occur during data import process unless the user imports a certain data set twice. Although this would eventually be refused by MS Access due to ‘key violation’, it is recommended not to enter this situation. Continue with step D6. Step D6: In the database window, select Get External Data from the File menu.
Data Processing in MS Access and Final Storage into the NBD Step D8: Click Import. The following Text Import Wizard pops up.
Data Processing in MS Access and Final Storage into the NBD The Report output files are comma-separated and thus fall into the category ‘delimited’. This has been done on purpose since importing this format into MS Access has proved to be straight forward and flawless. Continue with step 9. Step D9: Select ‘Delimited” and click ‘Next’. The screen illustrated below appears; it shows how the imported text will be divided into various columns according to the applied delimiter.
Data Processing in MS Access and Final Storage into the NBD Step D11: Check “In an Existing Table:” and navigate in the related list box to the Meteo_Hourly table. Click ‘Next’. This constitutes the final step in importing the ‘methrXX.txt’ ASCII file into the appropriate pre-defined MS Access table. If no importing errors are encountered, the Text Import Wizard finishes the process by giving the following message. Step D12: Click OK.
Data Processing in MS Access and Final Storage into the NBD This step concludes description of procedures for transfer of the contents of the Report Output file “methrXX.txt” into the appropriate location in MS Access in preparation for final processing of data. As already stated at the beginning of this paragraph, the import procedures for “metdayXX.txt” and “metrnXX.txt” are analog to the procedures presented above for the file “methrXX.txt”. To import these ASCII files into “NBD_Pre Processing.
Data Processing in MS Access and Final Storage into the NBD Table 4 presents the input and output tables for each of the above queries. AWS stands for Automatic Weather Station, which have been established in the region within the GCP/RAF/304/JPN and GCP/INT/752/ITA Projects. For reference purposes, the general query design is presented in Annex 5. The function and design of the ID-Converter table, included in the various queries, is discussed in Annex 6.
Data Processing in MS Access and Final Storage into the NBD Step D14: Click Yes. A message box similar to the one below follows. Step D15: Click Yes. This steps finalizes the data processing in MS Access. The destination NBD table is by its primary key settings protected against duplicate records. If a certain record would be added for the second time, the following message box shows up.
Data Processing in MS Access and Final Storage into the NBD The following screen presents the fully processed data set in the NBD format. Note the new Station-ID, combined Dates, Time and Source fields uniquely identifying each record. The above stepD16 concludes description of the procedures for data processing in MS Access and final storage of processed data records into the appropriate NBD destination table. Other meteorological data files can be processed by following the same procedures.
Data Processing in MS Access and Final Storage into the NBD 6.5 Transfer of Raw Input and Report Output Data Files to Backup Folder Once the meteorological data series are successfully processed in MS Access, the raw data files of the type “dataXXX. dat” and the respective extracted output ASCII text files, processed by using the Report utility (see chapter 5.3 of the manual), have for all purposes become obsolete.
Exchange of NBD Data with other users Exchange of NBD Data with other users 7.1 Problem Description The user may occasionally be in need to transfer some data from the main NBD database to other departments or agencies for their further use. To do so, one may require creating a smaller “Transfer” file with such data, which can either fit on a floppy disk or can be distributed by e-mail.
Exchange of NBD Data with other users A small window called “New Query”, will pop up on the screen with the default option “Design View” automatically highlighted. As this is exactly the option we need, proceed and click on “OK”. A second small window, called “Show Table”, will appear on the screen.
Exchange of NBD Data with other users The view shown above is called “Query 1: Select Query”. It is in this view where the user has to select/design a query which will look into the specified database table and select the data records targeted for transfer/exchange.
Exchange of NBD Data with other users Step E7: Select “Datasheet View” under “View” of the MS Access main menu to see if the query is selecting properly the desired data records; if it is not, change/amend the query criteria accordingly.
Exchange of NBD Data with other users selected data from any main database file/table into the newly created file as described in the steps E10 and E11. Data stored in the “Transfer” file can now easily be transferred to other users either by e-mail or on a floppy disk. If there is a need to transfer data from more than one station, and for different data records, the user should just repeat the steps described above but for other stations and/or different data records.
Annexes Annexes Annex 1: Annex 2: Annex 3: Annex 4: Annex 5: Annex 6: Consecutive Steps in Connecting Storage Module SM4M/SM192 to PC. Consecutive Steps in Retrieving Raw Data Files form SM4M/SM192 Storage Module to PC. Consecutive Steps in Processing Raw Data Files in Report. Consecutive Steps in Final Processing of Report Output Files in MS Access and Appending Processed New Data Sets to NBD. General Design of the Append Queries for Pre-Processing of the Report Output Files in MS Access.
Annexes Annex 1: Consecutive Steps in Connecting Storage Module SM4M/SM192 to PC. Annex 1: Consecutive Steps in Connecting Storage Module SM4M/SM192 to PC. Step A1 A2 A3 A4 A5 A6 A7 A8 A9 Action Connect the SM4M Storage Module to the SC532A Interface using the blue SC12 cable. Make sure the SC532A Interface is powered. On PC, double click the PC208W Datalogger Support Software Icon. Click the StgModule tab. Click the SM4M/SM16M tab at the top-right corner of the SMS window.
Annexes Annex 3: Consecutive Steps in Processing Raw Data Files in Report. Annex 3:Consecutive Steps in Processing Raw Data Files in Report. Step C1 C2 C3 C4 C5 C6 C7 Action Activate Report by clicking the Report tab on the PC208W toolbar. Choose Open from the File menu to activate a pre-defined parameter file. Navigate to the location on hard drive, which contains the parameter files. This is “C:\CAMPBELL\FILES\PARAMETR” if the Recommended Directory Structure is used.
Annexes Annex 5: General Design of the Append Queries for Final Processing of the Report Output Files in MS Access. The function of the pre-defined append queries is to perform the final processing steps in MS Access.
Annexes Annex 6: Design of the ID Converter Table The ID Converter table serves to transfer the datalogger IDs to official station identifiers. Due to memory allocation, the datalogger ID is a short integer between 1 and 254, which does not allow for proper station numbering in a comprehensive hydrometeorological network. The following screen presents the design of the ID Converter table.
