⚡ `energy`¶

Comprehensive U.S. energy data from the Energy Information Administration (EIA) and Mine Safety and Health Administration (MSHA). Covers electricity generation and retail prices, utility operations, power plant inventory, generator capacity changes, fossil fuel production, state energy consumption, natural gas storage, petroleum stocks, crude oil imports, refinery operations, and coal mine production. All state-level tables join with geo.state_ref via state_abbr. EIA API tables use OFFSET pagination against v2 endpoints. Bulk tables (EIA-860, EIA-861, EIA-860M, EIA-814) use TEXT format with transformers that download, extract, and parse XLSX/ZIP archives.

31 datasets · 339 columns

eia_electricity_generation · table¶

EIA API v2 electric power operational data. Monthly electricity generation (thousand MWh), total fuel consumption (MMBtu), fuel consumption for electricity generation (MMBtu), cost per MMBtu, sulfur content, and ash content. Broken down by state, energy source, and sector. One row per state + energy source + sector + year-month. Cross-references geo.state_ref via state_abbr. THIS TABLE CANNOT BE SUMMED NAIVELY — it interleaves rollup rows with the leaf rows they already contain, on all three axes at once, and a SUM that ignores any of them multiplies the answer: (1) energy_source_code mixes leaves with EIA's own rollups — 'ALL', 'FOS', 'REN' and 'NUC' each already total other rows, and even some leaf-looking codes are nested (COL = BIT + SUB + LIG). Use fuel_is_rollup to pick one level. (2) sector_code likewise carries '99' = "All Sectors" alongside the individual sectors it sums; see sector_is_rollup. (3) geo_level mixes 'state', 'division' and 'national' rows — see that column. For a state's annual generation mix, filter geo_level='state' AND sector_code='99', take the ALL/FOS/REN/NUC rows, and SUM across the 12 months — there is no annual sentinel row, so a per-year figure must be summed from months. Selecting a single month's value per category instead mixes different peak months and produces parts that do not add up to the total. The state_energy_mix view does exactly this and is the intended entry point for mix questions.

Column	Type	Null	Description
`generation_year`	integer	no	Calendar year of the generation period
`generation_month`	integer	no	Calendar month (1-12) of the generation period
`state_abbr`	string	yes	2-letter state abbreviation (e.g., 'CA')
`state_description`	string	yes	Full state name
`geo_level`	string	yes	Geographic granularity of this row's location: 'state' (real state_abbr), 'division' (Census division rollup, e.g. code '90' for Pacific), or 'national' (US total). Distinguishes the 3 location granularities that all share the same underlying EIA location field, to prevent overcounting on a naive SUM across state + division + national rows.
`energy_source_code`	string	yes	EIA energy source code (e.g., 'COL', 'NG', 'NUC', 'SUN')
`energy_source`	string	yes	Full energy source description
`fuel_is_rollup`	boolean	yes	True when energy_source_code is a rollup/aggregate fuel code (e.g. 'COW' aggregates 'COL'/'LIG'/'SUB', 'TSN' aggregates 'SPV'/'DPV') rather than a true leaf-level fuel, so it should be excluded from a naive SUM alongside its constituent codes.
`sector_code`	string	yes	EIA sector code (e.g., '1', '2', '98', '99')
`sector`	string	yes	Sector description (e.g., 'Electric Utility', 'IPP Non-CHP')
`sector_is_rollup`	boolean	yes	True when sector_code is a rollup/aggregate sector code (90, 94-99) rather than a true leaf-level sector (1-8), so it should be excluded from a naive SUM alongside its constituent sectors.
`generation_thousand_mwh`	double	yes	Net electricity generation in thousand MWh
`fuel_consumed_total_mmbtu`	double	yes	Total fuel consumed in MMBtu (all purposes)
`fuel_consumed_for_eg_mmbtu`	double	yes	Fuel consumed for electricity generation in MMBtu
`fuel_cost_per_mmbtu`	double	yes	Fuel cost per MMBtu in dollars
`sulfur_content`	double	yes	Sulfur content percentage of fuel
`ash_content`	double	yes	Ash content percentage of fuel

eia_electricity_prices · table¶

EIA API v2 retail electricity sales data, fetched at annual frequency (the source URL hardcodes frequency=annual, so price_month exists in the schema but is always null here). Average price (cents/kWh), revenue (million dollars), sales (million kWh), and customer counts by state and sector. One row per state + sector + year. Cross-references geo.state_ref via state_abbr.

Column	Type	Null	Description
`price_year`	integer	no	Calendar year
`price_month`	integer	yes	Calendar month (1-12), null for annual aggregates
`state_abbr`	string	yes	2-letter state abbreviation (e.g., 'CA')
`state_description`	string	yes	Full state name
`sector_code`	string	yes	EIA customer sector code (e.g., 'RES', 'COM', 'IND', 'TRA', 'ALL')
`sector`	string	yes	Sector description (e.g., 'Residential', 'Commercial', 'Industrial')
`avg_price_cents_kwh`	double	yes	Average retail electricity price in cents per kWh
`revenue_million_dollars`	double	yes	Total revenue in million dollars
`sales_million_kwh`	double	yes	Total electricity sales in million kWh
`customers`	integer	yes	Number of customer accounts

eia_utility_annual · table¶

EIA Form 861 annual electric utility survey. Covers all electric utilities, retail power marketers, and energy service providers. Fields include entity type, NERC region, service type, customer counts and sales by sector (residential, commercial, industrial, transportation), revenue, peak demand, and net generation. One row per utility + year. The transformer downloads and parses the annual ZIP archive of XLSX files from EIA. Cross-references geo.state_ref via state_abbr. To reach a utility's cross-schema identity FROM another schema rather than by name, join ref.canonical_org_entity on eia_utility_id = utility_id — that table carries one foreign key per source, so the utility's LEI/SEC CIK and its utility_id sit in the same row and the join is exact. Matching utility_name as text finds the wrong entities and misses utilities filing under a differently-worded name.

