🌦️ weather

U.S. weather and climate data from NWS (weather.gov), NOAA Climate Data Online (CDO v2), and NOAA bulk observation feeds. NWS tables provide weather station metadata and active severe weather alerts by state (no auth required). CDO tables provide historical monthly and annual climate summaries (GSOM/GSOY) with temperature, precipitation, and snowfall metrics (requires NOAA_CDO_TOKEN). All state-level tables join with geo.states via state_abbr or state_fips; county-grain tables join geo.counties via county_fips. (EPA AQS air-quality data now lives in the environment schema.)

12 datasets · 111 columns

weather_daily_by_county · table

Pre-aggregated county-level daily weather derived from ghcnd_daily joined to ghcnd_stations_with_county. One row per county per day. Simple mean of all reporting stations in the county. Primary join anchor for cross-schema research: join on county_fips + date to correlate with SEC filings, BLS wages, FEC contributions, census demographics, and health data.

View — columns are resolved by the query engine at runtime.

nws_stations · table

NWS weather station metadata from weather.gov. Includes station ID, name, coordinates, elevation, timezone, and zone IDs. One row per station. Cross-references geo.states via state_abbr. No authentication required.

Column	Type	Null	Description
station_id	string	no	NWS station ID (e.g., 'KORD')
station_name	string	yes	Station name
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA')
latitude	double	yes	Station latitude
longitude	double	yes	Station longitude
elevation_m	double	yes	Elevation in meters
timezone	string	yes	IANA timezone (e.g., 'America/Chicago')
forecast_zone	string	yes	NWS forecast zone ID
county_zone	string	yes	NWS county zone ID

nws_alerts · table

NWS active weather alerts from weather.gov. Includes alert type, severity, certainty, urgency, and temporal bounds. Snapshot of current alerts per state. Cross-references geo.states via state_abbr. No authentication required.

Column	Type	Null	Description
alert_id	string	no	NWS alert identifier
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA')
event	string	yes	Event type (e.g., 'Tornado Warning')
severity	string	yes	Extreme, Severe, Moderate, Minor, Unknown
certainty	string	yes	Observed, Likely, Possible, Unlikely
urgency	string	yes	Immediate, Expected, Future, Past
headline	string	yes	Alert headline
description	string	yes	Full description
onset	string	yes	ISO datetime of onset
expires	string	yes	ISO datetime of expiration
sender_name	string	yes	Issuing office
affected_zones	string	yes	Comma-separated zone IDs

cdo_stations · table

NOAA CDO weather station metadata (GHCND and COOP network stations). Includes CDO station ID, name, coordinates, elevation, data coverage date range (min_date/max_date), and coverage fraction (datacoverage). One row per station. Cross-references geo.states via state_fips. Requires NOAA_CDO_TOKEN environment variable. Unlike ghcnd_stations_with_county (no auth required), this table carries no county_fips — use ghcnd_stations_with_county for county-level station joins.

Column	Type	Null	Description
station_id	string	no	CDO station ID (e.g., 'GHCND:USW00094846', 'COOP:110050')
station_name	string	yes	Station name
state_fips	string	no	2-digit state FIPS code (e.g., '06' for California)
latitude	double	yes	Station latitude
longitude	double	yes	Station longitude
elevation	double	yes	Elevation (nullable)
min_date	string	yes	Earliest data available (ISO date)
max_date	string	yes	Most recent data available (ISO date)
datacoverage	double	yes	Coverage fraction 0-1

cdo_monthly_summaries · table

NOAA CDO Global Summary of the Month (GSOM) data. Includes temperature (TAVG, TMAX, TMIN), precipitation (PRCP), snowfall (SNOW), and other monthly climate metrics per station. One row per station+month+datatype. Monthly companion to cdo_annual_summaries (GSOY, same station network at annual grain). Cross-references geo.states via state_fips and cdo_stations via station_id. Requires NOAA_CDO_TOKEN environment variable.

Column	Type	Null	Description
state_fips	string	no	2-digit state FIPS code (e.g., '06' for California)
station_id	string	yes	CDO station ID
year	integer	no	Calendar year
month	integer	yes	Month (1-12)
datatype	string	yes	Measurement type (TAVG, TMAX, TMIN, PRCP, SNOW, etc.)
value	double	yes	Value in standard units
attributes	string	yes	Quality flags
date	string	yes	ISO date of measurement period

cdo_annual_summaries · table

