Do test of creating Borden Numbers in HRIA

[bferguso]

Trying to use direct SQL to generate skeleton record in HRIA. Need to do test to see if this is possible. Things to consider:

• [x] Does a skeleton record require a spatial feature? - No
• [x] Is there a way to connect from Arches application to the HRIA database? - Look into ESRI API
• [x] Think about security implications (is there another way to provide an API for Arches?)

If direct database is not possible, other (not ideal) options are:

1. Use HRIA bulk upload process
2. Manual process (last resort - some years have 500+ new entries)

Approach proposed:

1. Create an Arches application schema in HRIA databases w/ the following grants:
   • connect,
   • create procedure,
   • select on SDE.TFM_SITE, SDE.A101, object ID sequence,
   • insert on SDE.A101 (this could potentially be different as it is a generated table but for now it is correct in all environments.
   • Schema object grants may need WITH GRANT OPTION to allow logic to be wrapped into a function.
2. Provide network access between the Arches application server and the HRIA database
3. Write a database function in the HRIA Arches application schema that:
   • checks for the maximum borden number in a given borden grid using the TFM_SITE and A101 tables
   • inserts a new row in the TFM_SITE table with the next borden number
   • returns the new borden number to the caller

Testing will need to be done in HRIA to ensure that this process does not cause any issues within the HRIA application.

The borden grid WFS can be used to lookup what grid we should be searching for based on the geometry of the site. Proposing that we use the centroid of the geometry to do the lookup to find the point for the following WFS request: https://dlvrapps.nrs.gov.bc.ca/int/arches-bchp/bctileserver/geo/pub/WHSE_ARCHAEOLOGY.RAAD_BORDENGRID/ows?service=WFS&request=GetFeature&outputFormat=json&version=2.3.0&typeNames=WHSE_ARCHAEOLOGY.RAAD_BORDENGRID&cql_filter=DWITHIN(GEOMETRY,POINT(1161815%20452123),1,meters)

• [ ] Need to determine what value should go in SDE.A101.SDE_STATE_ID and any connected tables
• [ ] Need to determine what sequence is used for SDE.A101 and if it is auto-populated

[bferguso]

Created test insert and ran it w/ Archeology branch. Outcome is:

1. Was successfully able to create site with next borden number in HRIA without associated geometry
2. New entry was visible from within the HRIA UI
3. Next site was successfully created from with HRIA UI
4. The borden number generated in 3 was not visible in the SDE schema. It is held in ESRI and is only pushed back into the HRIA SDE schema as part of a nightly batch process
5. In consulting with Arch IT group there appears to be an ESRI API that can be used to access the full set of Borden Numbers in the system.

Was able to set contextual fields (USER IDs, creation/modification dates, createdusing = 'Arches' as part of the insert. Set the object ID to a negative number as it is unclear how the sequence number is generated.

[bferguso]

After further consultation w/ John L, found a potential way to make this work. John L. agrees in principle to the approach we are working on (added to the description).

Features that have been created w/ the UI are temporarily stored in the SDE.A101 table (in TEST and PROD). To find the next sequence number in the borden grid we need to perform a union of the SDE.TFM_SITE and SDE.A101 tables.

Although the table in TEST/PROD is A101, it is automatically generated. To confirm/identify the temporary table that is used for this the following query can be run:

select * from sde.table_registry where table_name = 'TFM_SITE';

John L. will try to identify the oracle sequence to populate the OBJECTID column or give me access to look at the sequences.

[bferguso]

@TMcFarland-Heritage, @BaldeepPal - would you please setup a meeting w/ security to see if there will be any issues accessing the HRIA database from our Arches application? The initial tests we have done look promising and the last steps are to ensure that we can open a port from Arches to HRIA database and then finally include the other vendor to see if there are any other issues or concerns they may have.

[emjohnst]

@bferguso looks like there was another ticket completed about the security review of this and I assume was ok. Let me know what next steps are to set up a meeting with vendor.

[bferguso]

Have an end-to-end running in my dev environment using DB logic and FDW to connect from Postgres to HRIATST1. Currently using copies of the A101 and TFM_SITE in my personal schema.