hacks/hackers demo day

README.md

Hacks/Hackers Demo Day

Sept 6, 2016

Data Observatory

Eviction

Data sent from Erin at Anti-eviction Mapping Project: https://team.carto.com/u/eschbacher/dataset/sf_evictions_with_counts

INSERT INTO hacks_hackers(the_geom, name)
SELECT the_geom, geoid
FROM OBS_GetBoundariesByGeometry((
  SELECT ST_SetSRID(ST_Extent(the_geom)::geometry, 4326)
  FROM sf_evictions_with_counts), 'us.census.tiger.zcta5_clipped'
) As m(the_geom, geoid)

INSERT INTO hacks_hackers(the_geom, name)
SELECT the_geom, geoid
FROM OBS_GetBoundariesByGeometry((
  SELECT ST_SetSRID(ST_Extent(the_geom)::geometry, 4326)
  FROM sf_evictions_with_counts), 'us.census.tiger.census_tract_clipped'
) As m(the_geom, geoid)
WHERE geoid ILIKE '06075%'

Add up the number of evictions

UPDATE hacks_hackers As h
SET num_evictions = (SELECT sum(units)
                       FROM sf_evictions_with_counts As c
                      WHERE ST_Intersects(h.the_geom, c.the_geom)
                        AND EXTRACT(year from date_filed) = 2015);

Add context!

Evictions per 10k households

UPDATE hacks_hackers As h
SET num_evictions_per_10k_households = 1e4 * num_evictions /  OBS_GetMeasureById(name, 'us.census.acs.B11001001', 'us.census.tiger.zcta5')

Housing value from Zillow

UPDATE hacks_hackers As h
SET median_home_value = OBS_GetMeasureById(name,
   'us.zillow.AllHomes_Zhvi',
   'us.census.tiger.zcta5')

Demographic Segments

UPDATE hacks_hackers As h
SET segment_name = OBS_GetCategory(ST_PointOnSurface(the_geom), 'us.census.spielman_singleton_segments.X10')