Power plants

lhbox duckdb --catalog commons/pubdata

SELECT country_code, country_name,
       round(sum(capacity_mw) / 1000, 1) AS total_gw,
       round(sum(capacity_mw) FILTER (WHERE primary_fuel IN ('Solar', 'Wind', 'Hydro')) / 1000, 1) AS renewable_gw,
       round(sum(capacity_mw) FILTER (WHERE primary_fuel = 'Nuclear') / 1000, 1) AS nuclear_gw
FROM commons_pubdata.environment.power_plants
GROUP BY 1, 2 ORDER BY total_gw DESC LIMIT 20;

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

SELECT country_code, country_name,
       round(sum(capacity_mw) / 1000, 1) AS total_gw,
       round(sum(capacity_mw) FILTER (WHERE primary_fuel IN ('Solar', 'Wind', 'Hydro')) / 1000, 1) AS renewable_gw,
       round(sum(capacity_mw) FILTER (WHERE primary_fuel = 'Nuclear') / 1000, 1) AS nuclear_gw
FROM pubdata.environment.power_plants
GROUP BY 1, 2 ORDER BY total_gw DESC LIMIT 20;

# Power plants

*~35,000 utility-scale power plants worldwide with capacity, fuel type, generation and coordinates.*

The World Resources Institute's Global Power Plant Database (v1.3.0): ~34,900 thermal (coal, gas, oil, nuclear, biomass, waste, geothermal) and renewable (hydro, wind, solar) power plants across 167 countries. One row per plant with its WRI identifier, name, ISO country codes, installed capacity in megawatts, primary and secondary fuel, commissioning year, owner, WGS84 coordinates, reported 2019 generation and estimated generation.

## At a glance

| | |
|---|---|
| Table | `pubdata.environment.power_plants` |
| Category | Environment · Environment, Spatial, Country |
| 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, ~34,900 plants in 167 countries; release v1.3.0 |

## Origin

- **Source:** [World Resources Institute (WRI), Global Power Plant Database v1.3.0](https://datasets.wri.org/dataset/globalpowerplantdatabase)
- **Licence:** CC BY 4.0
- **Attribution:** Global Energy Observatory, Google, KTH Royal Institute of Technology in Stockholm, Enipedia, World Resources Institute. Global Power Plant Database v1.3.0 (CC BY 4.0).
- **Recipe:** `pubdata/recipes/power_plants.py` in the LakehouseBox repository; registry entry `pubdata/registry/power_plants.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `plant_id` | string | WRI Global Power Plant Database identifier (gppd_idnr) |
| `name` | string | power plant name |
| `country_code` | string | ISO 3166-1 alpha-2 country code (joins reference.countries) |
| `iso3` | string | ISO 3166-1 alpha-3 country code |
| `country_name` | string | country name |
| `capacity_mw` | double | installed electrical generating capacity, megawatts (MW) |
| `primary_fuel` | string | Solar, Wind, Hydro, Gas, Coal, Oil, Nuclear, Biomass, Waste, Geothermal, Cogeneration, Storage, etc. |
| `other_fuel` | string | secondary fuel where reported |
| `commissioning_year` | int | year the plant entered service where known |
| `owner` | string | plant owner or majority shareholder |
| `latitude` | double | WGS84 latitude in decimal degrees |
| `longitude` | double | WGS84 longitude in decimal degrees |
| `generation_gwh_2019` | double | reported electricity generation in 2019, gigawatt-hours (GWh) |
| `estimated_generation_gwh_2017` | double | WRI model-estimated annual generation (2017), GWh |
| `reporting_source` | string | national or institutional source of the plant record |

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

## For agents

`capacity_mw` and `primary_fuel` are populated for every plant; `commissioning_year` and `generation_gwh_2019` are
often null outside the US and Europe, so use `capacity_mw` when comparing across all 167 countries.

## Example SQL (DuckDB)

```sql
-- installed capacity (GW) by country and primary fuel for top countries
SELECT country_code, country_name,
       round(sum(capacity_mw) / 1000, 1) AS total_gw,
       round(sum(capacity_mw) FILTER (WHERE primary_fuel IN ('Solar', 'Wind', 'Hydro')) / 1000, 1) AS renewable_gw,
       round(sum(capacity_mw) FILTER (WHERE primary_fuel = 'Nuclear') / 1000, 1) AS nuclear_gw
FROM pubdata.environment.power_plants
GROUP BY 1, 2 ORDER BY total_gw DESC LIMIT 20;
-- power plants within 50 km of a major earthquake (M >= 6.0) in the past year
SELECT e.event_time, e.magnitude, e.place, p.name AS plant_name, p.primary_fuel, p.capacity_mw
FROM pubdata.environment.earthquakes AS e
JOIN pubdata.environment.power_plants AS p
  ON p.latitude BETWEEN e.latitude - 0.5 AND e.latitude + 0.5
 AND p.longitude BETWEEN e.longitude - 0.7 AND e.longitude + 0.7
WHERE e.magnitude >= 6.0 AND e.event_time >= now() - INTERVAL 1 YEAR
ORDER BY e.magnitude DESC, p.capacity_mw DESC;
```

## 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.environment.power_plants LIMIT 10;
```

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