Building a Database for Managing Weather Data and Algorithm for Tracking Severe Weather Patterns

2.2. Weather Conditions

2.2.1. Air Pressure

The definition of air pressure varies in context, but they all mean the same things. Air pressure (AP) is the pressure exerted by the weight of air in the atmosphere of Earth, and it is proportional to the gravitational force. In a recent study conducted by Spiridonov et al. (2021), air pressure was defined as the force per unit area exerted on a surface by the weight of air force above it. AP often referred to as atmospheric pressure, is a fundamental aspect of Earth’s atmosphere, influencing various aspects of human society (Dohmen et al., 2020). In a study conducted by Cui et al. (2017) it was reported that a decrease in AP resulted to a decrease in respiratory flow rate at standard condition. The study also reported that Oxygen consumption and carbon dioxide significantly increase. However, respiratory quotient observed to increase. Like any other substances, AP has both positive and negative effect on human daily lives. Chen et al. (2023) explained the medical of AP to the medical system, according to him AP serves as a key indicator for weather forecasting. Also, meteorologists analyze pressure patterns to predict weather changes accurately. In a study conducted by Rabinovich (2020), the meteorological tsunamis are mainly produced by directly air pressure force. The result of this study shows that rise in AP often signifies fair weather, while falling pressure indicate the approach of a storm or other atmospheric disturbances. This predictive capability enables communities to prepare for impending weather events, reducing the risk of damage and ensuring public safety. Although, it was said that moderate AP conditions are essential for optimal agricultural productivity. It makes farmers rely on pressure readings to schedule planting, irrigation, and harvesting activities. A study conducted by Heinrich et al. (2022) explore the impact and significant of AP is aviation safety. It was recorded that pilots use pressure data to determine aircraft altitude, calculate airspeed, and ensure safe takeoffs and landings. According to National Research Council (US) Committee on Air Quality in Passenger Cabins of Commercial Aircraft (2002, p2), AP variations affect aircraft performance, particularly during ascent and descent. Accurate AP readings help pilots navigate safely through different atmospheric conditions, reducing the risk of aviation accidents and enhancing passenger safety. Extreme fluctuations in AP are severe and can have adverse effects on human health. In most cases sudden changes in pressure, often associated with weather fronts or storms, may trigger headaches, migraines, and joint pain in susceptible individuals (Lickiewiz et al., 2020). These health issues can significantly impact daily activities and quality of life, particularly for those with pre-existing medical conditions. In architectural science it was observed that drastic shifts in AP can cause structural damage to buildings and infrastructure (Askar et al., 2021). Also, high tense of winds is said to be associated with storms or hurricanes create pressure differentials that exert significant force on structures, leading to structural failures or collapse. Vulnerable buildings, such as those with weak foundations or inadequate reinforcement, are particularly susceptible to damage during extreme pressure events (Yang et al., 2018). In transportation, a study by Chen et al. (2023) reported that AP variations can disrupt transportation systems, leading to delays and cancellations. Changes in pressure affect aircraft performance, necessitating adjustments to flight schedules or routes. Similarly, fluctuations in pressure may impact rail and road networks, causing delays or closures due to safety concerns. Transportation disruptions can inconvenience commuters, disrupt supply chains, and have economic repercussions for affected regions.

2.2.2. Temperature