Column	Type	Null	Description
`utility_id`	integer	no	EIA utility identifier
`report_year`	integer	no	Report year
`utility_name`	string	yes	Utility company name
`entity_type`	string	yes	Entity type code (e.g., 'C', 'I', 'M', 'S', 'F', 'G', 'P', 'Q', 'W')
`state_abbr`	string	yes	2-letter state abbreviation (e.g., 'CA')
`ba_code`	string	yes	Balancing authority code
`nerc_region`	string	yes	NERC reliability region code
`service_type`	string	yes	Service type (Bundled, Energy, Delivery)
`data_type`	string	yes	Data type indicator
`activity_generation`	boolean	yes	Whether utility engages in generation
`activity_transmission`	boolean	yes	Whether utility engages in transmission
`activity_distribution`	boolean	yes	Whether utility engages in distribution
`customers_residential`	integer	yes	Number of residential customer accounts
`customers_commercial`	integer	yes	Number of commercial customer accounts
`customers_industrial`	integer	yes	Number of industrial customer accounts
`customers_transportation`	integer	yes	Number of transportation customer accounts
`customers_total`	integer	yes	Total number of customer accounts
`sales_residential_mwh`	double	yes	Electricity sales to residential customers in MWh
`sales_commercial_mwh`	double	yes	Electricity sales to commercial customers in MWh
`sales_industrial_mwh`	double	yes	Electricity sales to industrial customers in MWh
`sales_transportation_mwh`	double	yes	Electricity sales to transportation customers in MWh
`sales_total_mwh`	double	yes	Total electricity sales in MWh
`revenue_residential_thousand`	double	yes	Revenue from residential customers in thousands of dollars
`revenue_commercial_thousand`	double	yes	Revenue from commercial customers in thousands of dollars
`revenue_industrial_thousand`	double	yes	Revenue from industrial customers in thousands of dollars
`revenue_total_thousand`	double	yes	Total revenue in thousands of dollars
`summer_peak_demand_mw`	double	yes	Summer peak demand in MW
`winter_peak_demand_mw`	double	yes	Winter peak demand in MW
`net_generation_mwh`	double	yes	Net electricity generation in MWh

eia_service_territory · table¶

