Places · Public data

Countries & regions

Boundaries to put your points in a country or a region.

Overture Maps Foundation, divisions theme · CDLA-Permissive-2.0 · commons_pubdata.places.admin_areas

Read Markdown ↗ Copy page

• 5,210rows
• Worldwidecoverage
• Monthlyrefresh
• 10 Oct 2026last refreshed
• 3data files

Map Sample rows

Query it with DuckDB.

With an account Without an account

BashCopy

lhbox duckdb --catalog commons/pubdata

SQL · The regions of Spain by areaCopy

SELECT region_code, name, round(area_km2) AS km2
FROM commons_pubdata.places.admin_areas
WHERE subtype = 'region' AND country_code = 'ES' AND class = 'land'
ORDER BY km2 DESC;

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

• The regions of Spain by area

  SELECT region_code, name, round(area_km2) AS km2
  FROM commons_pubdata.places.admin_areas
  WHERE subtype = 'region' AND country_code = 'ES' AND class = 'land'
  ORDER BY km2 DESC;

• Which region is each of your points in (`&&` on the bounding box first, the exact polygon second)

  INSTALL spatial; LOAD spatial;
  SELECT p.point_id, a.region_code, a.name
  FROM my.points AS p JOIN commons_pubdata.places.admin_areas AS a
    ON a.subtype = 'region' AND a.class = 'land'
   AND a.geometry && ST_Point(p.lon, p.lat) AND ST_Contains(a.geometry, ST_Point(p.lon, p.lat));

Know your columns.

Column	Type	Meaning
area_id	string	Overture GERS id of the area
division_id	string	the division the area belongs to
subtype	string	country, dependency or region
class	string	land or maritime
country_code	string	ISO 3166-1 alpha-2 (joins reference.countries)
region_code	string	ISO 3166-2 region code for regions, e.g. ES-MD

View all 18 columns

name	string	primary name
is_land	boolean	land area (as opposed to maritime)
is_territorial	boolean	territorial extent
bbox_xmin	double	bounding box west (longitude)
bbox_ymin	double	bounding box south (latitude)
bbox_xmax	double	bounding box east
bbox_ymax	double	bounding box north
centroid_longitude	double	centroid longitude
centroid_latitude	double	centroid latitude
area_km2	double	spheroidal area, km²
geometry	geometry	the polygon or multipolygon (WGS84, longitude/latitude)
overture_release	string	the release this row came from

Join keys: country_code, region_code.

Before you query.

1. Filter class = 'land' unless you want maritime extents; a country can have both rows.
2. Region codes follow ISO 3166-2 where Overture has them.
3. geometry is an Iceberg v3 geometry column: read it with DuckDB 1.5.6 or newer (spatial extension); PyIceberg 0.12 cannot load v3 geometry tables yet.
4. Write spatial filters as geometry && <shape> AND ST_Intersects(geometry, <shape>): DuckDB skips files and row groups for && only.
5. The bounding box columns exist so a join also works without any spatial extension.

• Combine with Airports →Joins on country_code.
• Combine with Countries →Joins on country_code.
• Combine with Places of interest in Europe →Joins on country_code.

Source & licence.

Source

Overture Maps Foundation, divisions theme

Licence

CDLA-Permissive-2.0 (Overture); OpenStreetMap-derived parts under ODbL 1.0

Attribution

© Overture Maps Foundation; contains data from OpenStreetMap © OpenStreetMap contributors (ODbL) and geoBoundaries.

Coverage

worldwide, ~380 countries and dependencies, ~4,700 regions; the latest Overture release

Refresh

monthly, the whole table replaced; source version overture 2026-09-23.1

Table

commons/pubdata · places.admin_areas · Iceberg v3 · browse the catalog

Recipe

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

Source: Overture Maps Foundation, divisions theme · CDLA-Permissive-2.0 · Snapshot shown: 10 Oct 2026HTML for people. Markdown for agents.

# Countries & regions

*Boundaries to put your points in a country or a region.*

