Write Alerts Data Script

[ztephm]

Use CARTOFrames / CARTO Python SDK to:

• Extract real-time storm/flood alert data for Boston area from API
• Write the data to CARTO dataset (do not need to store old alerts)
• Join current data to geometry
  • this will be done w/SQL once data is in CARTO

[ztephm]

environment vars to set:

$ export CARTO_USER=stephaniemongon
$ export CARTO_API_KEY=use master until figure out which dataset will be updated
$ export CARTO_BASE_URL=https://$CARTO_USER.carto.com

to check:

$ echo $CARTO_USER
$ echo $CARTO_API_KEY
$ echo $CARTO_BASE_URL

[ztephm]

Time Conversion Tests

• NWS returns alert times in ISO8601DateTime
• ISO8601 +offset indicates the time zone offset to the east of Greenwich, England
• In Postgres POSIX time zone abbreviation, +offset indicates the time zone offset to the west of Greenwich
• POSIX abbreviations like UTC+7 do not take into account daylight savings time, so better to use time zone names like US/Eastern or America/Chicago

---

• original dataset: nws_alerts_pa.csv.zip

  • alert_end = 2019-08-06T12:15:00-04:00

• FINAL FOR UTC ALERT TIME: Convert UTC with offset to UTC

  UPDATE nws_alerts_pa 
  SET alert_end_utc = TO_TIMESTAMP(alert_end, 'YYYY-MM-DD HH24:MI:SS') 
  AT TIME ZONE 'UTC'
  

  • alert_end_utc = 2019-08-06T12:15:00Z
  • this just gives the UTC time that the alert is offset from, which is not the UTC time at the time event is happening

• Convert UTC with offset to UTC w/out offset, based on offset calculation

  UPDATE nws_alerts_pa 
  SET alert_end_alert_area_tz = TO_TIMESTAMP(alert_end, 'YYYY-MM-DD HH24:MI:SS') 
  AT TIME ZONE 'UTC-4'
  

  • alert_end_utc = 2019-08-06T16:15:00Z
  • incorrect because Postgres puts the UTC time 4 hours ahead instead of behind

• FINAL FOR ALERT ZONE LOCAL ALERT TIME: Convert UTC with offset to UTC w/out offset, based on reversed offset calculation

  UPDATE nws_alerts_pa 
  SET alert_end_alert_area_tz = TO_TIMESTAMP(alert_end, 'YYYY-MM-DD HH24:MI:SS') 
  AT TIME ZONE 'UTC+4'
  

  • alert_end_utc = 2019-08-06T08:15:00Z

• use time zone name to take daylight savings into account:

  UPDATE nws_alerts_pa 
  SET alert_end_alert_area_tz = TO_TIMESTAMP(alert_end, 'YYYY-MM-DD HH24:MI:SS') 
  AT TIME ZONE 'US/Eastern'
  

  • alert_end_utc = 2019-08-06T08:15:00Z
  • we shouldn't do this b/c we should assume the -XX:XX offset coming from NWS service already takes into account standard or daylight savings time, so we shouldn't need to convert that to UTC and then back again by zone name.

[ztephm]

SQL processes

should these be done in Python instead?

1. Create these columns in the imported dataset:

   • alert_end_utc
   • alert_end_alert_area_tz
   • alert_start_utc
   • alert_start_alert_area_tz

2. Translate alert times to UTC and alert area's local time:

   • alert_end_utc

     UPDATE nws_alerts_pa 
     SET alert_end_utc = TO_TIMESTAMP(alert_end, 'YYYY-MM-DD HH24:MI:SS') 
     AT TIME ZONE 'UTC'

   • alert_end_alert_area_tz
     use reverse offset, ie if service's ISO8601 offset is 'UTC-4' use 'UTC+4' in Postgres

     UPDATE nws_alerts_pa 
     SET alert_end_alert_area_tz = TO_TIMESTAMP(alert_end, 'YYYY-MM-DD HH24:MI:SS') 
     AT TIME ZONE 'UTC+4'

   • alert_start_utc

     UPDATE nws_alerts_pa 
     SET alert_start_utc = TO_TIMESTAMP(alert_start, 'YYYY-MM-DD HH24:MI:SS') 
     AT TIME ZONE 'UTC'

   • alert_start_alert_area_tz
     use reverse offset, ie if service's ISO8601 offset is 'UTC-4' use 'UTC+4' in Postgres

     UPDATE nws_alerts_pa 
     SET alert_start_alert_area_tz = TO_TIMESTAMP(alert_start, 'YYYY-MM-DD HH24:MI:SS') 
     AT TIME ZONE 'UTC+4'

