"datahub-transportation-gov/new-york-city-cv-pilot-event-data-one-month-sample-vxmm-9bi5:latest"."new_york_city_cv_pilot_event_data_one_month_sample"

SELECT
    ":id", -- Socrata column ID
    "eventheader_eventalertheard", -- Boolean value indicating whether the ASD detected that the alert was heard or detected.
    "bureaubin", -- A 2-character abbreviation of the NYC borough, extracted from the eventHeader_eventLocationBin field. The value can be "BK" for Brooklyn, "BX" for The Bronx, "MN" for Manhattan, "QN" for "Queens, and "SI" for Staten Island. This field is created for use within Socrata and is not present in the data sandbox.
    "timeofdaybin", -- Time period of the day, extracted from the eventHeader_eventTimeBin field. The value can be "NT" for night (12-6am), "AM" for morning (6-10am), "MD" for midday (10am-3pm), "PM" for afternoon (3-8pm), "EV" for evening (8pm-12am), and "N/A" for unspecified time of day. This field is created for use within Socrata and is not present in the data sandbox.
    "eventheader_parameters_8", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "maplist", -- Sequence of DF_MAPEvent dataframes in stringified array of json objects.
    "dayofweekbin", -- Day of the week (e.g. “TUE”) in a 3-character abbreviation, extracted from the eventHeader_eventTimeBin field. The value “N/A” may also appear as an additional level of obfuscation. This field is created for use within Socrata and is not present in the data sandbox.
    "eventheader_targetvehid", -- Temporary ID of the target vehicle at the time of the warning.  Valid only for V2V applications.
    "eventheader_lastplowed", -- Integer, minutes. Time (rounded to 5 minute values) since a snow plow last was seen on the roadway where the event occurred.  Data included is as provided by the city's snow plow tracking and reporting system.  A value of -1 indicates that snow plows were active in the city at the time of the event, but a snow plow has not yet travelled on the roadway where the event occurred.  When the city's snow plow tracking and reporting system is not active, this data element is omitted.
    "eventheader_speedcondition", -- Integer, miles per hour.  Average speed (mph) of the roadway where the event occurred, as provided by non-CV pilot based speed monitoring systems available.  Note that the average speed is reported for roadway segments which can be a mile or more in length; and may not truly represent the localized speeds on the roadway when the EVENT record was generated.  When average roadway speed information is not available, this data element is omitted.
    "eventheader_windspeed", -- Integer, wind speed (knots)
    "timlist", -- Sequence of DF_TIMEvent dataframes in stringified array of json objects.
    "eventheader_parameters_21", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_triggertvseqnum", -- Sequence number of the target vehicle's BSM which triggered the warning that was issued.  Refers to the MsgSeqNum in the  list of BSM records contained in the EVENT record.  Valid only for V2V applications.
    "eventheader_triggerhvseqnum", -- Sequence number of the host vehicle's BSM which triggered the warning that was issued.  Refers to the MsgSeqNum in the list of BSM records contained in the EVENT record.
    "eventheader_locationsource", -- Provides details on which method the location of the host vehicle was determined.  Note this parameter is only meaningful in ASD firmware versions 4.3 or higher. Values can include: "gpsrsu": location is determined through a combination of GSP GNSS sensors, vehicle speed and yaw rate, and triangulation to nearby RSUs configured to broadcast V2XLocate details to ASDs. "gps": location is determined through a combination of GSP GNSS sensors, vehicle speed and yaw rate. "directgps": location is determined by GPS GNSS sensors only.  Note in this location mode, BSM transmissions are halted as location is not know precisely enough for safety applications, and no event warnings should be issued.
    "eventheader_eventtimebin", -- String in the format “YYYY-MM-DAY-TP" to provide some details of when the event took place, where: YYYY is the year (e.g. “2021”); MM is the month in number format (e.g. “05”); DAY is the day of the week (e.g. “TUE”) in a 3-character abbreviation; TP is the time period of the day. Detailed description for DAY and TP can be found in the descriptions for columns dayOfWeekBin and timeOfDayBin.
    "eventheader_parameters_14", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "nearrsu", -- Boolean value indicating whether the roadway is near an RSU., extracted from the eventHeader_eventLocationBin field. This field is created for use within Socrata and is not present in the data sandbox.
    "roadtypebin", -- Bin providing insights into the road type where the event occurred, extracted from the eventHeader_eventLocationBin field. The value can be "1wayAve" for One-way Avenue in Manhattan, "2wayAve" for two-way Avenue in Manhattan, "1waySt" for one-way Street in Manhattan, "2waySt" for two-way Street in Manhattan, "Fwy" for a freeway, parkway, or otherwise controlled access roadway, "Art" for an arterial roadway, or "Oth" for other, generally collector or local level roadways. This field is created for use within Socrata and is not present in the data sandbox.
    "eventheader_parameters_18", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_eventlocationbin", -- String of the format “XX-YY-ZZZ”, to provide some details on where in NYC the event took place, where: XX indicates whether the roadway is near an road side unit (RSU), with "CV" if near and "NY" if not near; YY is a 2-character abbreviation of the NYC borough; ZZZ provides insights into the road type where the event occurred. Detailed description for YY and ZZZ can be found in the descriptions for columns bureauBin and roadTypeBin. Note when the event occurred outside of the NYC city limits, the code "nonNYC" is used as the entire entry for the eventLocationBin.
    "eventheader_parameters_19", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_parameters_5", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_parameters_13", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_parameters_15", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "spatlist", -- Sequence of DF_SPaTEvent dataframes in stringified array of json objects.
    "eventheader_parameters_9", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_eventalertactive", -- Boolean value indicating whether the ASD delivered the warning to the driver. True if the audible alert was issued, False if the alert was not issued (if ASD is configured for pre-deployment conditions or if the vehicle is part of the control group which does not received warnings).
    "eventheader_eventalertsent", -- Boolean value indicating whether the CV application determined if conditions warrant that a warning should be issued.
    "eventheader_parameters_17", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_parameters_11", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_grpid", -- Integer representing the group to which the vehicle is assigned. ID <= 19: Pilot test vehicles.  Vehicles used by the pilot team for ASD testing purposes throughout the pilot. ID  = 20: Control group vehicle.  Vehicles assigned to the control group operate in 'silent' mode throughout the deployment.  ASDs should not deliver warnings to the drivers, but will still record EVENTS when applications determine a warning condition exists. ID >= 21: Treatment group vehicle.  Vehicles assigned to the treatment group will all operate in 'silent' mode for the before deployment data collection period.  At the start of the after or live deployment, all treatment vehicles will transition to 'active' mode, and start to deliver application warning messages to the drivers. 
    "eventheader_eventtype", -- The CV application which triggered the warning. The value can be: "bsw" for Blind Spot Warning (V2V), "eebl" for Emergency Electronic Brake Lights (V2V), "fcw" for Forward Crash Warning (V2V), "ima" for Intersection Movement Assist (V2V), "lcw" for  Lane Change Warning (V2V), "vtrw" for Vehicle Turning Right Warning (V2V), "cspdomp" for Curve speed compliance (V2I, "evacinfo" for Emergency Communications and Evacuation Information (V2I), "ovcclearancelimit" for Oversize Vehicle Compliance (V2I), "pedinxwalk" for Pedestrian in Signalized Crosswalk Warning (V2I), "rlvw" for Red Light Violation Warning (V2I), "spdcomp" for Speed Compliance (V2I), "spdcompwz" for  Speed Compliance in Work Zone(V2I). See https://cvp.nyc/cv-safety-apps for details.
    "eventheader_airtemperature", -- Air temperature, in degrees Fahrenheit
    "eventheader_precipitation1hr", -- String, inches of precipitation in last hour; "M" indicates data is not available
    "eventheader_parameters_1", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "bsmlist", -- Sequence of DF_BSMEvent dataframes in stringified array of json objects.
    "eventheader_parameters_2", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_parameters_7", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_parameters_16", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_parameters_10", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_parameters", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_parameters_12", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_asdfirmwareversion", -- The firmware running on the ASD at the time of the recorded event.
    "eventheader_hostvehid", -- Temporary ID of the host vehicle at the time of the warning.
    "eventheader_parameters_6", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_parameters_3", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_eventstatus", -- Value is always “Obfuscated” for released EVENT records
    "eventheader_parameters_20", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_parameters_4", -- This is part of the set of parameters that contains configuration parameters which help define how the ASD applications operate and how data is recorded. It is noted that which parameters are included vary based on the CV application warning type that the EVENT record is documenting.
    "eventheader_weathercondition" -- String describing of the weather conditions (from NWS)
FROM
    "datahub-transportation-gov/new-york-city-cv-pilot-event-data-one-month-sample-vxmm-9bi5:latest"."new_york_city_cv_pilot_event_data_one_month_sample"
LIMIT 100;

sgr mount socrata \
  "datahub-transportation-gov/new-york-city-cv-pilot-event-data-one-month-sample-vxmm-9bi5" \
  --handler-options '{
    "domain": "datahub.transportation.gov",
    "tables": {
        "new_york_city_cv_pilot_event_data_one_month_sample": "vxmm-9bi5"
    }
}'