Thematic Map SQL query - Githubissues

SPTKL commented 3 years ago

Context: Quick update from our meeting with Joel today to get the conversation started for next week while I'm away. We think that our work on ACS data is done 🎉. Traditionally, you've checked the data in the app, but given that there's a delay in getting the data into the app Molly and Baiyue will be working to create digestible tables for you to review so that you can sign-off on the data. Your input on what information to report and how would be useful, and we can iterate off what Molly started mocking up.

Labs is working on implementing the thematic map feature for NTAs in PFF. Where did the current data driving this part of PFF come from? Did you create a separate table to drive this feature? If so, do you plan to create a dedicated table again? Can this be generated from the data Data Engineering creates?

SELECT 
    _popu181.geoid,
    support_geoids."label",
    _popu181.popu181,
    _mdgr.mdgr,
    _pbwpv.pbwpv,
    _pbwpv.pbwpv_p,
    _lgoenlep1.lgoenlep1,
    _fb1.fb1_p,
    _ea_bchdh.ea_bchdh,
    _ea_bchdh.ea_bchdh_p,
    _pop65pl1.pop65pl1
FROM (
    SELECT geoid, e as popu181 
    FROM acs."2019" 
    WHERE geotype LIKE 'NTA%' 
    AND variable = 'popu181'
) _popu181 

LEFT JOIN (
    SELECT geoid, e as mdgr 
    FROM acs."2019" 
    WHERE geotype LIKE 'NTA%' 
    AND variable = 'mdgr'
) _mdgr ON _popu181.geoid = _mdgr.geoid

LEFT JOIN (
    SELECT geoid, e as pbwpv, p as pbwpv_p 
    FROM acs."2019" 
    WHERE geotype LIKE 'NTA%' 
    AND variable = 'pbwpv'
) _pbwpv ON _popu181.geoid = _pbwpv.geoid

LEFT JOIN (
    SELECT geoid, e as lgoenlep1
    FROM acs."2019" 
    WHERE geotype LIKE 'NTA%' 
    AND variable = 'lgoenlep1'
) _lgoenlep1 ON _popu181.geoid = _lgoenlep1.geoid

LEFT JOIN (
    SELECT geoid, p as fb1_p 
    FROM acs."2019" 
    WHERE geotype LIKE 'NTA%' 
    AND variable = 'fb1'
) _fb1 ON _popu181.geoid = _fb1.geoid

LEFT JOIN (
    SELECT geoid, e as ea_bchdh, p as ea_bchdh_p 
    FROM acs."2019" 
    WHERE geotype LIKE 'NTA%' 
    AND variable = 'ea_bchdh'
) _ea_bchdh ON _popu181.geoid = _ea_bchdh.geoid

LEFT JOIN (
    SELECT geoid, e as pop65pl1 
    FROM acs."2019" 
    WHERE geotype LIKE 'NTA%' 
    AND variable = 'pop65pl1'
) _pop65pl1 ON _popu181.geoid = _pop65pl1.geoid

LEFT JOIN support_geoids 
ON _popu181.geoid = support_geoids.geoid
WHERE support_geoids.geotype LIKE 'NTA%';

SPTKL commented 3 years ago

@EricaMaurer you can find them here lgoenlep is NULL for all tables below cdta_choropleth_0610.csv cdta_choropleth_1519.csv nta_choropleth_0610.csv nta_choropleth_1519.csv

EricaMaurer commented 3 years ago

@SPTKL Thematic maps for this round should just be for the 2015-2019 ACS (not 2006-2010) and Decennial-- so the other three csv files you created are not needed.

Not sure what part you are doing vs part Labs is doing so will put all the information here for the variables and map breaks.

ACS maps:

lgoenlep should be referencing LgOEnLEP1
Population under 18 using ACS PopU181 will be replaced with the decennial data, PopU18_1
ACS Variables with unchanged map breaks: -- lgoenlep1 -- fb1_p -- ea_bchdh -- ea_bchdh_p -- pop65pl1
ACS Variables with changed breaks: -- pbwpv_p --- 44.0 - 100.0 --- 32.0 - 43.9 --- 25.0 - 30.9 --- 15.0 - 23.9 --- 0.0 - 14.9

