A query that counts the number of people in Census blocks that overlap Chicago's five ADU pilot areas.

population_density_in_adu_pilot_areas.sql

WITH place_for_cra_lending AS (
	SELECT 
		geom, 
		st_area(geom) AS area, 
		st_buffer(geom, 150) AS geom_buffer 
	FROM view_places WHERE type = 'chicagoadupilotarea'
), blockgroups as (SELECT 
	vp.metadata AS geoid,
	vp.slug AS slug,
	vp.name AS name,
	ST_AsGeoJSON(ST_Transform(ST_Simplify(vp.geom, 0.009), 4326), 5) AS geojson,
	ST_Within(vp.geom, place.geom_buffer) AS geom_within,
	vp.geom
FROM place_for_cra_lending AS place, view_places AS vp
WHERE 
	vp.type = 'censusblock'
	AND (ST_Within(vp.geom, place.geom_buffer) OR 
		CASE WHEN place.area > 10 * 43560 /* 10 acres */
		THEN st_area(st_intersection(vp.geom, place.geom)) / st_area(vp.geom) >= 0.05 /* at least 5 percent of a Census tract overlaps with the given Place */
		ELSE st_area(st_intersection(vp.geom, place.geom)) / st_area(place.geom) >= 0.1 /* at least 10 percent of the given Place overlaps with a tract */
		END
	)
) select 
	slug,
	bg.geoid,
	p1_001n_population as population,
	geom_within,
	bg.geom
into m_adu_pilot_areas_population_pop2020
	from blockgroups AS bg left join d_census_2020_illinois_population_blocks using (geoid);