Update polygons in the VZDB

[patrickm02L]

The following polygons #9189, #9186, #9188, #9192, #9184 have been QA-ed and are ready to be updated in the VZDB.

We'll need to take the geospatial data and convert into (geospatial) SQL statements which will be run on the VZDB to add/remove/modify polygons.

@adrien-atd can you provide a shapefile of the polygons which have been modified or added as well as a list of those which have been deleted? This should have the data required to form up that SQL.

If it’s easier to put the removed ones in that shapefile with a flag that says they were removed – that’s cool too. @frankhereford can use that as the “removed” list, and in that case, I’ll be ignoring any geometry associated with the records in the shapefile that are flagged for deletion.

[frankhereford]

TLDR

The data provided exhibits a systematic error which appears to have occured when the VZDB data first was put into maint. I am going to evaluate making that imperfect, but pretty darn close to correct, dataset the source of truth, and see if we can reconsider our update process to better leverage Adrien's GIS abilities coupled with her expert knowledge of how the data is used in VZ analysis. @patrickm02L, let me know if this is OK.

Details

I've received the data which has these UPDATE, INSERT and DELETE directives. I find that the data which has been provided is offset from the old polygon data, such as one might expect with a projection error somewhere along the line.

I spoke with Adrien, and we observed that the updated dataset does align with the data in Maint, so I'm thinking that the error occurred when data was initially populated into the maint DB from the VZDB.

Because the offset is minor (on the scale of 1-2 US feet), I am hopeful that we can squarely quantify the scale of the error, and provided it is as small as I believe it to be, we can take the Maint polygons and just decree that they are correct and then instead of making CRUD operations on the VZDB, take the whole maint database and replace what is in VZ.

This has an upside side effect of making the future process of updating them much easier -- Adrien who has both 1) GIS skills and 2) VZ subject matter expertise -- she can just edit things in place in maint, and when she has enough changes to warrant it, to ask us to run a program that DROPS the postgres polygons and pulls in a fresh set from maint.

I am going to evaluate the feasibility of this and size it as a proposed resolution in the morning when I can get my hands on an ESRI machine at work.

[frankhereford]

I spoke with the geo team this morning, and Andrew is putting a request to get me access to maint. We discussed also the situation described above, and it seems like it is a reasonable plan. Will follow up once I can get on maint and we'll go from there.

[frankhereford]

Also, @patrickm02L, there are many more changes than those listed in this issue. Is this the intention of this issue, or do we need to change it up?

[patrickm02L]

@frankhereford we need to capture all the Issues. I listed the ones I could find at the time.

[frankhereford]

@patrickm02L I've met with Adrien twice over the last two days, and in our meetings, she expressed that she has made changes that are outside the list of polygons that were in the set that are in the group that she and Alan worked on.

I don't think it's feasible or productive to ask her to track all the changes she is going to be making on her own in our system. I think we should focus on setting up a system like this:

• The polygons are stored in maint or AGOL, and the set of data there is the authoritative set.
• Adrien inherently has the authority and ability to edit these with the ESRI suite she already has and which using is her specialty.
• Daily, we have an ETL which wholly replaces or synchronizes the polygon dataset in the VZDB with this authoritative dataset.
• The LDM system takes care of exposing aggregated data about crashes, persons, and units involved in crashes which occur in a polygon, such as the number of fatalities, injuries, events, etc.

This system puts the onus of tracking those geometry changes on the VZ team through their capable team members and frees us to focus on the applications, which is our domain really. When it was Alan working stuff while Adrien was out on leave, then it made a ton of sense because we were doing the work, but I think we have an opportunity to grant that tracking back to the VZ crew here to do internally, which is where the changes are generated.

I don't feel like i've done a great job describing this - I'm game to sync on it or discuss in a team meeting if you'd like.

[patrickm02L]

In Planning meeting 1/11/23.

Work with Frank and GIS teams during the 1/11/23 Sprint to get more clarity on how to break this up into separate Issues.

Will need to update the ETL after LDM work is completed.

[patrickm02L]

Re-iterating the next steps