EIA-861 utility-to-county service-territory crosswalk. One row per utility + state + county the utility reported serving in that year (a multi-state or multi-county utility has one row per county). Sourced from the "Counties_States" sheet of the Service_Territory file in the same annual EIA-861 ZIP archive that eia_utility_annual downloads (Counties_States covers the 50 states + DC; the sibling Counties_Territories sheet is out of scope, matching this schema's existing 50-states+DC convention). Does not carry a per-county customer count or sales split — EIA-861 does not publish one — so this table is a service-territory membership list, not a weighted allocation; see eia_electricity_demand_by_county for the county-grain demand apportionment built from it. Cross-references geo.state_ref via state_abbr; joins eia_utility_annual via (utility_id, report_year).

Column	Type	Null	Description
`utility_id`	integer	no	EIA utility identifier (links to eia_utility_annual)
`report_year`	integer	no	Report year
`utility_name`	string	yes	Utility company name
`short_form_filer`	boolean	yes	Whether the utility filed the EIA-861 short form (less-detailed survey)
`state_abbr`	string	yes	2-letter state abbreviation (e.g., 'CA')
`county_name`	string	no	County name as reported by EIA (no 'County'/'Parish'/'Borough' suffix, e.g. 'Vermilion' not 'Vermilion Parish'). Not a FIPS code — join to geo.counties by name prefix match if a county_fips is needed (see eia_electricity_demand_by_county for a worked example).

eia_utility_reliability · table¶

Power-outage frequency and duration by electric utility: EIA-861 reliability indices SAIDI (average minutes of interruption per customer per year), SAIFI (average interruptions per customer per year) and CAIDI (average minutes to restore per interruption, = SAIDI/SAIFI). One row per utility + state + year, annual, data years 2013 onward (published with a ~3-year lag; the newest year is current-3). Sourced from the "Reliability_States" sheet of the Reliability workbook in the same annual EIA-861 ZIP archive that eia_utility_annual downloads (the Reliability_Territories sheet, covering only Puerto Rico/Guam/American Samoa, is out of scope). Each metric is reported under up to three bases: with Major Event Days (storms and other extreme events included), without Major Event Days (normal-conditions reliability), and — IEEE basis only — with loss-of-supply outages removed. Utilities report under either the IEEE 1366 standard (ieee_* columns) or their own non-IEEE standard (other_* columns); the two bases are not comparable, so they are kept in separate column groups and a utility usually fills only one group (the other is null). Reporting is voluntary for many small utilities, so coverage is partial and varies by year. Use the *_without_med columns to compare underlying grid reliability across regions and the *_with_med columns to see storm and extreme-event exposure. There is no statewide roll-up in this table; average across utilities weighting by ieee_customers/other_customers. Cross-references geo.state_ref via state_abbr; joins eia_utility_annual via (utility_id, report_year).

Column	Type	Null	Description
`utility_id`	integer	no	EIA utility identifier (links to eia_utility_annual)
`report_year`	integer	no	Report year
`utility_name`	string	yes	Utility company name
`state_abbr`	string	no	2-letter state abbreviation (e.g., 'CA')
`ownership_type`	string	yes	Utility ownership class as reported by EIA (e.g. 'Investor Owned', 'Cooperative', 'Municipal')
`ieee_saidi_with_med`	double	yes	SAIDI, minutes of interruption per customer per year, measured under IEEE 1366 standard, all events including Major Event Days (MED)
`ieee_saifi_with_med`	double	yes	SAIFI, interruptions per customer per year, measured under IEEE 1366 standard, all events including Major Event Days (MED)
`ieee_caidi_with_med`	double	yes	CAIDI, average minutes to restore per interruption (SAIDI/SAIFI), measured under IEEE 1366 standard, all events including Major Event Days (MED)
`ieee_saidi_without_med`	double	yes	SAIDI, minutes of interruption per customer per year, measured under IEEE 1366 standard, excluding Major Event Days
`ieee_saifi_without_med`	double	yes	SAIFI, interruptions per customer per year, measured under IEEE 1366 standard, excluding Major Event Days
`ieee_caidi_without_med`	double	yes	CAIDI, average minutes to restore per interruption (SAIDI/SAIFI), measured under IEEE 1366 standard, excluding Major Event Days
`ieee_saidi_loss_of_supply_removed`	double	yes	SAIDI, minutes of interruption per customer per year, measured under IEEE 1366 standard, all events including MED, minus loss-of-supply outages
`ieee_saifi_loss_of_supply_removed`	double	yes	SAIFI, interruptions per customer per year, measured under IEEE 1366 standard, all events including MED, minus loss-of-supply outages
`ieee_caidi_loss_of_supply_removed`	double	yes	CAIDI, average minutes to restore per interruption (SAIDI/SAIFI), measured under IEEE 1366 standard, all events including MED, minus loss-of-supply outages
`ieee_customers`	double	yes	Number of customers covered by the IEEE-basis figures
`other_saidi_with_med`	double	yes	SAIDI, minutes of interruption per customer per year, measured under a non-IEEE (utility-defined) standard, all events including Major Event Days (MED)
`other_saifi_with_med`	double	yes	SAIFI, interruptions per customer per year, measured under a non-IEEE (utility-defined) standard, all events including Major Event Days (MED)
`other_caidi_with_med`	double	yes	CAIDI, average minutes to restore per interruption (SAIDI/SAIFI), measured under a non-IEEE (utility-defined) standard, all events including Major Event Days (MED)
`other_saidi_without_med`	double	yes	SAIDI, minutes of interruption per customer per year, measured under a non-IEEE (utility-defined) standard, excluding Major Event Days
`other_saifi_without_med`	double	yes	SAIFI, interruptions per customer per year, measured under a non-IEEE (utility-defined) standard, excluding Major Event Days
`other_caidi_without_med`	double	yes	CAIDI, average minutes to restore per interruption (SAIDI/SAIFI), measured under a non-IEEE (utility-defined) standard, excluding Major Event Days
`other_customers`	double	yes	Number of customers covered by the non-IEEE-basis figures

eia_power_plants · table¶

EIA Form 860 annual electric generator inventory. One row per generator (plant + generator ID + year), sourced from BOTH the archive's "Operable" sheet and its "Retired and Canceled" sheet (2011 onward; earlier archives carry only an operable-equivalent). A generator therefore has a row in a given report_year's archive whether it was running that year or had already stopped — filter operating_status IN ('RE','CN') to isolate the latter, and read retirement_year for when it actually happened, as distinct from planned_retirement_year (a forecast made while still operating). Includes plant and utility identifiers, geographic location (state, county, city, lat/lon), NERC region, balancing authority, technology, prime mover, energy sources, capacity (nameplate, summer, winter, minimum load), ownership type, and energy storage flag. Transformer downloads and parses the annual ZIP archive from EIA. Cross-references geo.state_ref via state_abbr; joins eia_utility_annual via utility_id.

Column	Type	Null	Description
`plant_id`	integer	no	EIA plant identifier
`generator_id`	string	no	Generator identifier within the plant
`report_year`	integer	no	Report year
`plant_name`	string	yes	Plant name
`utility_id`	integer	yes	EIA utility identifier (links to eia_utility_annual)
`utility_name`	string	yes	Utility company name
`state_abbr`	string	yes	2-letter state abbreviation (e.g., 'CA')
`county_name`	string	yes	County name
`county_fips`	string	yes	5-digit county FIPS code
`city`	string	yes	City or town name
`latitude`	double	yes	Plant latitude
`longitude`	double	yes	Plant longitude
`nerc_region`	string	yes	NERC reliability region
`balancing_authority_code`	string	yes	Balancing authority code
`balancing_authority_name`	string	yes	Balancing authority name
`primary_purpose_naics`	integer	yes	Primary purpose NAICS code
`regulatory_status`	string	yes	Regulatory status (e.g., Regulated, Nonutility)
`sector_code`	integer	yes	EIA sector code
`sector`	string	yes	Sector description
`technology`	string	yes	Generation technology (e.g., 'Conventional Steam Coal', 'Onshore Wind Turbine')
`prime_mover`	string	yes	Prime mover code (e.g., 'ST', 'GT', 'IC', 'WT', 'PV')
`energy_source_1`	string	yes	Primary energy source code
`energy_source_2`	string	yes	Secondary energy source code
`energy_source_3`	string	yes	Tertiary energy source code
`nameplate_capacity_mw`	double	yes	Nameplate (installed) capacity in MW
`net_summer_capacity_mw`	double	yes	Net summer capacity in MW
`net_winter_capacity_mw`	double	yes	Net winter capacity in MW
`minimum_load_mw`	double	yes	Minimum load in MW
`ownership_code`	string	yes	Ownership type code
`operating_status`	string	yes	Operating status code — OP (operating), SB (standby/backup), OA (out of service, expected back), OS (out of service, not expected back), P/L/T/U/V (various under-construction / permitting stages), RE (retired) or CN (cancelled or postponed). RE and CN only appear on a row sourced from the "Retired and Canceled" sheet (see retirement_year) — a generator that stopped operating simply has no row on the "Operable" sheet in that year's archive rather than switching this code, so its last "Operable" row's status stays whatever it was the year before it disappeared.
`operating_month`	integer	yes	Month generator began commercial operation
`operating_year`	integer	yes	Year generator began commercial operation
`planned_retirement_month`	integer	yes	Planned retirement month, as reported by a still-operating generator on the "Operable" sheet — a forecast, not an actual retirement. Once a generator has actually retired this is no longer populated for it; see retirement_month.
`planned_retirement_year`	integer	yes	Planned retirement year — see planned_retirement_month. Not populated once a generator has actually retired; see retirement_year.
`retirement_month`	integer	yes	Actual retirement month, from EIA-860's "Retired and Canceled" sheet — only populated on a row whose operating_status is RE or CN. NULL on every "Operable" sheet row, including one whose planned_retirement_year suggests retirement is coming: this field records that a generator DID retire, not that it is expected to.
`retirement_year`	integer	yes	Actual retirement year — see retirement_month. To find when a generator retired, filter operating_status IN ('RE','CN') and read this column, not planned_retirement_year.
`energy_storage_flag`	boolean	yes	Whether generator includes energy storage capability

gas_pipelines · table¶

U.S. natural gas transmission pipeline network — one row per pipeline segment (~32,900 segments nationwide), sourced from EIA and mirrored as a public ArcGIS FeatureServer under Esri's Federal_User_Community org (the layer's own listing: "checked monthly for updates"; EIA's own Energy Atlas hosting for this layer is not currently available — verified live, not the PA Department of Health's Pennsylvania-only re-clip of the same underlying EIA data found during initial research, which this table does NOT use). Confirmed nationwide via a spot-check of returned segments across Illinois, Minnesota, Arizona, Utah, Nevada, and Colorado — not just the Marcellus-region subset the PA mirror is limited to. geometry_wkt carries each segment's own WGS84 line geometry (LINESTRING or, for a small number of segments, MULTILINESTRING); length_m is EIA's own projected segment length in meters, not a WGS84-degree approximation. No county field is present at the source — join geometry_wkt against geo.counties (DuckDB ST_Intersects) for county-grain rollups, following the same spatial-join pattern as lands.county_federal_fee_land_share; see energy.county_gas_pipeline_presence for a pre-built rollup. pipeline_type is Interstate or Intrastate (EIA/PHMSA classification); status is "Operating" for every row in the live source despite the field's stated Active/Inactive/Abandoned vocabulary — a real constant, not a transform bug. A small fraction of segments (~0.2%, confirmed live) carry no geometry at all; geometry_wkt is null for those rather than the row being dropped, so segment_id stays a complete FID census.

