Landscapes of Injustice

When the LOI site is rebuilt, one of the first tasks the build process performs is to download the archive-media-list.txt file from the server. This list is used in the diagnostic processes to check the existence of items on the server and in the HTML page creation. The XSLT function loi:getResponseFromServer (in the globals module) checks each pointer to an item on the server and checks whether or not that list contains that item, returning 404 if it's not on the list, 401 if it's on the list but is not public, and 200 otherwise.

That list is in /home1t/loi/www/archive and should be regenerated automatically

I converted the oral history mp3 files to m4a using ffmpeg:

for i in *.mp3; do ffmpeg -i "$i" -vn -ar 11025 -c:v aac "${i%.*}.m4a"; done

I uploaded a file using Transmit to the home1t/sarneil folder

cantaloupe.hcmc.uvic.ca% pwd
/home1t/sarneil

cantaloupe.hcmc.uvic.ca% ls -al ToDo.txt
-rw-rw-r-- 1 sarneil tapor 5691 Jun 10 14:52 ToDo.txt

cantaloupe.hcmc.uvic.ca% sudo su - loi
[sudo] password for sarneil:
su: warning: cannot change directory to /home/loi: No such file or directory

-bash-4.1$cp ToDo.txt ../loi/www/fb_dev/

-bash-4.1$ ls -al ../loi/www/fb_dev/ToDo.txt
-rw-rw-r-- 1 loi loi 5691 Sep 15 14:10 ../loi/www/fb_dev/ToDo.txt

To over-simplify a bit
- other than uploading and deleting the files, treat each of two Transmit windows as if they're read-only
- treat the terminal window treat it as if it has write but no read privileges
It's a pain to have to have numerous ftp and terminal windows open to the same directories, but with each logged in as different users (and keep track of which window is which user) in order to see, confirm and do even something as simple as this.

I'm using invisible captcha on the form for the museum feedback to prevent spam attacks etc. On form submit the action is a php page which attempts to get_file_contents of the file containing the json object with the captcha info in it.
The problem is that the php.ini file we're relying on for php7 has allow_url_fopen = 0, and I haven't been able to figure out a way to override that in a local php.ini file.
So, I've instead in the .htaccess file I've setEnv UVPHP_VERSION 56, because the php.ini file for php5 has allow_url_fopen = 1. So the get_file_contents method works, so the captcha verification works, so the form submission works and no errors are thrown.
Obviously, if anything happens to cause us to use php7, we've got to solve this problem more elegantly. One possibility is to have javascript do the verification https://developers.google.com/recaptcha/docs/verify or https://stackoverflow.com/questions/51507695/google-recaptcha-v3-example-demo

LOI has a number of search pages and since the site is massive, it's not feasible for it to use the standard static search approach for using standard exclusions (since the process would have to parse 19,000 files for a search of 300, for instance). So here's the process of creating a new search page:

1. Create a new map-entry in the code/generated/create_search_pages_master.xsl. The id should be the document's id (i.e. search_blort) and the value should be the search page's title ("Search across all the Blorts"). Note that this step may not be necessary in future if the searches pages are kept statically in the site (i.e. if custom text needs to be added etc--at that rate, the pages should just be stashed in an about folder and generated like the rest of the pages).

2. Add new metadata for the XHTML5 files in xhtml_head_module.xsl. The way I've done it so far is to add a new function (loi:createBlortMeta()) that is called if the document needs it (i.e. IF $documentType = "blort" THEN loi:createBlortMeta()). The blort meta function should create

<meta>

tags as per the Static Search documentation (add a meta with a class of staticSearch.(bool|num|date|desc), a `@name` and a `@value`): https://github.com/projectEndings/staticSearch/blob/master/docs/staticSearch.html.

You should also add a new class--call it blortMeta--so that your config can pinpoint that class of metas not to exclude (a weird double negative: by default the static search includes all filters [aka all meta tags] so we need to exclude all of them EXCEPT the blortMetas since we want those to show up on the search page).

3. Create a new config at the root of production, likely by copy pasting. It should likely contain all the same weighting rules as the other configs, but you will need to change the search file path (from whatever_search to blort_search). You'll also want to add a filter exclusion for everything that's not a blort meta--something like so:

       <exclude xpath="meta[not(contains-token(@class,'blortMeta'))]" type="filter"/>

4. In the build, create a new task for creating your blort search and, in that search, use the custom <createSearch> task. This task is just a simple function, basically, for doing the repeated operations for generating the sub-search pages. It just copies all of the necessary files over, does the search on them, moves all the files back out, and deletes the copied files. So it'll look something like:

   <createSearch 
            configPath="../search_config_blort.xml"
            searchTempDir="${site.dir}/blort"
            pattern="blort_**.html"
            searchFile="blort_search.html"
        />

Where:
- configPath is the path to the configuration
- searchTempDir is the same of the temporary search folder (name it whatever you'd like, but I'd use blort)
- pattern is the match pattern for the files (i.e. include="{$pattern}"). blort_* for instance.
- searchFile is the name of the search file html; here it's blort.html

5. Add that target to the main search target as a depends, and then run the search as either part of the full build (just ant) or by itself if you have a copy of the build already completed (ant search)

- Note that once all the searches are complete, the search pages (all pages starting search_) are passed through a quick XSLT that changes the styling/grouping of the search pages just to make it more consistent with the LOI UI. That might change in future or simply be eliminated; it is not a crucial step--just an aesthetic one.