-- mdgr --- 2500 - 9999999 --- 2000 - 2499 --- 1500 - 1999 --- 1200 - 1499 --- 0 - 1199

-- pbwpv --- 21000 - 9999999 --- 15000 - 20999 --- 10000 - 14999 --- 5000 - 9999 --- 0 - 4999

Decennial maps:

Population (pop1) -- 85000 - 99999999 -- 70000 - 84999 -- 55000 - 69999 -- 40000 - 54999 -- 30000 - 39999 -- 0 - 29999
Population Density (popperacre)-- use current breaks -- 150 - 9999999 -- 100 - 149.9 -- 50 - 99.9 -- 25 - 49.9 -- 0 - 24.9
Use Map Breaks for: -- White Non-Hispanic Population (WNH) -- Black Non-Hispanic Population (BNH) -- Asian Non-Hispanic Population (ANH) -- Hispanic Population (Hsp1) --- 40000 - 9999999 --- 30000 - 39999 --- 20000 - 29999 --- 10000 - 19999 --- 5000 - 9999 --- 0 - 4999
Use Map Breaks for: -- White Non-Hispanic as a Percent of Total Population ((WNH/Pop3)100) -- Black Non-Hispanic as a Percent of Total Population ((BNH/Pop3)100) -- Asian Non-Hispanic as a Percent of Total Population ((ANH/Pop3)100) -- Hispanic as a Percent of Total Population ((Hsp1/Pop3)100) --- 80.0 - 100.0 --- 65.0 - 79.9 --- 50.0 - 64.9 --- 35.0 - 49.9 --- 20.0 - 34.9 --- 0.0 - 19.9
Use Map Breaks for (can use these breaks for each change map listed below but not all will have every category): -- Population Change, 2010-2020 (Pop1 2020- Pop1 2010) -- White Non-Hispanic Change, 2010-2020 (WNH 2020- WNH 2010) -- Black Non-Hispanic Change, 2010-2020 (BNH 2020- BNH 2010) -- Asian Non-Hispanic Change, 2010-2020 (ANH 2020- ANH 2010) -- Hispanic Change, 2010-2020 (Hsp1 2020- Hsp1 2010) -- Population Under 18 Change, 2010-2020 (PopU18_1 2020 - PopU18_1 2010) --- 10000 - 9999999 --- 5000 - 9999 --- 1000 - 4999 --- 500 - 9999 --- -499 - 499 --- -500 - -999 --- -4999 - -1000 --- -5000 - -9999999
Use Map Breaks for (can use these breaks for each percent change map listed below but not all will have every category): -- Percent Change in Population, 2010-2020 (((Pop1 2020 - Pop1 2010)/Pop1 2010)100) -- Percent Change in the White Non-Hispanic Population, 2010-2020 (((WNH 2020- WNH 2010)/WNH 2010)100)-- remove NTAs with less than 5,000 White NH population in 2020 -- Percent Change in the Black Non-Hispanic Population, 2010-2020 (((BNH 2020- BNH 2010)/BNH 2010)100)-- remove NTAs with less than 5,000 Black NH population in 2020 -- Percent Change in the Asian Non-Hispanic Population, 2010-2020 (((ANH 2020- ANH 2010)/ANH 2010)100)-- remove NTAs with less than 5,000 Asian NH population in 2020 -- Percent Change in the Hispanic Population, 2010-2020 (((Hsp1 2020- Hsp1 2010)/Hsp1 2010)100)-- remove NTAs with less than 5,000 Hispanic population in 2020 -- Percent Change in the Population Under 18, 2010-2020 (((PopU18_1 2020- PopU18_1 2010)/PopU18_1 2010)100)-- remove NTAs with less than 5,000 population under 18 in 2020 --- 50.0 - 100.0 --- 25.0 - 49.9 --- 15.0 - 24.9 --- 10.0 - 14.9 --- 5.0 - 9.9 --- -4.9 - 4.9 --- -9.9 - -5.0 --- -14.9 - -10.0 --- -100.0 - -15.0
Population Under 18 (PopU18_1) -- 25000 - 9999999 -- 20000 - 24999 -- 15000 - 19999 -- 10000 - 14999 -- 0 - 9999
Population Under 18 as a Percent of Total Population (PopU18_1/Pop2) -- 30.0 - 100.0 -- 25.0 - 29.9 -- 20.0 - 24.9 -- 15.0 - 19.9 -- 0.0 - 14.9

