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()