🌦️ 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.state_ref 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.)

17 datasets · 160 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.state_ref 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.state_ref 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.state_ref 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.state_ref via state_fips and cdo_stations via station_id. No auth needed. Loaded from NCEI's GSOM bulk archive (gsom-latest.tar.gz); state_fips is the station's own state from ghcnd-stations.txt, and stations outside the 50 states and DC are not included.

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.state_ref via state_fips and cdo_stations via station_id. No auth needed.

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.state_ref 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 (all 12 months per station). Each state is fetched, paged and pivoted as one unit and stored as its own partition. 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

asos_observations · table

Sub-daily surface observations (one row per METAR report) for the 15 US airport ASOS stations that prediction-market daily-high-temperature contracts settle on: KLAX, KMIA, KAUS, KATL, KSFO, KSEA, KHOU, KLGA, KNYC (Central Park), KMDW, KORD, KDEN, KBKF, KDFW, KDAL. Source is the Iowa Environmental Mesonet ASOS archive (https://mesonet.agron.iastate.edu/cgi-bin/request/asos.py), a university-hosted mirror of NWS/FAA ASOS and MADIS METAR feeds, not an agency endpoint; re-verify if it starts failing. report_type separates three feeds: 'routine' (hourly METAR, ~minute :53), 'special' (SPECI, issued when conditions change), and '5min' (MADIS high-frequency METAR, every 5 minutes, available from about 2016). Years before a feed existed return no rows for it. observation_time_utc is UTC. Temperatures are kept as reported: air_temp_f / air_temp_c are IEM's parse of the METAR body (whole degrees; null on 5min rows), temp_c_tgroup is the exact tenths-of-a-degree Celsius from the METAR remarks T-group (present on most rows, including 5min), and metar is the full raw report text. A 5-minute and a routine report can share one minute, so (station_id, observation_time_utc, report_type) is not unique; add metar for a unique key. -61 C in temp_c_tgroup (METAR 'M61/M61') is the ASOS sensor-missing marker, kept as reported; filter it when taking daily maxima/minima. air_temp_f/air_temp_c occasionally disagree with the raw report, so prefer temp_c_tgroup when precision matters. The daily maximum over observation_time_utc rows differs from the official NWS daily climate-report maximum (ghcnd_daily tmax_c); this table is the record needed to reproduce the highest-observation-of-the-day measure. Use observation_date (UTC day) to group, not local day. No auth needed.

Column	Type	Null	Description
station_id	string	no	4-letter ICAO station identifier (e.g., 'KMIA')
observation_time_utc	string	no	Observation time in UTC, 'YYYY-MM-DD HH:MM'
observation_date	string	no	UTC calendar date of the observation (YYYY-MM-DD)
year	integer	no	Calendar year
report_type	string	no	Feed: 'routine' (hourly METAR), 'special' (SPECI), or '5min' (MADIS 5-minute METAR)
air_temp_f	double	yes	Air temperature in whole degrees F as parsed from the METAR body; null on 5min rows
air_temp_c	double	yes	Air temperature in degrees C as parsed by IEM from the METAR body; null on 5min rows
temp_c_tgroup	double	yes	Air temperature in degrees C to tenths, from the METAR remarks T-group; null when the report has no T-group
metar	string	yes	Full raw METAR report text

coops_stations · table

NOAA CO-OPS tide gauges that report water level and tides: 238 tidal stations (NWLON and others) on US coasts, territories and a few foreign/Pacific sites, from the CO-OPS Metadata API (api.tidesandcurrents.noaa.gov/mdapi, keyless, public domain). One row per station with name, state, coordinates, tide type (semidiurnal, mixed, diurnal) and time zone. state is as CO-OPS publishes it; state_abbr and state_fips are populated only for the 50 states, DC, PR, VI and American Samoa (null for Guam, Midway, Wake, Kwajalein and Bermuda). Great Lakes stations are excluded. Joins geo.state_ref via state_abbr or state_fips; coops_station_datums, coops_high_low and coops_tide_predictions join on station_id.

Column	Type	Null	Description
station_id	string	no	CO-OPS station id (7 digits, e.g., '8518750' The Battery, NY)
station_name	string	no	Station name
state	string	yes	State as published by CO-OPS: 2-letter code for states and most territories, otherwise a country or area name
state_abbr	string	yes	2-letter abbreviation (50 states, DC, PR, VI, AS); null where CO-OPS gives no US state
state_fips	string	yes	2-digit state FIPS code; null where state_abbr is null
latitude	double	no	Station latitude (decimal degrees)
longitude	double	no	Station longitude (decimal degrees, negative west)
affiliations	string	yes	Network affiliations (e.g., 'NWLON', 'NWLON,PORTS')
tide_type	string	yes	Tide regime as published: Semidiurnal, Mixed, Mixed Diurnal or Diurnal
timezone	string	yes	Local time zone abbreviation (e.g., 'EST')
timezone_offset_hours	integer	no	Standard-time offset from UTC in hours
observes_dst	boolean	no	Whether local time observes daylight saving
storm_surge_forecast	boolean	no	Whether CO-OPS publishes a storm surge forecast for the station

coops_station_datums · table

Tidal datum elevations per CO-OPS tide gauge, in meters above station datum (STND), from the Metadata API datums.json. One row per station. Water levels in coops_high_low and coops_tide_predictions are meters above MLLW, so mllw_m converts them to station datum and navd88_m (when published) ties them to NAVD88. Columns hold the current published epoch (epoch, e.g. '1983-2001'): MHHW, MHW, MTL, MSL, MLW, MLLW, NAVD88, plus LAT/HAT where published. A datum a station does not publish is null.

Column	Type	Null	Description
station_id	string	no	CO-OPS station id (joins coops_stations.station_id)
epoch	string	yes	National Tidal Datum Epoch the datums are computed over (e.g., '1983-2001')
orthometric_datum	string	yes	Orthometric datum the station is tied to (e.g., 'NAVD88')
datums_accepted	string	yes	Date the current datums were accepted (as published, e.g., 'Nov 19 2012')
stnd_m	double	yes	Station datum elevation, meters (reference 0)
mhhw_m	double	yes	Mean Higher-High Water, meters above station datum
mhw_m	double	yes	Mean High Water, meters above station datum
mtl_m	double	yes	Mean Tide Level, meters above station datum
msl_m	double	yes	Mean Sea Level, meters above station datum
mlw_m	double	yes	Mean Low Water, meters above station datum
mllw_m	double	yes	Mean Lower-Low Water, meters above station datum (the reference for coops_high_low and coops_tide_predictions)
navd88_m	double	yes	North American Vertical Datum 1988, meters above station datum; null when not tied
lat_m	double	yes	Lowest Astronomical Tide, meters above station datum; null when not published
hat_m	double	yes	Highest Astronomical Tide, meters above station datum; null when not published

coops_high_low · table

Observed high and low water events (tide extremes) from NOAA CO-OPS NWLON tide gauges, one row per event per station: roughly 2 to 4 per station per day (H/L semidiurnal, HH/LL on mixed tides). Source is the CO-OPS Data API (product=high_low, api.tidesandcurrents.noaa.gov, keyless, public domain; requests are limited to 365 days, so the ETL pulls one station-year per call). Heights are meters above MLLW (current tidal epoch) in UTC time, as verified (preliminary for recent months). Stations inactive in a year return no rows. Pair with coops_tide_predictions: observed minus predicted extreme is the storm-surge / anomaly signal, precomputed in the coops_daily_surge view. Join coops_stations on station_id for state_fips, latitude and longitude, and to geo.state_ref; coastal flooding in the disasters schema joins on state. Great Lakes stations are excluded (no tides); currents are not included.

Column	Type	Null	Description
station_id	string	no	CO-OPS station id (joins coops_stations.station_id)
event_time_utc	string	no	Time of the high or low water event in UTC, 'YYYY-MM-DD HH:MM'
event_date	string	no	UTC calendar date of the event (YYYY-MM-DD)
year	integer	no	Calendar year
water_level_m	double	yes	Observed water level of the high/low event in meters above MLLW (Mean Lower-Low Water, the station's current tidal epoch). Comparable across stations only via coops_station_datums
event_type	string	no	H = high water, L = low water, HH = higher high water, LL = lower low water (HH/LL appear on mixed and diurnal tide stations)
quality_flags	string	yes	CO-OPS data flags 'inferred,exceedsMax/Min,rateOfChange,limits' as 0/1 (e.g. '0,0')

coops_tide_predictions · table

Predicted high and low tide times and heights from NOAA CO-OPS harmonic prediction (product=predictions&interval=hilo), one row per predicted extreme per tidal station. Predictions are astronomical only (no weather), so water_level_m from coops_high_low minus the matching prediction isolates storm surge and other non-tidal anomaly; see the coops_daily_surge view. Heights are meters above MLLW (current tidal epoch), time in UTC. Source: api.tidesandcurrents.noaa.gov Data API, keyless, public domain, 365 days per call so the ETL pulls one station-year per call. Join coops_stations on station_id for state and coordinates. Currents predictions are not included.

Column	Type	Null	Description
station_id	string	no	CO-OPS station id (joins coops_stations.station_id)
event_time_utc	string	no	Time of the high or low water event in UTC, 'YYYY-MM-DD HH:MM'
event_date	string	no	UTC calendar date of the event (YYYY-MM-DD)
year	integer	no	Calendar year
water_level_m	double	yes	Predicted tide height of the high/low event in meters above MLLW (Mean Lower-Low Water, the station's current tidal epoch). Comparable across stations only via coops_station_datums
event_type	string	no	H = high water, L = low water, HH = higher high water, LL = lower low water (HH/LL appear on mixed and diurnal tide stations)