areas of selected wards, areas of the wards' overlapping ZIP codes, and areas of the overlap of the wards and ZIP codes

query_v2.sql

with zips as (
	select
		vp1.zcta as name,
--		am.name as park,
		st_difference(first(vp1.geom), st_union(am.geom)) as geom,
		st_area(st_difference(first(vp1.geom), st_union(am.geom))) area_zip_without_parks,
		st_area(first(vp1.geom)) area_zip
	from b_zcta_il_in_2020 as vp1, b_wards_2022 AS vp2, view_amenities_combined_2024_08_14 as am
	where st_intersects(vp1.geom, vp2.geom)
	and am.type = 'park'
	and st_intersects(vp1.geom, am.geom)
	and vp2.slug in (
		'ward-33', 
		'ward-35',
		'ward-39',
		'ward-40-no-rosehill-cemetery',
		'ward-46',
		'ward-47',
		'ward-48',
		'ward-49',
		'ward-50'
	)
	and vp1.slug in (
		'zip-60076',
		'zip-60202',
		'zip-60613',
		'zip-60618',
		'zip-60625-no-rosehill-cemetery',
		'zip-60626',
		'zip-60630',
		'zip-60631',
		'zip-60639',
		'zip-60640',
		'zip-60641',
		'zip-60645',
		'zip-60646',
		'zip-60647',
		'zip-60657',
		'zip-60659',
		'zip-60660',
		'zip-60712'
	)
	group by vp1.zcta
	--, am.name
), wards as (
	select 
		vp.ward as name,
		st_difference(first(vp.geom), st_union(am.geom)) as geom,
		st_area(first(vp.geom)) area_ward,
		st_area(st_difference(first(vp.geom), st_union(am.geom))) area_ward_without_parks,
		vp.ward::int as ward
	from b_wards_2022 as vp, view_amenities_combined_2024_08_14 as am
	where vp.slug in (
		'ward-33', 
		'ward-35',
		'ward-39',
		'ward-40-no-rosehill-cemetery',
		'ward-46',
		'ward-47',
		'ward-48',
		'ward-49',
		'ward-50'
	)
	and am.type = 'park'
	and st_intersects(vp.geom, am.geom)
	group by vp.ward
) 
--select * from zips where name = '60640';
select
	zips.name as zip,
	st_area(zips.geom) as area_zip_without_parks,
	wards.name as ward,
	st_area(wards.geom) as area_ward_without_parks,
	st_area(st_intersection(zips.geom, wards.geom)) as area_overlap
from wards, zips;

results_v2.tsv