PickOH, the travel site I run, has twelve ranking pages, one per month. Where to Go in August currently lists 62 countries and territories, from Malawi at #1 to Svalbard at #62. Nobody orders them by hand. A scoring formula and one Postgres query build all twelve pages from 30 years of climate data.
This post covers how the ranking works, the bug that gave four countries the same #1, and what happened when each assumption in the formula was tested.
The data: one point per country, 30 years deep
Each of the 250 countries and territories has twelve rows in Postgres, one per month: average daily high, average daily low, total rainfall and rain days. The numbers are 1991–2020 climate normals from the ERA5 reanalysis, fetched through Open-Meteo's archive API . Each one is measured at a single location, usually the capital.
Fetching it mostly took patience: 30-year requests use up Open-Meteo's free quota quickly, so the backfill had to cache and resume per location.…