AMV Database: Presentation of a SQL Tool for Quick and Efficient Analysis of the Winds
EUMETSAT is committed to support the operational monitoring of climate and the detection of global climate change. As part of this commitment, EUMETSAT provides consistent atmospheric climate data records from its operational satellites in low earth and geostationary orbits. Product validation is an important step when generating these records, and involves the comparison with independent data from various sources such as models, ground based measurements, and satellite products.
Both climate data records and validation data are distributed as numerous files on disk. Reading and processing such data is time consuming, and requires considerable software development so that data sources can be brought to identical spatial and temporal scales for comparison. In contrast to that, a geospatial database provides a single and direct access point to all data sources, and offers additional tools for spatial analysis, time series statistics, and webmapping.
In order to exploit such benefits, we recently developed a PostgreSQL (v11) database that facilitates the validation of atmospheric motion vectors at EUMETSAT. The database uses the PostGIS extension for spatial analysis. We found that spatially indexing and partitioning the database (per product and year) is essential to speed up query performance. Currently, there are ~4 TB or 16.4 billion rows of AMV data in the database, which we derived from polar and geostationary satellite orbits covering several decades. We routinely use the database for collocations with radiosondes, time series statistics, and bias analysis. Query speed is variable but generally within a few minutes of response time. We also developed a webmapping tool, which uses Geoserver to query the database in order to put AMV data on interactive maps for monitoring and validation.
Here, we will present the generic database structure, content and typical use cases. We will conclude with a demonstration of the database’s webmapping capabilities.