Research has shown that temperature is of two difference records; minimum and maximum which are known to be the fundamental aspect of climatic and weather condition. It serves is identified as one of the influencers of various natural processes and human operations. According to Luce and Callanan (2020), temperature represent the state of hotness or cold of a substance. Asif et al. (2018) stated that the minimum temperature (Mi-Temp) is the lowest record of a temperature within a specific time, mostly over 24-hour period or over the night. The Mi-Temp is observed early in the morning, before the sunrise. A study by Gobbett et al. (2021) stated that this type of temperature is essential for recording cold exposure risks, determining level of frost occurrence, serving as the basis for understanding the variation in temperature. When the temperature is at maximum, a form of solar radiation and atmospheric conditions occur leading to peak heat (Liu et al., 2021). According to Climate Change Knowledge Portal (2021) maximum temperature (Max-Temp) is the highest degree of temperature that could be recorded in during a period. Comprehending the highest recorded temperatures is essential for analysing the hazards associated with heat stress, measuring the amount of energy required, and predicting patterns in weather conditions. In a study conducted by Pranoto et al. (2019) it was shown that one of the major influencers of temperature is solar radiation, also regarded as sunlight. It is known to be the primary driver of temperature differences on earth’s surface. Latitude and seasonality are another great factor influencing the degree of temperature, a study by Sajadi et al. (2020) recognized latitude playing a significant role in temperature distribution. The equatorial regions are said to receive more direct sunlight and this led to higher temperatures compared to polar regions. On the other hand, variations in season caused by the earth’s axial tilt, result in temperature fluctuation between higher maximum (summer) and lower minimum (winter) (Ola et al., 2022). Other factors and parameters influencing the behavior of the temperature are topography and elevation, land cover and surface properties. Medically, it was reported that during the covid 19 pandemic, temperature was observed to be an environmental driver of the COVID-19 outbreak. Lower and higher temperatures might be positive to decrease the COVID-19 incidence (Mecenas et al., 2020). When a temperature is been influenced to behavior differently from normal, it becomes hazardous. Such case of temperature is termed to be extreme. This happens mostly when the temperature is cold or hot beyond normal. A study conducted by Li and Anming (2023) identified the threat of extreme temperature, and according to National Institute of Environmental Health Sciences (2024). Extreme hot temperature has an adverse effect related to illnesses such as heatstroke and dehydration; this is common among vulnerable populations such as the elderly and children. On the other hand, cold temperatures increase the risk of hypothermia and frostbite, necessitating protective measures and shelter provisions. Also, Jerry and John (2015) reported that, temperature differences impact the crop growth and yield. This is as a result of heat stress during flowering and fruiting stages can resulting to a reduction in crop yields and quality.

2.2.3. Humidity

According to Wu et al. (2020) humidity is the ratio of the actual water vapor pressure to the saturation water vapor pressure at the prevailing temperature. It amounts for the water vapor present in the air. Humidity can encompass the level of moisture in any substance; however, it is commonly used to describe the amount of water vapor in a gas or a combination of gases like the air in the atmosphere. The significant of humidity in the environment is identified in a study conducted by Xiong et al. (2017), affecting both living organisms and non-living systems. The weather dynamics is one of the importance of humidity, which its ability to shaping weather conditions worldwide. The high levels of humidity are known to contribute to the formation of clouds, precipitation and fog which impact rainfall patterns and cloud cover. It’s the other case when the humidity is low, leading to dryness, reduction in cloud formation and high evaporation rates. Human health and comfort are said to be related to humidity significantly impacting it, maintain indoor air quality and preserves structural integrity in architecture. Also, agriculture ecosystems and yield of crops are other things humidity influence. Excessive and high humidity comes with a lot of challenges. To human well-being, elevated temperature in conjunction with high humidity will cause discomfort and heat stress leading to stroke and dehydration (Abokhashabah et al., 2021). According to a microbiological study conducted by Romero et al. (2022) it was stated that excessive humidity enhances the proliferation of fungal diseases in crops.

2.2.4. Precipitation

Precipitation is an essential component of the Earth’s weather system, encompassing any type of water, including rain, snow, sleet, or hail, that descends from the atmosphere to the Earth’s surface (Allen et al., 2020). According to water is an essential part of the hydrological cycle, and it has a significant impact on replenishing freshwater supplies, sustaining ecosystems, and affecting several elements of human life. According to Atkinson (2019) rainfall occurs when water vapor in the atmosphere condenses into liquid droplets and falls to the Earth’s surface.

It is the most common form of precipitation and plays a vital role in sustaining vegetation, replenishing surface water sources, and maintaining ecological balance (Hao et al., 2019). Snowfall is another form of precipitate. According to Mohammadian et al. (2021) it is said to results from the condensation of water vapor into ice crystals in the atmosphere, forming snowflakes that accumulate and fall to the ground. In agriculture, snow is said to be significant for maintaining soil moisture levels, insulating plant roots, and supporting winter recreational activities. Sleet was identified as a precipitated in a study conducted by Rohli et al. (2021) it was said to be formed when raindrops freeze into ice pellets before reaching the ground, while freezing rain occurs when liquid raindrops freeze upon contact with cold surfaces. Both phenomena can create hazardous conditions, leading to icy roads, sidewalks, and power outages. Hailstones are solid ice pellets that form within thunderstorm clouds through a process of repeated freezing and melting of water droplets. Hailstorms can cause significant damage to crops, vehicles, and buildings, posing risks to agriculture and infrastructure (Allen et al., 2020). A severe insufficient or excessive precipitation is related to droughts, crop failures, soil erosion, and loss of agricultural productivity, impacting food security and livelihoods. A severe change in precipitation patterns can disrupt ecological balance, alter species distributions, and increase the risk of habitat loss and fragmentation. A severe precipitate is also related to flooding. According to Reed et al. (2022) flooding disrupts transportation networks, contaminates water supplies, and displaces populations. Intense rainfall and runoff accelerate soil erosion, sediment transport, and deposition, leading to soil degradation, river channel morphological changes, and sedimentation in reservoirs and waterways.