NOAA CDO Global Summary of the Year (GSOY) data — annual temperature, precipitation, and other climate metrics (see the datatype column for the specific element code of each row) per station. One row per station+datatype+year. Annual companion to cdo_monthly_summaries (GSOM, same station network at monthly grain — use that table for TAVG/TMAX/TMIN/PRCP/SNOW by month). Cross-references geo.states via state_fips and cdo_stations via station_id. Requires NOAA_CDO_TOKEN environment variable.

Column	Type	Null	Description
state_fips	string	no	2-digit state FIPS code (e.g., '06' for California)
station_id	string	yes	CDO station ID
year	integer	no	Calendar year
datatype	string	yes	Measurement type
value	double	yes	Value in standard units
attributes	string	yes	Quality flags
date	string	yes	ISO date

ghcnd_stations_with_county · table

NOAA GHCN-Daily station inventory enriched with county_fips via nearest Census county centroid assignment. One row per US station. Prerequisite for all cross-schema joins using ghcnd_daily and weather_daily_by_county. No auth needed. county_fips is assigned by nearest Euclidean distance to Census TIGER county centroids. state_fips is derived from the 2-letter state code in the station file. data_start_year and data_end_year come from ghcnd-inventory.txt.

Column	Type	Null	Description
station_id	string	no	GHCND station ID (e.g., 'USW00094846')
station_name	string	yes	Station name
state_fips	string	no	2-digit state FIPS code (e.g., '06' for California)
county_fips	string	yes	5-digit county FIPS (nearest Census county centroid)
latitude	double	yes	Station latitude
longitude	double	yes	Station longitude
elevation_m	double	yes	Elevation in meters
data_start_year	integer	yes	First year of observations (from ghcnd-inventory.txt)
data_end_year	integer	yes	Most recent year of observations (from ghcnd-inventory.txt)
distance_to_county_centroid_km	double	yes	Distance used for nearest-county centroid assignment

ghcnd_daily · table