- Also note that there is a process during the getStaticSearch target (the first one in the build's search target) that modifies staticSearch/build.xml such that the JVM is forked and given 4GB of memory; we haven't added any way for the static search codebase to get more memory conditionally (and we don't want to force people to use 4GB of RAM), so this is just a quick hack around that.

We wanted a folder called OralHistoryDocs to contain two subfolders "originals" and "redacteds". We wanted the originals folder to be not accessible other than to members of a specified user gropu (i.e. didn't want people able to browse to that folder in the web interface).

Here’s the deal with “hidden” folders in svn
To make a folder hidden, you have to add something like this to the svnusers file (user landscapes/oralHistoryDocs/originals as the example):
[landscapes:/oralHistoryDocs/originals]
* =
@landscapesradisheds = rw
The first line specifies which repo and path to affect
The second line denies read and write access to all users
The third line allows read and write access to members of the group landscapesradisheds

Results In the web-based interface:
- if I browse to landscapes/oralHistoryDocs I do not see the originals folder even if I'm logged in as a member of the landscapesradisheds group
- if I browse to landscapes/oralHistoryDocs/originals logged in as a member, I see the contents of that folder. I haven't tested this, but I'm assuming that if I'm not a member of the group, the authentication will fail and I won't get in.

Results on the command line:
The hidden folder seems
- to be invisible to the svn crawler
- has a record in whatever db structure svn uses
- that it's contents can be addressed

If I create an oralHistoryDocs folder locally and then svn checkout the oralHistoryDocs folder from the server, I do not get the originals subfolder, nor any of the files in it. If I then try to create an originals folder on the local copy and svn add it to the repo, I get at “originals folder already exists, so can’t be added” error from svn. So, there seems to be no way to get a nested svn-hidden folder to behave at the command line like an svn-normal folder.
On the other hand, if I create an originals folder locally and then from within that folder do an svn checkout of the originals folder from the server, I get the contents of the originals folder from the server. I can update, add, delete commit as normal. So, for each hidden folder in the server copy of the repo, the user would have to create a separate svn repo for just that folder and execute svn commands as usual, which will update the nested folder on the server.

Structure on server:
landscapes
- oralHistoryDocs
- - originals
- - - a.txt
- - - b.txt
- - redacteds
- - - c.txt

Structure on local
landscapes
- oralHistoryDocs
- redacteds
- - c.txt
oralHistoryDocsOriginals
- a.txt
- b.txt

I suspect the commands are being run as some kind of system user (which is not a member of the landscapesradisheds group), but have done no research.

GL discovered that the majority of the few hundred un-used person entries in the personography are from a single object in a TINA. He put pointers to the people in there, which is the right thing to do, but it ends up making the page too big for use once all the standoff people are injected. Since we already special case the directories for the exact same reason, we came up with an alternative plan: create a new pointer--"ajax:"-- that can precede another point:

"ajax:prs:obac1"

The resoluton of the ajax pointer is simply to spit out whatever it prefixes:

"ajax:prs:obac1" --> "prs:obac1" --> "names_o.xml#obac1"

Currently we add these "ajax:" links in the original xml creation only; I see no reason for an encoder to ever need to do that, but it will be helpful for lots of documents going forward (like the AZ Index, which will be gigantic).

Also discussed lots and titles with MH. Saxon was giving up during the standalone xml validation; the files were too big and too many. But we took this an indicator that we might not be doing something that made any sense. So rather than creating horizontal tables, we'll just links to its own map and its story.

Following conversation with SA and MH, we've decided to take MH's story/lot outputs from the old build process into the repo and use those as a basis for creating new story/lot pages for the site.

Rather than create a large map for each title, we'll do the following:

• If a lot or a title has an associated story, then add a link from the page (i.e. title_202.html) to its associated story (title_202_story.html)
• The story page will include the map and a combined table of all the data associated with that story

I'm now in the process of creating these story pages (sans map) and it's fairly straightforward (though it still takes a bit longer than I'd like in the build process).

This post gathers together a bit of the work that's been done so far for LOI, in no particular order.

• GL has all of the RADish objects splitting into their own files; this works nicely, but it exposes that we need to get the metadata for those objects finished. He's coming up with some good diagnostics for flagging these and we're considering some boilerplate messages for objects that are not yet finished
• All documents are being processed properly and are, more or less, rendering correctly; still lots of work to be done there, however.

• All AJAX fragments are being created properly. I've taken a slightly different approach: we construct the Ajax fragments BEFORE we create the standalone. Then, in the standalone, we just inject the ajax fragments that we need into the standoff bit (the `<text type="standoff">`). We create the XHTML renderings of those AJAX fragments as part of the HTML build.

  • Note that standalone processing does not yet inject the taxonomies/categories etc into the build. That will be necessary
• All XML is being validated; next step will be to get the XHTML validation working.
• The site build accepts the standard -DdocsToBuild parameter, but I've added Regex handling so that you can do something like -DdocsToBuild=protest_letter_\\d+ (note the double slash) and build all of the protest letters. (The double slash is necessary since ant removes a single slash; this could likely also be prevented by doing docsToBuild='protest_letter_\d+').

We still need to get the maps/stories back into the build, which will be a significant amount of work, but at least MH already has all the code for processing them from the dbs and the structure working, so that should be adaptable.

Met with SA today to discuss LOI site plan, now that migration is complete and all OH metadata shells are in the repo.

We'll follow Endings principles and create as many things as we think useful, while staying wary of file sizes.

A few points:

• Directories will not get all of the standoff content embedded in the standalone version of the documents (since it would end up embedding thousands of entries that aren't necessarily helpful). But the links will turn to Ajax calls if JS is enabled

• Names will have popups, constructed from Ajax fragments, that will provide:

  • Documents that tag this name
  • Other name entries that have this last name
• The name index will be split alphabetically and will be referencable by id
• For TINA: all of the file level items will become their own pages. This follows how we do boat ledgers etc.

• Most pages will, at least for this preliminary documentation stuff, feature two main sections:

  • A media section that will house whatever media bit (facsim, recording, document, map) it needs to. 
  • A transcription bit that will house any text / transcription etc.

Lots of work recently on getting the Migration build and schema progressing. Currently, I have a process (in ~/production/utilities/migration) that migrates all of the TEI data from the trunk/ directory, re-organizing and normalizing where possible; this includes:

• The personography is now split into alphabetical files (personography_a, et cetera); kept whole, the personography takes forever to validate on the best of machines, so this will allow better working conditions. Plus, in whatever interface we eventually create, the index of people will necessarily need to be split into alphabetical files for browsing etc.
• The RADish files are now broken into smaller bits, with special processing-instructions in the fonds list that explain the lower-level items that nested within a fonds; this will make working in these files simpler, too.

I've also started work on a new schema. After discussion with GL and SA, I think the best approach will be to create a harmonized schema that's written from scratch but embeds all of the previous work done by MH, SA, and GL on the various cluster schemas.

The approach I've taken is to create a new schema (production/data/schema/LOI.odd) that will now serve as the master ODD file. I've included all the modules we need and am slowly working my way through extending the schema where necessary. There's a few departures from the TEI schema: item can be a root element (see above), list can thus be empty (insofar as it is populated just with processing instructions), address can be mixed content, and that seg/@subtype can be data.enumerated rather than a single token. The last two were additions in the loiDirectories.odd, which I'll need to talk with MH about. Address having mixed content is everywhere in the directories, so I think that's a good rule to retain, but enumerated subtypes only appear a handful of times, so there might be a better solution here.

There's lots left to do here and many decision to be made, but the work is progressing steadily; I've also made a spreadsheet for SA and GL that attempts to account for and organize the various datasets so we can strategize on how to bring everything into coordination.

Met with SA and GL to familiarize myself with the various datasets and to plan out steps going forward. I am going to go through the current data and appraise the current processing, et cetera and come up with a plan for the next steps, which will include reviving the Infrastructure Google Doc (authored by me, SA, and GL) and making suggestions about both the technical infrastructure and the conceptual infrastructure for the data.

The goal here will be to get everything harmonized, documented, and constrained so that the data will all be amenable to conversion to HTML in future.  

Based on further experiments by Lindy, Erin and Lindsey, we've now got the following situation:
Archives:
done so far : ~2000 warning messages
still to do : ~13000 warning messages
going at : ~100 warning messages / day
Therefore, about 130 person-days (13000 warnings / 100 warnings per day) or 900 person-hours to go as of July 22.

Case Files
done so far : 11 reels
still to do : ~120 reels
going at : 5-6 minutes per case file or about 2.5 person-days per reel (to do person, family, gender, citizenship, where sent to, if exiled to Japan, birthday, occupation, employer, home address)
Therefore about 300 person-days (120 reels x 2.5 person-days / reel) or 2000 person-hours to go as of July 22.

Lindsey has 14 days to go so should get through about 1400 warnings.
Lindy and Erin each have 24 days to go so should get through about 10 reels each, 20 reels total.
As of Aug 31
total warnings done = 3500; warnings to go = 11,500 or about 120 person days or 17 person-weeks.
total Case File reels done = 30; Case File reels to go = 100 or about 250 person-days or 35 person-weeks.

Met the rest of the Landscapes team today. Chatted with GL and SA about various plans going forward, which might mean some repository reorganization and the possibility of ODD chaining. Lots of work to do, but I think it will all be worthwhile for future linked data possibilities.

(Posting hours from yesterday). Met with GL to catch up and to discuss future plans. Plotted out some actions and then sent out a few questions regarding the TINA_NNM. 

Time: 90 min

This post details a method used to fix the following problem:

XML files where downloaded through a web browser from revision.hcmc.uvic.ca. They were XML in UTF-8, but Apache didn't know that so it presented them as 8859-1, and the browser happily believed it. All Japanese characters inside the files therefore got borked.

Work proceeded without this being noticed, resulting in a pair of -ography files in the RADish part of the repo which had lots of useful work invested in them, but whose Japanese characters were all completely broken.

Googling around found the Python tool FTFY, which works a treat; it can disembork borken Unicode with remarkable effectiveness. The only situations where it failed were cases of a single isolated character which itself was an archaic or obsolete form.

So the question was how to present the isolated blocks of broken text to FTFY, get them fixed, and then re-integrate them into the file. This is how:

First I cloned the FTFY repo, and ran python3 setup.py install to get a command-line tool (this works better than running stuff in the python interpreter). Then I wrote two XSLT files:

RADish/xsl/fix_chars_1_extract_text_nodes.xsl processes the original file, and finds all text nodes which are ancestor::*[@xml:lang='ja'] or parent::g. It replaces each text node with a temporary &lt;distinct&gt; element with a unique xml:id. It also creates a separate text file which consists of a list of each of those ids, followed by a colon, followed by the borken texten. Then:

ftfy -o outputfile.txt inputfile.txt

fixes almost all of the text inside that text file.

RADish/xsl/fix_chars_2_reinsert_text_nodes.xsl then reads the external file and builds a hashmap from it, then processes the temporary &lt;distinct&gt; elements back into text nodes containing the unborken Japanese.

NH is now in the process of manually fixing the few dozen remaining issues, after we devised some XPath to discover them and fixed a hundred or so together to get the hang of it.

180 minutes.

Created a new spreadsheet for MO at JSR's request, based on titles sold by the Custodian and their preceding titles. May need to do some additional map work, to integrate multiple existing JSON files to create a single one for each row. 180 minutes.

The FODS tables to TEI tables conversion is now working; a quick spot check seems that the results are correct, but we'll need to run some diagnostics on it both to ensure that the conversion worked correctly and to identify any oddities in the source data. Added a bunch of documentation, too.

Spent some time evaluating the spreadsheets and thinking through the best process for turning them into RADish. Got a basic build set up with a conversion process. So far, the process looks like so:

1. Copy the files into a temporary directory and, in doing so, clean up the filenames (no brackets, no spaces)
2. Take those files and convert them to FODS using soffice (there's lots there so it takes a while)
3. Then, take those FODS and convert them into a TEI table; I'm still working on this bit, but, so far, I have a process whereby the XSLT gets all the fods in a particular directory (using collection()) and then combines them into TEI document with multiple tables. This might not be the right approach in the long run, but I think it makes the most sense for now, particularly if the various spreadsheets in a collection need to be reconciled

Coded the third new spreadsheet, which wasn't as straightforward as the others. Updated documentation and data dictionary. The final spreadsheet has been dropped from the plan because it doesn't actually make sense.

After some debugging, I've got the last-custodian and no-custodian-t3 versions of the spreadsheets working (I think -- Jenkins is running now), and I've mapped out how the third variant should work. That third variant, as it's currently specified, makes no sense at all to me, though, so I've written to JSR to get some clarification before I put in the considerable amount of work necessary to create it. Meanwhile I can work on the new titles spreadsheet.

Parameterized a couple of functions and implemented the changes required to get #3 on plan 9 done (that's the simplest). Running the test now.

Today was finalizing plan #8 and completing it (more or less); then formulating plan #9:

***********************
***
Eighth plan modification:
***

1. DONE: Test the assumption that Maple Ridge properties were heavily transacted between the T2 in the current spreadsheet and 1949-04-01.

2. DONE: Add a column to the lot spreadsheet which shows the full consideration in 2018 dollars for each title. E.g. T1_CONSIDERATION_2018.

3. DONE: In the lot-based spreadsheet, add a T0, which is the last non-nominal transaction prior to T1 which includes an ancestor property of the root property (for all rows in the spreadsheet).

4. DONE: Add documentation of the VLA spreadsheet to the data dictionary.

5. DONE: Specify end date for T5s in data dictionary.

6. DONE: Fix the sequencing of titles in the BreezeMap output. (CAVEAT: there appears to be a small subset of titles for which this is not working, including 26684. I can't figure out why yet, but it seems to be working fine for the vast majority, so unless it's a priority I'll put it aside for now.)
***********************

This is plan #9, for your final approval:

***********************
***
Ninth plan modification:
***

1. Rename the original lot spreadsheet property_stats_data_by_lot_2018_orig.

2. Create a post-custodian sales spreadsheet of titles
    (property_stats_data_by_title_post_custodian_2018) like this:
      a) Get all custodian sales.
      b) For each custodian sale title, get the properties.
      c) For all those properties, get descending properties.
      d) Get all titles covering that expanded set of properties.
      e) Filter those titles to exclude any which pre-date the
         original custodian sale.
      f) Add the remaining titles to a list.
      g) For each distinct title in the list, ordered by title id,
         output a row.

      QUESTION: Do you care about nominal versus non-nominal in
                this spreadsheet?

