def load_blockgroup_data(db_connection):
def load_crimes():
crimes = ("SELECT EXTRACT(YEAR FROM date_reported) AS year_reported, "
" EXTRACT(MONTH FROM date_reported) AS month_reported, "
" CONCAT(tract, blkgrp) AS agg_area, "
" count(*) "
"FROM public.crime_geocoded "
"GROUP BY year_reported, month_reported, tract, blkgrp")
crimes = pd.read_sql(crimes, con=db_connection)
crimes = crimes.dropna(subset=["agg_area"]).set_index("agg_area")
return crimes
def load_parcels():
parcels = ("SELECT parcel_id, "
" inspection_date, "
" CONCAT(tract, blkgrp) AS agg_area "
"FROM parcels_inspections AS parcels "
"JOIN shape_files.parcelid_blocks_grp_tracts_nhoods AS shape "
"ON parcels.parcel_id = shape.parcelid")
parcels = pd.read_sql(parcels, con=db_connection)
return parcels
return load_crimes(), load_parcels(), util.population_in_tracts()
def load_tract_data(db_connection, only_guncrimes=False):
def load_crimes():
logger.debug("Read crimes per census tract")
if only_guncrimes:
crimes = ("SELECT EXTRACT(YEAR FROM date_reported) AS year_reported, "
" EXTRACT(MONTH FROM date_reported) AS month_reported, "
" tract AS agg_area, "
" count(*) "
"FROM public.crime_geocoded "
"WHERE weapon LIKE '%%GUN%%'"
"GROUP BY year_reported, month_reported, tract")
else:
crimes = ("SELECT EXTRACT(YEAR FROM date_reported) AS year_reported, "
" EXTRACT(MONTH FROM date_reported) AS month_reported, "
" tract AS agg_area, "
" count(*) "
"FROM public.crime_geocoded "
"GROUP BY year_reported, month_reported, tract")
crimes = pd.read_sql(crimes, con=db_connection)
crimes = crimes.dropna(subset=["agg_area"]).set_index("agg_area")
return crimes
def load_parcels():
logger.debug("Read parcels")
parcels = ("SELECT parcel_id, "
" inspection_date, "
" tract AS agg_area "
"FROM parcels_inspections AS parcels "
"JOIN shape_files.parcelid_blocks_grp_tracts_nhoods AS shape "
"ON parcels.parcel_id = shape.parcelid")
parcels = pd.read_sql(parcels, con=db_connection)
return parcels
return load_crimes(), load_parcels(), util.population_in_tracts()
