mgraber commented 3 years ago

2020

Using DRAFT BYTES shapefiles, will update with published data eventually.

WITH 
blocks AS (
    SELECT 
        geoid,
        bctcb2020,
        RIGHT(bctcb2020, 10) as ctcb2020,
        SUBSTRING(bctcb2020, 2, 7) as ctcbg2020,
        ct2020,
        borocode,
        boroname,
        (CASE 
            WHEN LEFT(geoid, 5) = '36061' THEN 'New York'
            WHEN LEFT(geoid, 5) = '36005' THEN 'Bronx'
            WHEN LEFT(geoid, 5) = '36047' THEN 'Kings'
            WHEN LEFT(geoid, 5) = '36081' THEN 'Queens'
            WHEN LEFT(geoid, 5) = '36085' THEN 'Richmond'
        END) as county,
        LEFT(geoid, 5) as county_fips,
        ST_Centroid(wkb_geometry) as cb2020_centroid_geom
    FROM census_2020_draft.cb2020
),
tracts AS (
    SELECT
        a.geoid,
        a.bctcb2020,
        b.boroct2020,
        a.ctcb2020,
        a.ctcbg2020,
        a.ct2020,
        b.nta2020,
        b.ntaname,
        b.cdta2020,
        b.cdtaname,
        a.borocode,
        a.boroname,
        a.county_fips,
        a.county,
        a.cb2020_centroid_geom
    FROM blocks a
    JOIN census_2020_draft.ct2020 b
    ON a.borocode||a.ct2020 = b.boroct2020
),
fp_500 AS (
    SELECT 
        a.bctcb2020,
        ST_Intersects(a.cb2020_centroid_geom, b.wkb_geometry)::int as fp_500
    FROM tracts a, geo_lookups.flood_500 b
),
fp_100 AS (
    SELECT 
        a.bctcb2020,
        ST_Intersects(a.cb2020_centroid_geom, b.wkb_geometry)::int as fp_100
    FROM tracts a, geo_lookups.flood_100 b
),
park_union AS (
    SELECT ST_Union(wkb_geometry) as geom
    FROM  dpr_access_zone.latest
),
park_access AS (
    SELECT 
        a.bctcb2020,
        ST_Intersects(a.cb2020_centroid_geom, b.geom)::int as park_access
    FROM tracts a, park_union b
)
SELECT
    a.geoid2020,
    a.bctcb2020,
    a.boroct2020,
    a.ctcb2020,
    a.ctcbg2020,
    a.ct2020,
    a.nta2020,
    a.ntaname,
    a.cdta2020,
    a.cdtaname,
    a.borocode,
    a.boroname,
    a.county_fips,
    a.county,
    fp_500.fp_500,
    fp_100.fp_100,
    park_access.park_access
INTO census_2020_draft.geo_lookup
FROM
    tracts a
LEFT JOIN fp_500 ON a.bctcb2020 = fp_500.bctcb2020
LEFT JOIN fp_100 ON a.bctcb2020 = fp_100.bctcb2020
LEFT JOIN park_access ON a.bctcb2020 = park_access.bctcb2020
;

2010 to 2020

SELECT 
    b.ctcb2010 as geoid2010,
    a.geoid as geoid2020,
    a.bctcb2020,
    a.boroct2020,
    a.ctcb2020,
    a.ctcbg2020,
    a.ct2020,
    a.nta2020,
    a.ntaname,
    a.cdta2020,
    a.cdtaname,
    a.borocode,
    a.boroname,
    a.county_fips,
    a.county
FROM census_2020_draft.geo_lookup a
JOIN census_cb10_cb20 b
ON a.geoid = b.ctcb2020;

mgraber commented 3 years ago