PINGEO Updates: Add political geographies, census demographics
The CCAO maintains two tables in the SQL server: PINLOCATIONS and PROPLOCS. Combined, they show location-related data for every PIN in the County. In both 0_build_200class_modeldata and 0_build_200class_assmntdata, you can see the block below:
SELECT ...
FROM HEAD AS H
LEFT JOIN
( /* For addresses, census tracts, and xy */
SELECT *,
CASE WHEN PL_PIN IS NULL THEN Name ELSE PL_PIN END AS PIN_FILLED
FROM PINLOCATIONS
LEFT JOIN
PROPLOCS
ON LEFT(NAME, 10)=LEFT(PL_PIN, 10)
WHERE primary_polygon IN (1)
AND PIN999 NOT IN (1)
) AS X
ON HD_PIN=PIN_FILLED
We want to update this table with additional information. The file 0_build_pin_geography.R constructs the PINLOCATIONS table. Please modify this file to do the following:
-
Automatically download Census 5-year estimates Tract level tabulated data showing median HH income, % white, %black, %Hispanic, and %all others, and join this tabulated data into the existing table. The Census Bureau has an FTP site you can link directly to in R. -
Using shape files, identify the current geographic areas for all PINs: - Cook County Commissioner District
- City of Chicago Municipal Ward
- Illinois Legislature: House Representative District
- Illinois Legislature: Senator District
-
Create a reference table in data_dictionary_constituents listing the name of the representative for each of the above districts, with a key so that we can join these names in easily.
@njardine , do you have anything you want to add to this?
Edited by Ta-Yun Yang