Landscapes of Injustice

I've written the first pass at story maps for every title in the dbs. I think it's working, but we'll see what happens when Mr. Jenkins gets his hands on it. If it does work, I'll add an interactive query function to the map page so that you can type in a title or lot number to get its map.

I have the new VLA spreadsheet building, but it took a bit of reworking of the original transformations of db data, so I want to do a thorough check of the results. Also, I'm half-way through enhancing the web versions of these spreadsheets, and I'd like to get that done.

I've written, but not yet tested, functions for retrieving a list of V1 titles; getting a V0 title from a V1; and getting a V2 from a V1. I've also tweaked the way the plan GeoJSON is being generated so that the year is encoded in the filename for simplicity; and I've fixed the problem where the listings page for plans was not being generated. At least, I'm pretty sure I have; the results are still working through Jenkins. I've also raised a couple of queries for more detailed clarification on the spreadsheet plan.

The Maple Ridge data is proving problematic in the way that the VLA forms part of the process, so to analyse this, a new spreadsheet has been proposed (plan below). I'll begin work on this today.

*** Seventh plan modification: Adding a new spreadsheet for Maple Ridge VLA-sold properties***

 - For each SALE BY THE VLA TO ANY PURCHASER, nominal or not:

 - Let that sale be V1.

 - Based on V1, retrieve a preceding sale TO the VLA on that or any 
   overlapping property. 
     - We assume that properties were only subdivided, not combined, 
       so any sale to the VLA on an overlapping property constitutes 
       the sale of that property or its direct parent. This sale may also 
       be nominal.
       
 - Let that sale be V0.
 
 - Now retrieve the next non-nominal sale on any descendant 
   property of the property-set constituting the V1 sale.
   
 - Let that sale be V2.
 
Now output a single row as follows:

Row id (generated number)
V0 data
V1 data
V3 data

Data for each sale is identical to the data in the current lot-based
spreadsheet, except that for each sale we add the following 
additional columns:

 - VX_DECLARED_VALUE
 - VX_DECLARED_VALUE_PER_SQ_M
 - VX_DECLARED_VALUE_PER_SQ_M_2018
 - VX_MARKET_VALUE
 - VX_MARKET_VALUE_PER_SQ_M
 - VX_MARKET_VALUE_PER_SQ_M_2018
 
These columns are empty unless the title has a declared value 
and/or a market value.

Pace Mr. Jenkins, I have the downloadable package being created to support the chapter JSR is writing, and I've finished the data dictionary. I think this bit of the project should now be done.

Yesterday and today: JSR asked for more changes/additions to the original spreadsheet, and also for a second spreadsheet, organized by title. I've implemented the changes to the first spreadsheet, which is now renamed, and created the second. I found some anomalies in the data in the process, and fixed a couple of bad dates, while reporting some more suspicious numbers from the db. Also implemented an HTML version of the new spreadsheet linking to title maps, which will be handy.

This spreadsheet, which I started at the weekend, contains brief analyses of the numbers of properties which end up being included (directly or indirectly) in the main spreadsheet, compared with the total number of properties in the database, for each study area location.

At the same time, I diagnosed and fixed a bug in the generation of GeoJSON for maps, which would place two copies of the same property inside a single title category on the map.

Flags for nominal custodian sales and for sales intervening between T1 and T2. New plan for additional spreadsheet showing status of properties included and not included in the main spreadsheet, to be done next week.

I've got a working process that detects and removes actual duplicate rows from the table. But there's still the question of why we have multiple rows for the same starting property. I'm finding it impossible to figure that out so far.

Implementing the revisions from the latest discussions, I'm encountering situations in which the same property id has two or three rows. Only a few, but we can't figure out why they're happening. More to do, still in discussions with JSR.