Creating a map for Pavement Management Program - Public Works

Question

I need to update my city's public works Pavement Management Plan map. Specifically, it is a street map that illustrates pavement conditions. The County provides the Pavement Management report as an Excel file. A few years back an intern here created an Excel file, called Translate that he joined, in a new Access database, with the County's most recent Excel file. This Translate file must have been coded in a way, so it could be joined on the seg_id field between the two. His process includes a 33 step process. Access has since changed and I tried to complete these steps and rewrite them for streamlining the process, but no luck. There must be a better way! A code, model....something. In addition, I think the Public Works department could utilize ESRI applications. Suggestions?

Shelly

ShellyMorgan · Answer

William,

Thank you for your help.

As I mentioned, in my post, I am up-dating our city�??s road condition map, per OCTA Pavement Condition Survey. I short, I was able to complete the task that involved the following; preparing Excel data (from OCTA survey) that has an existing Section ID column, Pavement Condition Index (PCI), Name field etc., and adding a new column, SEGID (numerical). In addition, we have an existing �??Translate�?? table created by a prior intern (a programmer). I created a new Access database and merged these two tables using their common Seg ID fields in a new Access query table. I then joined this table to an existing GIS shapefile using ArcGIS.
However, I do not know how this Translate file was created; I am reviewing it for accuracy and the GIS shapefile as well. Ultimately, I�??d like to streamline this process for future use; perhaps create a model.
I appreciate any help or suggestions.

Shelly
Shelly,

Our Pavement Management Team has been using GIS to link pavement condition output to pavement segments/shapes drawn using CAD and imported into GIS. The result is a shapefile that can be used to display any number of attributes related to pavement condition, recommended repairs, cost for repair, etc. The linked image below provides a screenshot of this type of data exported to Google Earth.

[ATTACH=CONFIG]26258[/ATTACH]
https://kimley-horn.securevdr.com/d/s1363cec9d30458e9

The trick, as you mentioned, is to make sure each pavement section has a Unique ID that can be used to link to your pavement management database; I'd be happy to take a quick look to see if the data is ready to be joined using GIS or if you need to use some other descriptors to create a Unique ID on which to base this join.

Hope this helps,

William Reynolds
Kimley-Horn and Associates, Inc.
william.reynolds@kimley-horn.com

RamB · Answer

If no new roads have been built, then you could just do a join with the attributes. Also, you couls just replace old databasefile with a new one at the exact same location on your computer. If programmed properly, the new file will be read into ArcGIS or into the join. Then you change the display fields, or copy new fields into old fields.

regards,

WilliamReynolds3 · Answer

Shelly,

Our Pavement Management Team has been using GIS to link pavement condition output to pavement segments/shapes drawn using CAD and imported into GIS. The result is a shapefile that can be used to display any number of attributes related to pavement condition, recommended repairs, cost for repair, etc. The linked image below provides a screenshot of this type of data exported to Google Earth.

[ATTACH=CONFIG]26258[/ATTACH]
https://kimley-horn.securevdr.com/d/s1363cec9d30458e9

The trick, as you mentioned, is to make sure each pavement section has a Unique ID that can be used to link to your pavement management database; I'd be happy to take a quick look to see if the data is ready to be joined using GIS or if you need to use some other descriptors to create a Unique ID on which to base this join.

Hope this helps,

William Reynolds
Kimley-Horn and Associates, Inc.
william.reynolds@kimley-horn.com

VDOT Pavement Management.JPG