Column	Type	Null	Description
`segment_id`	integer	no	EIA/Esri pipeline segment feature ID (FID)
`pipeline_type`	string	yes	Interstate or Intrastate (EIA/PHMSA classification)
`operator`	string	yes	Pipeline operating company
`status`	string	yes	Operational status — 'Operating' for every row in the live source
`length_m`	double	yes	Segment length in meters (EIA's own projected measurement, not WGS84-degree)
`geometry_wkt`	string	yes	Segment line geometry as WGS84 WKT (LINESTRING/MULTILINESTRING); null for the small fraction of source rows with no geometry

interconnection_queue · table¶

Grid interconnection queue: one row per generation or storage project that has requested connection to the U.S. transmission grid, from Lawrence Berkeley National Laboratory's "Queued Up" dataset (Rand et al., 2026 edition, requests from 2000 through the end of 2025, CC BY 4.0), which LBNL compiles from the queue files of 7 ISO/RTOs and 50 non-ISO balancing areas (~98% of installed U.S. generating capacity; Alaska and Hawaii not covered; transmission-connected generation and storage only, no load interconnection or distribution-connected projects). ~38,200 rows: ~8,500 active, ~4,800 operational, ~24,200 withdrawn, ~670 suspended (q_status). Answers "how much new generation or storage is waiting to connect in this county, and of what type" — the forward-looking supply-side half of siting questions; county_interconnection_queue is the per-county rollup. county_fips is the project's FIRST listed county only (the source states multi-county requests keep just the first); the ~1,770 rows (~5%) whose source FIPS is blank have a null county_fips, and 22 rows sit outside the U.S. (state MX/ON/MB). mw_1/mw_2/mw_3 are the rated MW of resource type_1/type_2/type_3; a hybrid such as Solar+Battery has both mw_1 and mw_2, so total project MW is their sum, and LBNL's own published totals additionally include imputed storage MW that this file omits (active requests sum to ~1,865 GW here against ~2,061 GW in the report; the 8,513 active rows here are ~3% more than the report's 8,244, whose basis the file does not identify). 16 rows carry a negative mw_1 (-4 to -75 MW) in LBNL's own workbook and are kept as published, so SUM(mw) over a set that includes them is very slightly understated. q_id is unique only together with entity, and not even then for 135 pairs, so source_row (the row's position in the source sheet) is the only row identifier; a few q_id values were converted to dates or numbers by Excel in the source. Dates: q_date request entered queue, prop_date proposed online, on_date became operational, wd_date withdrawn, ia_date interconnection agreement signed. as_of_year is the edition's cut-off year. county holds the name as the queue wrote it and occasionally a town rather than the county (40 of 2,634 FIPS have more than one spelling); county_fips is authoritative. Snapshot table: the source is replaced wholesale each edition, not appended. SOURCE DURABILITY: emp.lbl.gov and eta.lbl.gov sit behind a Cloudflare managed challenge that no non-browser client passes (verified 2026-09-25: HTTP 403, cf-mitigated: challenge), so the workbook is read from the copy redistributed under CC BY 4.0 in the Zenodo deposit 10.5281/zenodo.22287832 (files are immutable per record). That deposit is a third party's replication package, pinned to the 2026 edition; a new Queued Up edition needs source.url pointed at a newer deposit. Joins to geo.counties via county_fips and to geo.state_ref via state (= state_abbr).

