Create ZoneFacts table and endpoints

[NealHumphrey]

Instead of using our API endpoints to recalculate derivative fields adhoc, and instead of performing client-side reconfiguration of data into display fields, we want a master table to do these calculations every time new data is loaded and make it easier to join all these fields to the project table. We've previously referred to this as the 'NeighborhoodFacts' table.

This ticket should a) create the table and b) make the data in the table part of the filter_data endpoint (which has a record for each project/subsidy combo). This will make all of this data available to the combined filter/layer view that is being used in V2.

• [x] Add a function that runs after all data loading is completed that drops the existing records from the zonefacts table and recalculates all the fields. Each row of the table should be one "zone" with the relevant calculated fields. Initial columns should be those that exist in our existing layers data set, with rows filled in for each zone for each of "ward", "neighborhood_cluster" and "census_tract".

  • [x] zone_type and zone_specific (i.e. "ward" and "Ward 1" or "neighborhood_cluster" and "Cluster 32"). zone_type should match the relevant standard column name and zone_specific should match the value for that column that is used in other tables
  • [x] Calculated fields from the "census_with_weighting" api endpoint: 'poverty_rate', 'fraction_black', 'income_per_capita', 'labor_participation', 'fraction_foreign', 'fraction_single_mothers'
  • [x] All additional columns in the census table with weighting factors applied for each zone type (including duplicate rows for census_tract, which will completely duplicate the census table for ease of use).
  • [x] building permits: all permits past 3 months, construction permits past 3 months
  • [x] crime: violent crime past 3 months, non-violent crime past 3 months, all crime past 3 months, and each of these columns as a rate per 100,000 people (copy code from "summarize observations" api endpoint).

• [x] Left Join the project table to this table within the filter_data API endpoint. Do a left join 3 times, once each for ward, neighborhood_cluster and census_tract. For each join, the zones should be renamed zonetype_columnname. This means each row of the project data will have the denormalized data associated with all relevant zone types.

[NealHumphrey]

Merging initial portions of this into dev per PR #416

Still to do:

• Make changes as noted in the PR review

[NealHumphrey]

Merged in some more changes in #436 . Changes requested from that code version:

PR changes requested by @nealhumphrey

• [x] Don't back-calculate building_permits from Ward data. Zone_facts should report null values if a zone aggregation field (i.e. census_tract) is not available in the source table. Instead, we should geocode building_permits to have census_tract data available during cleaning.
• [x] Create zone_facts table using meta.json instead of sqlalchemy to match with other tables. Or, at a minimum, add zone_facts to meta.json so that the 'display_text' and 'display_name' can be used for documentation purposes on the front end.

And @jkwening 's list in the PR:

• add code that allows calculating census_tract values from ward and/or neighborhood_cluster data; will resolve null values for building permits data overriden by above
• download and process building permits 2017 data into the database Moved to new issue #437
• add code that includes zone_type aggregate in zones column clarify what this means??
• [x] connect zone_facts table to "filter_data" API end point and join project table accordingly
• [x] complete unit test confirming validity of zone_facts calculated values
• [ ] complete get_residential_permits method that is called in summarize_observations method

[jkwening]

@NealHumphrey I crossed the to do bullet that you asked clarification about; it seems I misunderstood the following action item:

zone_type and zone_specific (i.e. "ward" and "Ward 1" or "neighborhood_cluster" and "Cluster 32"). zone_type should match the relevant standard column name and zone_specific should match the value for that column that is used in other tables

I think we discussed this a couple weeks ago. I thought this implied that in addition to each unique zone_specific value for a zone_type populated in the zone column of zone_facts table you wanted the zone_type included and aggregate values populate. That is, zone column should include "ward", "census_tract", and "neighborhood_cluster" values in addition to their unique zone_specific values.

[NealHumphrey]

Ah, ok - yep, all I meant was that we do it as you've already done, with one column that designates whether it's Ward or Cluster or Tract etc.

[NealHumphrey]

@jkwening just a quick note - I crossed off the building permits bullet point b/c I made a separate issue for that one. I think the only things left on this for now are the unit tests bullet points