NOAA GHCN-Daily station observations from NCEI annual bulk files (https://www.ncei.noaa.gov/pub/data/ghcn/daily/by_year/YYYY.csv.gz). One row per station per day (station-level grain): tmax_c, tmin_c, tavg_c, prcp_mm, snow_mm, snwd_mm, and awnd_ms, in metric units (°C, mm, m/s). Q_FLAG-filtered (only rows that passed all NCEI quality checks are included). Cross-references ghcnd_stations_with_county via station_id for county_fips joins; the weather_daily_by_county view pre-aggregates this table to county-level daily means for anyone who wants county grain instead of per-station rows. No auth needed.

Column	Type	Null	Description
station_id	string	no	GHCND station ID (e.g., 'USW00094846')
state_fips	string	no	2-digit state FIPS code (e.g., '06' for California)
date	string	no	Observation date (ISO format YYYY-MM-DD)
year	integer	no	Calendar year
month	string	no	2-digit month (01-12), derived from date
tmax_c	double	yes	Maximum temperature in °C (NCEI TMAX, tenths-of-°C ÷ 10)
tmin_c	double	yes	Minimum temperature in °C (NCEI TMIN, tenths-of-°C ÷ 10)
tavg_c	double	yes	Average temperature in °C (NCEI TAVG, tenths-of-°C ÷ 10)
prcp_mm	double	yes	Precipitation in mm (NCEI PRCP, tenths-of-mm ÷ 10)
snow_mm	double	yes	Snowfall in mm (NCEI SNOW, native mm)
snwd_mm	double	yes	Snow depth in mm (NCEI SNWD, native mm)
awnd_ms	double	yes	Average wind speed in m/s (NCEI AWND, tenths-of-m/s ÷ 10)
tmax_flag	string	yes	Measurement flag for tmax
tmin_flag	string	yes	Measurement flag for tmin
prcp_flag	string	yes	Measurement flag for prcp

drought_monitor_weekly · table

National Drought Monitor weekly county-level drought severity from the USDM API at droughtmonitor.unl.edu, which itself has published data since 2000 — but this table's own observedCoverage below is the source of truth for what's actually loaded here; check it rather than assuming a 2000 floor. Includes D0-D4 drought category percentages (exclusive area fractions) and the Drought Severity and Coverage Index (DSCI, range 0-500). week_date is the Tuesday release date. Cross-references geo.counties via county_fips and geo.states via state_abbr. No authentication required.

Column	Type	Null	Description
county_fips	string	no	5-digit county FIPS code
state_fips	string	no	2-digit state FIPS code (e.g., '06' for California)
state_abbr	string	no	2-letter state abbreviation (e.g., 'CA')
county_name	string	yes	County name
year	integer	no	Calendar year
week_date	string	no	Tuesday release date of the drought report (YYYY-MM-DD)
valid_start	string	yes	Start date of the drought week period (YYYY-MM-DD)
valid_end	string	yes	End date of the drought week period (YYYY-MM-DD)
none_pct	double	yes	% of county area with no drought
d0_pct	double	yes	% Abnormally Dry (exclusive D0 only)
d1_pct	double	yes	% Moderate Drought (exclusive D1 only)
d2_pct	double	yes	% Severe Drought (exclusive D2 only)
d3_pct	double	yes	% Extreme Drought (exclusive D3 only)
d4_pct	double	yes	% Exceptional Drought (exclusive D4)
dsci	double	yes	Drought Severity and Coverage Index (0-500, sum of cumulative D0+D1+D2+D3+D4)

hms_smoke_daily · table

NOAA Hazard Mapping System (HMS) satellite smoke detection aggregated to county level. Daily smoke coverage by density category (smoke_coverage_pct, heavy_smoke_pct, medium_smoke_pct, light_smoke_pct). NESDIS's own polygon archive is available from 2005, but this table's own observedCoverage below is the source of truth for what's actually loaded here — check it rather than assuming a 2005 floor or full year-to-year continuity (there is currently a 2014 gap). Downloads daily smoke polygon shapefiles from NESDIS (https://satepsanone.nesdis.noaa.gov/pub/FIRE/web/HMS/Smoke_Polygons/Shapefile/), performs a JTS spatial intersection against TIGER/Line county boundaries, and emits per-county coverage percentages. One batch per year+month; each batch processes all days in that month. The raw smoke plume polygon geometries (WKT) behind these percentages are retained separately in hms_smoke_polygons for spatial re-analysis against other boundaries. Uses HmsSmokeSpatialJoinProvider.

Column	Type	Null	Description
county_fips	string	no	5-digit county FIPS code
state_fips	string	no	2-digit state FIPS code (e.g., '06' for California)
date	string	no	Observation date (YYYY-MM-DD)
year	integer	no	Calendar year
month	string	no	2-digit month (01-12)
smoke_coverage_pct	double	yes	% of county covered by any smoke
heavy_smoke_pct	double	yes	% of county covered by heavy smoke
medium_smoke_pct	double	yes	% of county covered by medium smoke
light_smoke_pct	double	yes	% of county covered by light smoke

hms_smoke_polygons · table

Raw NOAA HMS smoke plume polygon geometries retained for spatial re-analysis. One row per smoke polygon per day. Geometry stored as WKT for direct use with DuckDB ST_GeomFromText() or re-joining against any boundary table (tracts, zip codes, watersheds, etc.) without re-downloading from NESDIS. Produced in the same pass as hms_smoke_daily — the HmsSmokeSpatialJoinProvider caches polygon rows during the hms_smoke_daily batch and emits them here.

Column	Type	Null	Description
date	string	no	Observation date (YYYY-MM-DD)
year	integer	no	Calendar year
month	string	no	2-digit month (01-12)
density	string	no	Smoke density classification: Light, Medium, or Heavy
geometry	string	yes	WKT polygon geometry of the smoke plume in WGS84 (EPSG:4326)

climate_normals_monthly · table

NOAA CDO Monthly Climate Normals (NORMAL_MLY dataset, 1981-2010 period). One row per station per month. Values are in metric units (°C, mm). CDO's raw response is in its "standard" units (tenths of °F for temperature, hundredths of an inch for precipitation, tenths of an inch for snowfall); ClimateNormalsTransformer converts each to metric with the correct formula per field (absolute temperatures use (°F-32)×5/9; the two stddev columns are a delta and use ×5/9 with no -32 offset). county_fips is joined from ghcnd_stations_with_county. Requires NOAA_CDO_TOKEN. Static dataset — update logic is not needed until NOAA releases a new 30-year period. Cross-references ghcnd_stations_with_county via station_id.

Column	Type	Null	Description
station_id	string	no	GHCND station ID
state_fips	string	no	2-digit state FIPS code (e.g., '06' for California)
county_fips	string	yes	5-digit county FIPS (from ghcnd_stations_with_county join)
month	integer	no	Month (1-12)
normal_tmax_c	double	yes	30-year normal daily max temperature (°C)
normal_tmin_c	double	yes	30-year normal daily min temperature (°C)
normal_tavg_c	double	yes	30-year normal daily mean temperature (°C)
normal_prcp_mm	double	yes	30-year normal daily precipitation (mm)
normal_snow_mm	double	yes	30-year normal daily snowfall (mm)
tmax_stddev	double	yes	Standard deviation of daily tmax (°C)
tmin_stddev	double	yes	Standard deviation of daily tmin (°C)
prcp_stddev	double	yes	Standard deviation of daily prcp (mm); NULL if not available from CDO