Column	Type	Null	Description
`source_row`	integer	no	Row number of the project in the source workbook's queue sheet (row identifier; q_id is not unique)
`q_id`	string	yes	Queue position/ID assigned by the transmission provider; unique only with entity, and 'not assigned' on some rows
`entity`	string	yes	Transmission provider that runs the queue (an ISO/RTO or a utility)
`q_status`	string	yes	Queue status: active, operational, withdrawn, suspended (or unknown)
`q_date`	date	yes	Date the request entered the queue
`prop_date`	date	yes	Proposed online date from the application (revised during the process)
`on_date`	date	yes	Date the project became operational, where it did
`wd_date`	date	yes	Date the request was withdrawn from the queue, where it was
`ia_date`	date	yes	Date the interconnection agreement was signed, where one was
`ia_phase_raw`	string	yes	Study phase exactly as the transmission provider reports it (not standardized)
`ia_phase_clean`	string	yes	Standardized study phase (IA Executed, System Impact Study, Feasibility Study, Facility Study, Cluster Study, IA Pending, Construction, Not Started, Suspended, Withdrawn, In Progress); 'IA Executed' is imputed for operational projects
`county`	string	yes	County name of the project's first listed county
`state`	string	yes	2-letter state abbreviation (FK to geo.state_ref.state_abbr); a few rows are MX/ON/MB, outside the U.S.
`county_fips`	string	yes	5-digit county FIPS of the first listed county (FK to geo.counties.county_fips); null where the queue lists no county or the project is outside the U.S.
`poi_name`	string	yes	Point of interconnection name — a substation or transmission-line tap
`region`	string	yes	Standardized region: CAISO, ERCOT, ISO-NE, MISO, NYISO, PJM, SPP, or West / Southeast (non-ISO)
`project_name`	string	yes	Project name (absent for most requests)
`utility`	string	yes	Utility name (same as entity in non-ISO/RTO balancing areas)
`developer`	string	yes	Project developer, not standardized (absent for most requests)
`cluster`	string	yes	Queue cluster the request belongs to, where the queue uses clusters
`service`	string	yes	Interconnection service type: NRIS, ERIS, NRIS/ERIS, or Other
`project_type`	string	yes	Request type: Generation, Surplus, Upgrade, or Replacement
`type_1`	string	yes	Primary resource type (Solar, Wind, Battery, Gas, Hydro, Coal, Offshore Wind, Nuclear, Geothermal, Diesel, Oil, Hydrogen, Other Storage, Other)
`type_2`	string	yes	Second resource type; populated only for hybrid/co-located projects
`type_3`	string	yes	Third resource type; populated only for hybrid/co-located projects
`type_clean`	string	yes	Standardized combined resource type, e.g. Solar, Battery, Solar+Battery
`mw_1`	double	yes	Rated capacity in MW of type_1
`mw_2`	double	yes	Rated capacity in MW of type_2 (hybrid projects only; LBNL's imputed storage MW omitted)
`mw_3`	double	yes	Rated capacity in MW of type_3 (hybrid projects only)
`q_year`	integer	yes	Year the request entered the queue (derived from q_date; null where q_date is missing)
`prop_year`	integer	yes	Proposed online year (derived from prop_date)
`as_of_year`	integer	no	Cut-off year of the Queued Up edition the row came from

eia_capacity_changes · table¶

EIA Form 860M generator inventory, one annual snapshot per snapshot_year. Captures planned additions and planned retirements with capacity (nameplate, summer, winter, energy storage). Transformer parses the XLSX directly. Cross-references geo.state_ref via state_abbr; joins eia_power_plants via plant_id. GRAIN — READ BEFORE AGGREGATING: this is a CUMULATIVE SNAPSHOT, not a change log. One row per generator per snapshot_year, so a single addition or retirement event REAPPEARS in every later snapshot it survives into — measured live, additions with change_year 2020-2024 appear in 1 to 6 distinct snapshot_years each. The row count therefore grows every year by construction (25,986 in 2016 to 38,359 in 2025) without any new events. SUMming nameplate_capacity_mw across snapshot_year multiplies every event by the number of snapshots it appears in: for change_type='Addition' and change_year 2020-2024 that is 746,721 MW across 18,688 rows, against 5,236 real generator-years — roughly 3.6x EIA's own published additions of ~40 GW/yr. To total real capacity change, first reduce to one row per (plant_id, generator_id, change_year) — e.g. pick the newest snapshot_year per generator with QUALIFY ROW_NUMBER() OVER (PARTITION BY plant_id, generator_id, change_year ORDER BY snapshot_year DESC) = 1 — and only then aggregate. snapshot_month is NULL on every row: only the December file is ingested, so the column carries no information and must not be used to disambiguate snapshots.

Column	Type	Null	Description
`plant_id`	integer	no	EIA plant identifier
`generator_id`	string	no	Generator identifier within the plant
`snapshot_year`	integer	no	Year of the December EIA-860M snapshot file this row came from. Every event reappears in each later snapshot it survives into, so pin one snapshot_year or reduce to the newest snapshot per (plant_id, generator_id, change_year) before summing capacity.
`snapshot_month`	integer	no	Always NULL — only the December file is ingested and no month dimension is set.
`change_type`	string	no	Source sheet the row came from: 'Addition' (Operating sheet), 'Retirement' (Retired), 'Planned Addition' (Planned) or 'Cancellation' (Canceled or Postponed).
`change_year`	integer	yes	Year the change took effect (Addition, Retirement) or is planned to (Planned Addition). NULL on 'Cancellation' rows: the source's Canceled or Postponed sheet has no date column.
`change_month`	integer	yes	Month the change took effect or is planned to; NULL where change_year is NULL
`plant_name`	string	yes	Plant name
`entity_id`	integer	yes	EIA entity (utility/owner) identifier
`entity_name`	string	yes	Entity name
`state_abbr`	string	yes	2-letter state abbreviation (e.g., 'CA')
`county_name`	string	yes	County name
`balancing_authority_code`	string	yes	Balancing authority code
`sector`	string	yes	Sector description
`technology`	string	yes	Generation technology
`energy_source_code`	string	yes	Primary energy source code
`prime_mover_code`	string	yes	Prime mover code
`nameplate_capacity_mw`	double	yes	Nameplate capacity in MW
`net_summer_capacity_mw`	double	yes	Net summer capacity in MW
`net_winter_capacity_mw`	double	yes	Net winter capacity in MW
`nameplate_energy_capacity_mwh`	double	yes	Nameplate energy capacity in MWh (for storage units)

pjm_capacity_auction_prices · table¶

PJM Base Residual Auction (BRA) capacity clearing prices by LDA (Locational Deliverability Area / pricing zone) and delivery year, sourced from PJM's public per-delivery-year auction-results workbook (pjm.com/markets-and-operations/rpm.aspx — no PJM Data Miner API key required). One row per LDA per delivery year: 'RTO' is the system-wide unconstrained zone; other LDAs (e.g. 'EMAAC', 'COMED', 'DPLSOUTH') layer a locational_price_adder on top when that zone is import-constrained. This is the actual capacity-procurement price mechanism behind PJM-region wholesale capacity costs — e.g. the RTO resource_clearing_price rose from $28.92/MW-day (2024/2025) to $269.92 (2025/2026) to $329.17 (2026/2027), a documented driver of PJM-region retail cost pressure. Currently covers only the Base Residual Auction (see auction_type); PJM's Incremental Auctions (1st/2nd/3rd IA) and other ISOs/RTOs (MISO, ERCOT, CAISO, NYISO, SPP) publish similar structured auction results and are a natural follow-on, not yet ingested (kenstott/govdata-ops#330).

Column	Type	Null	Description
`delivery_year`	integer	no	Delivery year — the start of the one-year capacity delivery period (e.g. 2024 for the "2024/2025" delivery year).
`lda`	string	no	Locational Deliverability Area / pricing zone code (e.g. 'RTO' for the system-wide unconstrained zone, 'EMAAC', 'COMED', 'DPLSOUTH'). The set of LDAs reported varies by delivery year as PJM adds, splits, or retires constrained areas — do not assume a fixed zone list across years.
`system_marginal_price`	double	yes	System Marginal Price — the clearing price for Capacity Performance Resources in the unconstrained area of RTO, in $/MW-day. Identical across every LDA row for a given delivery_year; the zone-specific price is resource_clearing_price.
`locational_price_adder`	double	yes	Locational Price Adder for this LDA relative to its immediate higher-level LDA, in $/MW-day. Zero for an unconstrained zone.
`resource_clearing_price`	double	yes	Resource Clearing Price = system_marginal_price + locational_price_adder, in $/MW-day — the actual capacity price paid to cleared resources in this LDA.
`auction_type`	string	no	Auction type; currently always 'Base Residual Auction' (BRA) — PJM's primary, delivery-year-ahead capacity auction. Constant until Incremental Auctions are added (see table comment), so excluded from the all-same-value DQ check.

eia_crude_oil_production · table¶

EIA API v2 petroleum crude oil production by state and month. Source is the single endpoint petroleum/crd/crpdn; fuel_type values are 'Crude Oil' or 'ANS Crude Oil' (Alaska North Slope). For natural gas production data, see eia_natural_gas_production table (separate endpoint, different units). One row per EIA area + process + year-month. Cross-references geo.state_ref via state_abbr.

Column	Type	Null	Description
`production_year`	integer	no	Calendar year of production
`production_month`	integer	no	Calendar month (1-12) of production
`eia_area_code`	string	yes	EIA area/region code (may be state abbreviation or PADD code)
`state_abbr`	string	yes	2-letter state abbreviation (e.g., 'CA')
`fuel_type`	string	no	Fuel type — 'Crude Oil' or 'ANS Crude Oil' (Alaska North Slope). For natural gas, see eia_natural_gas_production.
`process_code`	string	yes	EIA process code identifying the production activity
`process_name`	string	yes	Process description
`production_volume`	double	yes	Production volume in units specified by production_unit
`production_unit`	string	yes	Unit of measure — one of exactly two short codes: MBBL (thousand barrels) or MBBL/D (thousand barrels per day). The spelled-out forms in series_description ("Thousand Barrels", "Thousand Barrels per Day") never appear here and are not valid filter values (D-270) — filtering on the long form silently returns a near-empty result instead of an error.
`series_id`	string	yes	EIA API v2 series identifier. Composite key built by EIA from the endpoint's facets: area plus process (e.g. the state's crude oil field production series). The human-readable label is in series_description on the same row, and the decoded parts are in eia_area_code, state_abbr, and process_code. This table is long-format: one row per (series_id, year, month). Enumerate with SELECT DISTINCT series_id, series_description.
`series_description`	string	yes	Full series description

eia_natural_gas_production · table¶

EIA API v2 natural gas production by state and month. Source is the separate endpoint natural-gas/prod/sum. Units are in different scale than crude oil (typically MCF or MMCF, million cubic feet). For crude oil data, see eia_crude_oil_production. One row per EIA area + process + year-month. Cross-references geo.state_ref via state_abbr.

Column	Type	Null	Description
`production_year`	integer	no	Calendar year of production
`production_month`	integer	no	Calendar month (1-12) of production
`eia_area_code`	string	yes	EIA area/region code (state or region)
`state_abbr`	string	yes	2-letter state abbreviation (e.g., 'CA')
`process_code`	string	yes	EIA process code identifying the production activity
`process_name`	string	yes	Process description
`production_volume`	double	yes	Production volume in cubic feet (MCF or MMCF)
`production_unit`	string	yes	Unit of measure (e.g., MCF = million cubic feet, MMCF = million MCF)
`series_id`	string	yes	EIA API v2 series identifier for this production series
`series_description`	string	yes	Full series description

eia_state_energy_consumption · table¶

EIA API v2 State Energy Data System (SEDS). Annual energy consumption, expenditure, and price by state, sector, fuel type, and MSN (energy series code). The MSN code encodes fuel, sector, and metric in a compact 5-character string. The transformer decodes MSN values and maps them to human-readable sector, fuel_type, units, consumption_bbtu, expenditure_million, and price_per_mmbtu columns where applicable. One row per state + MSN + year. Cross-references geo.state_ref via state_abbr.

Column	Type	Null	Description
`year`	integer	no	Calendar year (partition column)
`consumption_year`	integer	no	Calendar year
`state_abbr`	string	yes	2-letter state abbreviation (e.g., 'CA')
`state_name`	string	yes	Full state name
`msn`	string	no	EIA Mnemonic Series Name (5-char code, e.g., 'TETCB', 'NGRCB')
`sector`	string	yes	Sector decoded from MSN (e.g., 'Residential', 'Commercial', 'Transportation', 'Total')
`fuel_type`	string	yes	Fuel type decoded from MSN (e.g., 'Natural Gas', 'Coal', 'Petroleum')
`value`	double	yes	Raw numeric value from the API response
`units`	string	yes	Units for the value field
`consumption_bbtu`	double	yes	Energy consumption in billion BTU (null if MSN is not a consumption series)
`expenditure_million`	double	yes	Expenditure in million dollars (null if MSN is not an expenditure series)
`price_per_mmbtu`	double	yes	Price per million BTU (null if MSN is not a price series)
`series_description`	string	yes	Full human-readable description of the SEDS series

eia_natural_gas_storage · table¶

EIA API v2 weekly natural gas underground storage data. Covers working gas, base gas, total gas, injections, and withdrawals by EIA storage region (East, West, Midwest, Mountain, Pacific, South Central, Salt, Non-Salt, National). One row per region + storage type + report date. Useful for gas market signal analysis and year-over-year storage comparison.

Column	Type	Null	Description
`year`	integer	no	Calendar year of the report date (partition column)
`report_date`	string	no	Report date in ISO format (YYYY-MM-DD)
`storage_year`	integer	no	Calendar year of the report date
`storage_week`	integer	no	Week number within the year (ISO week)
`eia_region_code`	string	yes	EIA storage region code (e.g., 'R10', 'R20', 'R30', 'NAT')
`region`	string	yes	Region description (e.g., 'East', 'West', 'National')
`storage_type_code`	string	yes	Storage type code (e.g., 'SAW' = total working gas, 'SAS' = salt, 'SAN' = non-salt)
`storage_type`	string	yes	Storage type description
`volume_bcf`	double	yes	Volume in billion cubic feet
`units`	string	yes	Units for volume (typically 'Bcf')
`series_id`	string	yes	EIA series identifier from the NG bulk archive. Encodes the storage region plus the storage measure; the decoded parts are in eia_region_code, region, storage_type_code, and storage_type (working gas, base gas, total gas, injections, withdrawals). This table is long-format: one row per (series_id, report_date), so ALWAYS filter on series_id (or storage_type_code) — the archive carries both regional and lower-48 totals, and mixing them double counts. Volumes are in Bcf. Enumerate with SELECT DISTINCT series_id.

eia_petroleum_stocks · table¶

EIA API v2 weekly petroleum product stocks. Covers crude oil and refined products (gasoline, distillate, jet fuel, residual fuel, etc.) by PADD district and national totals. One row per area + product + process + report date. Supports supply disruption analysis, seasonal inventory tracking, and refinery margin signals.

Column	Type	Null	Description
`report_date`	string	no	Report date in ISO format (YYYY-MM-DD)
`stock_year`	integer	no	Calendar year of the report date
`stock_week`	integer	no	Week number within the year (ISO week)
`eia_area_code`	string	yes	EIA area code (e.g., 'SAE', 'USA', 'PAD1', 'PAD2')
`padd`	string	yes	PADD district description or 'National'
`product_code`	string	yes	EIA petroleum product code (e.g., 'EPC0', 'EPM0', 'EPD0')
`product`	string	yes	Product description (e.g., 'Crude Oil', 'Finished Motor Gasoline')
`process_code`	string	yes	EIA process code identifying the stock measurement
`process_name`	string	yes	Process description
`stocks_kbbl`	double	yes	Stocks in thousand barrels
`series_id`	string	yes	EIA API v2 series identifier. Composite key encoding area (PADD or U.S.) plus product plus process; the human-readable label is in series_description on the same row, and the decoded parts are in eia_area_code, padd, product_code, and process_code. This table is long-format: one row per (series_id, report_date), so ALWAYS filter on series_id (or product_code plus padd) — national totals and PADD districts coexist here, so summing across series double counts. Stocks are in thousand barrels. Enumerate with SELECT DISTINCT series_id, series_description.
`series_description`	string	yes	Full series description

eia_natural_gas_price · table¶

EIA API v2 Henry Hub Natural Gas Spot Price (series RNGWHHD), daily, in dollars per million Btu. Full history in one unpartitioned fetch (~7,500 rows since 1997-01-07, paginated via OFFSET). Pairs with eia_natural_gas_storage / weekly_gas_storage_signal to answer inventory-vs-price questions the corpus previously had no price series for at all — only the storage/inventory side existed.

Column	Type	Null	Description
`report_date`	string	no	Report date in ISO format (YYYY-MM-DD)
`series_id`	string	yes	EIA API v2 series identifier (RNGWHHD = Henry Hub spot)
`series_description`	string	yes	Full series description
`product`	string	yes	Product description (Natural Gas)
`process_name`	string	yes	Process description (Spot Price)
`value_dollars_per_mmbtu`	double	yes	Price in dollars per million Btu
`units`	string	yes	Unit label as published ($/MMBTU)

eia_crude_oil_imports · table¶

EIA Form 814 monthly crude oil import survey. The transformer iterates all 12 monthly XLSX files for the given year (URL pattern impa{yy}{m}.xlsx where yy is 2-digit year and m is 1-based month digit) and unions them into a single partition. One row per import transaction per month (importer + origin country + entry port + receiving refinery). Includes API gravity, sulfur content, and volume in thousand barrels. Cross-references geo.state_ref via state_abbr (refinery state).

Column	Type	Null	Description
`rpt_period`	string	no	Reporting period in YYYYMM format
`import_year`	integer	no	Import year
`import_month`	integer	no	Import month (1-12)
`importer_name`	string	no	Name of the importing company
`origin_country`	string	yes	Country of origin name
`origin_country_code`	integer	no	EIA country code for the origin country
`entry_port_code`	string	yes	PADD district code for the entry port
`entry_port_city`	string	yes	Entry port city name
`entry_padd`	integer	yes	PADD district number of the entry port
`dest_padd`	integer	yes	PADD district number of the destination refinery
`receiving_company`	string	yes	Company receiving the crude at the destination
`refinery_site_id`	integer	no	EIA refinery site identifier
`refinery_site_name`	string	yes	Refinery site name
`refinery_state_abbr`	string	yes	2-letter state abbreviation of the receiving refinery
`api_gravity`	double	yes	API gravity of the imported crude
`sulfur_content_pct`	double	yes	Sulfur content as a percentage by weight
`volume_kbbl`	integer	yes	Volume imported in thousand barrels

eia_refinery_operations · table¶

EIA API v2 petroleum refinery process data in tall format. One row per EIA area + series (process_code) + year-month. Key series include crude inputs to distillation (CRDISS), operable distillation capacity (CAPACITY), and utilization rate (UTIL). Additional series cover catalytic cracking, coking, reforming, and other secondary processes. Pivoting into wide format is done via the refinery_utilization_summary view. One row per area + series_id + report year-month.

Column	Type	Null	Description
`report_year`	integer	no	Calendar year
`report_month`	integer	no	Calendar month (1-12)
`eia_area_code`	string	yes	EIA area code (e.g., PADD code or 'USA')
`padd`	string	yes	PADD district description or 'National'
`series_id`	string	yes	EIA API v2 series identifier. Encodes area plus refinery process; the process is also available decoded in process_code with its label in metric_name. Key process codes include CRDISS crude inputs to distillation and operable distillation capacity measures. This table is tall format: one row per (eia_area_code, series_id, year-month), so ALWAYS filter on series_id or process_code — units differ per process (see the units column) and national totals coexist with PADD districts. Enumerate with SELECT DISTINCT series_id, process_code, metric_name.
`process_code`	string	yes	EIA refinery process code
`metric_name`	string	yes	Metric description (e.g., 'Crude Oil Input to Atmospheric Crude Oil Distillation Units')
`units`	string	yes	Units of measure (e.g., 'Thousand Barrels per Day')
`value`	double	yes	Metric value

eia_coal_mines · table¶

MSHA MinesProdYearly coal mine production data (pipe-delimited CSV ZIP). The transformer downloads MinesProdYearly.zip and Mines.zip, performs an inner join on mine_id, filters to coal mines (COAL_METAL_IND = 'C'), and returns one row per mine + subunit + year. Includes mine demographics, operator/controller identity, geographic location (lat/lon, county, state), mine type and status, production volume in short tons, employee count, annual hours, labor productivity, and average seam height (underground mines). The transformer filters by the requested year dimension. Cross-references geo.state_ref via state_abbr. controller_name and operator_name are unstructured (no per-row id) — mine_id is per-mine, not per-company. ref.canonical_org_entity carries each as an already-resolved identity (eia_coal_controller_name, eia_coal_operator_name, each with an LEI when matched); join there on the name rather than re-running a fuzzy match against gleif_entities.legal_name by hand.

Column	Type	Null	Description
`mine_id`	string	no	MSHA mine identifier
`report_year`	integer	no	Production report year
`mine_name`	string	yes	Mine name
`controller_name`	string	yes	Mine controller company name
`operator_name`	string	yes	Mine operator company name
`state_abbr`	string	yes	2-letter state abbreviation (e.g., 'CA')
`county_fips`	string	yes	5-digit county FIPS code
`county_name`	string	yes	County name
`latitude`	double	yes	Mine latitude
`longitude`	double	yes	Mine longitude
`mine_type`	string	yes	Mine type (e.g., 'Surface', 'Underground')
`subunit_code`	string	no	MSHA subunit code (e.g., '01' = Underground, '02' = Surface, '03' = Auger)
`subunit`	string	yes	Subunit description
`mine_status`	string	yes	Mine status (e.g., 'Active', 'Temporarily Idled', 'Abandoned')
`production_short_tons`	double	yes	Coal production in short tons
`avg_employee_count`	double	yes	Average number of employees
`annual_hours`	double	yes	Total employee hours worked
`labor_productivity`	double	yes	Labor productivity in short tons per employee hour
`avg_mine_height_inches`	double	yes	Average mine height in inches (underground mines only)

ev_charging_stations · table¶

NREL Alternative Fuels Data Center (AFDC) public electric charging station registry — one row per station. Station-level port counts by charging level (ev_dc_fast_num / ev_level1_evse_num / ev_level2_evse_num) and available connector types (ev_connector_types, comma-separated — e.g. CHADEMO, J1772, J1772COMBO, TESLA). Does not model AFDC's per-charger connector/power breakdown (ev_charging_units) — deeper than any known query needs; the station-level aggregate counts and connector list answer "how much charging capacity exists where." Whole registry re-fetched and overwritten each run when Last-Modified changes (confirmed live: ~90K electric stations, 200-row page cap). Cross-references geo.state_ref via state_abbr. Source: developer.nlr.gov (NREL's API host as of the 29 May 2026 migration off the retired developer.nrel.gov domain; confirmed live).

