Add fed and state congressional district nums

[wrridgeway]

Just a quick addition of some columns in vw_pin_universe that are functions of pre-existing columns.

[wrridgeway]

select distinct
    census_data_year,
    census_congressional_district_geoid,
    census_congressional_district_num,
    census_state_representative_geoid,
    census_state_representative_num,
    census_state_senate_geoid,
    census_state_senate_num,
    census_acs5_data_year,
    census_acs5_state_representative_geoid,
    census_acs5_state_representative_num,
    census_acs5_state_senate_geoid,
    census_acs5_state_senate_num
from z_dev_wridgeway_default.vw_pin_universe

output

[wrridgeway]

issue (blocking): What's up with the 2011 IDs in the output? We may want to fix those in the source.

yeah, not sure. i'll take a look. i assumed that it was an expected change in formatting.

[wrridgeway]

select distinct
    census_data_year,
    census_congressional_district_geoid,
    census_congressional_district_num,
    census_state_representative_geoid,
    census_state_representative_num,
    census_state_senate_geoid,
    census_state_senate_num,
    census_acs5_data_year,
    census_acs5_state_representative_geoid,
    census_acs5_state_representative_num,
    census_acs5_state_senate_geoid,
    census_acs5_state_senate_num
from z_dev_wridgeway_default.vw_pin_universe

new output

We'll need to trigger a rebuild of location.census after this is merged.

[wrridgeway]

All gone.

[wrridgeway]

Did a quick investigation into which parcels are missing census geoids for 2024:

• 1026 pin10s are missing lat/lon so can't be spatially joined to census
• 22 pin10s are in the lake, here's a sample:

[dfsnow]

Did a quick investigation into which parcels are missing census geoids for 2024:

• 1026 pin10s are missing lat/lon so can't be spatially joined to census
• 22 pin10s are in the lake, here's a sample:

@wrridgeway I'm guessing this is also the cause of other missing census geographies? (e.g. null congressional district in 2022 sometimes, even though other census geos are filled?)

[wrridgeway]

I double checked this data, looking at 2016 which has the most null geoids (~12,000), and the vast majority is due to NA lat/long. Only about 800 are missing geoids from the spatial join, and those are from the same lake coast issue.