search

CityOfPhiladelphia / ais

Address Information System
19 stars 8 forks source link

Store service area values as key-value pairs in new ServiceAreaTag model #51

Open bertday opened 8 years ago

bertday commented 8 years ago

Currently, service area values are calculated in make_service_area_summary and stored in the service_area_summary table, where each column corresponds to a service area. Since service areas can be added ro removed in config.py, the table is generated dynamically on each run and does not have a fixed schema in models.py. This eases the configuration process but has created problems with migrations and initializing new engine databases. It may be helpful to deprecate the service_area_summary in favor of a service_area_tag table with a key-value structure like;

|   id   |   street_address   |   service_area_id   |   value   |
=================================================================
|   1    |   12 OAK LN        |   census_block      |   1       |
|   2    |   12 OAK LN        |   rubbish_day       |   Thurs   |

@mjumbewu do you think this could be workable in terms of API performance? With the right indexes?

bertday commented 8 years ago

Note: this wouldn't affect/deprecate the service_area_summary table in GeoDB2, so we'd still need a script to export service area tags in the existing format.