Project: Transportation Mitigation Web Map

[TracyLinder]

We are creating a web map to display TIA mitigation data for our internal stakeholders.

Background

ATD has collected and manages funds for TIA fee in lieu program. These funds are available for use by various stakeholders such as The Public Works Department. These stakeholders need a tool to explore the mitigation data spatially, identify potential funding opportunities, and link back to the KNACK TIA case to continue research.

Scope & Deliverables

This effort will involve resources from several teams:

• Transportation Engineering Review
• Transportation Development Services
• DTS Product
• DTS Apps
• DTS Geo
• DTS Dev
• DTS AMANDA

Deliverables include:

• Mitigation data scrubbing
• Create TIA Mitigation feature layer
• Build a prototype of possible visual solutions
• Build solution (web map)
• Automate: Knack to AGOL
• Test & iterate
• Release

Desired Outcomes

The mitigation funds ATD has collected will be easier for our stakeholders to find and use to construct the identified mitigation improvements.

Timebox

We estimate this will take 80-100 hours of total work.

---

This Github issue represents a project of Austin Transportation's Data & Technology Services team. Project status is documented regularly in the comments below.

[TracyLinder]

@ChrispinP can you please work with @johnclary to identify the next steps on this? I'd like to be able update the stakeholders on Monday

[johnclary]

Might want to turn this into an epic. Here are the steps @ChrispinP. Happy to review with you.

• Load all street segments into a new object in Knack. Create connection to mitigations object. @alan-deanda can get you a CSV of these records.
• Create a dedicated arcgis online map and app so that customers can easily lookup their street segment and view the segment ID (@alan-deanda can help with this)
• Embed the ArcGIS online app into the TIA app. See this rich text view in the Data Tracker for example

[TracyLinder]

This is already in an epic for TIA Mitigations. #4938

[TracyLinder]

@ChrispinP are you able to give me an estimated completion date based on John's input above?

[TracyLinder]

@johnclary I think you were correct, the mapping portion of mitigations will need it's own epic. I'm converting this one into an epic. It will be 1st three tier epic!

[ChrispinP]

Scheduled meeting with Alan to discuss work we need to do for this.

[johnclary]

Thanks @ChrispinP! Let me know if you need any help.

[ChrispinP]

Added Segment ID map to Mitigation page.

[johnclary]

@TracyLinder it looks like we need separate issues for the TIA feature which enable users to map their mitigations in the TIA module, versus this feature for allowing staff to view all mitigations on the map.

@ChrispinP has setup the former, which allows users to add their mitigation street segments—y'all need to review this together to decide what else is needed. E.g., page layout, map symbology, etc.

[ChrispinP]

Starting CSV import for Segment ID's into TIA_mitigation_geo

should take about an hour, will create connection with mitigations once complete.

[ChrispinP]

Updated mitigation fields. Mitigation Segment ID is now set automatically from the connected Mitigation Geo Segment ID. Alan updated Mitigation map to display Segment IDs without commas. Replaced Mitigation Location with Mitigation Geo connection field so reviewers can search or paste Mitigation Location by street name or segment ID. Updated GeoDisplay to show both Segment ID and Street Name for Mitigation Location and sorts mitigation tables alphabetically

[ChrispinP]

tested and everything looks good, closing as complete

[TracyLinder]

Reopening this to serve as the epic for all things related to the stakeholder map we will build for the TIA Mitigations data.

• This work is not in scope for the TIA Mod Project

[TracyLinder]

@dianamartin & @ChrispinP This is the epic i'm going to use for the mitigation map. I created it 2 years ago during the TIA Project waiting for prioritization. Just an FYI.

[TracyLinder]

Team Huddle Sync 3/23/23

• Chrispin sent mitigation table data to Andrew, Andrew put together an initial workflow, the table had 1,026 items and only 57 had a feature ID and can be tied to an intersection/segment and the others can't be mapped w/ existing data.
• Has Nelson spoken to Gilda about help with data scrubbing? - Not yet, Nelson has been out.
  • Sending email today
• Created issue for the map prototype & backfill page. Set for sprint beginning 4/5
  • Attached to this epic
• Tracy schedule next sync on the 3rd or 4th

[TracyLinder]

Gilda approved Drew helping backfill.

[TracyLinder]

Team Sync 4/3/23

• [x] Recap from last sync
• [x] Drew approved to help w/ backfill
• [x] Review backfill pages & workflow.
  • Where did we put notes from the internal review last week? I know we have clarifications to ask.
• [x] Discuss next steps

---

Notes

• Reviewed backfill page, see #11815
• Reviewed mitigation map prototype #11816
• Syncing again on 4/25

[TracyLinder]

Synced with Dev team 4/12/23

• John added notes to the huddle issue, here
• I created DEV issue #12011

[TracyLinder]

Update

The historical mitigation data is being scrubbed to add mappable ID's. The mitigation map draft looks great; next step is to identify and map the data we want published.

[TracyLinder]

Project Mtg Scheduled

