🌀 disasters¶
U.S. discrete emergency-event data: FEMA disaster declarations, Public Assistance and Hazard Mitigation grant projects, NFIP flood-insurance policies and paid claims, NOAA storm events, and NIFC WFIGS wildfire perimeters. Each row is a bounded incident with a date, geographic footprint, and measurable damage — distinct from the continuous meteorological measurements in the weather schema. Enables disaster-risk analysis joined to SEC filings (state-of-incorporation exposure), econ employment, lands national forests (burned NF acreage), and FEC contributions.
13 datasets · 171 columns
disaster_declarations · table¶
Canonical FEMA disaster-declaration record. One row per disaster-declaration-county-program-area combination. Partitioned by fiscal year declared (fyDeclared). Joins to geo.counties via county_fips and to sec.filing_metadata by state for risk-disclosure research. Refreshed daily (declarations issued continuously within 24h of signature). Source: fema.gov/api/open/v2/DisasterDeclarationsSummaries.
| Column | Type | Null | Description |
|---|---|---|---|
disaster_ |
integer | no | FEMA disaster number (e.g., 4611); join key across FEMA tables |
fema_ |
string | yes | Composite declaration id (e.g., 'EM-3610-PR') |
declaration_ |
string | yes | DR (major), EM (emergency), FM (fire mgmt), FS (fire suppression) |
declaration_ |
string | yes | Declaration title (e.g., 'TROPICAL STORM ERNESTO') |
declaration_ |
date | yes | Date of presidential declaration |
incident_ |
string | yes | Hurricane / Flood / Tornado / Wildfire / Severe Storm / etc. |
incident_ |
date | yes | Start of incident period |
incident_ |
date | yes | End of incident period (null if ongoing) |
state_ |
string | yes | 2-digit state FIPS code (FK to geo.states) |
county_ |
string | yes | 5-digit county FIPS code (FK to geo.counties) |
designated_ |
string | yes | Human-readable designated area (e.g., 'Adjuntas (Municipio)') |
region |
integer | yes | FEMA region number (1-10) |
ih_ |
boolean | yes | Individuals and Households Program declared — use this column for "was individual assistance activated," not ia_program_declared (D-285). FEMA's modern program name for the same concept individual filers know as "Individual Assistance"; confirmed live it varies genuinely with real disasters (e.g. true for Hurricane Harvey DR-4332-TX, which also carries a real lastIAFilingDate). |
ia_ |
boolean | yes | Source field iaProgramDeclared is FALSE for every row FEMA's API currently returns (confirmed live across hundreds of declarations spanning 2012-2026, including disasters unambiguously IA-activated in reality, e.g. Hurricane Harvey) — the field reads as dead/unpopulated at the source itself, not an ingest bug (D-285). Use ih_program_declared for a real individual-assistance signal; this column is kept only because it's still present in the raw feed, not because it carries information. |
pa_ |
boolean | yes | Public Assistance declared |
hm_ |
boolean | yes | Hazard Mitigation declared |
tribal_ |
boolean | yes | Whether the declaration was a tribal request |
id |
string | no | OpenFEMA row UUID (PK) |
public_assistance_projects · table¶
FEMA Public Assistance grants to state/local governments and nonprofits for disaster recovery — approved project_amount, federal_share_obligated, and total_obligated (USD) per funded project worksheet. Largest dollar-value FEMA dataset; one row per project worksheet, partitioned by declaration year. Joins to disaster_declarations by disaster_number (many-to-one; not a declared FK) and to geo.counties via county_fips. Source: fema.gov/api/open/v2/PublicAssistanceFundedProjectsDetails.
| Column | Type | Null | Description |
|---|---|---|---|
disaster_ |
integer | yes | FEMA disaster number (e.g., 4611); join key across FEMA tables |
pw_ |
integer | yes | Project worksheet number |
application_ |
string | yes | Project title |
applicant_ |
string | yes | FEMA applicant id |
declaration_ |
date | yes | Date of declaration (drives the year partition) |
incident_ |
string | yes | Incident type (Hurricane, Flood, etc.) |
damage_ |
string | yes | FEMA damage category code (A-G) |
damage_ |
string | yes | Damage category description |
project_ |
string | yes | Active / Closed / etc. |
project_ |
string | yes | Large or Small |
state_ |
string | yes | 2-digit state FIPS code (FK to geo.states) |
county_ |
string | yes | 5-digit county FIPS code (FK to geo.counties) |
county_ |
string | yes | County/parish name |
state_ |
string | yes | 2-char state abbreviation |
project_ |
double | yes | Approved project cost (USD) |
federal_ |
double | yes | Federal obligation to date (USD) |
total_ |
double | yes | Total obligated incl. non-federal share (USD) |
hash |
string | no | OpenFEMA row hash (PK) |
hazard_mitigation_projects · table¶
FEMA Hazard Mitigation Assistance projects (program_area: HMGP, BRIC, FMA, PDM, LPDM) — pre- and post-disaster resilience investment with project_amount, federal_share_obligated, and cost_share_percentage per project. One row per project, partitioned by program fiscal year (programFy). Joins to geo.counties via county_fips and to disaster_declarations by disaster_number (nullable for non-disaster BRIC/FMA programs). Source (API v4): fema.gov/api/open/v4/HazardMitigationAssistanceProjects.
| Column | Type | Null | Description |
|---|---|---|---|
project_ |
string | no | HMA project identifier (PK; e.g., 'DR-1980-0019-F') |
program_ |
string | yes | HMGP / BRIC / FMA / PDM / LPDM |
disaster_ |
integer | yes | FEMA disaster number (e.g., 4611); join key across FEMA tables |
region |
integer | yes | FEMA region number |
state_ |
string | yes | 2-digit state FIPS code (FK to geo.states) |
county_ |
string | yes | 5-digit county FIPS code (FK to geo.counties) |
state_ |
string | yes | State name |
county_ |
string | yes | County name |
project_ |
string | yes | Mitigation project type description |
status |
string | yes | Project status (Approved / Closed / etc.) |
recipient |
string | yes | Grant recipient (state/tribe) |
subrecipient |
string | yes | Subrecipient (local government/entity) |
date_ |
date | yes | Date approved |
date_ |
date | yes | Date closed (null if open) |
project_ |
double | yes | Total project cost (USD) |
federal_ |
double | yes | Federal share obligated (USD) |
cost_ |
double | yes | Federal cost-share fraction (e.g., 0.75) |
nfip_claims · table¶
Paid NFIP flood-insurance claims — amount_paid_building, amount_paid_contents, and amount_paid_icc per claim, plus total_building_coverage/total_contents_coverage for exposure context. One row per claim, partitioned by year of loss. Distinct from nfip_policies (policy-term coverage/premium transactions, not realized losses). Joins to geo.counties via county_fips and to disaster_declarations / wildfire_perimeters by county+date overlap. Source: fema.gov/api/open/v2/FimaNfipClaims. DEPRECATION WARNING (confirmed live 2026-09-11, D-283): OpenFEMA has deprecated this exact endpoint - its own API response metadata states the dataset is frozen (no further updates) since 2026-06-01 and will stop being served entirely after 2026-10-15, in favor of a renamed v3 dataset ("nfip-redacted-claims-v3", FIMA prefix dropped). Migrating this table's source URL/schema to the v3 dataset before that date is a separate, tracked follow-up - incremental updates will silently stop working once the old endpoint is pulled, though history already materialized here is unaffected.
| Column | Type | Null | Description |
|---|---|---|---|
state_ |
string | yes | 2-digit state FIPS code (FK to geo.states) |
county_ |
string | yes | 5-digit county FIPS code (FK to geo.counties) |
state_ |
string | yes | 2-char state abbreviation |
date_ |
date | yes | Date of flood loss |
flood_ |
string | yes | FEMA rated flood zone (AE, VE, X, etc.) |
cause_ |
string | yes | Cause-of-damage code |
occupancy_ |
integer | yes | Occupancy type code (1=single family, etc.) |
policy_ |
integer | yes | Number of policies represented by this claim record |
amount_ |
double | yes | Amount paid on building claim (USD) |
amount_ |
double | yes | Amount paid on contents claim (USD) |
amount_ |
double | yes | Amount paid on Increased Cost of Compliance claim (USD) |
total_ |
double | yes | Total building insurance coverage (USD) |
total_ |
double | yes | Total contents insurance coverage (USD) |
census_ |
string | yes | 11-digit census tract FIPS |
reported_ |
string | yes | Reported ZIP code |
latitude |
double | yes | Approximate latitude (to 1 decimal per FEMA privacy rules) |
longitude |
double | yes | Approximate longitude |
id |
string | no | OpenFEMA row UUID (PK) |
nfip_policies · table¶
NFIP flood-insurance policy transactions — policy_cost, total_insurance_premium, total_building_coverage, and total_contents_coverage per policy term. One row per policy term. VERY LARGE (~74M rows all-history; several million per year). Partitioned by policy effective year. dqRowLimit caps DQ sampling; prod loads everything. Distinct from nfip_claims (realized paid losses, not premium/coverage). Joins to geo.counties via county_fips and census_tract. Source: fema.gov/api/open/v2/FimaNfipPolicies.
| Column | Type | Null | Description |
|---|---|---|---|
state_ |
string | yes | 2-digit state FIPS code (FK to geo.states) |
county_ |
string | yes | 5-digit county FIPS code (FK to geo.counties) |
property_ |
string | yes | 2-char property state abbreviation |
policy_ |
date | yes | Policy effective date (drives the year partition) |
policy_ |
date | yes | Policy termination date |
flood_ |
string | yes | FEMA rated flood zone |
occupancy_ |
integer | yes | Occupancy type code |
policy_ |
integer | yes | Number of policies represented |
policy_ |
double | yes | Policy cost (USD) |
total_ |
double | yes | Total insurance premium of the policy (USD) |
total_ |
double | yes | Total building insurance coverage (USD) |
total_ |
double | yes | Total contents insurance coverage (USD) |
federal_ |
double | yes | Federal policy fee (USD) |
census_ |
string | yes | 11-digit census tract FIPS |
reported_ |
string | yes | Reported ZIP code |
latitude |
double | yes | Approximate latitude |
longitude |
double | yes | Approximate longitude |
id |
string | no | OpenFEMA row UUID (PK) |
storm_events · table¶
NOAA NCEI Storm Events Database — one row per storm event detail record (tornadoes, floods, hurricanes, hail, winter storms, wildfires, etc.) with event magnitude, direct/indirect deaths and injuries, and property/crop damage (damage_property, damage_crops; converted from K/M/B notation to USD). Partitioned by year, streamed from the per-year NCEI gzip CSV. Joins to geo.counties and to disaster_declarations by county+date overlap. state_fips/county_fips (D-284): NOAA's own STATE_FIPS uses 4 non-standard codes for Puerto Rico/Virgin Islands/Guam/American Samoa (99/96/98/97) instead of their real Census FIPS (72/78/66/60) — remapped so these join geo.states like every other state. Confirmed live this also recovers county_fips for Puerto Rico and the Virgin Islands specifically (their CZ_FIPS values are real Census municipio/district codes once paired with the corrected state code — 7,905/7,905 and 60/60 county-type rows now join geo.counties). Guam and American Samoa's CZ_FIPS values are NOT real Census codes even after the state remap (e.g. Guam's storm_events rows use "002"/"003"/"006", but geo.counties only carries a single unified "66010"; American Samoa uses "002"/"003" against geo.counties' real five-district "010"-"050") — NOAA evidently numbers these two territories' zones on its own internal scheme with no Census county-equivalent mapping. Puerto Rico has a smaller, closed variant of the same problem: 6 codes (72002/72004/72006/72008/72010/72012, 331 rows total across 2004-2008 only) are NWS PR public-zone codes NCEI stamped as CZ_TYPE='C' rather than 'Z' — interleaved with real odd-numbered municipio FIPS (72001, 72003, ...) in the same 2004-2008 window before NCEI switched to real-FIPS-only from 2009 onward. Because a zone spans multiple municipios there is no 1:1 remap that could recover a real municipio code from a zone code, so StormEventsStreamingTransformer explicitly nulls county_fips for these 6 values instead of emitting an unresolvable FIPS. A second, unrelated residual: for a small number of county-type rows in the 50 states (confirmed live: 72 in Alaska across this table's full history, 49 in Georgia isolated to Jan-2025-published rows, 1 in Louisiana), CZ_FIPS itself doesn't match any real county FIPS at all — e.g. Georgia's early-2025 batch pairs real county names like "WALKER" with CZ_FIPS values ("2", "4", "6"...) that belong to no actual Georgia county. Confirmed this is wrong in NOAA's own published file, not an artifact of this ingest. Neither residual has a crosswalk that recovers the correct FIPS from what NOAA actually published; a LEFT JOIN to geo.counties silently drops these rows, expect the gap rather than treating it as a join bug.
| Column | Type | Null | Description |
|---|---|---|---|
event_ |
string | no | NCEI event id (PK) |
episode_ |
string | yes | Episode id (groups related events) |
begin_ |
date | yes | Event begin date |
end_ |
date | yes | Event end date |
state_ |
string | yes | 2-digit state FIPS code (FK to geo.states) |
county_ |
string | yes | 5-digit county FIPS code (FK to geo.counties) |
state_ |
string | yes | State name (as published by NCEI) |
event_ |
string | yes | Tornado / Flash Flood / Hurricane / Blizzard / etc. |
cz_ |
string | yes | C (county), Z (NWS zone), M (marine) |
magnitude |
double | yes | Event magnitude (wind speed, hail size; type-dependent) |
magnitude_ |
string | yes | EG/ES/MS/MG magnitude type |
injuries_ |
integer | yes | Direct injuries |
injuries_ |
integer | yes | Indirect injuries |
deaths_ |
integer | yes | Direct fatalities |
deaths_ |
integer | yes | Indirect fatalities |
damage_ |
double | yes | Property damage USD (converted from K/M/B notation) |
damage_ |
double | yes | Crop damage USD (converted from K/M/B notation) |
begin_ |
double | yes | Event begin latitude |
begin_ |
double | yes | Event begin longitude |
wildfire_perimeters · table¶
NIFC WFIGS Interagency wildfire perimeters — one row per fire perimeter (~39k features) with a simplified WGS84 perimeter polygon (geometry_wkt), gis_acres/final_acres burned area, fire_cause (Human / Natural / Undetermined), and point-of-origin land ownership (landowner_kind: Federal / Private / Other; landowner_category: USFS, BLM, NPS, BIA, State, Tribal, City, County, etc.) and jurisdictional_agency — enabling federal-vs-nonfederal burn comparisons directly from this table. Snapshot table with no year partition (refreshed via overwritePartitions); fire_year is derived from the fire discovery date. Joins to geo.counties via county_fips (from point-of-origin FIPS) and to nfip_claims for post-fire flood exposure. Source: WFIGS_Interagency_Perimeters/FeatureServer/0.
| Column | Type | Null | Description |
|---|---|---|---|
incident_ |
string | no | WFIGS IRWIN id (PK) |
incident_ |
string | yes | Fire incident name |
unique_ |
string | yes | NIFC unique fire identifier (year-unit-localnumber) |
fire_ |
integer | yes | Year derived from fire discovery date |
discovery_ |
date | yes | Fire discovery datetime (date part) |
containment_ |
date | yes | Containment datetime (date part; null if active) |
state_ |
string | yes | 2-digit state FIPS code (FK to geo.states) |
county_ |
string | yes | 5-digit county FIPS code (FK to geo.counties) |
poo_ |
string | yes | Point-of-origin state (2-char abbreviation) |
landowner_ |
string | yes | Point-of-origin land ownership, top-level (Federal / Private / Other), from WFIGS attr_POOLandownerKind — the field a federal-vs-nonfederal wildfire comparison should filter/group on. |
landowner_ |
string | yes | Point-of-origin land ownership, agency-level detail (USFS, BLM, NPS, BIA, State, Private, Tribal, City, County, DOD, BOR, DOE, USFWS, etc.), from WFIGS attr_POOLandownerCategory. |
jurisdictional_ |
string | yes | Agency with fire-management jurisdiction at point of origin (may differ from landowner_category under cooperative protection agreements), from WFIGS attr_POOJurisdictionalAgency. |
fire_ |
string | yes | Human / Natural / Undetermined |
incident_ |
string | yes | WF (wildfire), RX (prescribed), etc. |
gis_ |
double | yes | GIS-calculated perimeter area in acres |
final_ |
double | yes | Reported final fire size in acres |
centroid_ |
double | yes | Perimeter centroid latitude (WGS84) |
centroid_ |
double | yes | Perimeter centroid longitude (WGS84) |
geometry_ |
string | yes | Simplified WGS84 perimeter polygon as WKT (display/spatial join) |
wildfire_risk_by_county · table¶
USFS Wildfire Risk to Communities (WRC) county-level summary — one row per county, from the workbook's own "Counties" sheet (3,144 rows, one per US county/equivalent). Forward-looking hazard/exposure measures, not after-the-fact burn perimeters (see wildfire_perimeters for those). Metrics are building-fraction-based (buildings_fraction_minimal/indirect/direct_exposure), not raw Census housing-unit counts — the closest federal equivalent to a "housing-unit risk" statistic, but a building-count proxy rather than a literal housing-unit statistic. burn_probability_ and risk__rank are state/national percentile ranks (0-1), not raw probabilities. Complements disasters.wildfire_perimeters (which records only fires that already happened) with a genuinely forward-looking hazard measure. Joins to geo.counties via county_fips. Source: wildfirerisk.org (official USFS product, mandated by the 2018 Consolidated Appropriations Act; hosted off the .fs.usda.gov domain, same acceptable pattern already used for housing.hud_subsidized_housing).
| Column | Type | Null | Description |
|---|---|---|---|
state_ |
string | yes | 2-digit state FIPS code (FK to geo.states) |
county_ |
string | yes | 5-digit county FIPS code (FK to geo.counties) |
state_ |
string | yes | 2-letter USPS state abbreviation |
county_ |
string | yes | County name with state suffix, e.g. "Autauga County, AL" |
total_ |
long | yes | Total building count in the county (WRC's own building inventory) |
buildings_ |
double | yes | Fraction (0-1) of county buildings in the Minimal wildfire Exposure zone |
buildings_ |
double | yes | Fraction (0-1) of county buildings in the Indirect wildfire Exposure zone (exposed to ember cast / radiant heat from a nearby fire, not direct flame contact) |
buildings_ |
double | yes | Fraction (0-1) of county buildings in the Direct wildfire Exposure zone |
burn_ |
double | yes | Annual burn probability, percentile rank (0-1) within the county's state |
burn_ |
double | yes | Annual burn probability, percentile rank (0-1) nationally |
risk_ |
double | yes | Risk to structures (burn probability x exposure x building value), percentile rank (0-1) within state |
risk_ |
double | yes | Risk to structures, percentile rank (0-1) nationally |
fema_nri_earthquake_by_county · table¶
FEMA National Risk Index (NRI) county-level earthquake hazard, exposure, and expected-annual-loss data — one row per county (3,142 rows). Verified live 2026-09-11: San Francisco County CA earthquake_risk_rating "Relatively High" (San Andreas exposure); Story County IA "Very Low" (mid-continent). Also carries the NRI's overall all-hazard composite (risk_score/risk_rating/risk_national_pctl/risk_state_pctl, eal_score/eal_rating) for context, plus population/building_value/agriculture_value/ area for normalizing the earthquake measures. The source layer carries 17 other hazard blocks (wildfire, hurricane, flood, tornado, etc.) on the same 367-field schema — not ingested here; a follow-on sourcing item, not a gap in this table. Hosted on FEMA's own ArcGIS Online tenant (services.arcgis.com/XG15cJAlne2vxtgt), not the .fema.gov domain itself — same accepted "agency-hosted ArcGIS mirror" pattern as housing.opportunity_zones; re-verify the item id if this 404s (the canonical hazards.fema.gov/nri URLs only redirect to a landing page, not this data). Joins to geo.counties via county_fips.
| Column | Type | Null | Description |
|---|---|---|---|
state_ |
string | yes | 2-digit state FIPS code (FK to geo.states) |
county_ |
string | yes | 5-digit county FIPS code (FK to geo.counties) |
state_ |
string | yes | Full state name, as published by FEMA NRI |
state_ |
string | yes | 2-letter USPS state abbreviation |
county_ |
string | yes | County name (no state suffix) |
population |
int | yes | County population (FEMA NRI's own 2016 vintage population figure) |
building_ |
double | yes | Total building value in the county (USD), FEMA NRI's own building inventory |
agriculture_ |
double | yes | Total agricultural value in the county (USD) |
area_ |
double | yes | County land area in square miles |
risk_ |
double | yes | NRI overall all-hazard composite risk score (not earthquake-specific) |
risk_ |
string | yes | NRI overall all-hazard composite risk rating (e.g. "Relatively High") |
risk_ |
double | yes | NRI overall composite risk, national percentile (0-100) |
risk_ |
double | yes | NRI overall composite risk, percentile within the county's own state (0-100) |
eal_ |
double | yes | NRI overall all-hazard composite expected-annual-loss score |
eal_ |
string | yes | NRI overall all-hazard composite expected-annual-loss rating |
earthquake_ |
double | yes | Historic earthquake event count used in NRI's earthquake model; null when FEMA has none on record for the county |
earthquake_ |
double | yes | Annualized frequency of a damaging earthquake for the county |
earthquake_ |
double | yes | Building value (USD) exposed to earthquake hazard |
earthquake_ |
double | yes | Population exposed to earthquake hazard |
earthquake_ |
double | yes | Population converted to a USD-equivalent exposure value (FEMA NRI methodology) |
earthquake_ |
double | yes | Total exposure (USD) — building value plus population equivalence |
earthquake_ |
double | yes | Historic earthquake loss ratio for buildings |
earthquake_ |
double | yes | Historic earthquake loss ratio for population |
earthquake_ |
string | yes | Historic earthquake loss ratio, overall rating |
earthquake_ |
double | yes | Expected annual loss (USD) to buildings from earthquake |
earthquake_ |
double | yes | Expected annual loss (population-equivalent units) from earthquake |
earthquake_ |
double | yes | Expected annual population loss converted to a USD-equivalent value |
earthquake_ |
double | yes | Total expected annual loss (USD) from earthquake |
earthquake_ |
double | yes | Earthquake expected-annual-loss score |
earthquake_ |
string | yes | Earthquake expected-annual-loss rating (e.g. "Very Low", "Relatively High") |
earthquake_ |
double | yes | Earthquake-specific hazard-type risk index score |
earthquake_ |
string | yes | Earthquake-specific hazard-type risk index rating — the primary column for "which counties have high earthquake risk" questions (ops#191's original ask) |
disasters_declarations_by_state_year · view¶
Annual disaster-declaration counts by state, incident_type, and year — disaster_count plus counts of declarations with IA (Individual Assistance, sourced from ih_program_declared - see disaster_declarations' own column comment, D-285: the source field behind ia_program_declared itself is dead at FEMA) and PA (Public Assistance) programs declared. Aggregates the county-program rows in disaster_declarations to distinct disasters per state/year/incident_type. Ready to join sec.filing_metadata on state for risk-disclosure research.
View — columns are resolved by the query engine at runtime.
disasters_pa_obligated_by_county · view¶
Public Assistance federal obligations aggregated by county, incident_type, and declaration year — project_count, total_project_amount, and total_federal_obligated (USD). Joins to geo.counties for mapping; a natural next join is to econ employment data for disaster-affected labor-market analysis.
View — columns are resolved by the query engine at runtime.
disasters_nfip_claims_by_county · view¶
Paid NFIP flood claims aggregated by county and year of loss — claim_count, total_paid_building, total_paid_contents, and total_paid (USD). Key measure of insured flood loss; joins to disaster_declarations and wildfire_perimeters by county for post-event exposure analysis.
View — columns are resolved by the query engine at runtime.
disasters_storm_impact_by_county · view¶
Storm-event human and economic impact aggregated by county and year — event_count, total_deaths, total_injuries, total_property_damage, and total_crop_damage (USD). Joins to disaster_declarations by county to link storm episodes to FEMA declarations, and to econ employment data for economic-impact research.
View — columns are resolved by the query engine at runtime.