ThaiWeather Series · Part 2
One of the first design decisions I made was to store all weather observations in my own PostgreSQL database. Instead of requesting weather information from the external provider every time a user visits the website, the data is collected at fixed intervals and stored locally. This reduces the number of external API requests, improves response times and makes historical weather data available.
PostgreSQLis a mature open source relational database that is widely used for web applications and backend services. It offers excellent performance, strong data integrity and powerful SQL capabilities while remaining easy to integrate with Python through SQLAlchemy.
The database currently consists of two main tables.
The cities table contains all supported locations. Each city has a unique identifier, a name, a country code and its geographical coordinates. Storing the coordinates allows the weather collector to request weather data directly from the external weather provider without relying on city name lookups.
The weather_observations table stores every weather measurement collected by the Python weather collector. Each observation is linked to a city through a foreign key relationship. Besides the measured temperature, the table also stores humidity, atmospheric pressure, wind speed, wind direction, cloud coverage, precipitation and the timestamp at which the observation was recorded.
By separating cities from weather observations, the database follows a normalized design. City information only needs to be stored once, while millions of weather observations can reference the same city. This avoids duplicated data and keeps the database efficient as it continues to grow.
To improve query performance, indexes are created on frequently searched columns such as the city identifier and measurement timestamp. These indexes make it possible to retrieve the latest observations or historical weather data efficiently, even after the database contains a large number of records.
Another important design choice was to preserve every collected observation instead of overwriting existing data. This allows the project to build historical weather graphs, calculate averages and compare weather conditions over longer periods. As more data is collected, the database becomes increasingly valuable for analysing weather trends.
Database Structure
The PostgreSQL database currently consists of two main tables: cities and weather_observations. Keeping these tables separate follows the principles of database normalization and avoids storing duplicate information.
The cities Table
The cities table contains all locations that are supported by ThaiWeather.asia. Each city is stored only once and receives a unique identifier that can be referenced by other tables.
The table contains the following columns:
| Column | Description |
|---|---|
id | Unique identifier for each city. |
name | Name of the city, for example Bangkok or Chiang Mai. |
country_code | ISO country code. Currently all cities belong to Thailand (TH). |
latitude | Latitude used when requesting weather data from the external API. |
longitude | Longitude used when requesting weather data from the external API. |
active | Indicates whether the city is currently enabled for data collection. |
Using latitude and longitude instead of only the city name makes API requests more reliable and avoids ambiguity between cities with identical names.
The weather_observations Table
The weather_observations table contains the actual weather measurements collected by the Python weather collector. Every time the scheduler runs, a new observation is inserted into this table.
Each observation is linked to a city through the city_id foreign key.
The table stores the following information:
| Column | Description |
|---|---|
id | Unique identifier for every weather observation. |
city_id | References the city in the cities table. |
temperature_c | Air temperature in degrees Celsius. |
humidity | Relative humidity percentage. |
pressure_hpa | Atmospheric pressure measured in hectopascals. |
wind_speed_ms | Wind speed in metres per second. |
wind_direction_deg | Wind direction in degrees. |
rain_mm | Amount of precipitation in millimetres. |
clouds_percent | Cloud coverage as a percentage. |
weather_code | Weather condition code returned by the weather provider. |
measured_at | Timestamp of when the weather measurement was recorded. |
source | Identifies the weather provider that supplied the observation. |
inserted_at | Timestamp indicating when the observation was inserted into PostgreSQL. |
Why two tables?
Separating cities from weather observations keeps the database efficient and scalable.
A city such as Bangkok only needs to be stored once in the cities table. Every weather observation then references Bangkok through the city_id column instead of storing the city’s name and coordinates repeatedly.
This design reduces storage requirements, improves consistency and simplifies maintenance. If the coordinates of a city ever need to be updated, they only have to be changed in one place.
As the project grows and millions of weather observations are collected, this structure will continue to perform efficiently while remaining easy to extend with new cities, additional weather providers or new weather parameters.
In the next article, I will explain how SQLAlchemy is used to define the database models and how Python communicates with PostgreSQL without writing raw SQL for every operation.