Column	Type	Null	Description
`id`	long	no	AFDC station ID (PK)
`station_name`	string	yes	Station name
`street_address`	string	yes	Street address
`intersection_directions`	string	yes	Free-text directions to the station
`city`	string	yes	City
`state_abbr`	string	yes	2-letter state abbreviation (e.g., 'CA')
`zip`	string	yes	ZIP code
`country`	string	yes	ISO country code (station registry also covers CA/MX)
`latitude`	double	yes	Station latitude
`longitude`	double	yes	Station longitude
`access_code`	string	yes	'public' or 'private'
`access_days_time`	string	yes	Free-text access hours
`access_detail_code`	string	yes	Access restriction detail code, when access_code is private
`groups_with_access_code`	string	yes	Groups permitted access (e.g. 'Public', 'Private')
`facility_type`	string	yes	Facility type (e.g. 'FLEET_GOVT', 'HOTEL', 'PARKING_GARAGE')
`owner_type_code`	string	yes	Owner type code (e.g. 'LG'=local govt, 'SG'=state govt, 'P'=private, 'T'=utility)
`status_code`	string	yes	Station status ('E'=available, 'P'=planned, 'T'=temporarily unavailable)
`open_date`	date	yes	Date the station opened
`date_last_confirmed`	date	yes	Date AFDC last confirmed this record
`expected_date`	date	yes	Expected opening date, for planned stations
`updated_at`	timestamp	yes	AFDC's last-updated timestamp for this record
`ev_connector_types`	string	yes	Comma-separated connector types available (e.g. 'CHADEMO,J1772,J1772COMBO')
`ev_dc_fast_num`	integer	yes	Count of DC fast charging ports
`ev_level1_evse_num`	integer	yes	Count of Level 1 charging ports
`ev_level2_evse_num`	integer	yes	Count of Level 2 charging ports
`ev_network`	string	yes	Charging network operator (e.g. 'TESLA', 'CHARGEPOINT', 'Non-Networked')
`ev_network_web`	string	yes	Network operator website
`ev_pricing`	string	yes	Free-text pricing description
`ev_workplace_charging`	boolean	yes	Whether this is workplace charging
`ev_renewable_source`	string	yes	On-site renewable generation source, when present
`restricted_access`	boolean	yes	Whether access is restricted
`maximum_vehicle_class`	string	yes	Maximum vehicle class served (e.g. 'LD', 'MD', 'HD')
`station_phone`	string	yes	Station contact phone number
`geocode_status`	string	yes	AFDC geocoding confidence status

