Query to calculate how many dwelling units are allowed at each parcel in Chicago

chicago_zoning_capacity.sql

with properties as (
	SELECT 
		data.pin14 AS pin14, 
		ca.community,
  		joined_2.zone_class AS zone_class, 
  		CASE WHEN lot_area_per_unit IS NOT NULL AND lot_area_per_unit > 0 AND property_class != '2-99' 
        /* this excludes condos, property class of 2-99, because those parcels are duplicates of the footprint parcel */
  			THEN floor(area/lot_area_per_unit) 
  			ELSE null 
  			END AS units_allowed 
  	FROM propertytaxes_09_18_combined AS data 
  	INNER JOIN communityarea AS ca ON (ST_Intersects(data.geom_2021_coalesced, ca.geom) AND ST_Area(ST_Intersection(data.geom_2021_coalesced, ca.geom)) > ST_Area(data.geom_2021_coalesced) * 0.5)
  	INNER JOIN view_places AS place ON (ST_Intersects(place.geom, data.geom_2021_coalesced) OR data.city = 'CHICAGO') 
  	INNER JOIN zoning_2022_08_17 AS joined_2 ON (ST_Intersects(data.geom_2021_coalesced, joined_2.geom)) 
  	LEFT JOIN zoning_lookup_20220829 AS joined_234 ON (joined_234.zone_class = joined_2.zone_class) 
  	WHERE place.slug = 'municipality-chicago' 
  	AND ST_Area(ST_Intersection(data.geom_2021_coalesced, joined_2.geom)) > ST_Area(data.geom_2021_coalesced) * 0.5)
select 
	sum(units_allowed), 
	community
from properties
where units_allowed is not null
group by community