Implement replication of BCRHP data to Data BC

[bferguso]

Need to replace the current replication process of the BCRHP data from HRIA with a new process from Arches

Some issues/questions that have come up in the implementation:

• [x] How to choose authority record - Max authority (Prov > Muncipal > Federal?), order by date desc? Look at DcRu-234 for comment - Can we include all separate by comma or only pick one? This would be same logic for CRHP submission if we have to pick one, so Prov-Mun-Fed. EJB - There is a government name and startdate associated with this value in different columns There would only be an implicit link if we made all of them ";" separated values. Okay let's make highest level then, same as CRHP export. EJB - For sittes that have 2 at the highest level, should we pick the one with the most recent date? EJB: YES
• [x] What is address_heritageexportedtofed? In HRIA we can indicate which address to send to fed, this is under the legal description table, field called 'heritage export to fed', maybe this is that field EJB BF - We don't have that in Arches. Do we need it? I want to say no because to select one address for a multi-address site wouldn't be accurate and I think that HRIA column wouldn't make sense for the way we have cleaned up that legal address/civic address data. Did we figure out this logic for the CRHP xml or no? EJB - BF: Removed
• [x] What to do with multiple parcel IDs and/or addresses? (DcRu-234) This one probably generated by address validation. This has multiple addresses in HRIA. Can we include all seperated by comma? What was our process handling this for CRHP export? EJB - We are just picking the first address. Whatever we choose to do we should probably do the same in both places as you're suggesting. EJB: YES FIRST ADDRESS IS FINE
• [x] Authority->Government Name null values? In the past it was our process to only include Gov name in the highest authority in the table in HRIA but it is our intent to fix all Government name null values in Arches EJB We could populate the Provincial records with Government of British Columbia on import if desired. If so, please turn this point into a new issue and I can update the HRIA ETL process EJB: NOT WORTH THE EFFORT, THERE WOULD BE A SMALL NUMBER OF PROVINCIAL MISSING IT, CAN DO IT MANUALLY
• [x] DcRu-234 - export to CRHP different - showing 'Y' in HRIA but no record in the export list generated by Emerald - This record does not have a CRHP table or the required fields so this must be inaccurate. Maybe this is pulling from field mentioned above 'heritage export to fed' in legal description table. This record has a Y beside 138-140 gov street- EJB - OK, seems like what we're exporting is more correct so marking this point as resolved.
• [x] Do we want to replace the DOCUMENTKEY with anything? No idea what this is, do you have example record? EJB The DOCUMENTKEY is a link to a digital object in HRIA. I'm not quite sure why it would have been exposed in the first place. Agree let's remove.
• [x] Do we need to expose the geometry attributes (UTM info, area, boundry length, etc)? We are using UTM instead of lat and long right? I would say yes EJB No, we're using Lat/Long in Arches. Added UTM fields
• [x] Remove mapsheet number / scale columns No the mapsheet numbers aren't coming into Arches, it will be in Legacy HRIA Data RM in the future so please remove those. EJB
• [x] We are currently not downloading TENURERESERVES_JURISDICTION data into Arches (column near the end of the current view). Should we be? No, majority of this info is automatically generated in HRIA and can be found elsewhere. Any manually entered comments in this section in HRIA will be preserved in the LEgacy HRIA data info, no need to export for this sheet EJB
• [x] We have merged the street number and street name in Arches. Would you like to do the same for the view or do we need to separate them? Keep them merged EJB Column name is "street_address"
• [x] Can we add a link pointing people to Arches? Either general website or record specific site? I guess this can't be done until after we go public but wondering if its worthwhile adding a placeholder for this now? - I've hard coded what the PROD resource link will be for now. At some point we should create a ticket to allow these values to be instance-specific. I'd like to think about what the best way is to do that though so lets create a ticket for after MVP for that? EJB: DONE TICKET #777
• [x] Currently the list of sites is limited to those with Registration Status = 'Registered', 'Legacy', 'Federal Jurisdiction'. We now have a "Restricted" field. Should we use that along with BCRHP Status ('Approved - Basic Record' or 'Approved - Full Record') - to filter the sites instead? EJB: i THINK REGISTERED; FEDERAL JURISDICTION; RECORDED/UNPROTECTED IS WHAT WE WANT. AND YES WITH THE APPROVED BASIC/FULL
• [x] Should we standardize the column naming conventions? Some multi-word columns have underscores (eg government_name) and some don't (eg governmentlevel) EJB: YES BF: Taken a first cut for feedback.
• [x] Should we change "ishistoricsite" to "is_officially_recognised"? EJB: YES

  Dependencies

Epic

• [x] bcgov/BCHeritage/issues/709
• [ ] bcgov/BCHeritage/issues/768
• [x] bcgov/BCHeritage/issues/789
• [x] bcgov/BCHeritage/issues/814

This view is now less restrictive to allow a larger dataset to be used by RAAD. Data BC to apply additional filters. Filter for the view is:

bcrhp_submission_status in ('Approved - Basic Record','Approved - Full Record')
and registration_status in ('Federal Jurisdiction', 'Recorded/Unprotected', 'Registered', 'Legacy')
and not restricted

[bferguso]

So far we have the following. I will update as we progress: column_name	datatype	nullable	default
site_id	uuid
borden_number	text
is_officially_recognized	text
registration_status	text
common_name	text
other_name	text
parcel_id	text
street_address	text
city	text
province	text
locality	text
location_description	text
government_name	text
government_level	text
protection_type	text
protection_start_date	date
reference_number	text
construction_date	numeric
construction_date_qualifier	text
significance_type	text
physical_description	text
heritage_value	text
defining_elements	text
document_location	text
architect_name	text
builder_name	text
current_function	text
historic_function	text
dimensions_area_sqm	numeric(19,2)
gis_latitude	numeric(10,6)
gis_longitude	numeric(10,6)
utm_zone	numeric(2,0)
utm_northing	numeric(10,0)
utm_easting	numeric(10,0)
site_url	text
site_boundary	geometry