3. Create a new version of the original lot spreadsheet
    (property_stats_data_by_lot_2018_no_cust_T3) in which
    T3 is never allowed to be a custodian sale. Call this the
    no-custodian-T3 rule.

4. Create another new version of the original lot spreadsheet
    (property_stats_data_by_lot_2018_last_cust) in which we always
    use the last transaction by the custodian rather than the first
    (the last-custodian rule).

5. Create a new version of the current lot spreadsheet
    (property_stats_data_by_lot_2018_strict_descent) where the
    descendant-property scenario is modified such that at each
    stage, we only retrieve descendants of properties in the
    preceding stage, rather than any descendant of the ur-property.

    QUESTION: does it matter that this will inevitably give rise
              to chains where the later titles have no land in
              common with the earlier titles?
    QUESTION: should this spreadsheet use the no-custodian-T3 rule?
    QUESTION: should this spreadsheet use the last-custodian rule?

******************* 

Did some work on the developing schema for RADish.

As instructed by JSR, did a deep dive into the property. Results:

Summary: the lot spreadsheet appears to be constructed (in this test case, at least) according to the rules that we set up. The full process for checking this appears in a short version and a long version below, but first, my take:

MY TAKE ON THIS:

There are two issues here. First, the Maple Ridge properties start to fragment very quickly indeed, giving rise to a huge number of descendant properties and therefore titles; the chances of getting a simple, clean sequence which aligns with the narrative you're chasing are probably pretty slim.

Second, we don't have a rule which says that when we are retrieving T3, it should NOT be a custodian sale IF the T2 is a custodian sale. I think it would make sense to have that rule, to avoid the situation we see in this case where T2, T3 and T4 are all custodian sales. Alternatively, we could adopt a rule which says that when there are multiple custodian sales to choose from at the T2 stage, we always take the latest rather than the earliest. Nevertheless, these measures may not solve your problem; do we need a similar rule that says that if T3 is a VLA sale, then T4 is not allowed to be one? What if T3 is not a VLA sale, but T4 turns out to be one?

Another option would be to adopt a rule which says that at every stage, the subsequent property in the sequence must be a descendant property not of the original ur-property but of the property or properties which were on the preceding retrieved title -- so T3 must be on descendant properties of the properties on T2, not on T1, and T4 must cover descendant properties of T3. This is more likely to result it what looks like a coherent sequence, but it is also much more likely to fail to retrieve a successful sequence at all, resulting in a much diminished number of spreadsheet rows, and therefore less statistically convincing results.

---------------------------------------------------------------------

DEEP DIVE INTO PROPERTY 21178

Base property: T:12 S:20 Q:NE 1/4 PL:3189 L:1 (p_21178)

---------------------------------------------------------
QUERY on overlaps.xml:

declare default element namespace "http://hcmc.uvic.ca/ns";

let $baseProp := '21178',
$overlaps := //overlap[@prop1=$baseProp or @prop2=$baseProp][xs:float(@percentProp1InProp2) ge 50],
$overlapIds := for $o in $overlaps return ($o/@prop1, $o/@prop2),
$distinctOverlapIds := distinct-values($overlapIds)
return string-join($distinctOverlapIds, ', ')
---------------------------------------------------------

Overlapping properties: 21373, 21298, 21297, 21622, 21623, 21616, 21617, 21618, 21621, 21619, 21620, 21574, 21575, 21416, 21682, 21681, 21374, 21614, 21376, 21375, 21615, 21330, 21329, 21255, 21252, 21257, 21256, 21254, 21253

Ids of titles which cover all of these properties:

---------------------------------------------------------
XQuery on landscapes_mapridgelive_current_titleBased.xml:

let $titles := //title[props/prop[@refid=('21373', '21178', '21298', '21297', '21622', '21623', '21616', '21617', '21618', '21621', '21619', '21620', '21574', '21575', '21416', '21682', '21681', '21374', '21614', '21376', '21375', '21615', '21330', '21329', '21255', '21252', '21257', '21256', '21254', '21253')]],

