Develop Data Schema for facility and county level capacity dataset

[lossyrob]

What information exactly do we want to see in the UI at the county and facility level?

See https://gitter.im/covid19-healthsystemcapacity/community?at=5e70ec9c30ea7f57f72dbd12

columns like update timestamp, source url (if avail), and method of update (automatic, expert-sourced, crowd-sourced, etc) So one can trace provenance back to that for any row/facility Also as a rough guess of what data fields would get updated most often either auto- or manually sourced, it's probably about total bed count, and its occupancy rate

This data schema can be baked into the functionality of #22

[daveluo]

Notes for creating a schema, here's the data dictionary from https://globalepidemics.org/2020/03/17/caring-for-covid-19-patients/:

HRR – Hospital Referral Region (HRR), specifying a market within which people generally go to the same hospitals

Total Hospital Beds – Count of all hospitable beds within an HRR that are set up and staffed

Total ICU Beds – Count of all ICU beds within an HRR that are set up and staffed

Available Hospital Beds – How many hospital beds are unoccupied at any given time, on average

Potentially Available Hospital Beds – How many beds could be available if occupancy rate was reduced by 50% for non-COVID patients

Available ICU Beds – How many ICU beds are unoccupied on average

Potentially Available ICU Beds – How many beds could be available if occupancy rate was reduced by 50% for non-COVID patients

Adult Population – How many people over the age of 18 living within the HRR

Population 65+ – How many people over the age of 65 living within the HRR

Projected Infected Individuals – How many individuals over the age of 18 are expected to get infected with COVID-19 over the entire course of the pandemic

Projected Hospitalized Individuals – How many individuals over the age of 18 are expected to need hospitalization due to COVID-19 over the entire course of the pandemic

Projected Individuals Needing ICU – How many individuals over the age of 18 are expected to need ICU care due to COVID-19 over the entire course of the pandemic

Hospital/ICU Beds Needed, X Months – How many hospital/ICU beds would have to be available to care for all patients requiring hospital care within X months

Percentage of Available Hospital/ICU Beds Needed, X Months – What percentage of available hospital/ICU beds would need to be committed to COVID patients to care for all patients in X months

Percentage of Potentially Available Hospital/ICU Beds Needed, X Months – What percentage of potentially available hospital/ICU beds would need to be committed to COVID patients to care for all patients in X months

Percentage of Total Hospital/ICU Beds Needed, X Months – What percentage of all hospital/ICU beds would need to be committed to COVID patients to care for all patients in X months

[daveluo]

and from #35, Definitive Healthcare dataset:

Number of Licensed beds: is the maximum number of beds for which a hospital holds a license to operate; however, many hospitals do not operate all the beds for which they are licensed. This number is obtained through DHC Primary Research. Licensed beds for Health Systems are equal to the total number of licensed beds of individual Hospitals within a given Health System.

Number of Staffed Bed: is defined as an "adult bed, pediatric bed, birthing room, or newborn ICU bed (excluding newborn bassinets) maintained in a patient care area for lodging patients in acute, long term, or domiciliary areas of the hospital." Beds in labor room, birthing room, post-anesthesia, postoperative recovery rooms, outpatient areas, emergency rooms, ancillary departments, nurses and other staff residences, and other such areas which are regularly maintained and utilized for only a portion of the stay of patients (primarily for special procedures or not for inpatient lodging) are not termed a bed for these purposes. Definitive Healthcare sources Staffed Bed data from the Medicare Cost Report or Proprietary Research as needed. As with all Medicare Cost Report metrics, this number is self-reported by providers. Staffed beds for Health Systems are equal to the total number of staffed beds of individual Hospitals within a given Health System. Total number of staffed beds in the US should exclude Hospital Systems to avoid double counting. ICU beds are likely to follow the same logic as a subset of Staffed beds.

Number of ICU Beds - ICU (Intensive Care Unit) Beds: are qualified based on definitions by CMS, Section 2202.7, 22-8.2. These beds include ICU beds, burn ICU beds, surgical ICU beds, premature ICU beds, neonatal ICU beds, pediatric ICU beds, psychiatric ICU beds, trauma ICU beds, and Detox ICU beds.

Bed Utilization Rate: is calculated based on metrics from the Medicare Cost Report: Bed Utilization Rate = Total Patient Days (excluding nursery days)/Bed Days Available