[emjohnst]

@bferguso

• [ ] what is it doing with multiple dates? We could narrow date_histdatetype to only include construction dates but there still might be possible. Could make rule to include earliest date. BF - We could either ';' separate multiple dates (but that would lose the link of "Circa" if one was approximate and others aren't), or as you say pick the earliest date. I've done the second for now but can change it; EJB: is this also losing the link to date type? I think it's lets confusing to pick earliest date.
• [x] Can we remove address_heritageexportedtofed - as discussed above this is not in Arches
• [x] Can we remove is_accepted_by_feds - this field is more nuanced in Arches now (submitted, approved, update needed, etc.) I don't think people using this need to know its been submitted to Canadian Register. Done. This means that there is nothing being displayed from the Site Admin part of the model
• [x] Were functions included on original views your working from? Not sure if necessary if there is SoS. There could be multiple functions as well. - Yes, functions were. They're ";" delimited values in the current views

[bferguso]

@emjohnst - Updated the table definition in my comment above and added a comment to the unchecked task in your last comment. I will send you a spreadsheet by email of the data exposed by the view for your review.

[emjohnst]

@bferguso reviewed the data exposed view and have a couple questions/edits, some of these may just be data issues: @bferguso I'm updating this comment with some additions as of Feb 1:

• [ ] Please create two views (same fields) but 1 view should be Historic Places - Recognized (without legacy and recorded unprotected sites) and the other view should be Historic Places - Not Recognized (with recorded/unprotected sites) but please also remove legacy and cancelled sites. This how our data is displayed in RAAD and iMap with two layers. BF: Please see comment below Please add the following columns:
• [x] please add location description
• [x] please add legal act reference number
• [x] please add statement of significance description
• [x] please add statement of significance heritage value
• [x] please add statement of significance char def elements
• [x] please add statement of significance type *if multiple SoS choose high level similar to legislative act section
• [x] please add statement of significance document location
• [ ] please add general remark on view for all records “Please contact the Heritage Branch for information about this site at heritage@gov.bc.ca. To confirm information about a site that is registered under the Local Government Act, contact the local government directly.”

Data Questions

• [ ] Some sites are appearing twice (DeSf-13; DdSe-25) is this because of two common names in Arches/HRIA?- BF: Yes. What is the desired behaviour here
• [ ] There seem to be a lot of missing cities, particularly for registered sites with civic addresses there should be a city (310 records) - Lets look at this after we perform the civic address validation
• [ ] Seem to be a lot of missing PIDs, even for places with civic addresses
• [x] Province is blank for most records 5590 - in the data migration from HRIA to Arches can we add Province automatically? Then the spreadsheet would be resolved? BF - This was caused by a value mis-match in the load "BC" coming back from the validation, "British Columbia" in our LOV.

[bferguso]

For point 1 above:

1. I believe this filter is done on the DataBC side, I'm not sure we need to do it in our views. ok
2. I'm currently filtering records by:
   • [x] BCRHP Submission Status in: ('Approved - Basic Record','Approved - Full Record')
   • [x] Registration Status in ('Federal Jurisdiction', 'Legacy', 'Registered') - Please remove Legacy sites and add Recorded/Unprotected
3. For the multi-line SoS information - what kind of formats do you want? They're currently stored as HTML for the rich formatting. What is the desired output in the view? Just text, ideally no <,> characters

[bferguso]

Current data maximum column lengths. Some fields are numbers - We can work on scale and precision:

[2024-02-06 12:49:47] site_id: 36
[2024-02-06 12:49:47] borden_number: 9
[2024-02-06 12:49:47] is_officially_recognized: 1
[2024-02-06 12:49:47] registration_status: 20
[2024-02-06 12:49:47] common_name: 120
[2024-02-06 12:49:47] other_name: 311
[2024-02-06 12:49:47] parcel_id: 9
[2024-02-06 12:49:47] street_address: 33
[2024-02-06 12:49:47] city: 50
[2024-02-06 12:49:47] province: 16
[2024-02-06 12:49:47] locality: 416
[2024-02-06 12:49:47] location_description: 1925
[2024-02-06 12:49:47] government_name: 38
[2024-02-06 12:49:47] government_level: 10
[2024-02-06 12:49:47] protection_type: 34
[2024-02-06 12:49:47] protection_start_date: 4
[2024-02-06 12:49:47] reference_number: 25
[2024-02-06 12:49:47] construction_date: 4
[2024-02-06 12:49:47] construction_date_qualifier: 5
[2024-02-06 12:49:47] significance_type: 16
[2024-02-06 12:49:47] physical_description: 1336
[2024-02-06 12:49:47] heritage_value: 3988
[2024-02-06 12:49:47] defining_elements: 3961
[2024-02-06 12:49:47] document_location: 300
[2024-02-06 12:49:47] architect_name: 106
[2024-02-06 12:49:47] builder_name: 80
[2024-02-06 12:49:47] current_function: 133
[2024-02-06 12:49:47] historic_function: 138
[2024-02-06 12:49:47] dimensions_area_sqm: 18
[2024-02-06 12:49:47] gis_latitude: 18
[2024-02-06 12:49:47] gis_longitude: 19
[2024-02-06 12:49:47] utm_zone: 2
[2024-02-06 12:49:47] utm_northing: 7
[2024-02-06 12:49:47] utm_easting: 7
[2024-02-06 12:49:47] site_url: 80