• 6/5/23
• Discuss data backfill and next steps

[TracyLinder]

Update

The majority of historical mitigation data has been scrubbed to add segment ID's. There are 110 records remaining that can't be mapped with the information available in KNACK. The team is meeting on 6/5 to sync and discuss next steps which should include:

• Identify methodology for adding segments to the 110 records without location data.
• Discuss the data to publish and where it maps.

[TracyLinder]

Team Sync 6/5/23

• [x] 110 Records w/o location data
• [x] Sync on web map (Andrew)

110 Records w/o location data

• Drew showed us
• Filter by blank mitigation location
• Let's try to export the locations from AMANDA
  • [x] (T) Create an issue #12479

Sync on web map

• Needs to turn intersection ID's into something (I missed it)
• High level of what would be published
  • Project & mitigation details
  • Link to the final TIA memo (need to discuss the logistics of where/how we link the final memo. Load to KNACK or map to network drive where they live now.) need more scoping #12478
  • [x] (T) schedule scoping 👆🏼 w/ team, Nelson, Matiur
• [x] (T) schedule sync w/ Chrispin to select the data/fields to publish & Andrew as optional. #12480
• [x] (T) schedule next project sync

[TracyLinder]

Update 6/22/23

Tracy, Cody, Diana, Chrispin, Matiur, Andrew

• Nelson no longer with TPW
• Tracy gave an overview of project & work to-date
• Cody: this is still a big need; will still have management support to continue the work
• Who is our primary sponsor in place of Nelson? --- Cody

Cody needs to be able to answer finance related questions:

• How much fiscal is available? --- This is something Nelson was calculating manually using various sources of information, including his own spreadsheets. --- Not something the KNACK app can provide currently.
• We need to talk to finance - who? Ask Tim T. & Erin who to invite. Tracy will schedule meeting.

We discussed the data available for funding and it's usefulness.

Why can't we see historical mitigations on the TIA Mitigation page?

Quickly mentioned TIA Memos. Ran out of time.

TIA Memos live in AMANDA and on local drive. Some are also in KNACK. We need a single source; probably AMANDA.

Make sure next project sync is scheduled.

[TracyLinder]

8/3/23

• Drew is done; only 22 record locations couldn't be found.
• Cody was able to speak to Van about the finance portion of mitigation data, but the data isn't easy to understand or be used to determine what fees have been spent.

Action Items

• [x] @ChrispinP export data related to the 22 records without a mitigation location
• [ ] Move forward with action items on #12478
• [x] DTS will internally analyze data & Tracy will schedule (meeting on 8/29)
• [ ] Cody - Find data about any mitigation funds we have spent/paid out to-date.
• [x] Tracy - Schedule next project sync (Scheduled for 9/9/23)

[TracyLinder]

Project Update as of Aug 17, 2023

We are done scrubbing the mitigation data to add geo ID's. We've moved into data mapping and are currently working on 2 major items:

1. Migrating all TIA Memos to their respective TIA Case in the TDS Portal
2. Learning about how TPW manages the allocation of mitigation fees, and where we can find data about fees allocated to date.

[TracyLinder]

Conversation with Van from TPW Finance

• ATD began with TIA program in 2019
• The mitigation data we have in KNACK does not match the revenue data Van is maintaining.
• Van will have time to help us after mid-september
• These are considered CIP funds and currently reviewed by Tim T. in finance
• There's no point in discussing how the money is allocated or the allocation data (lives in ecapri) because our current mitigation data may not be reliable. We'll come back to this.
• Van needs to join our next project meeting.

Who manages the mitigation money collected prior to 2019? - DSD

[TracyLinder]

I updated the project evaluation for this today.

[TracyLinder]

Project Sync Notes

• Van will validate her finance spreadsheet to make sure it's accurate and will send to this team
• Chrispin wants to do a data analysis w/ Van's finance data against our KNACK data
• Cody is onboarding a new Nelson soon - a few weeks.
• Tracy is super confused about how to understand the KNACK data.
• We'll re-sync next month. Tracy will schedule.

[TracyLinder]

Project Meeting 11/8/23

• Meet Kat!
• Review data together
• TIA Memo location in the [G drive location] dfs\TPW\ATD\Trans\Transportation & Engineering\Programs\Development Transportation Improvements Fiscal Projects\Program Management\Accounting_Finance
• Van and Kat will meet to work on making sense of the data and identifying areas where we may need to focus on cleaning up or mapping.
• Tracy will schedule a sync for the last of Nov or early December.

[TracyLinder]

Project Meeting 12/5/23

• We discussed Kat & Van's data analysis and what they need to be able to clean the data up.

• Mitigation Record Column Discussion:

  • Thinking of removing Mitigation Cost, Share$, Share Paid --- but more research is needed to determine if those data points are needed or not based on the way the original data was captured.

DTS Dev Action Items:

• Build a mitigation memo attachment button/feature on the Historical Mitigation Page View details.

  • Use existing TIA Memo object
  • Need to attach multiple types of documents, and they want to identify attachment types (Response, Final Memo, LOC (line of credit), Release, Fiscal, Other)
  • Add the attachment table to the advanced search

