Population grid (H3)

lhbox duckdb --catalog commons/pubdata

SELECT h3_index, population, density_per_km2, latitude, longitude
FROM commons_pubdata.population.grid_h3
ORDER BY population DESC LIMIT 15;

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.population;
CREATE VIEW pubdata.population.grid_h3 AS SELECT * FROM iceberg_scan('s3://commons--pubdata/population/grid_h3');

SELECT h3_index, population, density_per_km2, latitude, longitude
FROM pubdata.population.grid_h3
ORDER BY population DESC LIMIT 15;

# Population grid (H3)

*World population mapped onto Uber H3 hexagonal cells, with centroids and density.*

Global resident population distributed across Uber's H3 resolution-4 hexagonal spatial index (~22 km edge, ~1,770 km² per cell) by Kontur, fusing the European Commission JRC Global Human Settlement Layer (GHSL), Meta High Resolution Settlement Layer, Microsoft Building Footprints, WorldPop and OpenStreetMap. One row per populated hexagon (~71,300 cells worldwide, summing to ~8.03 billion people) with the H3 index, resident population, spheroidal area, population density, WGS84 centroid and bounding box. Built for catchment analysis, site selection and spatial joins without needing raster tools.

## At a glance

| | |
|---|---|
| Table | `pubdata.population.grid_h3` |
| Category | Population · Population, Spatial |
| Rows | n/a |
| Size | n/a data file(s) |
| Iceberg format | v? |
| Last refreshed | not loaded yet |
| Source version | n/a |
| Refresh cadence | monthly (replace) |
| Coverage | worldwide, ~71,300 populated H3 resolution-4 hexagons (~8.03 billion people); latest Kontur release (2023-11-01) |

## Origin

- **Source:** [Kontur Population (Humanitarian Data Exchange, H3 resolution 4)](https://data.humdata.org/dataset/kontur-population-dataset-22km)
- **Licence:** CC BY 4.0
- **Attribution:** Kontur Population: Global Population Density for H3 Hexagons (CC BY 4.0, Kontur Inc., via Humanitarian Data Exchange), derived from EC JRC GHSL, Meta HRSL, Microsoft Building Footprints, WorldPop and OpenStreetMap.
- **Recipe:** `pubdata/recipes/population_grid.py` in the LakehouseBox repository; registry entry `pubdata/registry/population_grid.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `h3_index` | string | Uber H3 resolution-4 cell index (15-character hex string, e.g. 843e629ffffffff) |
| `population` | long | estimated resident population inside the H3 hexagon |
| `area_km2` | double | spheroidal cell area, km² |
| `density_per_km2` | double | population per km² (population / area_km2) |
| `latitude` | double | cell centroid latitude (WGS84 degrees) |
| `longitude` | double | cell centroid longitude (WGS84 degrees) |
| `bbox_xmin` | double | cell bounding box west longitude (WGS84) |
| `bbox_ymin` | double | cell bounding box south latitude (WGS84) |
| `bbox_xmax` | double | cell bounding box east longitude (WGS84) |
| `bbox_ymax` | double | cell bounding box north latitude (WGS84) |

**Join keys:** `h3_index`.

## For agents

Every cell is at H3 resolution 4 (~1,770 km² average cell area); unpopulated ocean and desert cells are omitted.
Coordinates (`latitude`, `longitude`) and bounding boxes (`bbox_xmin`..`bbox_ymax`) are in WGS84 degrees (converted
from the source GeoPackage's EPSG:3857 Web Mercator), so distance queries and joins with `places.admin_areas` work
directly in standard lon/lat without reprojection.

## Example SQL (DuckDB)

```sql
-- the 15 most populous H3 resolution-4 cells on Earth
SELECT h3_index, population, density_per_km2, latitude, longitude
FROM pubdata.population.grid_h3
ORDER BY population DESC LIMIT 15;
-- total population within 100 km of Madrid's centre (40.4168 N, 3.7038 W)
SELECT count(*) AS cells, sum(population) AS population_within_100km
FROM pubdata.population.grid_h3
WHERE latitude BETWEEN 39.4 AND 41.4 AND longitude BETWEEN -5.0 AND -2.4
  AND 6371 * acos(least(1.0, cos(radians(40.4168)) * cos(radians(latitude))
                  * cos(radians(longitude) - radians(-3.7038))
                  + sin(radians(40.4168)) * sin(radians(latitude)))) <= 100;
```

## 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.population.grid_h3 LIMIT 10;
```

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