2.2.5. Wind Speed

For a very long time the Anemology has been providing insight on the significant of wind and its impact on nature. In 2019, a study by Zeng et al. (2019) defined wind power as a rapid growing alternative energy source. And according to Topić et al. (2014) wind energy is said to have a positive impact on the grid system, positively influenced the sociological structures, and negatively impacts the environment. Wind speed is primarily driven by differences in air pressure between high-pressure and low-pressure areas. In a study conducted by Li et al. (2019) the pressure transient was analyzed using the tunnel portal design, the result shows that the results indicate a significant reduction in pressure transients caused by train tunnel entry, compared to a tunnel with a constant cross-section. The result of a study conducted by Li et al. (2021) shows that surface roughness, such as terrain features and vegetation, can slow down wind speed by exerting frictional resistance on the moving air. Wind speeds tend to be higher at higher altitudes where frictional effects are minimal. Differences in temperature between adjacent air masses can create localized pressure variations, leading to wind circulation patterns such as sea breezes, mountain breezes, and monsoons. According to National Geographic (2023), wind influences several areas of lives, higher speeds of wind result in increased power production of electricity, wind speed influences weather patterns, including the formation and movement of weather systems such as storms, fronts, and cyclones. Severe winds are called strong winds which pose hazards to navigation, leading to delays, diversions, and safety concerns. Severe wind events are characterized by their intensity, duration, and geographical extent.

2.3. The Role of Machine Learning and Databases in Weather Tracking

Machine learning and databases play pivotal roles in the field of weather tracking, enhancing the accuracy of forecasts and providing valuable insights into weather patterns. The integration of these technologies enables meteorologists and researchers to process vast amounts of data and generate predictive models that are essential for various applications, from agriculture to disaster management. Machine learning in weather tracking involves data analysis and pattern recognition, where algorithms analyze large datasets to identify patterns and correlations that may not be immediately apparent to human analysts (Chen et al., 2023). These patterns can include temperature fluctuations, precipitation levels, wind speeds, and atmospheric pressure changes. Techniques such as regression analysis, classification, clustering, and neural networks are used to predict future weather conditions based on historical data (Adekunle et al., 2024). Predictive modeling is another crucial aspect, using machine learning to forecast short-term and long-term weather conditions. These models can predict temperature, humidity, precipitation, storm paths, and other critical weather elements. Models such as Random Forests, Support Vector Machines (SVMs), and Deep Learning architectures like Convolutional Neural Networks (CNNs) and Recurrent Neural Networks (RNNs) are commonly employed to improve forecast accuracy (Chen et al., 2023). Relational databases (e.g., MySQL, PostgreSQL) and NoSQL databases (e.g., MongoDB, Cassandra) are used to store different types of weather data, including numerical readings, satellite images, and textual reports (Khan et al., 2023). Databases also integrate weather data from various sources, providing a unified platform for analysis. Data warehouses and data lakes are often used to consolidate and manage large volumes of weather data, enabling efficient retrieval and analysis. The volume of weather data is enormous, requiring big data technologies for storage, processing, and analysis. Apache Hadoop and Spark are popular frameworks that facilitate distributed processing of large datasets (ASAP, n.d.). These technologies support advanced analytics and machine learning workflows, allowing for the extraction of meaningful insights from vast weather datasets.

2.4. Related Research Work