Potential Increase in Bed Capacity: This metric is computed by subtracting “Number of Staffed Beds from Number of Licensed beds” (Licensed Beds – Staffed Beds). This would provide insights into scenario planning for when staff can be shifted around to increase available bed capacity as needed.

[lossyrob]

Current Examples:

Facility level:

    {
      "type": "Feature",
      "properties": {
        "Estimated Beds Available (Conventional) - Lower": 4,
        "Estimated Beds Available (Conventional) - Upper": 8,
        "Estimated Additional Ventilators Required (Conventional) - Lower": 0,
        "Estimated Additional Ventilators Required (Conventional) - Upper": 0,
        "Physicians Required to Staff Beds (Conventional) - Lower": 1,
        "Physicians Required to Staff Beds (Conventional) - Upper": 1,
        "Respiratory Therapists Required to Staff Beds (Conventional) - Lower": 1,
        "Respiratory Therapists Required to Staff Beds (Conventional) - Upper": 1,
        "Critical Care Nurses Required to Staff Beds (Conventional) - Lower": 1,
        "Critical Care Nurses Required to Staff Beds (Conventional) - Upper": 2,
        "Estimated Beds Available (Contingency) - Lower": 13,
        "Estimated Beds Available (Contingency) - Upper": 26,
        "Estimated Additional Ventilators Required (Contingency) - Lower": 0,
        "Estimated Additional Ventilators Required (Contingency) - Upper": 0,
        "Physicians Required to Staff Beds (Contingency) - Lower": 1,
        "Physicians Required to Staff Beds (Contingency) - Upper": 1,
        "Respiratory Therapists Required to Staff Beds (Contingency) - Lower": 1,
        "Respiratory Therapists Required to Staff Beds (Contingency) - Upper": 1,
        "Critical Care Nurses Required to Staff Beds (Contingency) - Lower": 1,
        "Critical Care Nurses Required to Staff Beds (Contingency) - Upper": 3,
        "Estimated Beds Available (Crisis) - Lower": 33,
        "Estimated Beds Available (Crisis) - Upper": 65,
        "Estimated Additional Ventilators Required (Crisis) - Lower": 0,
        "Estimated Additional Ventilators Required (Crisis) - Upper": 25,
        "Physicians Required to Staff Beds (Crisis) - Lower": 1,
        "Physicians Required to Staff Beds (Crisis) - Upper": 1,
        "Respiratory Therapists Required to Staff Beds (Crisis) - Lower": 1,
        "Respiratory Therapists Required to Staff Beds (Crisis) - Upper": 3,
        "Critical Care Nurses Required to Staff Beds (Crisis) - Lower": 3,
        "Critical Care Nurses Required to Staff Beds (Crisis) - Upper": 13,
        "Name": "SOUTHEAST HEALTH MEDICAL CENTER",
        "State": "AL",
        "County": "HOUSTON"
      },
      "geometry": {
        "type": "Point",
        "coordinates": [
          -85.3625287,
          31.2159117
        ]
      }
    }

County/State/HRR aggregations (each include identifies for the region, example is county):

