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