create table nyc_bgs_h3s as
select
geoid,
h3_polygon_to_cells(geom, 10)
from
nys_2021_census_block_groups
where
left(geoid, 5) in ('36061', '36005', '36047', '36081', '36085')
-- Get the count of cells in each block group
with a as (
select
geoid,
count(*) as count
from
nyc_bgs_h3s
group by
geoid
order by
count(*) desc
),
-- Join the total population to each H3 cell
b as (
select
h3.geoid,
h3.h3_polygon_to_cells as h3,
c.population as pop
from
nyc_bgs_h3s h3
join nys_2021_census_block_groups c on h3.geoid = c.geoid
),
-- Find the proportional population by dividing the total
-- population by the H3 cell count per block group
c as (
select
b.pop :: numeric / a.count :: numeric as pop,
b.h3,
b.geoid
from
b
join a using (geoid)
),
-- Find the scaled values for each target data point
d as (
select
c.*,
abs(70000 - bgs.income) as income,
abs(35 - bgs.age) as age
from
c
join nys_2021_census_block_groups bgs using (geoid)
),
-- Get the tree count in each cell
e as (
select
d.h3,
count(t.ogc_fid) as trees
from
d
join nyc_2015_tree_census t on d.h3 :: text = t.h3
group by
d.h3
),
-- Add the min and max values for each data point to an array
f as (
select
array [min(trees), max(trees)] as trees_s,
array [min(pop), max(pop)] as pop_s,
array [min(income), max(income)] as income_s,
array [min(age), max(age)] as age_s
from
e
join d on d.h3 = e.h3
),
-- Join the two previous CTEs together
g as (
select
e.trees,
d.age,
d.income,
d.pop,
d.h3
from
d
join e on d.h3 = e.h3
),
-- Calculate the 0 to 1 index
h as (
select
g.h3,
(
(g.trees :: numeric - f.trees_s [1]) /(f.trees_s [2] - f.trees_s [1])
) as trees_i,
1 - ((g.age - f.age_s [1]) /(f.age_s [2] - f.age_s [1])) as age_i,
1 - (
(g.income - f.income_s [1]) /(f.income_s [2] - f.income_s [1])
) as income_i,
((g.pop - f.pop_s [1]) /(f.pop_s [2] - f.pop_s [1])) as pop_i
from
g,
f
)
-- Add up to find the final index value
select
*,
trees_i + age_i + income_i + pop_i
from
h