SPTKL commented 3 years ago

thanks @EricaMaurer this is super helpful! CC @NYCPlanning/engineering @godfreyyeung @TylerMatteo

EricaMaurer commented 3 years ago

Info box text change for Median Gross Rent: Median gross rent (in 2019 inflation-adjusted dollars)

Legend/info box text for maps-- these maps will appear under Census heading:

for maps with 5 categories, use current colors
for maps with 6 categories, add darker red for first category, then use the 5 current colors
HEX codes listed below for change and percent change maps. Not sure how well these will translate to PFF so we may have to tweak them.

Population (info box: Total population, 2020) 85,000 or more 70,000 to 84,999 55,000 to 69,999 40,000 to 54,999 30,000 to 39,999 Less than 30,000

Population Density (current breaks, labeling, and info box)

Use Map Breaks for: White Non-Hispanic (info box: White non-Hispanic population, 2020) Black Non-Hispanic (info box: Black non-Hispanic population, 2020) Asian Non-Hispanic (info box: Asian non-Hispanic population, 2020) Hispanic (info box: Hispanic population, 2020) 40,000 or more 30,000 to 39,999 20,000 to 29,999 10,000 to 19,999 5,000 to 9,999 Less than 5,000

Use Map Breaks for: White Non-Hispanic (percent) (info box: Percent of the population that is White non-Hispanic, 2020) Black Non-Hispanic (percent) (info box: Percent of the population that is Black non-Hispanic, 2020) Asian Non-Hispanic (percent) (info box: Percent of the population that is Asian non-Hispanic, 2020) Hispanic (percent) (info box: Percent of the population that is Hispanic, 2020) 80% or more 65% to 79% 50% to 64% 35% to 49% 20% to 34% Less than 20%

