search

ccao-data / data-architecture

Codebase for CCAO data infrastructure construction and management
https://ccao-data.github.io/data-architecture/
6 stars 4 forks source link

Correct `env_airport_noise_data_year` fill misjoin #285

Closed wrridgeway closed 9 months ago

wrridgeway commented 9 months ago

'omp' is a value for env_airport_noise_data_year in our location data crosswalk, but can't be joined to our generic yearcolumn. Airport noise is already filled during ingest so we don't need to use env_airport_noise_data_year when joining data for location.vw_pin10_fill

wrridgeway commented 9 months ago

Please don't laugh at how long this took me.

wrridgeway commented 9 months ago 
select
    year, env_airport_noise_data_year, count(*) as count
from "z_ci_284-correct-determination-of-max-env-airport-noise-data-year-in-locationcrosswalk-year-fill_location".vw_pin10_location_fill
where env_airport_noise_dnl is not null
group by year, env_airport_noise_data_year
year env_airport_noise_data_year count
2023 omp 1412712
2022 omp 1413874
2021 omp 1415410
2020 2020 1415664
2019 2019 1415193
2018 2018 1414731
2017 2017 1414896
2016 2016 1414449
2015 2015 1414155
2014 2014 1413788
2013 2013 1413759
2012 2012 1413732
2011 2011 1413698
2010 2010 1413699