• Store the polygons in a main database (either maint or AGOL) which is considered the authoritative source. Adrien would have the ability to edit these polygons using the ESRI software she is familiar with.
• Update a daily ETL process that would synchronize the polygons in the VZDB with the authoritative source, so that the LDM system would then use this data to provide aggregated information about crashes, injuries, etc. within the polygons.

This system would shift the responsibility of tracking changes to the VZ team and allow DTS to focus on the applications you are responsible for.

[frankhereford]

I have not heard back from CTM regarding access to maint, but that makes me wonder if I never had my access revoked from when I was on the VZ team. Provided that there is an open desk in the geo area tomorrow, I will login and see if I can access the data. I'd hate for a non-item to be blocking bring this on-deck when it's time.

[frankhereford]

I spoke with @andrewshensky this morning, and he has very kindly has advanced task forward by having the polygon dataset on maint published to AGOL. He indicated that an automated synchronization task from maint to AGOL exists, but it is initiated via CTN and has a side-effect of also pushing the data into DataMart.

I asked if he wouldn't mind pushing that data up now, which unblocks this issue, and if we can wait to reach out to CTN for full automation until we have a better handle on the frequency of updates and if that is needed at all. If they are genuinely infrequent, which I suspect, it may be better to let VZ communicate to the GIS team when a sync is needed, probably on the order of 1 request every few months at most. Andrew indicated that his team meets with Adrien regularly, and that provides a good time to communicate over sync needs. Also through that conversation, Andrew and Adrien can discuss if and when automation should be set up.

This will let the VZ team bunch their changes into coherent releases, and this will unblock the next step of this process, which is to adapt John's recent city council script to pull this data from AGOL and insert it into the VZDB.

John's script, I believe, does not have (nor needed) to handle the concept of authentication, so there is a question of if the new script / ETL will be able to authenticate to AGOL. I imagine there is a way, but I also feel like it might get a little hacky. Ideally (from the dev point of view), this dataset would be public so the whole question of authentication is put out the window, but that may not align with the goals or wishes of the VZ team, who ultimately should be making this decision.

@patrickm02L, can we put this on the agenda for the VZ call tomorrow to find out if making that feature layer public is workable with them, or if it needs to remain private?

[frankhereford]

@andrewshensky sent these links on slack. He has shared the VZ Polygon dataset in maint onto AGOL with the following links. They are presently shared with the VZ and DTS groups only, pending a discussion with the VZ stakeholders.

AGOL Location https://austin.maps.arcgis.com/home/item.html?id=f9db1ee0aaa2438284154ccc47302eb5 Service URL https://services.arcgis.com/0L95CJ0VTaxqcmED/arcgis/rest/services/VZ_polygons/FeatureServer

Thank you Andrew, I appreciate your help a ton!

[patrickm02L]

In Planning 2/8/23. Get permission to make dataset public if not use John's script for privacy authentication. Lewis L. expressed making it public in the last VZ Sprint Review.

We should send the a link to VZ teams and copy Chia and Frank AGOL Location https://austin.maps.arcgis.com/home/item.html?id=f9db1ee0aaa2438284154ccc47302eb5

[patrickm02L]

2/13/23, Lewis responded by email asking about public polygons

Confirmed that the polygon shapes will be public and we’re okay with that...

[patrickm02L]

We'll close this Issue once Andrew has updated the locations_polygons layer in MAINT with the new vision_zero_polygons layer, which is represented in #12636

[patrickm02L]

7/17

Got an update from Frank on this, here's a summary:

This Issue involves updates to 5 polygons that were worked on by Adrien and Alan, stored in the MAINT data backed up by Andrew and later replaced with data from the vision zero database.

Unfortunately, this work will need to be redone, likely by Milad, starting from the new data in MAINT to avoid any offset issues.

These polygons are part of a set of reshape requests that were discussed by Xavier during the previous sprint review, but it is uncertain if Milad has a list of the requests made during the Adrien/Alan era.

Action

• Patrick to follow up with Xavier and Milad about outstanding polygons reference #8388

[patrickm02L]

8/16/23

Followed up with Milad, Xavier, Joel

Action Items

• Milad to review Polygon Checklist Issue: https://github.com/cityofaustin/atd-data-tech/issues/8388
• Franks polygonized updates
• DTS Team to Officially make Polygonizer: https://github.com/cityofaustin/atd-data-tech/issues/13414