Populating the Raw Inmate Data from the V1.0 API Database
It is possible to backfill the raw Inmate data from the V1.0 API database. The backfill will only go back to August 17th, 2013.
To do this first load data from the V1.0 API database into the following in memory models (logical view):
The select command used to find the relevant inmate info is:
- select * from countyapi_countyinmate where booking_date >= '2013-08-17' or discharge_date_earliest >= '2013-08-17'
Notice that each day consists of a list of inmates in Jail on that day. The Inmate entries point to three lists:
- Charges
- Court Dates
- Housing Locations These lists are a date and a pointer to a detail entry for the list type.
To output the raw Inmate data means to walk through the list of days and for each day output an entry for each inmate that was in jail that day, including their charge associated with that day, their next court date and the housing location they were in on that date. Simple.
By breaking the problem up into two steps loading the information into an easily walkable set of models and then walking them makes the population problem easy to solve.
Once this is down populating the V2.0 database means just running its data loader and it will read in all of the data files and as a result the database will be fully populated.