3. Aggregate alerts by zone (use last one in next step)

   • Trim brackets & spaces from string of alert zones (too expensive to do in one step?)

     UPDATE nws_alerts_pa
     SET alert_zones = substr(replace(alert_zones,' ', ''), 2, length(alert_zones) - 2)

   • Split alert zones + attributes into separate rows:

     WITH da AS (
             SELECT alert_state, 
                    alert_event, 
                    alert_start, 
                    alert_start_utc, 
                    alert_start_alert_area_tz, 
                    alert_end, 
                    alert_end_utc, 
                    alert_end_alert_area_tz,
                    UNNEST(string_to_array(alert_zones, ',')) AS zone
             FROM nws_alerts_pa
     )
     SELECT * FROM da

   • Aggregate alert attributes by zone (without space after comma):

     WITH da AS (
             SELECT alert_event, 
                    alert_start_utc, 
                    alert_start_alert_area_tz, 
                    alert_end_utc, 
                    alert_end_alert_area_tz,
                    UNNEST(string_to_array(alert_zones, ',')) AS alert_zone
             FROM nws_alerts_pa
     )
     SELECT alert_zone, 
            string_agg(
                'EVENT: ' || alert_event || ', ' || 
                'START TIME (UTC): ' || alert_start_utc || ', ' || 
                'START TIME (AREA LOCAL): ' || alert_start_alert_area_tz || ', ' || 
                'END TIME (UTC): ' || alert_end_utc || ', ' || 
                'END TIME (AREA LOCAL): ' || alert_end_alert_area_tz , '; '
            )
     FROM da
     GROUP BY alert_zone
     ORDER BY alert_zone

4. Join to zone geometry:

   • Trim quotes from alert zone names for join

     UPDATE nws_alerts_pa
     SET alert_zones = regexp_replace(alert_zones, '''', '', 'g')

   • Trim Z from alert zone names for join, b/c alert zone polygon dataset's state_zone col looks like PA070 not PAZ070

     UPDATE nws_alerts_pa
     SET alert_zones = regexp_replace(alert_zones, 'Z', '', 'g')

   • Perform the join (& don't use cartodb_id b/c it can create multiple ones?):

     WITH da AS (
     SELECT alert_event, 
            alert_start_utc, 
            alert_start_alert_area_tz, 
            alert_end_utc, 
            alert_end_alert_area_tz,
            UNNEST(string_to_array(alert_zones, ',')) AS alert_zone
     FROM nws_alerts_mi
     ),
     al AS (
     SELECT row_number() over() as cartodb_id,
        alert_zone, 
        string_agg(
          'EVENT: ' || alert_event || ', ' || 
          'START TIME (UTC): ' || alert_start_utc || ', ' || 
          'START TIME (AREA LOCAL): ' || alert_start_alert_area_tz || ', ' || 
          'END TIME (UTC): ' || alert_end_utc || ', ' || 
          'END TIME (AREA LOCAL): ' || alert_end_alert_area_tz , '; '
        ) AS alerts
     FROM da
     GROUP BY alert_zone
     ORDER BY alert_zone
     )
     SELECT z.the_geom, z.the_geom_webmercator, al.alert_zone, al.alerts
     FROM al
     LEFT JOIN z_10se19 z 
     ON al.alert_zone = z.state_zone
     ORDER BY alert_zone

[ztephm]

To install CARTOframes develop branch in Python 3.7 venv (CF doesn't work with python 3.8):
pip install git+https://github.com/CartoDB/cartoframes.git#develop