Enhancement: Populate `WORK_DATE` and `LAST_WORK_DATE` fields on markings data in GISMAINT

[ChristinaTremel]

Related to a problem?

• Jordan is working on a dashboard and noticed that the dates are not as updated on GISMAINT as they are in Open Data Portal.
  • Susanne and Andrew noted that the reason for this is because the WORK_DATE is being populated in the open data portal and not currently being updated in GISMAINT. This issue would be to resolve that and ensure that dates are being populated across the board
• Similar to #14706, we are needing to update the WORK_DATE field in GISMAINT dates tables with accurate completed dates
• Set up overnight process to run that populates the LAST_WORK_DATE on the assets in GISMAINT with the max WORK_DATE from the dates tables

Desired Outcome?

• Populate WORK_DATE in the markings dates layers in GISMAINT
  • Markings Specialty Point Dates
  • Markings Specialty Line Dates
  • Markings Short Line Dates
• Populate LAST_WORK_DATE in the markings asset layers in GISMAINT
  • ATD_ADMIN.markings_short_line
  • ATD_ADMIN.markings_specialty_line
  • ATD_ADMIN.markings_specialty_point

Additional Context? Flow of data: Work order Date in ODP -> Dates Table in ODP -> Dates Table in GISMAINT

[ChristinaTremel]

• Will want to try this first in GISMAINT test or dev with short line data

[ChristinaTremel]

Existing Process

• Currently the CTM process pushes the Maint data to AGOL and this could be an opportunity to get away from this entirely. An annoying piece of that is the AGOL dataset is owned by CTM, not TPW. If there's a schema change we have to go through the CTM process and the reason we sometimes get backup manager errors, they need to have the ability to export the dataset from AGOL. If we owned the dataset we could just set that up ourselves. Also none of the related tables get pushed to AGOL.

Proposed Changes

• Create a parallel process to CTM and create our own services to push the markings assets and dates related tables to AGOL.
  • David has a script that shows where datasets are being used. Will need to identify a process for switching out the services and ensure they are pointing to our services.
• Request to CTM to change ownership of the feature services to us and we would overwrite them with the schema we identify. Geometry layer and URL would stay the same and we would add the related table and update the FME process to write to the existing data.
• Writing directly to MAINT could cause some issues so we like the idea of streamlining the entire process and we could keep MAINT as light as possible by using a similar flow to the long line layer. We will only populate the work dates in the AGOL layers and that would be the data used by the SMD team.