Development and Use of a Climate Database of Daily and Synoptic Data for All Africa at ACMAD

1. Development and Use of a Climate Database of Daily and Synoptic Data for All Africa at ACMAD

2. Database Design and Architecture • The underlying design of the data model is the model for CLIMSOFT version 3, which was last updated in 2010. The updated model took into account new WMO requirements, e.g. ability to store information on station movement. • Data model allows storage of different data formats: numeric, text, Boolean (Yes/No) and graphical images. • Though originally designed in MS Access, the database design can be ported easily to any Relational Database Management System (RDBMS). Currently the design has been ported to MySQL and PostgreSQL using automated SQL scripts. • For the ACMAD climatological database, the open source PostgreSQL was chosen for its efficiency, stability and robustness. • The PostgreSQL database is connected to MS Access through the technology of open database connectivity (ODBC). MS Access serves as a graphical front-end to the PostgreSQL database mainly because of Access’s excellent graphical query building tools. • PostgreSQL data storage capacity is on the order of Terabytes (TB).

3. Ingestion of Daily Data • Automatic download of daily data from NOAA FTP site. An FTP client at ACMAD has been configured to download required data at a fixed time daily. • The selected data includes the elements: Precip, Tmax, Tmin and Tmean for all GTS stations in Africa. • The total number of stations is close to 1000. • After download, the data are imported into the CLIMSOFT PostgreSQL database using special routines developed as an extension to CLIMSOFT. • Currently the data go through basic QC before using the data for products. • This process of downloading and ingesting daily data on a routine basis was implemented during the first quarter of 2012.

4. Ingestion of Synoptic Data • Routines have also been developed in CLIMSOFT for the ingestion of synoptic data from the CoroborMessir Aero message switching system. • Synoptic data is transferred automatically by FTP from the Messir Aero PC to the workstation holding the climate database. • The data are automatically imported into the CLIMSOFT database at a scheduled time which can be easily modified from the CLIMSOFT user interface. • The module for the ingestion of synoptic data was operationally implemented from mid-2012.

5. Current Database Status • Longest daily data records in the database as available from the NOAA FTP site date back to 1972. • Few months of synoptic data from mid-2012. Imported from Corobor’sMessir Aero message switching system installed at ACMAD • Total database size is approximately 24 GB • Total number of observation records in the CLIMSOFT PostgreSQL back-end database is about 21 000 records. • Currently, basic QC is carried out. There are plans to implement more rigorous QC checks.

6. Data Products for Routine ACMAD Climate Monitoring and for Special Applications • Table of 10-day summaries for selected stations for all Africa for 10-Day Bulletin • Data output in format suitable for direct use in special applications, namely, RClimDex, CPT, Instat, AgroMetShell. Easy addition of output for other required applications. • Query outputs upon requests e.g. on rainfall extremes during August 2012 flooding over West Africa.

7. Sample Data Output for RClimDex

8. Data Output for INSTAT

9. Data Output for African RCC Data Services • Long-term climatological means generated for all GTS stations in Africa. Climatological means available for the period 1981-1990, 1991-2000, 2001-2010, 1981-2010. • Data products in XML format. PHP used for graphical interface for viewing data. Data can be viewed on any browser. Dynamic web updating when XML files have been updated.