CensusDataForDevelopers/create_weight_table.sql at master · richarddunks/CensusDataForDevelopers · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
CREATE TABLE demographics.demographic_data_by_711 AS (
WITH buffer AS (
  --create buffer shapes around points
  SELECT
  gid,
  ST_Buffer(geom::geography,100) geom
  FROM demographics.manhattan_711
), transform AS (
  --convert the buffer from geography to geometry
  SELECT gid, ST_Transform(geom::geometry, 4269) geom
  FROM buffer
), weight AS (
  --create the weighting based on the intersection area
  SELECT
  t.gid,
  dp1.geoid10,
  ST_Area(ST_Intersection(t.geom,dp1.geom))/ST_Area(t.geom) weight
  FROM transform t
  INNER JOIN demographics.dp1
  ON ST_Intersects(t.geom, dp1.geom)
  ORDER BY gid, geoid10
), --Create demographic features table from raw table

  SELECT
    gid,
    total_pop,
    --median age
    demographics.find_median_age(
      total_pop,
      total_under_5,
      total_5_9,
      total_10_14,
      total_15_19,
      total_20_24,
      total_25_29,
      total_30_34,
      total_35_39,
      total_40_44,
      total_45_49,
      total_50_54,
      total_55_59,
      total_60_64,
      total_65_69,
      total_70_74,
      total_75_79,
      total_80_84
      ) as median_age,
    --median age male
    demographics.find_median_age(
      total_male,
      male_under_5,
      male_5_9,
      male_10_14,
      male_15_19,
      male_20_24,
      male_25_29,
      male_30_34,
      male_35_39,
      male_40_44,
      male_45_49,
      male_50_54,
      male_55_59,
      male_60_64,
      male_65_69,
      male_70_74,
      male_75_79,
      male_80_84
      ) as median_age_male,
    --median age female
    demographics.find_median_age(
      total_female,
      female_under_5,
      female_5_9,
      female_10_14,
      female_15_19,
      female_20_24,
      female_25_29,
      female_30_34,
      female_35_39,
      female_40_44,
      female_45_49,
      female_50_54,
      female_55_59,
      female_60_64,
      female_65_69,
      female_70_74,
      female_75_79,
      female_80_84
      ) as median_age_female,
    --race stats
    nonHisp_whiteAlone/total_pop as percent_white_nonhispanic,
    nonHisp_aaAlone/total_pop as percent_black_nonhispanic,
    nonHisp_aianAlone/total_pop as percent_american_indian_alaskan_native_nonhispanic,
    nonHisp_asianAlone/total_pop as percent_asian_nonhispanic,
    nonHisp_nhopiAlone/total_pop as percent_native_hawaiian_other_pacific_islander_nonhispanic,
    nonHisp_otherAlone/total_pop as percent_other_race_nonhispanic,
    total_hisp/total_pop as percent_hispanic,
    --pop in owner occupied housing
    popInOwnerOccupiedHousingUnits/total_pop as percent_owner_occupied_housing,
    --pop in renter occupied housing
    popInRenterOccupiedHousingUnits/total_pop as percent_renter_occupied_housing
  FROM demographics.m_711_dem_data
)


);