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 / chicago_pod_cameras_031816.geojson
Last active February 7, 2024 04:04
Map of Chicago's Police Observation Device (POD) cameras
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@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 / array_trim.sql
Last active May 11, 2023 15:26
Trim the string elements of an array in PostgreSQL (wrap this function around an array or another function that returns an array, like string_to_array).
CREATE OR REPLACE FUNCTION array_trim(text[])
RETURNS text[]
AS
$$
DECLARE
text ALIAS FOR $1;
retVal text[];
BEGIN
FOR I IN array_lower(text, 1)..array_upper(text, 1) LOOP
retVal[I] := trim(text[I]);
@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;
@stevevance
stevevance / bike_parking_letter_to_aldi.txt
Created August 11, 2017 16:16
letter to Aldi about their bad bike parking
Hello, I am thrilled that Aldi has reopened here, with a new design, and is more pedestrian-friendly, because one of the entrances is on the sidewalk.
However, the store doesn't have a good place to park a bike. The bike rack model that has been installed hold a very small number of bicycles (2-4 depending on how people use it; it's hard to park here because the metal tubes are so close together), but it is also an insecure model.
The bike rack uses thin, round metal tubing, which can be easily cut with a handheld pipe cutter. The bike rack is installed on the ground with only 2 thin screws, which can be removed with hand tools obtained from any hardware store.
Because of this insecure installation, and the trouble of shuffling my bike into a crowd of other bikes at this rack, I lock my bike to the guard rails at the shopping cart pen.
The store is only going to get busier with bicyclists as more of your previous customers (and all of the new residents who've moved in when the store was gone) see that
@stevevance
stevevance / adjacent_parcels.sql
Created January 25, 2022 03:03
How to find adjacent parcels
/* thanks to https://stackoverflow.com/a/12521268/464883 for help writing the query */
/* find 2 parcels that share an edge */
SELECT DISTINCT v1.pin14, v1.property_class
FROM view_cook_scavenger_sale_2022 v1
WHERE EXISTS (
SELECT v2.pin14 FROM view_cook_scavenger_sale_2022 v2
WHERE st_touches(v1.geom, v2.geom)
and v2.area >= 3000
)