Use Map Breaks for: Population (change) (info box: Population change, 2010 to 2020) Gain of 10,000 or more (HEX: #012661) 5,000 to 9,999 (#0B5476) 1,000 to 4,999 (#0473AD) 500 to 9,999 (#B5D5E5) -499 to 499 (#FFFFFF) -500 to -999 (#FFC0B4) -4,999 to -1,000 (#F46C59) Loss of 5,000 or more (#C81D00)

White Non-Hispanic (change) (info box: White non-Hispanic population change, 2010 to 2020) Gain of 10,000 or more (HEX: #012661) 5,000 to 9,999 (#0B5476) 1,000 to 4,999 (#0473AD) 500 to 9,999 (#B5D5E5) -499 to 499 (#FFFFFF) -500 to -999 (#FFC0B4) -4,999 to -1,000 (#F46C59) Loss of 5,000 or more (#C81D00)

Black Non-Hispanic (change) (info box: Black non-Hispanic population change, 2010 to 2020) 1,000 or more (#0473AD) 500 to 9,999 (#B5D5E5) -499 to 499 (#FFFFFF) -500 to -999 (#FFC0B4) -4,999 to -1,000 (#F46C59) Loss of 5,000 or more (#C81D00)

Asian Non-Hispanic (change) (info box: Asian non-Hispanic population change, 2010 to 2020) Gain of 10,000 or more (HEX: #012661) 5,000 to 9,999 (#0B5476) 1,000 to 4,999 (#0473AD) 500 to 9,999 (#B5D5E5) -499 to 499 (#FFFFFF) -500 to -999 (#FFC0B4) -4,999 to -1,000 (#F46C59) Loss of 5,000 or more (#C81D00)

Hispanic (change) (info box: Hispanic population change, 2010 to 2020) 5,000 or more (#0B5476) 1,000 to 4,999 (#0473AD) 500 to 9,999 (#B5D5E5) -499 to 499 (#FFFFFF) -500 to -999 (#FFC0B4) -4,999 to -1,000 (#F46C59) Loss of 5,000 or more (#C81D00)

Under 18 (change) (info box: Change in population under 18 years, 2010 to 2020) 5,000 or more (#0B5476) 1,000 to 4,999 (#0473AD) 500 to 9,999 (#B5D5E5) -499 to 499 (#FFFFFF) -500 to -999 (#FFC0B4) -4,999 to -1,000 (#F46C59) Loss of 5,000 or more (#C81D00)

Use breaks for percent change maps (remove NTAs with less than 5,000 for each group in 2020-- if it can't be easily filtered, I can provide exact data for these maps): Population (percent change) (info box: Percent change in population, 2010 to 2020)-- (remove NTAs with less than 5,000 population in 2020-- if it can't be easily filtered, I can provide data) Gain of 50% or more (HEX: #012661) 25% to 49% (#0B5476) 15% to 24% (#0473AD) 10% to 14% (#5FA4CB) 5% to 9% (#B5D5E5) -4% to 4% (#FFFFFF) Loss of 5% or more (#FFC0B4)

White Non-Hispanic (percent change) (info box: Percent change in the White Non-Hispanic population, 2010 to 2020)-- (remove NTAs with less than 5,000 White NH in 2020-- if it can't be easily filtered, I can provide data) Gain of 50% or more (HEX: #012661) 25% to 49% (#0B5476) 15% to 24% (#0473AD) 10% to 14% (#5FA4CB) 5% to 9% (#B5D5E5) -4% to 4% (#FFFFFF) -9% to -5% (#FFC0B4) -14% to -10% (#F46C59) Loss of 15% or more (#C81D00)

Black Non-Hispanic (percent change) (info box: Percent change in the Black Non-Hispanic population, 2010 to 2020)-- (remove NTAs with less than 5,000 Black NH in 2020-- if it can't be easily filtered, I can provide data) Gain of 25% or more (#0B5476) 15% to 24% (#0473AD) 10% to 14% (#5FA4CB) 5% to 9% (#B5D5E5) -4% to 4% (#FFFFFF) -9% to -5% (#FFC0B4) -14% to -10% (#F46C59) Loss of 15% or more (#C81D00)

Asian Non-Hispanic (percent change) (info box: Percent change in the Asian Non-Hispanic population, 2010 to 2020)-- (remove NTAs with less than 5,000 Asian NH in 2020-- if it can't be easily filtered, I can provide data) Gain of 50% or more (HEX: #012661) 25% to 49% (#0B5476) 15% to 24% (#0473AD) 10% to 14% (#5FA4CB) 5% to 9% (#B5D5E5) -4% to 4% (#FFFFFF) -9% to -5% (#FFC0B4) -14% to -10% (#F46C59) Loss of 15% or more (#C81D00)

Hispanic (percent change) (info box: Percent change in the Hispanic population, 2010 to 2020)-- (remove NTAs with less than 5,000 Hispanic in 2020-- if it can't be easily filtered, I can provide data) Gain of 50% or more (HEX: #012661) 25% to 49% (#0B5476) 15% to 24% (#0473AD) 10% to 14% (#5FA4CB) 5% to 9% (#B5D5E5) -4% to 4% (#FFFFFF) -9% to -5% (#FFC0B4) -14% to -10% (#F46C59) Loss of 15% or more (#C81D00)

Under 18 (percent change) (info box: Percent change in the population under 18 years, 2010 to 2020)-- (remove NTAs with less than 5,000 under 18 in 2020-- if it can't be easily filtered, I can provide data) Gain of 50% or more (HEX: #012661) 25% to 49% (#0B5476) 15% to 24% (#0473AD) 10% to 14% (#5FA4CB) 5% to 9% (#B5D5E5) -4% to 4% (#FFFFFF) -9% to -5% (#FFC0B4) -14% to -10% (#F46C59) Loss of 15% or more (#C81D00)

Under 18 (info box: Population under 18 years, 2020) 25,000 or more 20,000 to 24,999 15,000 to 19,999 10,000 to 14,999 Less than 10,000

Under 18 (percent) (info box: Percent of the population under 18 years, 2020) 30% or more 25% to 29% 20% to 24.9 15% to 19% Less than 15%

CC @NYCPlanning/engineering @godfreyyeung @TylerMatteo

NYCPlanning / db-factfinder

Thematic Map SQL query #207