Area (in s.f.) of overlap between wards and ZIP codes (actually, ZCTAs) in Chicago

area_overlap_query.sql

with zips as (
	select
		vp1.zcta as name,
		vp1.geom as geom,
		st_area(vp1.geom) area_zip
	from b_zcta_il_in_2020 as vp1, b_wards_2022 AS vp2
	where st_intersects(vp1.geom, vp2.geom)
	and vp1.slug not in (
		'zip-60625-no-rosehill-cemetery'
	)
), wards as (
	select 
		vp.ward as name,
		vp.geom as geom,
		st_area(vp.geom) area_ward,
		vp.ward::int as ward
	from b_wards_2022 as vp
	where vp.slug not in (
		'ward-40-no-rosehill-cemetery'
	)
) 
select
	zips.name as zip,
	zips.area_zip as area_zip,
	wards.name as ward,
	wards.area_ward as area_ward,
	st_area(st_intersection(zips.geom, wards.geom)) as area_overlap
from wards, zips
where st_area(st_intersection(zips.geom, wards.geom)) > 0;

results.tsv