Weather forecast

lhbox duckdb --catalog commons/pubdata

SELECT capital_city, day, tmax_c, tmin_c, precipitation_mm, precipitation_prob_pct, wind_max_kmh
FROM commons_pubdata.weather.forecast
WHERE country_code IN ('ES', 'GB', 'JP') AND day BETWEEN current_date AND current_date + 7
ORDER BY capital_city, day;

INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs;
CREATE SECRET pubdata (TYPE S3, ENDPOINT 'lakehousebox-public-data.b-cdn.net', URL_STYLE 'path', REGION 'us-east-1');
ATTACH ':memory:' AS pubdata;
CREATE SCHEMA pubdata.weather;
CREATE VIEW pubdata.weather.forecast AS SELECT * FROM iceberg_scan('s3://commons--pubdata/weather/forecast');

SELECT capital_city, day, tmax_c, tmin_c, precipitation_mm, precipitation_prob_pct, wind_max_kmh
FROM pubdata.weather.forecast
WHERE country_code IN ('ES', 'GB', 'JP') AND day BETWEEN current_date AND current_date + 7
ORDER BY capital_city, day;

# Weather forecast

*Sixteen-day weather forecast and recent 7-day history for every world capital.*

Daily numerical weather predictions and recent past-week analyses for the world's capital cities from Open-Meteo, blending national weather service models (ECMWF IFS, NOAA GFS, DWD ICON, Météo-France and UK Met Office). One row per capital and calendar day across a rolling 23-day window (the last 7 days, today and 15 days ahead): WMO weather code, maximum, minimum and mean temperature, apparent temperature, precipitation sum and probability, maximum wind speed and solar radiation. Bridges the multi-day station reporting lag of `weather.daily` into a 16-day outlook.

## At a glance

| | |
|---|---|
| Table | `pubdata.weather.forecast` |
| Category | Weather · Daily, Time series, Weather |
| Rows | n/a |
| Size | n/a data file(s) |
| Iceberg format | v? |
| Last refreshed | not loaded yet |
| Source version | n/a |
| Refresh cadence | daily (replace) |
| Coverage | ~208 world capitals (every capital with coordinates in reference.countries); rolling 23-day window (past 7 days through 15 days ahead), updated daily |

## Origin

- **Source:** [Open-Meteo Weather Forecast API (ECMWF, NOAA GFS, DWD ICON, Météo-France, UKMO)](https://open-meteo.com/en/docs)
- **Licence:** CC BY 4.0
- **Attribution:** Weather data by Open-Meteo.com (CC BY 4.0), from ECMWF, NOAA, DWD, Météo-France and UK Met Office numerical weather prediction models.
- **Recipe:** `pubdata/recipes/weather_forecast.py` in the LakehouseBox repository; registry entry `pubdata/registry/weather_forecast.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `country_code` | string | ISO 3166-1 alpha-2 of the capital's country (joins reference.countries) |
| `iso3` | string | ISO 3166-1 alpha-3 of the country |
| `capital_city` | string | name of the capital city |
| `latitude` | double | WGS84 latitude of the forecast grid point |
| `longitude` | double | WGS84 longitude of the forecast grid point |
| `day` | date | calendar day (UTC) of the forecast or analysis |
| `is_forecast` | boolean | true for today and future days, false for the 7-day recent model analysis |
| `weather_code` | int | WMO weather interpretation code (0 clear, 1-3 cloudy, 51-67 rain, 71-77 snow, 95-99 thunderstorm) |
| `tmax_c` | double | maximum daily air temperature at 2 m, °C |
| `tmin_c` | double | minimum daily air temperature at 2 m, °C |
| `tmean_c` | double | mean daily air temperature at 2 m, °C |
| `apparent_tmax_c` | double | maximum apparent (feels-like) temperature, °C |
| `apparent_tmin_c` | double | minimum apparent (feels-like) temperature, °C |
| `precipitation_mm` | double | total daily precipitation (rain, showers, snow water equivalent), mm |
| `precipitation_prob_pct` | int | maximum daily probability of precipitation, 0-100% |
| `wind_max_kmh` | double | maximum daily wind speed at 10 m, km/h |
| `shortwave_radiation_mj_m2` | double | daily sum of solar shortwave radiation, MJ/m² |

**Join keys:** `country_code`, `iso3`, `day`.

## For agents

One row per `(country_code, day)`. `is_forecast` is true from the refresh date onwards and false for the preceding 7
days of seamless model analysis (useful to fill the 3-7 day GHCN reporting lag in `weather.daily`). Join with
`weather.daily` via `weather.stations` on `(country_code, day)` or with `calendars.public_holidays` on
`f.country_code = h.country_code AND f.day = h.date`.

## Example SQL (DuckDB)

```sql
-- 7-day forecast for Madrid, London and Tokyo
SELECT capital_city, day, tmax_c, tmin_c, precipitation_mm, precipitation_prob_pct, wind_max_kmh
FROM pubdata.weather.forecast
WHERE country_code IN ('ES', 'GB', 'JP') AND day BETWEEN current_date AND current_date + 7
ORDER BY capital_city, day;
-- European capitals with heavy rain (> 10 mm) or freezing temperatures in the next week
SELECT f.country_code, f.capital_city, f.day, f.tmin_c, f.tmax_c, f.precipitation_mm
FROM pubdata.weather.forecast AS f
JOIN pubdata.reference.countries AS c USING (country_code)
WHERE c.region = 'Europe & Central Asia'
  AND f.day BETWEEN current_date AND current_date + 7
  AND (f.precipitation_mm >= 10 OR f.tmin_c < 0)
ORDER BY f.precipitation_mm DESC, f.day;
```

## How to read it

With a LakehouseBox account:

```bash
# with a LakehouseBox account (any organisation): the public catalog by name, read-only
lhbox duckdb --catalog commons/pubdata     # opens DuckDB attached as `commons_pubdata`
SELECT * FROM commons_pubdata.weather.forecast LIMIT 10;
```

Public URL of the table's storage: `https://s3.lakehousebox.com/commons--pubdata/weather/forecast/`. This card: `https://lakehousebox.com/pubdata/cards/weather_forecast.md`; the page: https://lakehousebox.com/public-data/weather_forecast. Every dataset as a table: `commons_pubdata.meta.datasets`; as Markdown: https://lakehousebox.com/public-data.md.