Study	Weather condition	Effect on	Case	Result
Wu et al. (2020)	Humidity vs Temperture	Covid 19	Severe	Revealed that temperature and relative humidity were both negatively related to daily new cases and deaths.
Shi et al. (2020)	Temperature	Covid 19	Severe	COVID-19 incidence changed with temperature as daily incidence decreased when the temperature rose
Dohmen et al. (2020)	Air pressure	Oxygen and dyspnea		There was a significant association between barometric pressure and SpO2 < 96%, and we found that a reduction of 166.67 hPa was needed to get a 1% reduction in SpO2. The change in atmospheric pressure was not significantly associated with shortness of breath, also not in subjects with reduced lung function.
Akira et al. (2000)	Air pressure	Visual field defect		Decreased air pressure reduced the occurrence of visual field defects significantly

3.2. Data Collected

The dataset utilized for this project is sourced from OpenWeather a valuable extension to the existing Nigerian Meteorological Agency (NiMet) dataset. It includes comprehensive weather information for cities across Nigeria, with a total of 22,541 observations. The dataset has been carefully structured in a CSV format with 21 attributes, providing detailed meteorological and geographical data. Attributes: 1) Country, 2) City, 3)Latitude, 4)Longitude, 5)Temperature, 6) Minimum temperature, 7) Maximum temperature, 8)Pressure measured in hPa, 9)Humidity percentage, 10)Sea level measured in hPa, 11)Ground level measured in hPa, 12)Wind speed, 13)Wind degree, 14)Sunrise, 15)Sunset, 16)Time zone, 17)Cloud, 18)Description of the weather condition, 19)Region, 20)Population, 21)Date: Date when the measurement was recorded.

3.3. Data Base Designs and Implementation

To design the database, such that it maintains the characteristic of a good database by avoiding redundancy, scalability, data independency, and integrity. To achieve these, the database was normalized. At first, the database structure was initially drafted on Ms Excel before the implementation as seen in Figure 1 below.

Four tables were created based on independence and dependences. This process involves Identifying an entity as a logical collection of items related to the stated database and an attribute which is descriptive or quantitative characteristic of the entity. The entity which is known to be a thing, place, person or object that is independent of another, it doesn’t require any certain function and are uniquely identifiable. The tangible entity which contains attributes that can be touched and felt physically identified in the data to be country, city, region, and population. The intangible entity was also identified to be date and description which are abstract. For each of the table drafted, there are primary keys assigned to each of the entities serving as an ID number, which makes it different from other entities. Location table with ID_location, weather condition table with ID_Weather_condition, geography table with ID_geography, and date taken with ID_date_taken. All these serves as uniquely defined, no presence of duplicate. The three tables (weather condition, geography, and data taken) were all depending on the location table. This made it necessary to create a relationship between the independent and the dependents. And the relationship was created such that there is one-to-one correspondence between entities of the dependent tables to that of independent. The creating of the database was done using a structure query language Mysql which is sufficient for creating a relational database. This process involves the implementation of the designed in Figure 2[(a) - (d)].

3.4. Building a Supervised Classification Algorithm

After the database has been successfully designed and implemented. The supervised machine learning (ML) was trained on it for classification (Figure 3).

3.4.1. Model Selection

A binary classifier was trained involving three models (i.e., logistic regression, a random forest, and a binary neural network) was trained for comparison purpose using the data from the database to classify severe weather data and un-severe weather data. These models were selected based on strength to handle variable of two classes, such as event occurring and the other not occurring. The two classes are mutually exclusive and mutually exhaustive. In this case, severe and un-severe. The logistic regression uses maximum likelihood estimate to find the best least and unbiased estimate (i.e. coefficients) that minimizes the log-odd (equation 2) of predicting a weather condition to be true.

