Update location polygons in the Vision Zero database

[atdservicebot]

What application are you using?

Vision Zero Editor

Describe the problem.

This request is partially based on an email exchange with Andrew Shensky on Feb 28, 2024. His response is mentioned at the end of the message.

Request: The new set of VZ polygons is ready to be replaced or used as an improved alternative to the old location polygons. We ask to use this new set in the new LDM. We request you inform us about the new layer's name, location, and all the confirmations that need to be processed upon the implementation.

The newly maintained Feature Layer is hosted on https://austin.maps.arcgis.com/apps/mapviewer/index.html?layers=0a0a62605831484e90577506058ce322

Where two layers are grouped under VZ Polygon Comparison (Old Level 5 and New 1-5):

1) Final Output 4: This new set of VZ polygons includes all 1-5 ASMP locations. 2) Location Polygons Current Level 5: This is the old level 5 used to compare new level 5s against old level 5 polygons.

The problem we are trying to solve: The new set of polygons (Final Output 4) must replace the current production polygons. This will ensure all polygons (levels 1-5) are in one place. However, other triggers must be implemented to avoid aggregating crashes from level 5 polygons to level 1-4 polygons.

The new IDs are assigned, and the old ones are matched. Multiple fields (e.g., "Location_Description") will be constant, so I proceeded to populate or match them. The attribute table still needs a little work, and the most important one is to map it to the order of current polygons at production (read-replica for VZ = atd_txdot_locations on gismaint1 server).

While the index order of fields could be subject to another discussion, I am trying at least to keep the names of the fields (column) as they are at the production level (atd_txdot_locations) to help Xavier and myself maintain the SQL queries that have been written for multiple projects. However, some filed names are subject to change in the LDM.

Since I had editing challenges on the ATD_ADMIN.vision_zero_polygons I decided to make a copy of it and do the job while being able to see the immediate changes appear on the screen and, once done, append features to the ATD_ADMIN.vision_zero_polygons.

I believe many fields, including the ones related to the crash data, must be calculated directly in the database and updated regularly by the person who maintains the PostGIS table for production and, of course, for creating a read-replica for Vision Zero access (the current one is atd_txdot_locations).

While I could remove, change, and update features (rows) from ATD_ADMIN.vision_zero_polygons I do not have permission to change or delete the fields. A proper append requires both tables to have the same fields.

The current ATD_ADMIN.vision_zero version under my versioning has no rows, but fields are present.

Andrew Shensky's response:

"Since the ATD_ADMIN.vision_zero_polygon data is owned by our admin account within the maint database, all schema changes will need to be made using our admin credentials. It would probably be best for us to set up a quick discussion to see what changes you are needing and then the DTS team can make those changes for you. Also, since this data is pushed from maint to AGOL we will need to update that feature service and the FME process that pushes the data. I’m not aware of any other downstream items that may be impacted by these schema changes, but it would be good to confirm if any additional data/process will need to be updated."

Let me know if you have any questions.

Thanks,

Describe the outcome you'd like to see when this feature is implemented.

The outcome consists of an alternative to the current set of vision zero polygons with more accurate geometries and combinations between levels 1-4 and 5.

Describe any workarounds you currently have in place or alternative solutions you've considered.

We currently use the old polygons as a workaround.

Requested By Milad M.

Request ID: DTS24-116835

[andrewshensky]

@johnclary it sounds like Milad has made some schema/data updates to a vision zero polygon service he has been maintaining in AGOL. My understanding is that the vision zero polygon data should be maintained in the maint database, then pushed to AGOL, and then that service is used by the vision team. If we update the feature class in maint with the changes Milad has made and push those to the AGOL service below, will that have any downstream implications for the VZ team.

https://austin.maps.arcgis.com/home/item.html?id=64c4509d548547ceb079455f3e5be896#overview

[johnclary]

@andrewshensky you are free to make any changes to maint or AGOL layers without impacting things on the Vision Zero database.

in the future, we would like to set up an integration that periodically loads these features into the Vision Zero postgresql database. i am going to continue this conversation in Teams because i have a few questions for Milad about the schema changes.

[johnclary]

Note to Milad (cc Andrew and Xavier) via Teams:

this is definitely something we'd love to work on as soon as we go live with the new data model. we'll need to work with y'all to agree on the priority of the various requests y'all have in the queue.

i will have more specific questions once we pull this work makes it to the top of our todo list, but for now this seems like a clear enough request to me.

you mentioned that you are still planning to clean up the attribute table, which is great, because i see a lot of columns which do not appear to be used. it would be nice to get the column set down to the bare minimum that is needed/maintained.

[johnclary]

I schedule a meeting to discuss with the Milad and Xavier this Thursday, 9/5. I excluded Andrew because I think we have plenty of business-process and data related questions for now.