# Countries

*The reference table every other dataset joins on.*

ISO 3166-1 codes (alpha-2, alpha-3, numeric) and names from the ISO/Debian iso-codes data, with the World Bank's region, income group and capital city with coordinates. One row per country or territory (249). Use it to turn a code into a name, a region or a capital, and as the spine for joining the other public datasets.

## At a glance

| | |
|---|---|
| Table | `pubdata.reference.countries` |
| Category | Reference · Country, Reference |
| Rows | 249 |
| Size | 0.0 MB in 1 data file(s) |
| Iceberg format | v2 |
| Last refreshed | 2026-10-10T10:48:49Z |
| Source version | pycountry 26.2.16 + worldbank 73f56217ac23 |
| Refresh cadence | monthly (replace) |
| Coverage | 249 countries and territories; current state |

## Origin

- **Source:** [ISO 3166 (iso-codes via pycountry) + World Bank country API](https://data.worldbank.org/)
- **Licence:** iso-codes: LGPL-2.1 (data); World Bank: CC BY 4.0
- **Attribution:** ISO 3166 names and codes from the Debian iso-codes project through pycountry (LGPL-2.1); region, income level and capitals from the World Bank Indicators API (CC BY 4.0).
- **Recipe:** `pubdata/recipes/countries.py` in the LakehouseBox repository; registry entry `pubdata/registry/countries.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `country_code` | string | ISO 3166-1 alpha-2, the join key used across pubdata |
| `iso3` | string | ISO 3166-1 alpha-3 (the World Bank's key) |
| `iso_numeric` | int | ISO 3166-1 numeric |
| `name` | string | ISO short name in English |
| `official_name` | string | ISO official name where defined |
| `common_name` | string | ISO common name where defined |
| `wb_region` | string | World Bank region, e.g. Europe & Central Asia; null for territories the Bank does not classify |
| `wb_income_level` | string | World Bank income group: High income, Upper middle income, Lower middle income, Low income |
| `capital_city` | string | capital city as the World Bank lists it |
| `capital_longitude` | double | capital's longitude (WGS84) |
| `capital_latitude` | double | capital's latitude (WGS84) |
| `flag` | string | the flag emoji |

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

## For agents

`country_code` (alpha-2) is the join key across pubdata; the World Bank indicators carry `iso3` as well, and this
table maps one to the other. Kosovo has no ISO alpha-2 code (the Bank uses XK), so a few Bank rows have no
`country_code`. Territories (e.g. Greenland) are rows here even when the Bank has no region for them.

## Example SQL (DuckDB)

```sql
-- countries per World Bank region and income group
SELECT wb_region, wb_income_level, count(*) AS countries
FROM pubdata.reference.countries
WHERE wb_region IS NOT NULL
GROUP BY 1, 2 ORDER BY 1, 2;
-- attach a name and a region to your country codes
SELECT s.country_code, c.name, c.wb_region, sum(s.revenue) AS revenue
FROM my.sales AS s JOIN pubdata.reference.countries AS c USING (country_code)
GROUP BY 1, 2, 3;
```

## How to read it

Without an account, over HTTPS (the bucket is public):

```sql
INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs;
-- anonymous: the bucket is public, requests go unsigned, through the public-data cache
CREATE OR REPLACE SECRET pubdata (TYPE S3, ENDPOINT 'lakehousebox-public-data.b-cdn.net', URL_STYLE 'path', REGION 'us-east-1');
CREATE OR REPLACE VIEW countries AS SELECT * FROM iceberg_scan('s3://commons--pubdata/reference/countries');
SELECT * FROM countries LIMIT 10;
```

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.reference.countries LIMIT 10;
```

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