$Odds={\displaystyle \frac{p(Y=severe)}{P(Y=un-severe)}}={\displaystyle \frac{P(Y=severe)}{1-p(Y=severe)}}-------equation1$$\log Odds={\displaystyle \frac{P(Y=severe)}{1-p(Y=severe)}}=\log p\left(Y=severe\right)-\log (1-p(Y=severe)--equation2$The logistic function, also referred to as the sigmoid function (equation 3), is employed to convert the result of the linear equation into a probability value ranging from 0 to 1.

$S\left(X\right)={\displaystyle \frac{1}{1+e^{-x}}}-------equation3$Logistic regression models acquire knowledge of a decision boundary that divides the two classes inside the feature space. In logistic regression, the decision border is commonly depicted as a linear barrier defined as the collection of points where the probability is precisely 0.5. And any probability less than the threshold is classified as 0 and probability greater than the threshold is classified as 1 as seen in Figure 4.

The mathematical representation of the model is the linear combination of all the independent variables with some weights (i.e. coefficients) contribution to the prediction of the probability of predicting the weather condition to be severe which is given below:

$p\left(Y=severe|X_i\right)={\displaystyle \frac{e^{k_0+k_1{X}_1+\dots +k_i{X}_i}}{1+e^{k_0+k_1{X}_1+\dots +k_i{X}_i}}}-------equation4$

3.4.2. Data Modification: Determining Weather Severity Classification

By default, the collected data has no severe label. To assign this label, crucial step involved classifying weather severity to facilitate predictive modeling was taken. The classification was based on specific thresholds for various weather parameters, creating a binary categorization of ‘Severe’ and ‘Non-Severe’ weather conditions. The criteria for classifying weather as ‘Severe’ included high temperature, low pressure, extreme humidity levels, high wind speed, and certain weather descriptions indicating severe conditions. Temperature weather was classified as severe if the temperature exceeded 313.15 Kelvin (40 °C) in accordance with National weather service (n. d.) report, indicating extreme heat conditions. Low atmospheric pressure often correlates with severe weather conditions. Hence, weather was deemed severe if the pressure dropped below 1000 hPa (Dodmen et al., 2020). Both extremely high and low humidity levels can indicate severe weather. Therefore, weather was classified as severe if the humidity was either above 90% or below 20% (National weather service, n. d.). High wind speeds are a common indicator of severe weather, such as storms or hurricanes. Thus, weather was classified as severe if the wind speed exceeded 10 meters per second. Certain keywords in the weather description, such as “storm,” “thunderstorm,” or “heavy rain,” were used to identify severe weather conditions. Additionally, if the cloud cover was greater than 80% and these keywords appeared in the description, the weather was classified as severe. The Figure 5a,b below show the geographical representation of the severed and not severed state in Nigeria.

3.4.3. Data Preparation and Preprocessing

The first thing that was done while preparing the dataset is to address any form of data issues in the dataset. The data was normalized using the equation below after which the dataset was partitioned into 80% for fitting data, and 20% for validation.

$X\text{'}={\displaystyle \frac{x-\mathrm{m}\mathrm{i}\mathrm{n}\left(x\right)}{\max \left(x\right)-\mathrm{m}\mathrm{i}\mathrm{n}\left(x\right)}}$Imbalancing issue in the fitting data was rectify using the mixed method which involves the combination of oversampling and under-sampling to equalize the distribution of the target variable as seen in the Figure 6a,b below.

3.4.4. Model Evaluation Process

As stated in the previous subchapter, the test data which is 20% of the entire dataset was used to test the performance the model. This data served as a new instance to be used for cross-validation methods which can be used to test the performance of the logistic regression and to obtain a reliability estimate of the model. The evaluation involves the assessment of various metrics, including accuracy, precision, recall, F1-score (Adekunle et al., 2024).

Equation 4: Performance metrics evaluation equation

$$\mathrm{Accuracy}={\displaystyle \frac{{TP}_{total}}{Totalnumberofinstances}}$$

True Positives (TP): This represents the number of weather condition correctly identified as severe by the model.

True Negatives (TN): This is the number of weather condition correctly identified as not severe.

False Positives (FP): This represents the number of weather condition incorrectly identified as severe when it is not severe.

False Negatives (FN): This is the number of weather condition incorrectly identified as not severe when it is severe.

3.4.5. Tools and Material

The implementation of this study was done in R programming software version X in an R studio integrated development environment on a Windows operating system. Several packages were used for a successful implementation. Packages such as tidyverse was used for data manipulation, wrangling, and visualization while dlookr was used to explore and visualize patterns of missingness through Pareto plots. Class imbalance in the weather severity outcome was addressed using the ROSE package caTools package was employed to split the data into training and testing subsets. To handle spatial data, the sf package was utilized for working with geographic coordinates, and interactive map visualizations were generated using mapview, enabling the mapping of weather severity patterns across locations in Nigeria. For predictive modeling, logistic regression was implemented using base R’s glm function, random forest model with the randomForest package, and neural network models were trained using the nnet package. Model evaluation was carried out using the caret package. All packages used are open-source and widely adopted in the scientific community for environmental and spatial data analysis, ensuring reproducibility and transparency of the results.(The code: https://github.com/josephdamilare01/Building-a-database-for-managing-weather-data-and-Algorithm-for-tracking-severe-weather-patterns./blob/main/Weather%20analysis.R )