Reference · Public data

Countries

The reference table every other dataset joins on.

ISO 3166 (iso-codes via pycountry) + World Bank country API · iso-codes: LGPL-2.1 · commons_pubdata.reference.countries

Read Markdown ↗ Copy page

• 249rows
• 249 countries and territoriescoverage
• Monthlyrefresh
• 10 Oct 2026last refreshed
• 18 kBtable size

Map Sample rows

Query it with DuckDB.

With an account Without an account

BashCopy

lhbox duckdb --catalog commons/pubdata

SQL · Countries per World Bank region and income groupCopy

SELECT wb_region, wb_income_level, count(*) AS countries
FROM commons_pubdata.reference.countries
WHERE wb_region IS NOT NULL
GROUP BY 1, 2 ORDER BY 1, 2;

Read it where you work.

Public, read-only data. With an account, lhbox duckdb attaches the catalog as commons_pubdata; an agent with the LakehouseBox connector opens it with open_public_catalog("commons/pubdata"). Without one, DuckDB reads the public files directly.

Example queries

• Countries per World Bank region and income group

  SELECT wb_region, wb_income_level, count(*) AS countries
  FROM commons_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 commons_pubdata.reference.countries AS c USING (country_code)
  GROUP BY 1, 2, 3;

Know your columns.

Column	Type	Meaning
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

View all 12 columns

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.

Before you query.

1. 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.
2. Kosovo has no ISO alpha-2 code (the Bank uses XK), so a few Bank rows have no country_code.
3. Territories (e.g.
4. Greenland) are rows here even when the Bank has no region for them.

• Combine with Population & economy →Joins on country_code, iso3.
• Combine with Airports →Joins on country_code.
• Combine with Countries & regions →Joins on country_code.

Source & licence.

Source

ISO 3166 (iso-codes via pycountry) + World Bank country API

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).

Coverage

249 countries and territories; current state

Refresh

monthly, the whole table replaced; source version pycountry 26.2.16 + worldbank 73f56217ac23

Table

commons/pubdata · reference.countries · Iceberg v2 · browse the catalog

Recipe

pubdata/recipes/countries.py, registry entry pubdata/registry/countries.yaml

Source: ISO 3166 (iso-codes via pycountry) + World Bank country API · iso-codes: LGPL-2.1 · Snapshot shown: 10 Oct 2026HTML for people. Markdown for agents.

# 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.