This post describes creating a work order plan template using surface streets report to create a pavement markings plan.

Check it out here:

The purpose of this notebook is to create a Street and Bridge Work Order plans based on segment IDs and additional comments on long line. Markings feature layers are published in the City of Austin ArcGIS Portal page available for public view as well.

The schedule for when and where sealcoat and overlay streets are completed is received through email by Street and Bridge Operations on a weekly basis.

The only manual process the user will have to do is to:

  • Input Segment IDs
  • Make comments on long line markings
  • Specify file path to retrieve the table of completed streets paved for PDF name and file path
  • Create any missing markings assets that are not visible in aerial imagery

This process will cut down on the previous process of manually editing a plans layout through copy-pasting imagery and writing Location IDs, work groups, markings found, and the exporting plans one at a time.

An excel document will be created based on this input and read segment IDs to find all short line and specialty point markings. This will ideally generate multiple PDF plans in a faster and shorter time frame.

Below is a table comparing the old process versus the new process for time it takes to complete on a busy seasonal schedule.

Process Time to complete
Manual Plan Creation 24 hours per month
New Plan Creation 6 hours per month

This project would not have been completed without the help from:

  • pandas to create dataframe of extracted table and transform the data
  • openpyxl to edit excel files
  • arcgis to search for markings feature layer dataset
  • pathlib to find path to excel document if it exists
  • PyPDF2 to merge PDFs for cover and pages in order
  • archook to search for arcgis and makes arcpy available to python
  • arcpy to create whereabouts markings plans using ESRI ArcMap Desktop software