eia_drilling_activity · table¶

EIA Short-Term Energy Outlook (STEO) Table 10a upstream drilling activity — active rig count, new wells drilled, new wells completed, and drilled-but- uncompleted (DUC) well inventory, monthly, for the 6 regions STEO covers (Permian, Bakken, Eagle Ford, Appalachia, Haynesville, and Rest of Lower 48 excluding Gulf of America). This is the successor to EIA's standalone Drilling Productivity Report, folded into STEO as Table 10a in June 2024 (confirmed live: full 2013-01 through present history, 24 series, ~3,900 rows, one request). Rig counts/wells drilled lead crude and gas production by months, which energy's production/capacity tables alone cannot show. One row per (region, metric, period). Whole series set re-fetched and overwritten each run when content changes.

Column	Type	Null	Description
`series_id`	string	no	EIA STEO series ID (e.g. 'RIGSPM'); PK component with period
`series_description`	string	yes	Full EIA series description
`region_code`	string	yes	DPR region code decoded from series_id (PM/BK/EF/AP/HA/R48)
`region_name`	string	yes	Region name decoded from series_id (Permian/Bakken/Eagle Ford/ Appalachia/Haynesville/Rest of Lower 48 excluding Gulf of America)
`metric_code`	string	yes	Metric decoded from series_id (RIGS/NWD/NWC/DUCS)
`metric_name`	string	yes	Metric name decoded from series_id (Active Rigs/New Wells Drilled/ New Wells Completed/Drilled but Uncompleted Wells)
`period`	string	no	Report period, YYYY-MM; PK component with series_id
`report_year`	integer	yes	Calendar year parsed from period
`report_month`	integer	yes	Calendar month (1-12) parsed from period
`value`	double	yes	Metric value — rig count and well counts are physical counts (not dollars/percent); DUC is an end-of-month inventory level, not a flow