{
    "type": "Feature",
    "properties": {
        "STATE": "01",
        "COUNTY": "001",
        "GEO_ID": "0500000US01001",
        "Estimated Beds Available (Conventional) - Lower": 1,
        "Estimated Beds Available (Conventional) - Upper": 1,
        "Estimated Additional Ventilators Required (Conventional) - Lower": 0,
        "Estimated Additional Ventilators Required (Conventional) - Upper": 0,
        "Physicians Required to Staff Beds (Conventional) - Lower": 1,
        "Physicians Required to Staff Beds (Conventional) - Upper": 1,
        "Respiratory Therapists Required to Staff Beds (Conventional) - Lower": 1,
        "Respiratory Therapists Required to Staff Beds (Conventional) - Upper": 1,
        "Critical Care Nurses Required to Staff Beds (Conventional) - Lower": 1,
        "Critical Care Nurses Required to Staff Beds (Conventional) - Upper": 1,
        "Estimated Beds Available (Contingency) - Lower": 2,
        "Estimated Beds Available (Contingency) - Upper": 4,
        "Estimated Additional Ventilators Required (Contingency) - Lower": 0,
        "Estimated Additional Ventilators Required (Contingency) - Upper": 0,
        "Physicians Required to Staff Beds (Contingency) - Lower": 1,
        "Physicians Required to Staff Beds (Contingency) - Upper": 1,
        "Respiratory Therapists Required to Staff Beds (Contingency) - Lower": 1,
        "Respiratory Therapists Required to Staff Beds (Contingency) - Upper": 1,
        "Critical Care Nurses Required to Staff Beds (Contingency) - Lower": 1,
        "Critical Care Nurses Required to Staff Beds (Contingency) - Upper": 1,
        "Estimated Beds Available (Crisis) - Lower": 6,
        "Estimated Beds Available (Crisis) - Upper": 11,
        "Estimated Additional Ventilators Required (Crisis) - Lower": 0,
        "Estimated Additional Ventilators Required (Crisis) - Upper": 5,
        "Physicians Required to Staff Beds (Crisis) - Lower": 1,
        "Physicians Required to Staff Beds (Crisis) - Upper": 1,
        "Respiratory Therapists Required to Staff Beds (Crisis) - Lower": 1,
        "Respiratory Therapists Required to Staff Beds (Crisis) - Upper": 1,
        "Critical Care Nurses Required to Staff Beds (Crisis) - Lower": 1,
        "Critical Care Nurses Required to Staff Beds (Crisis) - Upper": 2,
        "NAME": "Autauga",
        "LSAD": "County",
        "CENSUSAREA": 594.436,
        "fips_code": "01001",
        "Population": 55601.0,
        "Population (20+)": 41177.0,
        "Population (65+)": 8653.0,
        "Estimated Beds Available (Conventional) - Lower [Per 1000 People]": 0.018,
        "Estimated Beds Available (Conventional) - Lower [Per 1000 Adults (20+)]": 0.024,
        "Estimated Beds Available (Conventional) - Lower [Per 1000 Elderly (65+)]": 0.116,
        "Estimated Beds Available (Conventional) - Upper [Per 1000 People]": 0.018,
        "Estimated Beds Available (Conventional) - Upper [Per 1000 Adults (20+)]": 0.024,
        "Estimated Beds Available (Conventional) - Upper [Per 1000 Elderly (65+)]": 0.116,
        "Estimated Additional Ventilators Required (Conventional) - Lower [Per 1000 People]": 0.0,
        "Estimated Additional Ventilators Required (Conventional) - Lower [Per 1000 Adults (20+)]": 0.0,
        "Estimated Additional Ventilators Required (Conventional) - Lower [Per 1000 Elderly (65+)]": 0.0,
        "Estimated Additional Ventilators Required (Conventional) - Upper [Per 1000 People]": 0.0,
        "Estimated Additional Ventilators Required (Conventional) - Upper [Per 1000 Adults (20+)]": 0.0,
        "Estimated Additional Ventilators Required (Conventional) - Upper [Per 1000 Elderly (65+)]": 0.0,
        "Physicians Required to Staff Beds (Conventional) - Lower [Per 1000 People]": 0.018,
        "Physicians Required to Staff Beds (Conventional) - Lower [Per 1000 Adults (20+)]": 0.024,
        "Physicians Required to Staff Beds (Conventional) - Lower [Per 1000 Elderly (65+)]": 0.116,
        "Physicians Required to Staff Beds (Conventional) - Upper [Per 1000 People]": 0.018,
        "Physicians Required to Staff Beds (Conventional) - Upper [Per 1000 Adults (20+)]": 0.024,
        "Physicians Required to Staff Beds (Conventional) - Upper [Per 1000 Elderly (65+)]": 0.116,
        "Respiratory Therapists Required to Staff Beds (Conventional) - Lower [Per 1000 People]": 0.018,
        "Respiratory Therapists Required to Staff Beds (Conventional) - Lower [Per 1000 Adults (20+)]": 0.024,
        "Respiratory Therapists Required to Staff Beds (Conventional) - Lower [Per 1000 Elderly (65+)]": 0.116,
        "Respiratory Therapists Required to Staff Beds (Conventional) - Upper [Per 1000 People]": 0.018,
        "Respiratory Therapists Required to Staff Beds (Conventional) - Upper [Per 1000 Adults (20+)]": 0.024,
        "Respiratory Therapists Required to Staff Beds (Conventional) - Upper [Per 1000 Elderly (65+)]": 0.116,
        "Critical Care Nurses Required to Staff Beds (Conventional) - Lower [Per 1000 People]": 0.018,
        "Critical Care Nurses Required to Staff Beds (Conventional) - Lower [Per 1000 Adults (20+)]": 0.024,
        "Critical Care Nurses Required to Staff Beds (Conventional) - Lower [Per 1000 Elderly (65+)]": 0.116,
        "Critical Care Nurses Required to Staff Beds (Conventional) - Upper [Per 1000 People]": 0.018,
        "Critical Care Nurses Required to Staff Beds (Conventional) - Upper [Per 1000 Adults (20+)]": 0.024,
        "Critical Care Nurses Required to Staff Beds (Conventional) - Upper [Per 1000 Elderly (65+)]": 0.116,
        "Estimated Beds Available (Contingency) - Lower [Per 1000 People]": 0.036,
        "Estimated Beds Available (Contingency) - Lower [Per 1000 Adults (20+)]": 0.049,
        "Estimated Beds Available (Contingency) - Lower [Per 1000 Elderly (65+)]": 0.231,
        "Estimated Beds Available (Contingency) - Upper [Per 1000 People]": 0.072,
        "Estimated Beds Available (Contingency) - Upper [Per 1000 Adults (20+)]": 0.097,
        "Estimated Beds Available (Contingency) - Upper [Per 1000 Elderly (65+)]": 0.462,
        "Estimated Additional Ventilators Required (Contingency) - Lower [Per 1000 People]": 0.0,
        "Estimated Additional Ventilators Required (Contingency) - Lower [Per 1000 Adults (20+)]": 0.0,
        "Estimated Additional Ventilators Required (Contingency) - Lower [Per 1000 Elderly (65+)]": 0.0,
        "Estimated Additional Ventilators Required (Contingency) - Upper [Per 1000 People]": 0.0,
        "Estimated Additional Ventilators Required (Contingency) - Upper [Per 1000 Adults (20+)]": 0.0,
        "Estimated Additional Ventilators Required (Contingency) - Upper [Per 1000 Elderly (65+)]": 0.0,
        "Physicians Required to Staff Beds (Contingency) - Lower [Per 1000 People]": 0.018,
        "Physicians Required to Staff Beds (Contingency) - Lower [Per 1000 Adults (20+)]": 0.024,
        "Physicians Required to Staff Beds (Contingency) - Lower [Per 1000 Elderly (65+)]": 0.116,
        "Physicians Required to Staff Beds (Contingency) - Upper [Per 1000 People]": 0.018,
        "Physicians Required to Staff Beds (Contingency) - Upper [Per 1000 Adults (20+)]": 0.024,
        "Physicians Required to Staff Beds (Contingency) - Upper [Per 1000 Elderly (65+)]": 0.116,
        "Respiratory Therapists Required to Staff Beds (Contingency) - Lower [Per 1000 People]": 0.018,
        "Respiratory Therapists Required to Staff Beds (Contingency) - Lower [Per 1000 Adults (20+)]": 0.024,
        "Respiratory Therapists Required to Staff Beds (Contingency) - Lower [Per 1000 Elderly (65+)]": 0.116,
        "Respiratory Therapists Required to Staff Beds (Contingency) - Upper [Per 1000 People]": 0.018,
        "Respiratory Therapists Required to Staff Beds (Contingency) - Upper [Per 1000 Adults (20+)]": 0.024,
        "Respiratory Therapists Required to Staff Beds (Contingency) - Upper [Per 1000 Elderly (65+)]": 0.116,
        "Critical Care Nurses Required to Staff Beds (Contingency) - Lower [Per 1000 People]": 0.018,
        "Critical Care Nurses Required to Staff Beds (Contingency) - Lower [Per 1000 Adults (20+)]": 0.024,
        "Critical Care Nurses Required to Staff Beds (Contingency) - Lower [Per 1000 Elderly (65+)]": 0.116,
        "Critical Care Nurses Required to Staff Beds (Contingency) - Upper [Per 1000 People]": 0.018,
        "Critical Care Nurses Required to Staff Beds (Contingency) - Upper [Per 1000 Adults (20+)]": 0.024,
        "Critical Care Nurses Required to Staff Beds (Contingency) - Upper [Per 1000 Elderly (65+)]": 0.116,
        "Estimated Beds Available (Crisis) - Lower [Per 1000 People]": 0.108,
        "Estimated Beds Available (Crisis) - Lower [Per 1000 Adults (20+)]": 0.146,
        "Estimated Beds Available (Crisis) - Lower [Per 1000 Elderly (65+)]": 0.693,
        "Estimated Beds Available (Crisis) - Upper [Per 1000 People]": 0.198,
        "Estimated Beds Available (Crisis) - Upper [Per 1000 Adults (20+)]": 0.267,
        "Estimated Beds Available (Crisis) - Upper [Per 1000 Elderly (65+)]": 1.271,
        "Estimated Additional Ventilators Required (Crisis) - Lower [Per 1000 People]": 0.0,
        "Estimated Additional Ventilators Required (Crisis) - Lower [Per 1000 Adults (20+)]": 0.0,
        "Estimated Additional Ventilators Required (Crisis) - Lower [Per 1000 Elderly (65+)]": 0.0,
        "Estimated Additional Ventilators Required (Crisis) - Upper [Per 1000 People]": 0.09,
        "Estimated Additional Ventilators Required (Crisis) - Upper [Per 1000 Adults (20+)]": 0.121,
        "Estimated Additional Ventilators Required (Crisis) - Upper [Per 1000 Elderly (65+)]": 0.578,
        "Physicians Required to Staff Beds (Crisis) - Lower [Per 1000 People]": 0.018,
        "Physicians Required to Staff Beds (Crisis) - Lower [Per 1000 Adults (20+)]": 0.024,
        "Physicians Required to Staff Beds (Crisis) - Lower [Per 1000 Elderly (65+)]": 0.116,
        "Physicians Required to Staff Beds (Crisis) - Upper [Per 1000 People]": 0.018,
        "Physicians Required to Staff Beds (Crisis) - Upper [Per 1000 Adults (20+)]": 0.024,
        "Physicians Required to Staff Beds (Crisis) - Upper [Per 1000 Elderly (65+)]": 0.116,
        "Respiratory Therapists Required to Staff Beds (Crisis) - Lower [Per 1000 People]": 0.018,
        "Respiratory Therapists Required to Staff Beds (Crisis) - Lower [Per 1000 Adults (20+)]": 0.024,
        "Respiratory Therapists Required to Staff Beds (Crisis) - Lower [Per 1000 Elderly (65+)]": 0.116,
        "Respiratory Therapists Required to Staff Beds (Crisis) - Upper [Per 1000 People]": 0.018,
        "Respiratory Therapists Required to Staff Beds (Crisis) - Upper [Per 1000 Adults (20+)]": 0.024,
        "Respiratory Therapists Required to Staff Beds (Crisis) - Upper [Per 1000 Elderly (65+)]": 0.116,
        "Critical Care Nurses Required to Staff Beds (Crisis) - Lower [Per 1000 People]": 0.018,
        "Critical Care Nurses Required to Staff Beds (Crisis) - Lower [Per 1000 Adults (20+)]": 0.024,
        "Critical Care Nurses Required to Staff Beds (Crisis) - Lower [Per 1000 Elderly (65+)]": 0.116,
        "Critical Care Nurses Required to Staff Beds (Crisis) - Upper [Per 1000 People]": 0.036,
        "Critical Care Nurses Required to Staff Beds (Crisis) - Upper [Per 1000 Adults (20+)]": 0.049,
        "Critical Care Nurses Required to Staff Beds (Crisis) - Upper [Per 1000 Elderly (65+)]": 0.231
    },
    "geometry": {
        "type": "Polygon",
        "coordinates": [
            [
                [-86.496774, 32.344437],
                [-86.717897, 32.402814],
                [-86.814912, 32.340803],
                [-86.890581, 32.502974],
                [-86.917595, 32.664169],
                [-86.71339, 32.661732],
                [-86.714219, 32.705694],
                [-86.413116, 32.707386],
                [-86.411172, 32.409937],
                [-86.496774, 32.344437]
            ]
        ]
    }
}

[lossyrob]

This was done as part of the v0.1 release