ccao-data / data-architecture

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

Add Chicago cbd boundary to `default.vw_pin_universe` #372

Closed Damonamajor closed 2 months ago

Damonamajor commented 2 months ago

This adds a central business district boundary, which calculates a 1 for parcels in the district, and an empty value for those that are not.

wrridgeway commented 2 months ago

Let's lint the R scripts and run some tests to make sure 1) only parcels from chicago show up in the central business district 2) there are the same number of rows per year for vw_pin_universe as in the default athena database

Damonamajor commented 2 months ago

Let's lint the R scripts

A lot of the linting errors are pre-existing and relate to functions being too many characters. For example remote_file_industrial_growth_zone is more than 30 characters.

Damonamajor commented 2 months ago
  1. only parcels from chicago show up in the central business district

image

SELECT census_acs5_place_geoid, econ_central_business_district_num, COUNT(*) AS combination_count FROM "z_ci_368-add-chicago-cbd-boundary-to-pin-universe_default"."vw_pin_universe" WHERE econ_central_business_district_num = '1' GROUP BY census_acs5_place_geoid, econ_central_business_district_num;

Damonamajor commented 2 months ago
  1. there are the same number of rose per year for vw_pin_universe as in the default athena database

SELECT a.year, a.row_count AS row_count_a, b.row_count AS row_count_b FROM (SELECT year, COUNT() AS row_count FROM "z_ci_368-add-chicago-cbd-boundary-to-pin-universe_default"."vw_pin_universe" GROUP BY year) a JOIN (SELECT year, COUNT() AS row_count FROM "default"."vw_pin_universe" GROUP BY year) b ON a.year = b.year;

image