Tract Quintile ETL

The concentration of separate equity demographics at the tract level is used in the Equity Tracker as a geographic proxy when other metrics aren't themselves directly tied to equity data. These can be either inherently spatial data (such as tree canopy), or data reported spatially (such as cardiovascular disease mortality). The tract-level concentrations and quintile rankings are stored in Elmer.equity.tract_shares and accessed through the View Elmer.equity.v_tract_shares.

The data is developed using this R script, using the functions get_psrc_equity_shares() and write_shares_to_elmer(). The script provides an example of using mapply() and lapply() to generate data across the entire range available years at the same time, which can be done to regenerate the entire table. If PSRC decides to alter EFA variable definitions, we would recommend this, truncating the table before writing from R. If merely appending additional years, those years alone can be run and the SQL MERGE command in write_shares_to_elmer() will insert the new rows without impacting existing data.

In a limited handful of cases, over 20 percent of tracts have the same value (such as zero for LEP share in Kitsap County). This means tracts in the lower quintiles can't be ranked, and are thus assigned the same quintile value (e.g. 2, or "Low Medium"). Downstream Equity Tracker code fills out a matching "Low" quintile in these cases for display purposes. For the time being, this requires an ad-hoc SQL step to feed a unique value:

WITH cte AS( SELECT data_year, county FROM [equity].[tract_shares] GROUP BY data_year, county HAVING min(lep_quintile)=2) UPDATE ts SET lep_quintile=0 FROM [equity].[tract_shares] AS ts JOIN cte ON cte.data_year=ts.data_year AND cte.county=ts.county WHERE ts.lep_quintile=2; ` `

We expect in the near future the downstream code will be adjusted to identify these cases without requiring Elmer store a special value, at which point this extra step can be erased.