search

geoffcorvera / aphis-inspection-reports-dashboard

0 stars 0 forks source link

Add new table to database with pre-joined data from 'citations' & 'inspections' #25

Closed geoffcorvera closed 4 months ago

geoffcorvera commented 4 months ago

To let user explore data more easily with the UI, add a new db table with data combining useful citation & inspection information.

geoffcorvera commented 4 months ago

In #26 I used sqlite-utils to run a query that joins citations & inspections, output results to csv and create a new table from the file.

geoffcorvera commented 4 months ago

One workflow it enables is faceting by state, then by hash_id to find the worst inspection from the state with the most code infractions

http://127.0.0.1:8001/aphis_reports/citation_inspection?_sort=rowid&_facet=customer_state&customer_state=MO&_facet=hash_id

geoffcorvera commented 4 months ago

26 adds a make command to create new table with combined citations & inspections data & updates