Landscapes of Injustice

After long discussions this morning, we've arrived at a detailed description of how the new db, in its first incarnation for testing purposes, will differ from the original Powell Street one. They're included below. There is some discussion still happening over the new fields in the Properties table, and whether they could be integers or have to be strings. Based on what's agreed so far, I've constructed the db itself (landscapes_mapridgedev) on the db server and imported the small amount of existing data into it. I've left the auto-increment values exactly what they were in the original dump, on the basis that we may more easily be able to distinguish data from the two locations if the records are merged at some point. I've also set up the user permissions, created reader and editor credentials, and set up the local_classes.php file for the Adaptive DB, but I haven't yet implemented a web interface for the new db because I want to be sure the details are nailed down first, and I'd also like to give it a distinct colour scheme so the RAs can tell which one they're looking at. Informal spec:

NOTES FROM SKYPE 2016-04-19 RE STRUCTURE OF DB

The Maple Ridge database will be based on the existing Powell Street database.

The existing data from Powell Street will be imported directly into the following tables:

      Locations
      Ethnicities
      Institution types

The ethnicity table will be hidden, as will the Institution Types table.

The Census Tracts table will be removed (it's not visible in the interface of the Powell Street db, but it's there).

The Properties table would be restructured with the following fields (* = new field. [] = links to other tables):

      id
      *Description
      *Township
      District
      *Section
      *Quadrant
      Plan
      *Sketch
      Lot
      [Location] THIS WILL BE FIXED TO MAPLE RIDGE, AND HIDDEN
      Street number
      Street name
      [Linked titles]
      [Related properties]
      Notes

The new Description field would be a plain-text field in which the RAs would insert a description of the lot which constitutes /what they would like to see/ for that lot in the drop-down list when selecting lots for a title. This would replace (possibly temporarily) an algorithm that would generate that content. If in the long run an algorithm can be specified to produce that data, the field would be hidden and the text for the drop-down list generated automatically.

The following fields would be removed from Properties:

     Subdivision
     Parcel

Note also that there is an old, currently-hidden field which still exists in the Powell Street db:

     prp_property_code
     
I would also remove that from the new db. There is also an original field called "prp_block" which was an integer value intended to identify blocks; when it became clear that blocks were not always identifiable by a pure integer, that was replaced by the current Block (prp_block_code) field, which is a short piece of text. I would also remove the prp_block field in the new db and rename the prp_block_code field to prp_block; and remove the prp_plan field and rename prp_plan_block to prp_plan, for simplicity's sake.

The following addition would be made to the Titles table:

A one-to-many field called "Extinguished lots" would provide a way to formally link a title to one or more lots that cease to exist as a result of the title and its associated plan. This would enable us to track when a lot ceased to exist, something we cannot currently do in the Powell Street db. Note that if research is done backwards (i.e. starting with the present titles and working back in time), this field will be difficult to complete at the time when a title record is first created, because the "extinguished" lots will not necessarily yet be in the Properties table. However, if working forwards, the lots will have been created in order to be linked from the preceding title, so it will be easier to remember to add them.

The ethnicity field would initially be hidden, but then made available later. Ditto for Instituion type.

Finally, I will build the new version of the database using the latest incarnation of my back-end system, which allows for a read-only view of the data; we don't have to use it, but if we do want to share access to the data with people outside the immediate team, we can easily do that without worrying that they might edit it. 

Although no concrete guidance has emerged yet, other than the fact that we will not include census tract information, we've had discussions today about the format of the db for Maple Ridge, and in particular how images of plans should be linked to it. At the moment, the most straightforward approach seems to be:

• Filenames of plans are precisely configured based on their identification numbers to be unique and predictable from a property record.
• Images are stored on the LOI filespace on home1t by the RAs.
• If extra metadata is required, it's stored in a text file with the same name as the image but a txt extension, alongside it.
• Property records, assuming they are correctly entered, can thus be automatically linked to the appropriate image on the filesystem.
• We may make images available through a symlink in the www folder.

JSR called with a request for substantial changes to the spreadsheets being generated for the GIS team. We worked out a spec (documented on Basecamp, and in email), and I've implemented it. The basics from a programmatic point of view are these:

