def tractCentroids(request): """This endpoint returns census tract centroids used to determine circle position on map""" geos = get_censustract_geos(request) if geos is None: return HttpResponseNotFound("Missing one of lat/lon bounds or metro") tracts_geo_json = geo_as_json(geos) return HttpResponse(json.dumps(tracts_geo_json), content_type='application/json')
def msas(request): try: query = get_censustract_geos(request, metro=True) msas = {'msas': [msa.geoid for msa in query]} return HttpResponse(json.dumps(msas), content_type='application/json') except: return HttpResponseBadRequest("request failed; details: %s" % request)
def race_summary(request): """Race summary statistics""" geos = get_censustract_geos(request) if len(geos) > 0: query = Census2010RaceStats.objects.filter(geoid__in=geos) else: query = Census2010RaceStats.objects.all() return query
def msas(request): """return a list of MSA ids visible by bounding coordinates""" try: msas = get_censustract_geos(request, metro=True) msa_list = [metro.geoid for metro in msas] return HttpResponse(json.dumps(msa_list), content_type='application/json') except: return HttpResponseBadRequest("Invalid bounding coordinates")
def race_summary_csv(request): institution_id = request.GET.get('lender') metro = request.GET.get('metro') action_taken_param = request.GET.get('action_taken') action_taken_selected = action_taken_param.split(',') tracts_in_msa = get_censustract_geos(request) queryset = Census2010RaceStats.objects.filter(geoid__in=tracts_in_msa) file_name = 'HMDA-Census-Tract_2013_Lender%s_MSA%s.csv' % (institution_id, metro) response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = 'attachment; filename=%s' % file_name writer = csv.writer(response, csv.excel) #response.write(u'\ufeff'.encode('utf8')) # BOM (optional...Excel needs it to open UTF-8 file properly) writer.writerow([ smart_str(u"geoid"), smart_str(u"Total Population"), smart_str(u"Hispanic Percentage"), smart_str(u"White Only Percentage"), smart_str(u"Non Hispanic Black Only Percentage"), smart_str(u"Non Hispanic Asian Only Percentage"), smart_str(u"HMDA LAR Count"), smart_str(u"Total Households"), ]) for obj in queryset: geoid = "'%s'" % str(obj.geoid.geoid) lar_count = HMDARecord.objects.filter( institution_id=institution_id, geo=obj.geoid, action_taken__in=action_taken_selected).filter( base_hmda_query()).count() census_households = Census2010Households.objects.filter( geoid=obj.geoid).first() if census_households: num_households = census_households.total else: num_households = 0 writer.writerow([ smart_str(geoid), smart_str(obj.total_pop), smart_str(obj.hispanic_perc * 100), smart_str(obj.non_hisp_white_only_perc * 100), smart_str(obj.non_hisp_black_only_perc * 100), smart_str(obj.non_hisp_asian_only_perc * 100), smart_str(lar_count), smart_str(num_households), ]) return response
def loan_originations(request): institution_id = request.GET.get('lender') metro = request.GET.get('metro') action_taken_param = request.GET.get('action_taken') lender_hierarchy = request.GET.get('lh') peers = request.GET.get('peers') census_tracts = get_censustract_geos(request) query = HMDARecord.objects.filter(base_hmda_query()) #if lender param key is passed in if institution_id: institution_selected = get_object_or_404(Institution, pk=institution_id) if lender_hierarchy == 'true': hierarchy_list = institution_selected.get_lender_hierarchy( False, False) if len(hierarchy_list) > 0: query = query.filter(institution__in=hierarchy_list) else: query = query.filter(institution=institution_selected) elif peers == 'true' and metro: metro_selected = Geo.objects.filter(geo_type=Geo.METRO_TYPE, geoid=metro).first() peer_list = institution_selected.get_peer_list( metro_selected, True, False) if len(peer_list) > 0: query = query.filter(institution__in=peer_list) else: query = query.filter(institution=institution_selected) else: query = query.filter(institution=institution_selected) if len(census_tracts) > 0: query = query.filter(geo__in=census_tracts) if action_taken_param: action_taken_selected = action_taken_param.split(',') if action_taken_selected: query = query.filter(action_taken__in=action_taken_selected) #count on geo_id query = query.values('geo_id', 'geo__census2010households__total', 'geo__centlat', 'geo__centlon').annotate(volume=Count('geo_id')) return query
def loan_originations(request): institution_id = request.GET.get('lender') metro = request.GET.get('metro') action_taken_param = request.GET.get('action_taken') lender_hierarchy = request.GET.get('lh') peers = request.GET.get('peers') year = request.GET.get('year') census_tracts = get_censustract_geos(request) query = HMDARecord.objects.all() if institution_id: institution_selected = get_object_or_404( Institution, pk=institution_id) if lender_hierarchy == 'true': hierarchy_list = institution_selected.get_lender_hierarchy( False, False, year) if len(hierarchy_list) > 0: query = query.filter(institution__in=hierarchy_list) else: query = query.filter(institution=institution_selected) elif peers == 'true' and metro: metro_selected = Geo.objects.filter( geo_type=Geo.METRO_TYPE, geoid=metro).first() peer_list = institution_selected.get_peer_list( metro_selected, True, False) if len(peer_list) > 0: query = query.filter(institution__in=peer_list) else: query = query.filter(institution=institution_selected) else: query = query.filter(institution=institution_selected) if len(census_tracts) > 0: query = query.filter(geo__in=census_tracts) if action_taken_param: action_taken_selected = action_taken_param.split(',') if action_taken_selected: query = query.filter(action_taken__in=action_taken_selected) #count on geo_id query = query.values('geo_id', 'geo__census2010households__total', 'geo__centlat', 'geo__centlon', 'geo__state', 'geo__county', 'geo__tract').annotate(volume=Count('geo_id')) return query
def race_summary_csv(request): institution_id = request.GET.get('lender') metro = request.GET.get('metro') year = request.GET.get('year') if institution_id and metro: lar_data = loan_originations_as_json(request) tracts_in_msa = get_censustract_geos(request) queryset = Census2010RaceStats.objects.filter(geoid__in=tracts_in_msa) file_name = 'HMDA-Census-Tract_Year%s_Lender%s_MSA%s.csv' % ( year, institution_id, metro) response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = 'attachment; filename=%s' % file_name writer = csv.writer(response, csv.excel) writer.writerow([ smart_str(u"geoid"), smart_str(u"Total Population"), smart_str(u"Hispanic Percentage"), smart_str(u"White Only Percentage"), smart_str(u"Non Hispanic Black Only Percentage"), smart_str(u"Non Hispanic Asian Only Percentage"), smart_str(u"Originated Loans"), smart_str(u"Total Households"), ]) for obj in queryset: geoid = "'%s'" % str(obj.geoid.geoid) if lar_data.get(obj.geoid.geoid, None): lar_count = lar_data[obj.geoid.geoid]['volume'] num_households = lar_data[obj.geoid.geoid]['num_households'] else: lar_count = 0 # if there's no loan data we can still get household data num_households = get_object_or_404( Census2010Households, geoid_id=obj.geoid.geoid).total writer.writerow([ smart_str(geoid), smart_str(obj.total_pop), smart_str(obj.hispanic_perc * 100), smart_str(obj.non_hisp_white_only_perc * 100), smart_str(obj.non_hisp_black_only_perc * 100), smart_str(obj.non_hisp_asian_only_perc * 100), smart_str(lar_count), smart_str(num_households), ]) return response else: raise Http404("Invalid Institution or Metro")
def race_summary_csv(request): institution_id = request.GET.get('lender') metro = request.GET.get('metro') year = request.GET.get('year') if institution_id and metro: lar_data = loan_originations_as_json(request) tracts_in_msa = get_censustract_geos(request) queryset = Census2010RaceStats.objects.filter(geoid__in=tracts_in_msa) file_name = 'HMDA-Census-Tract_Year%s_Lender%s_MSA%s.csv' % (year, institution_id, metro) response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = 'attachment; filename=%s' % file_name writer = csv.writer(response, csv.excel) writer.writerow([ smart_str(u"geoid"), smart_str(u"Total Population"), smart_str(u"Hispanic Percentage"), smart_str(u"White Only Percentage"), smart_str(u"Non Hispanic Black Only Percentage"), smart_str(u"Non Hispanic Asian Only Percentage"), smart_str(u"Originated Loans"), smart_str(u"Total Households"), ]) for obj in queryset: geoid = "'%s'" % str(obj.geoid.geoid) if lar_data.get(obj.geoid.geoid,None): lar_count = lar_data[obj.geoid.geoid]['volume'] num_households = lar_data[obj.geoid.geoid]['num_households'] else: lar_count = 0 # if there's no loan data we can still get household data num_households = get_object_or_404(Census2010Households, geoid_id=obj.geoid.geoid).total writer.writerow([ smart_str(geoid), smart_str(obj.total_pop), smart_str(obj.hispanic_perc * 100), smart_str(obj.non_hisp_white_only_perc * 100), smart_str(obj.non_hisp_black_only_perc * 100), smart_str(obj.non_hisp_asian_only_perc * 100), smart_str(lar_count), smart_str(num_households), ]) return response else: raise Http404("Invalid Institution or Metro")
def race_summary_csv(request): lar_data = loan_originations_as_json(request) tracts_in_msa = get_censustract_geos(request) queryset = Census2010RaceStats.objects.filter(geoid__in=tracts_in_msa) file_name = 'HMDA-Census-Tract_2013_Lender%s_MSA%s.csv' % (request.GET.get('lender'), request.GET.get('metro')) response = HttpResponse(content_type='text/csv') response['Content-Disposition'] = 'attachment; filename=%s' % file_name writer = csv.writer(response, csv.excel) #response.write(u'\ufeff'.encode('utf8')) # BOM (optional...Excel needs it to open UTF-8 file properly) writer.writerow([ smart_str(u"geoid"), smart_str(u"Total Population"), smart_str(u"Hispanic Percentage"), smart_str(u"White Only Percentage"), smart_str(u"Non Hispanic Black Only Percentage"), smart_str(u"Non Hispanic Asian Only Percentage"), smart_str(u"HMDA LAR Count"), smart_str(u"Total Households"), ]) for obj in queryset: geoid = "'%s'" % str(obj.geoid.geoid) try: lar_count = lar_data[obj.geoid.geoid]['volume'] except: lar_count = 0 try: num_households = lar_data[obj.geoid.geoid]['num_households'] except: num_households = 0 writer.writerow([ smart_str(geoid), smart_str(obj.total_pop), smart_str(obj.hispanic_perc * 100), smart_str(obj.non_hisp_white_only_perc * 100), smart_str(obj.non_hisp_black_only_perc * 100), smart_str(obj.non_hisp_asian_only_perc * 100), smart_str(lar_count), smart_str(num_households), ]) return response