county_gas_pipeline_presence · view¶

Per-county natural gas pipeline presence, spatially joining gas_pipelines' segment-level line geometry (geometry_wkt) against geo.counties' own boundary geometry (its geometry column, latest TIGER vintage year) with DuckDB spatial (ST_Intersects/ST_Intersection/ST_Length over WGS84 WKT — both sources use SRID 4326, no reprojection needed), following the same pattern as lands.county_federal_fee_land_share, including its validity repair of the county polygons (ST_CollectionExtract(ST_MakeValid(ST_Buffer(g, 0.0)), 3)) because GEOS overlay throws a TopologyException on an invalid county boundary. Unlike that view, no per-row state filter is applied before the spatial predicate: gas_pipelines carries no state column to prefilter on; ST_Intersects itself rejects non-overlapping bounding boxes before any exact geometry test, which keeps the ~3,144 x ~32,900 cross product tractable without one. pipeline_length_miles is not a raw degree-based length (DuckDB ST_Length has no CRS awareness, so a WGS84-degree length is not a distance) — instead, for each intersecting segment, the fraction of the segment's own line length that falls within the county (ST_Length(ST_Intersection(...)) / ST_Length(full segment), both in the same degree units, so the CRS-unaware-ness cancels out in the ratio) is applied to that segment's real EIA-published length_m (meters, projected, not degree-based) before converting to miles. A segment crossing multiple counties is prorated across each and sums back to (approximately) its own total length nationally, rather than counting its full length in every county it touches. pipeline_segment_count / interstate_segment_count / intrastate_segment_count count distinct segment_id (a segment that re-enters the same county's boundary more than once, e.g. along a winding line, is still counted once). Both input geometries are simplified/generalized (TIGER counties to their own Census generalization; pipeline segments are EIA/Esri's own digitized routes, not survey-grade), so treat pipeline_length_miles as a close estimate rather than a pipeline-operator's as-built figure — consistent with county_federal_fee_land_share's own accuracy caveat. LEFT JOIN so every county appears, including a 0 count/length when no pipeline segment intersects it.