• Rather than being by year, the spreadsheets are now being generated based on specific dates; although I'm doing the same original by-year data for 1920 to 1960, those sheets are now generated based on January 1 explicitly.
• Three specific meaningful dates in 1941, 43 and 50 have also been added to the set at JSR's request; these are the ones he's primarily interested in.
• Full ethnicity information is now being included, instead of just Japanese info, and it's provided for both buyers and sellers.
• Where institutions form part of the buyer or seller set, since they have no ethnicity, their presence affects the values of the boolean ethnicity fields in specific ways worked out in our email correspondence.
• Plan as well as block and lot now identify individual properties.
• Booleans are now in zeros and ones instead of text true/false.

In the process of doing this I've tweaked the lot-based XML view of the db quite a lot, and I anticipate doing more of that as we move forward.

SB sent me a hi-res JPG, so I vips-ed it and made a Zoomify version for the team to use.

Request from JSR to add these rows to the spreadsheet:

New_Title_Seller_DurationOwn_SincePrev, New_Title_Seller_DurationOwn_SincePrevNonNom.

So say:

New title:       1940-12-03

Prev title:      1940-11-03

Prev non-nom:    1940-10-03

That means that the prev non-nom was held for 31 days, the prev was held for 30 days.

Yesterday and today, many tweaks to the spreadsheet content: replacing true/false with 1/0, implementing a column assigning each title to a specific 5-year span of time, and a couple of other bug-fixes as well as tweaks requested by JSR.

Meeting to discuss setting up the db for the Maple Ridge research. The plan is to duplicate the current db, only empty; and then to focus first on tweaks to the structure of property records, which will most likely have different descriptors. Initially these are likely to be loosely-defined text fields, but may later be migrated to more sophisticated structures. We must have the initial db ready by April 21, and then R, the title search expert, will work with AM and RS to pilot retrieval of property records (probably initially about 150 pre-identified as being Japanese-owned). We'll then tweak the db before the 25th, so that it can be ready for the team to start entering titles on May 2.

JSR reported what he thought were problems in the spreadsheet output, but having investigated at length, I can't find any; all the data looks to be what we would expect from what's in the database. He also asked for some tweaks, all of which I've done except one, where I'm waiting for clarification on exactly how it should work.

MA and I went through the list of Japanese Provisional owners, independently, and compared notes; on that basis, 26 owners have been reassigned to Other, 2 to EastAsia_Other, and the rest to Japanese. For the record, these are the commands:

UPDATE `owners_to_ethnicities` set `ote_ethnicity_id_fk`="11" WHERE `ote_owner_id_fk` IN (8922,6660)
UPDATE `owners_to_ethnicities` set `ote_ethnicity_id_fk`="5" WHERE `ote_owner_id_fk` IN (8351,8400,8401,8441,8475,9340,9454,9472,9487,5450,9445,9403,9444,9455,9476,8442,8476,9474,9488,5309,5351,5410,5308,5350,5411,6749)
UPDATE `owners_to_ethnicities` set `ote_ethnicity_id_fk`="1" WHERE `ote_ethnicity_id_fk`="8"

I have a basic build process generating Excel spreadsheets, one for each year from 1920 through 1960, showing the last pertinent title information for each lot. The results are included in the build products.

Since these initial files are intended for mapping Japanese versus non-Japanese ethnicity, although I have included consideration values, I have not skipped nominal transactions. The spreadsheets are very simple in structure.

However, this has revealed a problem we suspected might be the case: Block + Lot is not a unique identifier. There are many cases where more than one plan has the same block + lot combination on it. Should we, though, assume that the combination of Plan + Block + Lot is unique?

An example: Block 1 Lot 51 appears in Plan 4759 and Plan 184. How are the GIS folks handling that? Are they, in the end, the same actual physical lot, just redefined on a new plan? Waiting to hear...

Over the last couple of days, in preparation for producing the spreadsheets needed by the GIS team, I've been reconsidering how we work with the data, and done two things:

• Set up a codebase for generating FODS files from XML table structures. When coupled with LibreOffice command-line headless conversion to other formats (such as XLSX), this gives us a much more robust way to write spreadsheets out.
• Created a transformation of the XML dump of the db file which reconfigures it to present a view based on the lot rather than the title. All our processing is based around transactions on lots, so this is a good head start on everything we want to do. This is now integrated into the build process.

The next steps involve creation of year-based views of lot ownership in spreadsheet form, based on these foundations.