$titleDesc := for $t in $titles order by $t/titleInfo/ttl_date return concat($t/@id, ': ', $t/titleInfo/ttl_title_code, ' (', $t/titleInfo/ttl_date, ')')

return string-join((for $t in $titles order by $t/titleInfo/ttl_date return $t/@id), ', ')
---------------------------------------------------------

26684, 26959, 27763, 26688, 26675, 26723, 26732, 26733, 26725, 26730, 26788, 26796, 26791, 26792, 26799, 26807, 26811, 26809, 26812, 26814, 26816, 26762, 26763, 26883, 26927, 26854, 26884, 26885, 26929, 26974, 26984, 26857, 26849, 26987, 26871, 27564, 26945, 26956, 26944, 26955, 26958, 26994, 27581, 27582, 27929, 27014, 27015, 27016, 27571, 27017, 27020, 27654, 26949, 26970, 27930, 27649, 27936, 27021, 27667, 27668, 27712, 27714, 27673, 27729, 27728, 27730, 27731, 27734, 27733, 27677, 27940, 27727, 27759, 27679, 27766, 27710, 27769, 27724, 27708, 27761

Details of these titles:

XQuery on landscapes_mapridgelive_current_lotBased.xml:
---------------------------------------------------------
let $titleIds := ('26684', '26959', '27763', '26688', '26675', '26723', '26732', '26733', '26725', '26730', '26788', '26796', '26791', '26792', '26799', '26807', '26811', '26809', '26812', '26814', '26816', '26762', '26763', '26883', '26927', '26854', '26884', '26885', '26929', '26974', '26984', '26857', '26849', '26987', '26871', '27564', '26945', '26956', '26944', '26955', '26958', '26994', '27581', '27582', '27929', '27014', '27015', '27016', '27571', '27017', '27020', '27654', '26949', '26970', '27930', '27649', '27936', '27021', '27667', '27668', '27712', '27714', '27673', '27729', '27728', '27730', '27731', '27734', '27733', '27677', '27940', '27727', '27759', '27679', '27766', '27710', '27769', '27724', '27708', '27761'),

$titles := for $tId in $titleIds return //titles/title[@id = $tId][not(preceding::titles/title[@id=$tId])],

$titleInfos := for $t in $titles order by $t/effectiveDate return concat($t/@id, ': ', $t/code, '	',
$t/effectiveDate, ' ',
' nominal: ', if ($t/@isNominal = 'true') then 'yes ' else 'no  ',
' custSale: ', if ($t/hasCustodianSeller = '1') then 'yes ' else 'no  ',
' vlaBuy: ', if ($t/hasVlaOwner = '1') then 'yes ' else 'no  ',
' vlaSell: ', if ($t/hasVlaSeller = '1') then 'yes ' else 'no  '
)