• Clean Up Mitigation Details

  • Removing fee sent, fee paid

• Edit Mitigation Details Feature - Allow users to edit the following:

  • Council district, review area,
  • Case status - Chrispin has tech concerns with randomly updating case status' due to system automation based on status. He'd prefer we assign specific statuses to Historical Mitigations that aren't used by the TIA group for current work.

• Edit Mitigations - Allow users to edit the following:

  • Mitigation Type, Mitigation Cost

• View Mitigations Landing Page

  • Add columns to the view: Council district, reviewer area, Fee Type (new field),

• New field on Mitigation Details: Fee Type

  • Values: Fiscal, Fee-in-Lieu, Combo
  • Only allow one value to be chosen

[TracyLinder]

Project Meeting 1/5/24

• [ ] Remove fee fields from edit mitigation form (screenshot below)

• We discussed the last updates Chrispin made on 14842

• We discussed needing to bring in a rep from TDS to speak to any potential changes to the TIA mod. Kat will speak to Nathan at a meeting next week to discuss whom to add.

• Until Nate is consulted we will hault making further DB updates.

• Chrispin showed a map modal mockup. Everyone likes it.

• Idea: Have devs run scripts to auto-insert the missing council districts & reviewer area. Diana said we did something similar for Data Tracker.

• About 1,100 mitigation records need to be scrubbed.

[TracyLinder]

Debrief with Kat on TDS Sync 1/30/24

• People need to see information about encumbered funds. Where do we put that data?
• For this database we don't need to track both spent and encumbered. That data is in Ecapri. We only need to track what has been encumbered i.e. unavailable.
• Scope should be financial management of mitigations vs project/implementation management.

Remembering the goal: For people who can spend mitigation monies to identify available funds

For Discussion @ Project Sync

• Rename the SPENT field to ENCUMBERED
• Reorganizing mitigation table columns and edit form to make data entry easier. On the landing page, and mitigation details page.
  • Review all fields to determine if they are still necessary and identify why they exist
• Adding the FDU field to Mitigation Details object vs on the mitigation line item.
• Adding a field to capture the ECAPRI record(s)?
• Is there any other financial data that Van/finance needs to record that isn't being recorded in Ecapri?

[TracyLinder]

Project Team Sync 2/1/24

Summary:

• We attempted to define the mitigation data columns. Why they exist, what data is meant to be there, who uses it.... We hit a wall due to TDS using the same data object and not being in the room.
• It's agreed we don't need to collect data on how the money is spent. The scope is only what is necessary to publish available funds.
• We've decided to pause on data cleanup until there is a consensus on the data. To that end DTS is meeting internally to devise a POA as to how we approach this challenge. Then we will meet with the project team and TDS to define the data.
  • DTS meeting internally on 2/8
  • Larger group meeting scheduled for 2/20

Steps to publishing available data

1. Making sure we understand our data
2. Determine what data to be published on the map, per case
3. Map the data and bring Andrew in to setup the map

[TracyLinder]

Feb 20, 2024

Today we brought together TDS & TED to review the mitigation page data fields and discuss:

• Why they exist
• Which group maintains it
• Definitions of certain fields
• Overview of formula fields

[TracyLinder]

Project Sync Feb 26, 2024

[TracyLinder]

Selecting Fields for Mitigation Map

Project Level:

• Show projects with status of Verified & Allocatable
• and Remaining Funds is >$0

Mitigation Line Item:

• Only show mitigation line items with a Fund Status of Available

Next Steps:

• [ ] Tracy to create ticket for the updates for the apps team (Chrispin)

We need to figure out the expiration date logic

[TracyLinder]

April 1, 2024 Project Sync

Agenda:

• [x] App updates #16202
• [x] Map updates
• [x] Kat's email
• [x] Next steps

[TracyLinder]

Project Sync May 2, 2024

Agenda:

• [ ] Review Map prototype (#16480 #16481 #16482)
• [ ] Next steps for publishing map

Map Prototype Feedback: Filters:

• [ ] Add council districts
• [ ] Add SIF areas
• [ ] Add Mitigation Fee Types
• [ ] Remove TIA types

Other:

• [ ] Able to see council districts and SIF areas on the map
• [ ] Open text search
• [ ] Search by street/address

Modal:

• [ ] Should only include case level information

Action Items:

• [ ] Andrew to get project team access to the draft map
• [ ] Kat gathering feedback on map internally
• [x] Tracy schedule next sync
• [ ] Andrew update map with feedback from today.

[TracyLinder]

The project team needs to discuss mitigations that apply to trails. See #11462. Should we add trail as an improvement type?

[TracyLinder]

Missing mitigation cases

1. Nate said he had a resource for the data entry. Update?

Accounts w/ Issues

[TracyLinder]

Project Closing

The mitigation webmap has been in production for over a month. There are no other outstanding deliverables.