Countries, dependencies and first-level regions (states, provinces, Länder, autonomous communities) as polygons, from Overture Maps' divisions theme. One row per area with its name, codes, bounding box, centroid, area and a native geometry column (Iceberg v3, rows ordered by country). The polygons are detailed (the table is about 460 MB, almost all geometry): a query that leaves `geometry` out reads little, one that selects or tests it reads most of the table. Filter with `geometry && <shape>` first and an exact predicate such as `ST_Contains` second.

## At a glance

| | |
|---|---|
| Table | `pubdata.places.admin_areas` |
| Category | Places · Places, Spatial, Country |
| Rows | 5,210 |
| Size | 3 data file(s) |
| Iceberg format | v3 |
| Last refreshed | 2026-10-10T10:21:36Z |
| Source version | overture 2026-09-23.1 |
| Refresh cadence | monthly (replace) |
| Coverage | worldwide, ~380 countries and dependencies, ~4,700 regions; the latest Overture release |

## Origin

- **Source:** [Overture Maps Foundation, divisions theme](https://docs.overturemaps.org/guides/divisions/)
- **Licence:** CDLA-Permissive-2.0 (Overture); OpenStreetMap-derived parts under ODbL 1.0
- **Attribution:** © Overture Maps Foundation; contains data from OpenStreetMap © OpenStreetMap contributors (ODbL) and geoBoundaries.
- **Note:** Overture releases monthly; the release name is the source version.
- **Recipe:** `pubdata/recipes/admin_areas.py` in the LakehouseBox repository; registry entry `pubdata/registry/admin_areas.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `area_id` | string | Overture GERS id of the area |
| `division_id` | string | the division the area belongs to |
| `subtype` | string | country, dependency or region |
| `class` | string | land or maritime |
| `country_code` | string | ISO 3166-1 alpha-2 (joins reference.countries) |
| `region_code` | string | ISO 3166-2 region code for regions, e.g. ES-MD |
| `name` | string | primary name |
| `is_land` | boolean | land area (as opposed to maritime) |
| `is_territorial` | boolean | territorial extent |
| `bbox_xmin` | double | bounding box west (longitude) |
| `bbox_ymin` | double | bounding box south (latitude) |
| `bbox_xmax` | double | bounding box east |
| `bbox_ymax` | double | bounding box north |
| `centroid_longitude` | double | centroid longitude |
| `centroid_latitude` | double | centroid latitude |
| `area_km2` | double | spheroidal area, km² |
| `geometry` | geometry | the polygon or multipolygon (WGS84, longitude/latitude) |
| `overture_release` | string | the release this row came from |

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

## For agents

Filter `class = 'land'` unless you want maritime extents; a country can have both rows. Region codes follow ISO
3166-2 where Overture has them. `geometry` is an Iceberg v3 geometry column: read it with DuckDB 1.5.6 or newer
(spatial extension); PyIceberg 0.12 cannot load v3 geometry tables yet. Write spatial filters as
`geometry && <shape> AND ST_Intersects(geometry, <shape>)`: DuckDB skips files and row groups for `&&` only. The
bounding box columns exist so a join also works without any spatial extension.

## Example SQL (DuckDB)

```sql
-- the regions of Spain by area
SELECT region_code, name, round(area_km2) AS km2
FROM pubdata.places.admin_areas
WHERE subtype = 'region' AND country_code = 'ES' AND class = 'land'
ORDER BY km2 DESC;
-- which region is each of your points in (`&&` on the bounding box first, the exact polygon second)
INSTALL spatial; LOAD spatial;
SELECT p.point_id, a.region_code, a.name
FROM my.points AS p JOIN pubdata.places.admin_areas AS a
  ON a.subtype = 'region' AND a.class = 'land'
 AND a.geometry && ST_Point(p.lon, p.lat) AND ST_Contains(a.geometry, ST_Point(p.lon, p.lat));
```

## 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 admin_areas AS SELECT * FROM iceberg_scan('s3://commons--pubdata/places/admin_areas');
SELECT * FROM admin_areas 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.places.admin_areas LIMIT 10;
```

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