Skip to content

Instantly share code, notes, and snippets.

View stevevance's full-sized avatar
🏠
Working from home

Steven Vance stevevance

🏠
Working from home
View GitHub Profile
@stevevance
stevevance / query_v2.sql
Last active August 17, 2024 22:46
areas of selected wards, areas of the wards' overlapping ZIP codes, and areas of the overlap of the wards and IZP codes
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'
@stevevance
stevevance / query.sql
Last active August 17, 2024 21:15
Area of select Chicago wards (with and without parks) in square feet. Park data comes from OpenStreetMap, June 2024.
/* the main query */
select
st_area(first(vp.geom)) area_ward,
st_area(st_difference(first(vp.geom), st_union(am.geom))) area_ward_without_parks,
vp.metadata::int as ward
from view_places as vp, view_amenities_combined_2024_06_19b as am
where vp.type = 'ward'
and vp.metadata in (
'33',
'35',
@stevevance
stevevance / votes_on_SO2023-0004770.json
Last active August 7, 2024 05:11
Voting for ordinance SO2023-0004770
[
{
"date": "2024-06-11T05:00:00+00:00",
"votes": [],
"description": "Substituted"
},
{
"date": "2024-06-11T05:00:00+00:00",
"votes": [],
"description": "Recommended to Pass"

Dear Alderperson [their name],

My name is [your first and last name], and I am a Ward [x] resident residing at [your address]. I am writing to share my strongest support for Alderperson Lawson's ordinance to support the legalization of Accessory Dwelling Units citywide. As my elected official, I urge you to vote in favor of this ordinance.

Legalizing ADUs citywide will expand housing options, leading to numerous benefits that are well-documented in other parts of the United States and Chicago is just beginning to know. These include offering lower income renters more options, allowing older residents to age in place and supporting multi-generational households, offering additional income streams through rent to prevent the displacement of low-income homeowners and retirees on fixed-incomes, providing gentle density in existing neighborhoods without impacting its appearance, and reducing demand for sprawling, greenfield development. New construction ADUs are also highly efficient and often all-electric, leve

@stevevance
stevevance / two_flats_with_coach_houses.sql
Created November 24, 2023 18:30
Finding assessed values of two-flats in Chicago with and without coach houses
/* select properties with coach houses */
with properties as (select
pt.pin14,
pt.total_assessed_value,
pin_num_cards > 1 as has_coach_house
from assessor_single_mf_characteristics AS ac inner join propertytaxes_combined AS pt on ac.pin = pt.pin14
where year = '2023'
and city = 'CHICAGO'
and property_Class = '2-11'
and ac.units = 2
@stevevance
stevevance / population_density_in_adu_pilot_areas.sql
Created March 22, 2023 00:50
A query that counts the number of people in Census blocks that overlap Chicago's five ADU pilot areas.
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,
@stevevance
stevevance / affordable_lasalle.md
Last active February 15, 2023 16:32
affordability requirements for use of city funds in LaSalle Street Reimagined

Affordable Housing

Text from page 11 of the City of Chicago LaSalle Street Reimagined Invitation for Proposals

Housing proposals must provide at least 30% of the total units as on-site affordable units. The affordable units must be affordable to households earning up to 60% of Area Median Income (AMI), as updated annually, provided that (a) the maximum income level for any affordable unit may not exceed 80% of the AMI, (b) at least one-third must be affordable to households at or below 50% of the AMI, of which one-sixth must be affordable to households at or below 40% of the AMI, and (c) all income levels must be multiples of 10% of the AMI.

A selected developer can sell or lease the affordable units to an authorized agency, provided the rental subsidy by the authorized agency to the landlord combined with the rent paid by the eligible household may not exceed an amount affordable to households at 100% of the AMI, unless otherwise permitted by federal or state law. The authorized agency must sign a 30-

@stevevance
stevevance / zoning_capacity_by_communityarea.tsv
Created October 10, 2022 20:16
community areas ordered by zoned dwelling unit capacity
units communityarea
396565 Loop
175567 Near North Side
129848 Near West Side
65285 Lake View
47508 Grand Boulevard
45747 South Shore
44116 West Town
42172 Uptown
39826 Lincoln Park
@stevevance
stevevance / chicago_zoning_capacity.sql
Created October 10, 2022 20:02
Query to calculate how many dwelling units are allowed at each parcel in Chicago
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
@stevevance
stevevance / cast_dates.sql
Created October 9, 2022 00:13
handle invalid dates in Illinois Secretary of State incorporation data
create or replace function is_date(s varchar) returns boolean as $$
/* function via https://stackoverflow.com/a/25374982 */
begin
perform s::date;
return true;
exception when others then
return false;
end;
$$ language plpgsql;