Hurricane Data
This wiki page walks through how to create a census block group level by date table with an indicator for the highest intensity of any tropical cyclone that the geography encountered that day using the last ten years' worth of NOAA's global hurricane tracks data in Google Cloud's BigQuery table bigquery-public-data.noaa_hurricanes.hurricanes.
Creating this table will enable you to analyze customer behavior pre/post major storm activity.
The National Oceanic and Atmospheric Administration (NOAA) is an American scientific agency within the United States Department of Commerce that focuses on the conditions of the oceans, major waterways, and the atmosphere. NOAA warns of dangerous weather, charts seas, guides the use and protection of ocean and coastal resources and conducts research to provide the understanding and improve stewardship of the environment.
Note: some of this descriptive content is copy/paste from the NOAA/other sources and consolidated here in an effort to create a quick learning guide.
Saffir–Simpson Hurricane Wind Scale (SSHWS)
The SSHWS is a 1 to 5 rating based on a hurricane's sustained wind speed. This scale estimates potential property damage. Hurricanes reaching Category 3 and higher are considered major hurricanes because of their potential for significant loss of life and damage. Category 1 and 2 storms are still dangerous, however, and require preventative measures.
A tropical cyclone is a rotating low-pressure weather system that has organized thunderstorms but no fronts (a boundary separating two air masses of different densities). Tropical cyclones with maximum one-minute sustained surface winds of less than 39 miles per hour (mph) are called tropical depressions. Those with maximum one-minute sustained winds of 39 mph or higher are called tropical storms. When a storm's maximum one-minute sustained winds reach 74 mph, it is called a hurricane.
-
Scales:
CategoryDamagem/sknots (kn)mphkm/hFiveCatastrophic≥ 70 m/s≥ 137 kn≥ 157 mph≥ 252 km/hFourCatastrophic58–70 m/s113–136 kn130–156 mph209–251 km/hThreeDevastating50–58 m/s96–112 kn111–129 mph178–208 km/hTwoExtensive43–49 m/s83–95 kn96–110 mph154–177 km/hOneSome33–42 m/s64–82 kn74–95 mph119–153 km/hTropical Storm18–32 m/s34–63 kn39–73 mph63–118 km/hTropical Depression≤ 17 m/s≤ 33 kn≤ 38 mph≤ 62 km/h
-
Category One
Very dangerous winds will produce some damage: Well-constructed frame homes could have damage to roof, shingles, vinyl siding and gutters. Large branches of trees will snap and shallowly rooted trees may be toppled. Extensive damage to power lines and poles likely will result in power outages that could last a few to several days.
-
Category Two
Extremely dangerous winds will cause extensive damage: Well-constructed frame homes could sustain major roof and siding damage. Many shallowly rooted trees will be snapped or uprooted and block numerous roads. Near-total power loss is expected with outages that could last from several days to weeks.
-
Category Three
Devastating damage will occur: Well-built framed homes may incur major damage or removal of roof decking and gable ends. Many trees will be snapped or uprooted, blocking numerous roads. Electricity and water will be unavailable for several days to weeks after the storm passes.
-
Category Four
Catastrophic damage will occur: Well-built framed homes can sustain severe damage with loss of most of the roof structure and/or some exterior walls. Most trees will be snapped or uprooted and power poles downed. Fallen trees and power poles will isolate residential areas. Power outages will last weeks to possibly months. Most of the area will be uninhabitable for weeks or months.
-
Category Five
Catastrophic damage will occur: A high percentage of framed homes will be destroyed, with total roof failure and wall collapse. Fallen trees and power poles will isolate residential areas. Power outages will last for weeks to possibly months. Most of the area will be uninhabitable for weeks or months.
-
Census block group table that lists the highest storm wind by date (rolling ten years).
GEOID_BLK_GRPSTORM_DTSIDNAMESSHS1205700650232017-09-112017242N16333IRMACAT14820131400222017-08-282017228N14314HARVEYTropical Storm1200500200012018-10-102018280N18273MICHAELCAT57211307130032017-09-202017260N12310MARIACAT41207104031222017-09-102017242N16333IRMACAT3-
GEOID_BLK_GRP: Unique identifier for each census block group. A concatenation of the current state FIPS code, county FIPS code, census tract code, and block group code (first character of block code). -
STORM_DT: Date the tropical cyclone hit the census block group. -
SID: Storm ID of the storm with the greatest winds that day. -
NAME: Name of the storm with the greatest winds that day. -
SSHS: Highest wind classification for that day. Tropical storm or Saffir-Simpson hurricane wind scale category 1-5.
-
-
Census block group level table with flags that indicate whether it was struck by a tropical cyclone in the past 90 days.
This view aggregates the data from the above table with 90 day indicator columns.
CREATE OR REPLACE VIEW `blkgrp_hurr_r3` AS SELECT A.geo_id GEOID_BLK_GRP , MAX(CASE WHEN B.GEOID_BLK_GRP IS NOT NULL THEN 1 ELSE 0 END) HURR_90DAY_IND , MAX(CASE WHEN B.SSHS = 'Tropical Storm' THEN 1 ELSE 0 END) HURR_90DAY_TS , MAX(CASE WHEN B.SSHS = 'CAT1' THEN 1 ELSE 0 END) HURR_90DAY_F1 , MAX(CASE WHEN B.SSHS = 'CAT2' THEN 1 ELSE 0 END) HURR_90DAY_F2 , MAX(CASE WHEN B.SSHS = 'CAT3' THEN 1 ELSE 0 END) HURR_90DAY_F3 , MAX(CASE WHEN B.SSHS = 'CAT4' THEN 1 ELSE 0 END) HURR_90DAY_F4 , MAX(CASE WHEN B.SSHS = 'CAT5' THEN 1 ELSE 0 END) HURR_90DAY_F5 FROM `bigquery-public-data.geo_census_blockgroups.blockgroups_*` A LEFT JOIN `blkgrp_hurr` B ON A.geo_id = B.GEOID_BLK_GRP AND STORM_DT BETWEEN DATE_SUB(CURRENT_DATE("America/New_York"), INTERVAL 90 DAY) AND CURRENT_DATE("America/New_York") GROUP BY 1;
GEOID_BLK_GRPHURR_90DAY_INDHURR_90DAY_TSHURR_90DAY_F1HURR_90DAY_F2HURR_90DAY_F3HURR_90DAY_F4HURR_90DAY_F5212219702003000000011001002302111000002200395050031010010
This source data contains the most complete global set of historical tropical cyclones available. The NOAA's IBTrACS provides this tropical cyclone best track data in a centralized location to aid the understanding of the distribution, frequency, and intensity of tropical cyclones worldwide:
The historical positions and intensities along the tracks of global tropical cyclones (TC) are provided by NOAA’s International Best Track Archive for Climate Stewardship (IBTrACS). Tropical Cyclones are known as hurricanes in the north Atlantic and northeast Pacific ocean basins, typhoons in the northwest Pacific ocean basin, cyclones in the north and south Indian Ocean basins, and tropical cyclones in the southwest Pacific ocean basin. IBTrACS collects data about TCs reported by international monitoring centers who have a responsibility to forecast and report on TCs (and also includes some important historical datasets). Presently, IBTrACS includes data from 9 different countries. Historically, the data describing these systems has included best estimates of their track and intensity (hence the term, best track).
-
Sample Data:
SIDNAMEISO_TIMEDIST2LANDUSA_RMWUSA_WINDLONGITUDELATITUDE... 2014284N10231ANA2014-10-18 12:00:00 UTC2142070-157.718.22019246N15225AKONI2019-09-04 12:00:00 UTC17146025-140.013.52018218N18134YAGI2018-08-09 21:00:00 UTC9678037132.58322.52282015068S12151NATHAN2015-03-11 00:00:00 UTC2174045146.8-13.752016041S14170WINSTON2016-02-21 12:00:00 UTC42512115173.2-17.6-
SID: Storm Identifier. -
NAME: Name provided by the agency. IBTrACS ignores most names that include digits or abbreviations. -
ISO_TIME: ISO Time provided in Universal Time Coordinates (UTC). Format is YYYY-MM-DD HH:mm:ss Most points are provided at 6 hour intervals. Some agencies provided 3 hour points (e.g., New Delhi) or times at important observations (e.g., landfall times in the North Atlantic, etc.). -
DIST2LAND: Distance to land from the current position. The land dataset includes all continents and any islands larger than XX. The distance is the nearest at the present time in any direction. -
USA_RMW: radius of max winds, 0 - 999 n mi. NOT BEST TRACKED (not reanalyzed) -
USA_WIND: Maximum sustained wind speed in knots: 0 - 300 kts. -
LONGITUDE: -
LATITUDE: -
etc..: 12 columns that are the wind radii maximum extent in the direction of specific quadrants with a specific knot indicator. ExampleUSA_R34_NE= 34 kt wind radii maximum extent in northeastern quadrant.
-
The simplified explanation of this process is taking each tropical cyclone's data point and tagging all census block group centroids that are within 'x' miles of the data point, 'x' defined by the distance the wind intensity can reach.
-
t01: create base data
In this step, we grab the past ten years' worth of data that we need as well as calculate a few fields /
LEAD()several columns by the storm ID ordered by the timestamp. All of the lead attributes are the subsequent data point in time for the storm. This will allow us to later see if the distance between the two data points is too great to where we'll need to interpolate additional data points.SELECT SID, NAME , EXTRACT(DATE FROM iso_time) STORM_DT , dist2land - usa_rmw LAND_AFFECT , ISO_TIME , USA_WIND, usa_rmw , LEAD(USA_WIND) OVER(PARTITION BY SID ORDER BY iso_time) USA_WIND2 , LEAD(usa_rmw) OVER(PARTITION BY SID ORDER BY iso_time) usa_rmw2 , LONGITUDE, LATITUDE , LEAD(LONGITUDE) OVER(PARTITION BY SID ORDER BY iso_time) LONG2 , LEAD(LATITUDE) OVER(PARTITION BY SID ORDER BY iso_time) LAT2 , usa_r34_ne, usa_r34_nw, usa_r34_se, usa_r34_sw , usa_r50_ne, usa_r50_nw, usa_r50_se, usa_r50_sw , usa_r64_ne, usa_r64_nw, usa_r64_se, usa_r64_sw , LEAD(usa_r34_ne) OVER(PARTITION BY SID ORDER BY iso_time) usa_r34_ne2 , LEAD(usa_r34_nw) OVER(PARTITION BY SID ORDER BY iso_time) usa_r34_nw2 , LEAD(usa_r34_se) OVER(PARTITION BY SID ORDER BY iso_time) usa_r34_se2 , LEAD(usa_r34_sw) OVER(PARTITION BY SID ORDER BY iso_time) usa_r34_sw2 , LEAD(usa_r50_ne) OVER(PARTITION BY SID ORDER BY iso_time) usa_r50_ne2 , LEAD(usa_r50_nw) OVER(PARTITION BY SID ORDER BY iso_time) usa_r50_nw2 , LEAD(usa_r50_se) OVER(PARTITION BY SID ORDER BY iso_time) usa_r50_se2 , LEAD(usa_r50_sw) OVER(PARTITION BY SID ORDER BY iso_time) usa_r50_sw2 , LEAD(usa_r64_ne) OVER(PARTITION BY SID ORDER BY iso_time) usa_r64_ne2 , LEAD(usa_r64_nw) OVER(PARTITION BY SID ORDER BY iso_time) usa_r64_nw2 , LEAD(usa_r64_se) OVER(PARTITION BY SID ORDER BY iso_time) usa_r64_se2 , LEAD(usa_r64_sw) OVER(PARTITION BY SID ORDER BY iso_time) usa_r64_sw2 , ( SELECT MIN(x) FROM UNNEST( [ usa_rmw , usa_r34_ne, usa_r34_nw, usa_r34_se, usa_r34_sw , usa_r50_ne, usa_r50_nw, usa_r50_se, usa_r50_sw , usa_r64_ne, usa_r64_nw, usa_r64_se, usa_r64_sw ] ) x ) MIN_DIST -- Minimum Distance FROM `bigquery-public-data.noaa_hurricanes.hurricanes` WHERE CAST(SEASON AS INT64) >= ( EXTRACT(YEAR FROM CURRENT_DATE) - 10 ) AND USA_WIND IS NOT NULL LIMIT 5;
-
t02: generate additional coordinates if needed
In this step, we run all of our coordinates through our
udf_intrpl_latlon()function which generates a linestring between our current point and the next point if the distance between them is shorter than the minimum wind affect difference. This is an important step to ensure that we're not overlooking tagging census block groups that have been impacted by a tropical storm simply because the NOAA data had gaps between coordinates. So we use this function to generate coordinates between them by using the forward azimuth and assuming the storm's path between the two coordinates is a straight line.Then we remove all final points that aren't close enough to affect land.
SELECT A.* , ( ST_DISTANCE(ST_GEOGPOINT( LONGITUDE, LATITUDE) , ST_GEOGPOINT(LONG2, LAT2 ))/ 1609.344) DIST , INTRPL , OFFSET FROM t01 A , UNNEST( `udf.udf_intrpl_latlon` (LATITUDE, LONGITUDE, LAT2, LONG2,MIN_DIST) ) INTRPL WITH OFFSET WHERE LAND_AFFECT <= 0;
-
t03: interpolate wind values for generated coordinates
For every coordinate that we generated in
t02, we interpolate the wind values by slowly increasing/decreasing between the values from the original coordinates that we used to generate the new coordinates.For example, if we created
coor Bmidway betweencoor Aandcoor C, then for wind variableusa_r34_ne, we take the midway between A and C's value forusa_r34_ne.SELECT SID, NAME , STORM_DT , ISO_TIME , INTRPL.LONG , INTRPL.LAT , ST_GEOGPOINT( INTRPL.LONG, INTRPL.LAT) point , USA_WIND + COALESCE(( ((USA_WIND2-USA_WIND)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) USA_WIND , usa_r34_ne + COALESCE(( ((usa_r34_ne2-usa_r34_ne)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_r34_ne , usa_r34_nw + COALESCE(( ((usa_r34_nw2-usa_r34_nw)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_r34_nw , usa_r34_se + COALESCE(( ((usa_r34_se2-usa_r34_se)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_r34_se , usa_r34_sw + COALESCE(( ((usa_r34_sw2-usa_r34_sw)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_r34_sw , usa_r50_ne + COALESCE(( ((usa_r50_ne2-usa_r50_ne)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_r50_ne , usa_r50_nw + COALESCE(( ((usa_r50_nw2-usa_r50_nw)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_r50_nw , usa_r50_se + COALESCE(( ((usa_r50_se2-usa_r50_se)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_r50_se , usa_r50_sw + COALESCE(( ((usa_r50_sw2-usa_r50_sw)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_r50_sw , usa_r64_ne + COALESCE(( ((usa_r64_ne2-usa_r64_ne)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_r64_ne , usa_r64_nw + COALESCE(( ((usa_r64_nw2-usa_r64_nw)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_r64_nw , usa_r64_se + COALESCE(( ((usa_r64_se2-usa_r64_se)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_r64_se , usa_r64_sw + COALESCE(( ((usa_r64_sw2-usa_r64_sw)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_r64_sw , usa_rmw + COALESCE(( ((usa_rmw2-usa_rmw)/(1 + floor(DIST/MIN_DIST))) * OFFSET),0) usa_rmw FROM t02 A;
-
t04: Create geog points for every U.S. Census Block Group's centroid.
SELECT A.GEO_ID , ST_GEOGPOINT( internal_point_lon, internal_point_lat) point , internal_point_lon , internal_point_lat FROM `bigquery-public-data.geo_census_blockgroups.blockgroups_*` A;
-
t05: crossjoin all U.S. Census Block Centroids with all of our hurricane coordinates
In this step, we calculate the distance between each point as well as the bearing at which the hurricane is headed.
SELECT A.* , B.* , (ST_DISTANCE(A.point, B.point) / 1609.344) DIST_MI , `udf.udf_bearing` (A.LAT, A.LONG, B.internal_point_lat, B.internal_point_lon) BEARING FROM t03 A , t04 B ;
-
t06: Calculate the maximum wind speed that affected each census block
Using the bearing of the hurricane, we check all wind columns that are in that direction and apply that wind affect as affecting that census block group if the distance of the wind affect is within range of the block group.
For example, if a hurricane has a bearing of 78 degrees, then the top wind speed from the northeast direction columns is applied.
SELECT GEO_ID GEOID_BLK_GRP , SID, NAME , STORM_DT , MAX( CASE WHEN DIST_MI <= ( SELECT MAX(x) FROM UNNEST( [ CASE WHEN (BEARING BETWEEN 0 AND 90 OR BEARING = 360) THEN (SELECT MAX(y) FROM UNNEST([usa_r34_ne,usa_r50_ne , CASE WHEN USA_WIND BETWEEN 34 AND 63.9999 AND COALESCE(usa_r34_ne,usa_r50_ne,usa_r64_ne) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN BEARING BETWEEN 90 AND 180 THEN (SELECT MAX(y) FROM UNNEST([usa_r34_se,usa_r50_se , CASE WHEN USA_WIND BETWEEN 34 AND 63.9999 AND COALESCE(usa_r34_se,usa_r50_se,usa_r64_se) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN BEARING BETWEEN 180 AND 270 THEN (SELECT MAX(y) FROM UNNEST([usa_r34_sw,usa_r50_sw , CASE WHEN USA_WIND BETWEEN 34 AND 63.9999 AND COALESCE(usa_r34_sw,usa_r50_sw,usa_r64_sw) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN (BEARING BETWEEN 270 AND 360 OR BEARING = 0) THEN (SELECT MAX(y) FROM UNNEST([usa_r34_nw,usa_r50_nw , CASE WHEN USA_WIND BETWEEN 34 AND 63.9999 AND COALESCE(usa_r34_nw,usa_r50_nw,usa_r64_nw) IS NOT NULL THEN usa_rmw END]) y) END ]) x) THEN 1 ELSE 0 END) HURR_TS , MAX( CASE WHEN DIST_MI <= ( SELECT MAX(x) FROM UNNEST( [ CASE WHEN (BEARING BETWEEN 0 AND 90 OR BEARING = 360) THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 64 AND 82.9999 THEN usa_r64_ne END , CASE WHEN USA_WIND BETWEEN 64 AND 82.9999 AND COALESCE(usa_r34_ne,usa_r50_ne,usa_r64_ne) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN BEARING BETWEEN 90 AND 180 THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 64 AND 82.9999 THEN usa_r64_se END , CASE WHEN USA_WIND BETWEEN 64 AND 82.9999 AND COALESCE(usa_r34_se,usa_r50_se,usa_r64_se) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN BEARING BETWEEN 180 AND 270 THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 64 AND 82.9999 THEN usa_r64_sw END , CASE WHEN USA_WIND BETWEEN 64 AND 82.9999 AND COALESCE(usa_r34_sw,usa_r50_sw,usa_r64_sw) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN (BEARING BETWEEN 270 AND 360 OR BEARING = 0) THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 64 AND 82.9999 THEN usa_r64_nw END , CASE WHEN USA_WIND BETWEEN 64 AND 82.9999 AND COALESCE(usa_r34_nw,usa_r50_nw,usa_r64_nw) IS NOT NULL THEN usa_rmw END]) y) END ]) x) THEN 1 ELSE 0 END) HURR_F1 , MAX( CASE WHEN DIST_MI <= ( SELECT MAX(x) FROM UNNEST( [ CASE WHEN (BEARING BETWEEN 0 AND 90 OR BEARING = 360) THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 83 AND 95.9999 THEN usa_r64_ne END , CASE WHEN USA_WIND BETWEEN 83 AND 95.9999 AND COALESCE(usa_r34_ne,usa_r50_ne,usa_r64_ne) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN BEARING BETWEEN 90 AND 180 THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 83 AND 95.9999 THEN usa_r64_se END , CASE WHEN USA_WIND BETWEEN 83 AND 95.9999 AND COALESCE(usa_r34_se,usa_r50_se,usa_r64_se) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN BEARING BETWEEN 180 AND 270 THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 83 AND 95.9999 THEN usa_r64_sw END , CASE WHEN USA_WIND BETWEEN 83 AND 95.9999 AND COALESCE(usa_r34_sw,usa_r50_sw,usa_r64_sw) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN (BEARING BETWEEN 270 AND 360 OR BEARING = 0) THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 83 AND 95.9999 THEN usa_r64_nw END , CASE WHEN USA_WIND BETWEEN 83 AND 95.9999 AND COALESCE(usa_r34_nw,usa_r50_nw,usa_r64_nw) IS NOT NULL THEN usa_rmw END]) y) END ]) x) THEN 1 ELSE 0 END) HURR_F2 , MAX( CASE WHEN DIST_MI <= ( SELECT MAX(x) FROM UNNEST( [ CASE WHEN (BEARING BETWEEN 0 AND 90 OR BEARING = 360) THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 96 AND 112.999 THEN usa_r64_ne END , CASE WHEN USA_WIND BETWEEN 96 AND 112.999 AND COALESCE(usa_r34_ne,usa_r50_ne,usa_r64_ne) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN BEARING BETWEEN 90 AND 180 THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 96 AND 112.999 THEN usa_r64_se END , CASE WHEN USA_WIND BETWEEN 96 AND 112.999 AND COALESCE(usa_r34_se,usa_r50_se,usa_r64_se) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN BEARING BETWEEN 180 AND 270 THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 96 AND 112.999 THEN usa_r64_sw END , CASE WHEN USA_WIND BETWEEN 96 AND 112.999 AND COALESCE(usa_r34_sw,usa_r50_sw,usa_r64_sw) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN (BEARING BETWEEN 270 AND 360 OR BEARING = 0) THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 96 AND 112.999 THEN usa_r64_nw END , CASE WHEN USA_WIND BETWEEN 96 AND 112.999 AND COALESCE(usa_r34_nw,usa_r50_nw,usa_r64_nw) IS NOT NULL THEN usa_rmw END]) y) END ]) x) THEN 1 ELSE 0 END) HURR_F3 , MAX( CASE WHEN DIST_MI <= ( SELECT MAX(x) FROM UNNEST( [ CASE WHEN (BEARING BETWEEN 0 AND 90 OR BEARING = 360) THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 113 AND 136.999 THEN usa_r64_ne END , CASE WHEN USA_WIND BETWEEN 113 AND 136.999 AND COALESCE(usa_r34_ne,usa_r50_ne,usa_r64_ne) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN BEARING BETWEEN 90 AND 180 THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 113 AND 136.999 THEN usa_r64_se END , CASE WHEN USA_WIND BETWEEN 113 AND 136.999 AND COALESCE(usa_r34_se,usa_r50_se,usa_r64_se) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN BEARING BETWEEN 180 AND 270 THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 113 AND 136.999 THEN usa_r64_sw END , CASE WHEN USA_WIND BETWEEN 113 AND 136.999 AND COALESCE(usa_r34_sw,usa_r50_sw,usa_r64_sw) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN (BEARING BETWEEN 270 AND 360 OR BEARING = 0) THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND BETWEEN 113 AND 136.999 THEN usa_r64_nw END , CASE WHEN USA_WIND BETWEEN 113 AND 136.999 AND COALESCE(usa_r34_nw,usa_r50_nw,usa_r64_nw) IS NOT NULL THEN usa_rmw END]) y) END ]) x) THEN 1 ELSE 0 END) HURR_F4 , MAX( CASE WHEN DIST_MI <= ( SELECT MAX(x) FROM UNNEST( [ CASE WHEN (BEARING BETWEEN 0 AND 90 OR BEARING = 360) THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND >= 137 THEN usa_r64_ne END , CASE WHEN USA_WIND >= 137 AND COALESCE(usa_r34_ne,usa_r50_ne,usa_r64_ne) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN BEARING BETWEEN 90 AND 180 THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND >= 137 THEN usa_r64_se END , CASE WHEN USA_WIND >= 137 AND COALESCE(usa_r34_se,usa_r50_se,usa_r64_se) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN BEARING BETWEEN 180 AND 270 THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND >= 137 THEN usa_r64_sw END , CASE WHEN USA_WIND >= 137 AND COALESCE(usa_r34_sw,usa_r50_sw,usa_r64_sw) IS NOT NULL THEN usa_rmw END]) y) END , CASE WHEN (BEARING BETWEEN 270 AND 360 OR BEARING = 0) THEN (SELECT MAX(y) FROM UNNEST([ CASE WHEN USA_WIND >= 137 THEN usa_r64_nw END , CASE WHEN USA_WIND >= 137 AND COALESCE(usa_r34_nw,usa_r50_nw,usa_r64_nw) IS NOT NULL THEN usa_rmw END]) y) END ]) x) THEN 1 ELSE 0 END) HURR_F5 FROM t05 GROUP BY 1, 2, 3, 4 HAVING (HURR_TS + HURR_F1 + HURR_F2 + HURR_F3 + HURR_F4 + HURR_F5) > 0;
-
hurricanes: aggregate data up to the census block group by date level
We create the final table in this step assigning the Saffir-Simpson hurricane wind scale categories based on the top wind speeds that hit each census block group.
SELECT GEOID_BLK_GRP , STORM_DT , ARRAY_AGG( ( SELECT AS STRUCT SID, NAME , CASE WHEN HURR_F5 = 1 THEN 'CAT5' WHEN HURR_F4 = 1 THEN 'CAT4' WHEN HURR_F3 = 1 THEN 'CAT3' WHEN HURR_F2 = 1 THEN 'CAT2' WHEN HURR_F1 = 1 THEN 'CAT1' WHEN HURR_TS = 1 THEN 'Tropical Storm' END SSHS ) ORDER BY HURR_F5 DESC , HURR_F4 DESC , HURR_F3 DESC , HURR_F2 DESC , HURR_F1 DESC , HURR_TS DESC , SID LIMIT 1 )[SAFE_OFFSET(0)].* FROM t06 GROUP BY 1, 2;