return string-join($titleInfos, '
')
---------------------------------------------------------

26688: 32738E    1920-05-10  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26675: 74159E    1927-10-16  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26684: 106669E    1934-03-03  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26723: 106822E    1934-03-21  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26732: 107183E    1934-04-24  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26733: 107254E    1934-05-02  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26725: 107963E    1934-07-18  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26730: 109668E    1934-12-06  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26788: 113071E    1935-10-29  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26796: 116972E    1936-09-18  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26791: 132983E    1939-09-15  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26792: 149801E    1942-04-02  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26799: 166279E    1943-12-28  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26807: 169926E    1944-04-17  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26811: 169945E    1944-04-17  nominal: no   custSale: yes  vlaBuy: yes vlaSell: no
26809: 169933E    1944-04-18  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26812: 169952E    1944-04-18  nominal: no   custSale: yes  vlaBuy: yes vlaSell: no
26814: 170808E    1944-04-25  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26816: 170828E    1944-04-25  nominal: no   custSale: yes  vlaBuy: yes vlaSell: no
26762: 171885E    1944-05-18  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26763: 171903E    1944-05-18  nominal: no   custSale: yes  vlaBuy: yes vlaSell: no
26883: 175591E    1944-09-02  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26927: 281892E    1951-04-16  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26854: 291940E    1952-01-11  nominal: no   custSale: no   vlaBuy: no vlaSell: yes
26884: 295379E    1952-04-16  nominal: no   custSale: no   vlaBuy: no vlaSell: yes
26885: 295380E    1952-04-16  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26929: 313897E    1953-08-05  nominal: no   custSale: no   vlaBuy: yes vlaSell: no
26974: 322796E    1954-02-23  nominal: no   custSale: no   vlaBuy: no vlaSell: yes
26984: 359159E    1956-01-26  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26857: 412345E    1958-07-28  nominal: yes  custSale: no   vlaBuy: yes vlaSell: no
26849: 423191E    1958-12-05  nominal: yes  custSale: no   vlaBuy: no vlaSell: yes
26987: 448583E    1959-12-14  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26871: 450855E    1960-01-07  nominal: yes  custSale: no   vlaBuy: no vlaSell: yes
27564: 452250E    1960-01-18  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26945: 467021E    1960-09-26  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26956: 469702E    1960-11-17  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26944: 468183E    1960-11-30  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26955: 468184E    1960-11-30  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26958: 484848E    1961-09-12  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
26994: 507210E    1962-11-20  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26959: 533338E    1964-04-09  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27581: 538458E    1964-07-13  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27582: 538459E    1964-07-13  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27929: 608026E    1967-08-14  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27014: 627673E    1968-03-13  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27015: 627674E    1968-03-13  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27016: 627675E    1968-03-13  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27571: 634784E    1968-06-18  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27017: 634747E    1968-07-09  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27020: 640865E    1968-09-25  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27654: 656339E    1969-03-10  nominal: no   custSale: no   vlaBuy: no vlaSell: no
26949: 658596E    1969-04-09  nominal: yes  custSale: no   vlaBuy: no vlaSell: yes
26970: 658597E    1969-04-09  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27930: 660594E    1969-05-02  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27649: 675235E    1969-10-30  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27936: 683572E    1970-01-27  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27021: 728793E    1971-09-21  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27667: G72055E    1971-10-29  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27668: G112249E    1972-02-01  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27712: H10857E    1972-02-24  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27714: H10856E    1972-02-24  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27673: H14697E    1972-02-28  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27729: H48509E    1972-05-30  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27728: H42064E    1972-06-07  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27730: H58317E    1972-06-20  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27731: H60992E    1972-07-10  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27734: H65306E    1972-07-24  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27733: H90242E    1972-09-13  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27677: H92378E    1972-09-15  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27940: H96413E    1972-09-27  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27727: H101791E    1972-10-18  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27759: H124001E    1972-12-06  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27679: J5100E           1973-01-24  nominal: no   custSale: no   vlaBuy: no   vlaSell: no
27766: J55243E    1973-06-06  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27710: J71976E    1973-07-04  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27763: J86568E    1973-07-06  nominal: no   custSale: no   vlaBuy: no vlaSell: no
27769: K20906E    1974-03-14  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27724: K68691E    1974-08-02  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27708: K85245E    1974-08-20  nominal: yes  custSale: no   vlaBuy: no vlaSell: no
27761: L91605E    1975-11-07  nominal: no   custSale: no   vlaBuy: no vlaSell: no


To select T1 for this property:

Get the latest non-nominal property FOR THE ORIGINAL BASE PROPERTY before the starting date (1942-03-04):

26675: 74159E    1927-10-16  nominal: no   custSale: no   vlaBuy: no vlaSell: no

To select T2 for this property and its descendants we first look for any custodian seller
titles in the period at all, nominal or not:

26811: 169945E    1944-04-17  nominal: no   custSale: yes  vlaBuy: yes vlaSell: no
26812: 169952E    1944-04-18  nominal: no   custSale: yes  vlaBuy: yes vlaSell: no
26816: 170828E    1944-04-25  nominal: no   custSale: yes  vlaBuy: yes vlaSell: no
26763: 171903E    1944-05-18  nominal: no   custSale: yes  vlaBuy: yes vlaSell: no

Since we find some, we take the first in the list as our T2:

26811: 169945E    1944-04-17  nominal: no   custSale: yes  vlaBuy: yes vlaSell: no

To get T3, we now get the next qualifying title (i.e. the next qualifying title which follows
the custodian sale. That also happens to be a custodian sale, this time to the VLA:

26812: 169952E    1944-04-18  nominal: no   custSale: yes  vlaBuy: yes vlaSell: no

To get T4, we do the same thing again: get the next qualifying title, which turns out
to be another custodian sale:

26816: 170828E    1944-04-25  nominal: no   custSale: yes  vlaBuy: yes vlaSell: no

To get T5, we get the last qualifying transaction on a descendant property which happens before
the end date (1972-03-04):

27936: 683572E    1970-01-27  nominal: no   custSale: no   vlaBuy: no vlaSell: no

This sequence makes sense according to the rules and the spreadsheet. 

Met with JSR and we looked at the stats work. There appears to be an anomaly around the average 2018 price per metre for Maple Ridge properties in the 42-49 date-range, so we planned a diagnostic for that; I've now written that, and it seems like the anomaly must be due to some specific transactions from the period which don't happen to make it into the lot-based spreadsheet. More checking to do there. Meanwhile, I updated the data dictionary to include the VLA spreadsheet, and the new plan also includes a significant addition to the main lot-based spreadsheet.

Working with GL and GN, created an example TEI encoding for the Zotero data. Needs an ODD file w/schema and documentation, as well as Oxygen project.

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.

The fourth plan necessitates rewriting all the code to retrieve T1 and T2, so I'm a good way into that, but more questions arose while doing that, which are still being worked out, so we're not done this week, unfortunately.

This is it, to be implemented ASAP:

1. ensure that custodian sales are found NOT subject to the requirement to be non-nominal; then add a constructed variable: NOMINAL_CUSTODIAN_SALE=1.

2. After finding a T2, search for any qualifying transactions on descendant properties of the T1 which occur before 1942-03-04, and output a constructed variable with a count of these: T1_T2_INTERVENING=5. 

3. After finding T1 and a subsequent custodian sale T2, search for NON-NOMINAL sales to JCs on any descendant property of the original property between the T1 date and the custodian sale date. In such a case, abandon construction of the original row and instead construct a new row in which that non-nominal sale is the T1 (retaining the already-found custodian sale as T2).

4. After finding T1 and a subsequent custodian sale T2, and assuming action #3 does not cause the row to be rejected, search for NOMINAL sales to JCs on any descendant property of the original property between the T1 date and the custodian sale date. Output a constructed variable with a count of these (NOMINAL_LATE_SALES_TO_JC=x).

Discussions over the weekend led to a meeting today in which we thrashed out some of the issues, and we're half-way to a new plan; the immediate requirement was to investigate the cases where there is a sale to a JC buyer between the start date of the custodian activity and the actual sale of a property by the custodian. For the record, the quick-and-dirty XQuery to answer this question is below, along with the answers. It's clear these cases are not edge cases we can ignore; there's a definite pattern here.

let $baseDir := 'file:///home/mholmes/WorkData/history/stanger-ross/landscapes_of_injustice/svnrepo/trunk/xml/',
$startDate := xs:date('1942-03-04'),
$vanDbFile := concat($baseDir, 'landscapes_live_current_lotBased.xml'),
$mapDbFile := concat($baseDir, 'landscapes_mapridgelive_current_lotBased.xml'),
$dbs := (doc($vanDbFile), doc($mapDbFile)),

$custSales := distinct-values(($dbs//title[hasCustodianSeller != '0'][@isNominal='false'][not(matches(effectiveDate, '00'))]/@id))

for $t in $custSales
let $title := $dbs/descendant::title[@id = $t][1],
$tDate := xs:date($title/effectiveDate),
$lotIds := $title/lotsForTitle/lot/@id,
$targets := $dbs/descendant::title[not(matches(effectiveDate, '00'))][lotsForTitle/lot/@id = $lotIds][someOwnersJapanese != '0'][xs:date(effectiveDate) lt $tDate and xs:date(effectiveDate) gt $startDate]

return if (count($targets) gt 0) then concat('Title ', $t, ' has JC purchases in the key window: ', string-join(distinct-values($targets/@id),  ','), '
') else ()

 Title 1034 has JC purchases in the key window: 1111,1159,1160
 Title 1701 has JC purchases in the key window: 1805,1807
 Title 2926 has JC purchases in the key window: 2963
 Title 5323 has JC purchases in the key window: 5379,5423
 Title 5460 has JC purchases in the key window: 5502
 Title 3799 has JC purchases in the key window: 1172
 Title 4113 has JC purchases in the key window: 4192
 Title 4640 has JC purchases in the key window: 4708
 Title 5068 has JC purchases in the key window: 5122,5123
 Title 6453 has JC purchases in the key window: 6456
 Title 6605 has JC purchases in the key window: 6607
 Title 6649 has JC purchases in the key window: 6661
 Title 29141 has JC purchases in the key window: 29144
 Title 29329 has JC purchases in the key window: 29278

Per plan worked out in email and on the phone with JSR, itemized as plan_3.txt, made a range of changes to the process of generating the spreadsheet. Some changes still being figured out.

In emails with JSR, worked out the plan below, and implemented all the code parts; I'm now halfway through the data dictionary, which is proving helpful in clarifying our procedures for me as well as a potential reader.

Made a number of updates to generation of XML, GeoJSON and spreadsheet information to make for clearer output, and to build 2018 values into the whole process so any new products are consistent. Maps are now easier to read, and all include 2018 dollars. Lots more to be done, though.

Today was long and complicated but it seems to be working. I'm now producing JSON files for each row in the spreadsheet table, and I have an HTML representation of the spreadsheet which provides links to the maps from those story files. That puts us in a position to examine lots of real cases and see whether there are any flaws in our retrieval and processing, as well as to get a sense of the actual stories we're uncovering.

As far as I can tell, the spreadsheet is now doing what it's supposed to, after consultation with JSR and reworking. We will need only one spreadsheet rather than two. I'm now working on generating GeoJSON file for each row, so we can look at what we're generating and do human sanity checks.

It lacks comparison title(s) pending decisions about how those should be selected, but it works! Took all day to get there. Lots of outstanding questions, but I think we're on the way.

Did a lot of work today on creating and testing output for specific lot/title combinations, as cell sequences, and rendering them into TSV. I've also re-worked the lot-based view a little to add precomputed values of all kinds that I need for the spreadsheet, and completed more functions that retrieve properties and titles according to various parameters. It's getting to a point where I can see what now needs to be done for the first target output spreadsheets, and I've sent a couple of questions to JSR regarding precise details of what's required, where the plan was a little vague.

I'm now creating individual JSON files for each property that has coordinates, and those are viewable on the map. I've created a list page for them, but also added the link property to the databases so you can get to them directly from there. I still need to add links from the feature to all the related title features, and vice versa, so you could navigate from one to the other.

After substantial updates to the AdaptiveDB code today (HTML5, support for auto-linked fields) I've updated the two live and two dev databases so that they now provide direct links to the Jenkins-built map pages for each title.

Got the new function for loading feature geometries while retaining the existing set working, which was very tricky, but I still have a problem with the geometries of the loaded features; they're defaulting to Zero Island, so I'm guessing they're somehow not being loaded at all. Still, half-way to supporting arbitrary collections of features on the same map.

Finally found usable versions of the 2018 data and manipulated it appropriately, adding documentation about exactly how and why the conversion code was constructed. Wrote and tested a bunch of functions for serializing data as CSV/TSV.

Did more work on util functions for the process but realized that the primary requirement for development is to have more flexible maps that can combine different GeoJSON files together, so I've diverted into some dev work on BreezeMap.

I've created the core set of files required to do the processing, and I've started writing some of the simpler functions we'll need. I have a function test module which I'll use to develop the function library; it's going to be essential to develop and test all the functions individually before we start chaining them together to create the monstrous output files, at which point it will be hard to see the wood for the trees. Found a lot of instances of substantial overlap (14%+) between lots which are supposed to be contiguous, so our threshold will have to be pretty high.

Got some more detail in place today, and I've started a new folder in the trunk called propertyStats, and a build file which will be the basis for the process. I'll start by defining resources, then writing some functions (pinching a bit from previous work) in XSLT. This codebase will be cleaner and better-documented than previous work because JSR would like to publish it in association with the book.

Met with JSR and worked on the plan for the next round of statistical work. I now have a working plan which will involve making use of the overlap data generated during the summer to determine the sequence of titles/lots rather than using the rather flaky sequences put together from title data. I've already processed the CSV overlap data into expanded FODS files, then merged it all into a very simple XML file that can be used in the transformations as required.

Along with documentation of my responses to reviewer comments.

Met with JSR, DBM and CB to discuss possible solutions to calculating the area of lot overlap. We've been hacking at this problem without a solution for some weeks, but it's possible that CB can implement something that will solve it for us.

There's a persistent number of properties without coordinates, and in some cases an entire title has no properties with coordinates (usually where there's only one property on the title). This was generating invalid GeoJSON because it was unable to calculate an initial map view area. I've diagnosed and fixed that bug; the results are now showing up correctly in the Jenkins build products.

Ran the ethnicity process for all the new Kits owners.

Adapted the code originally written to process the XML-from-MySQL to incorporate the more rigorous algorithm we refined when processing the directory material, then processed the remaining Maple Ridge owners to assign ethnicity. Only one marginal case, John William Kobata, who might be Japanese, Polish or Slovakian.

Geo-reffed a couple more lots, this time in Maple Ridge, and in the process wrote new documentation for QGIS 3.0, in as much detail as possible. Then started on the next new plan, but it seems to be located rather in the wrong place, or there's somethingt very odd happening with it; it shows a street which doesn't exist where the plan should cover. Need to check back with the researcher.

Since the missing plans have now been retrieved from the LTO, I'm now taking AC's role to geo-reference the, I followed her excellent instructions, which unfortunately no longer match the version of QGIS that's current on my machine (I've moved from 2.18 to 3.0), so it took me a long time to figure out exactly what to do. I'll rewrite those instructions as a separate version in the same documentation file for version 3.0 when I've done a couple more. The main thing that took me AGES to realize was that to stop drawing a polygon and finalize it as a feature, you have to right-click. There's no GUI control at all, and no other way to do it as far as I can see.

SR has offered to handle the polygon problem, so I generated for her a GeoJSON file containing all the features from the Maple Ridge db. I now have a library for doing that. KF needed a list of J-C owners and their properties as of 1942-03-04, so I've written and somewhat debugged that library (in XQuery); the results are odd, though, since they show no owners from Steveston or Salt Spring, so there needs to be further investigation there. Finally, one of our collaborators needs a hard copy of MP3 versions of our oral history wav files, so I resurrected my Python script and I'm running it now. It should be done by tonight or tomorrow.

Lots of attempts to use both turf and jsts to find intersection or union of polygons, all failing so far. Very frustrating.

Finally figured out how to get area calculation working for polygons in OpenLayers, and I'll put it in here so I can find it the next time I'm pulling my hair out:

var wgs84Sphere = new ol.Sphere(6378137);
      
var getGeodesicArea = function(polygon) {
  var geom = (polygon.clone().transform('EPSG:3857', 'EPSG:4326'));
  var area = Math.abs(wgs84Sphere.geodesicArea(geom.getLinearRing(0).getCoordinates()));
  return area;
};

Pass a polygon geometry to the function and you'll get a value in m2. The magic number used to construct the sphere is the radius of the earth. abs is needed because the result is puzzlingly sometimes negative and sometimes positive; that doesn't seem to depend on the order of the coordinates, since my polygons are all properly wound. I can now go on to calculating intersection areas.

Put a lot of time into figuring out if I could use node and turf to do the lot calculation, but it seems unlikely; the current release of turf lacks the advertised boolean-overlap function as far as I can see, and the turf.area() function gives me very weird results (such as negative numbers). Had a go at using OpenLayers under node, but that's very difficult because of its requirement for a browser window object, document, and so on; so I'm now investigating the use of a hosting browser page, which seems more promising. Lots of work to do, though.

Met with JSR and discussed the situation wrt geo plans and properties. Collected the remaining issues with missing plans and turned them into detailed requests for the NWLTO to go back to original titles and confirm/correct data to find them; sent this to JSR to forward. Also did stats on the problems generated by these issues; the number of affected titles is quite low, but we don't know what the number of affected "chains" is yet, because we don't know how chains should be constructed. However, I did make some progress on this. I've started some XSLT to build a spreadsheet of per-lot value histories per sq metre in 2016 dollars.

The next stage is to figure out how to continue the chain for an extinguished lot. My suggested approach is to analyse the potential overlap of all lots, in order to discover any pairs which overlap by more than 50% of the area of one of them; this should be sufficient to generate a link between them, and then any chain can be continued by following [one of] the lot[s] into which it was [de]composed. I think this can be done in JavaScript using Node and the Turf.js library, specifically turf intersect. I'll need to script and test this, then it'll run for many hours to generate all the required lists.

New plans provided by JSR from LTO turned out to have been dealt with already by AC. Put a couple of them into Zotero, confirmed that others were still missing or still wrong.

The next part of the plan to handle the Plans is to use the basic GeoJSON we now have in WGS84 to create BreezeMap-compatible GeoJSON with all the extra info we need in it. I decided to do this with XSLT3, to get familiar with the new JSON-handling functionality, and it seems pretty straightforward once you get the hang of it. I'm now producing all the output I need, but I haven't yet figured out how to create an index for these pages.

Wrote another node script using proj4 to convert all our Plan and Sketch files to WGS84. This is a key component of creating TEI files which merge the geo data from the plans and sketches with the land title data. I've also massaged the UIDs of the Maple Ridge DB lots so that they match the new ones in the db. That means that the most reliable resources for mapping are the *_wgs84.json files.

From the existing TEI, built a map for a specific dataset that shows the sequence of titles with rising prices for JC property that was seized. JSR now wants to add plans to the picture, so I've started to build a transformation that works from the GML of the plan files to create GeoJSON; that would give us one map per plan, with all the child lots, which could be linked to their titles. The current issue I'm wrestling with is that the numbers for coordinates are a bit too large for the XSLT to handle, so some of them end up being transformed into exponential notation. It looks like I'll have to try to figure out some way to call an external function to convert all the numbers to WGS84 first. Not sure how to do that; may have to work from the GeoJSON files I've already created, and use Node/Proj4 to do it. In any case, I'm using the XSLT 3 JSON reading-and-writing functionality, so it should be possible to transform the GeoJSON and then use that as the source for the GML, perhaps; then transform that GML.

Met with JSR and started to thrash out some of the requirements for the next block of processing. Outcomes:

1. (Urgent): this week, put together a demo map of a single sequence of titles pertaining to a single Maple Ridge property. Do this by getting a list of titles in a chain, then combining their generated TEI place files into a single file; then edit that file to make info more human-friendly; then generate GeoJSON and create a map.
2. The existing TSV seems to have all the required info in it for the various calculations that will need to be done.

JSR raised the possibility of using the cadastrals on top of the title maps. I've looked at the GeoTiffs created by AC, and determined that I can use gdalwarped like this:

gdalwarp input.tif output.WGS84.tif -t_srs "+proj=longlat +ellps=WGS84" 

to create a version in degrees in WGS84; then I can use:

gdalinfo output.WGS84.tif

to see the resulting coordinates. I think we can then use convert to get PNGs and use ol.source.StaticImage to put the results on the map, but I haven't actually succeeded in making that work yet; still wrestling with it.

I have the map working for individual titles, and I've added lots of extra warning info to the description of the title for cases where lot information is incomplete. The current streets tables now link to the map. I've also updated the code which builds the TSV file of transactions by lot in 2016 values, to include both Powell Street and Maple Ridge together.

I discovered an issue with our databases. When we moved from the Powell Street database to the Maple Ridge db, I intentionally set the new database to assign automatic ids to new items starting from numbers beyond the totals reached in the Powell Street db. So (for example) the original Powell Str properties maxed out at 1039, and the Maple Ridge db was set to start at 1105, allowing a buffer of 65 for small changes to the Powell St db.

Similarly, with titles, the Powell St db maxed out at 6459, and I started the Maple Ridge db at 6555, leaving a buffer of just under a hundred.

However, we later made the decision to start entering Kitsilano data into the Powell Street database, and this turned out to be on a scale which pushed it beyond the buffer and created overlapping ids. The result is that we have properties and titles in both databases with identical ids, meaning that we cannot easily combine that data. Similar overlaps are happening with owners, and presumably also lawyers and so on.

This is not a disaster; the two databases are separate, so there's no confusion unless and until we start trying to combine them. Similarly, AC's work on maps is divided into five distinct sets which each map to one or other of the dbs.

However, we had to solve the problem because we are now in the business of merging this data, and overlapping ids are a bad idea generally. My solution, which I tested thoroughly on the dev db before doing it to the live db, was to simply update the Maple Ridge owners, titles and properties tables to add 20,000 to each of their id fields. This propagated automatically through the linking tables, which was very neat. Then I just reset the next-autoincrement value appropriately (that doesn't happen automatically). Seems to have worked perfectly.

I've also corrected some inconsistencies between the location table values in the two dbs, and I'm working on some properties which have no assigned location in the Powell St db.

I have a basic OSM map working for any given title. This involved hacking the BreezeMap code a bit; I'll port some of the changes back into BreezeMap itself. What remains to be done is to create index pages for the titles, fix the links between maps (they're done with a ttl: prefix), push the map build into the products, add links to the titles from the street pages, and (perhaps most urgent) fix the build, which is currently broken for reasons I don't understand.

I've solved the problem of converting the coordinates from 32610 in metres to WGS84 in the process of prepping the data for insertion into the MySQL dbs. This is the script:

/* This file is designed to be run against a series of GeoJSON 
 * files to create SQL statements which insert polygon and 
 * area information into our Land Title Databases. */
 
/* The GeoJSON files are generated from QGIS. */

// We need fileio and proj4 for coordinate conversion.
var fs = require("fs"), content, i, maxi, j, maxj, k, maxk, l, maxl, 
              gj, output, id, area, pathBits, outFile, arrCoords,
              degreeCoords, coords, ids=[], idList='',
              idCheck='', vancouverFiles=[], 
              mapleRidgeFiles=[], proj4 = require("proj4");
              
//We create these Proj4JS definitions so we can more easily refer to them later.
proj4.defs('epsg32610', '+proj=utm +zone=10 +ellps=WGS84 +datum=WGS84 +units=m +no_defs');
proj4.defs('WGS84', "+title=WGS 84 (long/lat) +proj=longlat +ellps=WGS84 +datum=WGS84 +units=degrees");

//GeoJSON files

//Two sets of data are for properties in the Vancouver db.
vancouverFiles.push('KitsilanoPlans/MergedKitsilano/MergedKitsilano_geom.json');
vancouverFiles.push('PowellStreetPlans/MergedPowellStreet/MergedPowell_geom.json');

//Three sets are for properties in the Maple Ridge db.
mapleRidgeFiles.push('MapleRidgePlans/MergedMapleRidge/MergedMapleRidge_geom.json');
mapleRidgeFiles.push('SaltSpringPlans/MergedSaltSpring/MergedSaltSpring_geom.json');
mapleRidgeFiles.push('StevestonPlans/MergedSteveston/MergedSteveston_geom.json');

console.log("\n *START* \n");

processFileArray(vancouverFiles, 'vancouver.sql');
processFileArray(mapleRidgeFiles, 'mapleRidge.sql');

//Process an array of files, creating a single output file
//with SQL commands to set the coordinate and area fields.
function processFileArray(files, outputFile){
  output = '';
  for (i=0, maxi=files.length; i<maxi; i++){
      ids = [];
      content = fs.readFileSync(files[i]);
      console.log('Read in ' + files[i] + '.');
      gj = JSON.parse(content);
      console.log('Found ' + gj.features.length + ' features in the file.')
      for (j=0, maxj=gj.features.length; j<maxj; j++){
//The UID is the value of the prp_property_id field in the database props table.
        id = gj.features[j].properties["UID"];
        if (id !== null){
//Keep a list of ids so we can do some checking later if necessary.

          ids.push('"' + id + '"');
        
//The area in sqm needs to be rounded to two decimal places.
          area = Math.round(parseFloat(gj.features[j].properties.area) * 100) / 100;
          
//Here we convert the original coordinates from 32610/metres to WGS84 (for GeoJSON).
          arrCoords = gj.features[j].geometry.coordinates;
          for (k=0, maxk=arrCoords.length; k<maxk; k++){
            for (l=0, maxl=arrCoords[k].length; l<maxl; l++){
              degreeCoords = proj4(proj4('epsg32610'), proj4('WGS84'), arrCoords[k][l]);
              //console.log(JSON.stringify(arrCoords[k][l]));
              //console.log(JSON.stringify(degreeCoords));
              arrCoords[k][l] = degreeCoords;
            }
          }

          coords = JSON.stringify(gj.features[j].geometry.coordinates);
          
//Write the two update statements for this property record.
          output += 'UPDATE `props` set `prp_coords` = "' + coords  + '" WHERE `prp_property_id`="' + id + '";\n';
          output += 'UPDATE `props` set `prp_area` = "' + area + '" WHERE `prp_property_id`="' + id + '";\n';
        }
      }
      idList = '(' + ids.join(', ') + ')';
      idCheck = '#id check: \n\n# ' + 'let $ids := ' + idList + ' return for $i in $ids where not(//prp_property_id[. = $i]) return $i \n\n'
      
      //idCheck = 'CREATE TYPE id_nums AS TABLE OF NUMBER;\n';
      //idCheck += 'SELECT * FROM TABLE(id_nums(' + idList + ')) '
      //idCheck += ' WHERE NOT IN (SELECT `prp_property_id` from `props`)\n\n';
      output = idCheck + output;
  }
  fs.writeFile(outputFile, output, function(err){});
}

console.log("\n *EXIT* \n");

My next problem with creating the GeoJSON is that I have coords in EPSG 32610, in meters, and I need them in WGS84 for the GeoJSON. This approach will probably work:

npm install proj4

node...

proj4.defs('epsg32610', '+proj=utm +zone=10 +ellps=WGS84 +datum=WGS84 +units=m +no_defs');
proj4.defs('WGS84', "+title=WGS 84 (long/lat) +proj=longlat +ellps=WGS84 +datum=WGS84 +units=degrees");

proj4(proj4('epsg32610'), proj4('WGS84'), [493251.00240006257,5459084.067122783])
[ -123.09280153510765, 49.28447161994075 ]

This converts a single point; I need to figure out how to convert polygons.

• Integrated the new price-per-metre data into the spreadsheet output for stats. Reported to JSR.
• Began work on generating mappable data for titles. TEI files are now generated from the title-based XML view, and these can be converted with the BreezeMap code to GeoJSON. This is working, but in the process I discovered that a set of GeoJSON coordinates (for 121 locations) inadvertently got truncated during the import process, so I'll have to rescue that data.

Ids of property records with truncated coords:

152, 159, 168, 174, 177, 184, 190, 193, 194, 266, 267, 268, 278, 281, 283, 288, 293, 294, 295, 320, 321, 322, 323, 328, 329, 332, 333, 338, 339, 347, 396, 424, 439, 480, 481, 482, 483, 484, 485, 488, 489, 490, 491, 494, 552, 553, 568, 569, 570, 571, 572, 573, 574, 575, 577, 590, 610, 623, 630, 636, 700, 701, 705, 706, 707, 708, 710, 711, 713, 820, 821, 824, 825, 831, 832, 844, 845, 846, 847, 848, 849, 850, 851, 852, 853, 854, 856, 857, 859, 860, 861, 862, 863, 864, 865, 866, 867, 872, 873, 877, 891, 896, 900, 925, 977, 980, 984, 985, 994, 996, 997, 999, 1000, 1004, 1006, 1012, 1013, 1018, 1021, 1024, 1031

After putting the coords and areas into the dbs yesterday, I reworked the XSLT that generates the title-based and lot-based XML views of the data so that they include price-per-square-metre for titles and lots, and also a version of it normalized to 2016 values. There are still lots of unknowns about how we handle this data, especially the edge cases (missing lot info, extinguished lots, etc.), but I'm working this stuff out now with JSR.

I figured out how to use QGIS to generate a GeoJSON file with areas in it, and documented that in the protocol document; then I wrote some JS for nodejs to generate SQL update statements to add that data to the db. I created two new fields in the landscapes_dev (my testing db):

ALTER TABLE `props` ADD `prp_coords` VARCHAR( 256 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL ,
ADD `prp_area` DECIMAL( 8, 2 ) NULL ;

Took backups of the dbs, then merged the new data into it. There are 262/1079 Powell St lots without coords/area, and 56/1120 Maple Ridge.

I'm trying to find a straightforward way to calculate the areas of all the polygons in the lot maps created in QGIS by AC, but so far I haven't got one. Trying with OpenLayers and the GeoJSON output created with ogr2ogr, but for some reason the JS won't read the JSON files.

When CN met with me recently, she left a set of permissions forms which had not been uploaded to the server for me to do that. Today I scanned and uploaded them, and let her know.

She's the new Oral History person. Went through the shambles that is the current collection of ill-assorted and ill-named files on the server; she will take control of them and do some reorganization, de-duping and renaming. Went through the process with FileZilla, although she doesn't yet have a netlink id so she can't do anything for the moment.

I've written three tiny scripts to generate usable data in GML, GeoJSON and KML from the binary ESRI shapefiles created with QGIS, and I've added all those the repo. I've been investigating the simplest way to generate usable area calculations, and it seems to be this:

ogrinfo -dialect SQLite -sql 'SELECT ST_Area(geometry)/10000 FROM Plan47534' Plan47534.shp

INFO: Open of `Plan47534.shp'
      using driver `ESRI Shapefile' successful.

Layer name: SELECT
Geometry: None
Feature Count: 2
Layer SRS WKT:
(unknown)
ST_Area(geometry)/10000: Real (0.0)
OGRFeature(SELECT):0
  ST_Area(geometry)/10000 (Real) = 0.597841877829159

OGRFeature(SELECT):1
  ST_Area(geometry)/10000 (Real) = 0.7206513230636

This should be parsable with XSLT (for instance), so an ant task to generate all these things, and hook them up with their lot ids (in ogr:UID elements in the GML files), then generate a stack of SQL statements to update the databases, would seem to be straightforward, if JSR wants us to go that way tomorrow.

Finished the redraft of the article for submission to another journal, and submitted it.

JAEH reviewers reckon, quite rightly, that my article is DH and not ethnic history, so I'm now rewriting it for another CfP that just came out.

Received a mailed thumbdrive from RS containing her oral history work. Made a local copy, which will be backed up to Rutabaga as a matter of course, and pushed a copy up to nfs in loi/oralhistory/interviews/fromRebeca2017.

AC took GN and me step by step through her working process, to ensure that we understand everything. We determined afterwards that the QGIS project files were in fact being stored outside the repo, so we copied the Maple Ridge ones into the repo; they don't work at the moment because they're full of hard-coded and broken relative paths, but they're XML so that will be easy to fix. In the afternoon I reviewed and commented on the protocol document.

Did some archaeology on the apparent missing titles from Powell St and reported as follows:

My backup of the database from September 2014 (when I think we were just creating it) contains a total of 3489 titles, including those for B43 L2. The next backup, from November, has only 1513 titles, so nearly 2,000 were deleted.

This was planned. These are my notes from our discussions and our action at the time:

-----
October 3:
Met with JSR and SF to discuss refining data in the LTD. First, we create a new duplicate of the existing db. SF will generate a list of known-good titles (in that they've been fully edited using the final protocols). I'll then generate lists of titles that don't match that set, which will be candidates for deletion; she will check those. Then we delete those. Then we generate lists of now-unlinked people (owners and sellers), other documents, and legal descriptions, which again are assessed as candidates for deletion.

October 14:
SF and I have been working on generating and checking lists of records which we believe can be deleted from the db. I've made a landscapes_backup db and cloned the current content into it before we start deleting; it looks like we'll be removing over 2,000 title records, but we're still doing some checking; then we'll remove associated unlinked items.

October 17:
Did a number of planned deletions, and then some additional work identifying now-unlinked owners; there are 2713 which could be deleted. Many of these are additions by the recent research team, which were added, but whose titles were then mistakenly linked to earlier identical or similar entries. SF is now analysing this situation, and we will eventually prune all the unwanted owners from the system. Then comes the issue of identifying and linking or merging owners we believe to be the same person.

October 27:
Generated lists of owners who were previously connected to Block 43 and 52, so that they can be eliminated from the db if they're now no longer connected to anything else. SF is doing this work.

November 7:
SF and I worked through a lot of different approaches to confirming that no useful data was deleted during our cleanup. We have plausible explanations for all but 32 of the orphaned owners; and we have identified about ten titles which were deleted by editors during the summer work period; these must have been purposefully deleted around the time they were created -- they never got saved into a backup -- so they must have been erroneously entered. I think these are the plausible explanation for the orphaned owners.

In the process I added a new titles as seller field to the owners table, and we confirmed the consistency of data in lots of other respects, so we're looking good.
-----

So we know these deletions were intentional, they were carefully checked, and they seem to have been primarily associated with blocks 43 and 52. If the intention was that these titles should be re-captured, that apparently never happened. 

Email discussion with NH about approaches to obscure, old and obsolete characters; I've adopted a couple of the ideas that resulted into the schema, and elaborated some of the existing descriptors we have. We're slowly getting towards an understanding of how best to approach these oddities.

AC has listed a number of lots which appear in the titles db but are not linked to titles. A deep dive on one of them shows that it was linked to a number of titles in the database that pre-dates the Landscapes project; those titles go back to 1949. We don't yet know whether there is lost or missed data in the current version of the db, or whether these cases are omitted for some specific reason.

Spent some time discussing issues arising out of the Powell St data and db with AC, as she's getting started on it. Looks like some of the work done by the GIS team can be re-purposed. Generated a list of properties from the db which are linked to titles (many aren't, I think for project history reasons).

AC has produced an excellent protocol doc for the mapping, which I've read through and commented. I'm beginning to understand the process a bit better, but there are still gaps in my knowledge...

Spent couple of hours with KJ working out how to map fields from the NNM csv output to fields conformant with the RAD standard for input to AtoM. More or less completed the collection, series and file levels, started on the item level.
To be researched further:
- external links to jpgs (vs uploading into db as digital objects
- create authority record for Mr. Sato and figure out how to link to it.
- check RAD spec for any constraints on e.g. identifier values, or syntactic requirements

csv import documentation for AtoM 2.1 at https://www.accesstomemory.org/en/docs/2.1/user-manual/data-templates/rad-template/#template-description

RAD specification at http://www.cdncouncilarchives.ca/RAD/RAD_Chapter01_July2008.pdf

Met with JSR and RRR to discuss the geo-reffing of the Maple Ridge plans. Decisions are in my notes; the basic idea is to use the identifiers from the land titles db properties table for the GIS polygons. I started examining how well our data fits with the key plans, and it's not a perfect fit at all. More work to be done here.

Finished draft three of the JAEH article, with additional material on multi-ethnicity in Japan suggested by JSR based on something in EO's paper. That's now back with JSR.

Yesterday and today, working with SA on his TEI abstract; we now have a more sophisticated approach to the obsolete kanji and their modern equivalents, encapsulated in the ODD.