Fall 2017
A Newsletter from the Federal Highway Administration (FHWA)
www.gis.fhwa.dot.gov

SDDOT’s Top 3 Reasons for a Migration to Microsoft SQL 2014

Article by Kimberly Zerr, South Dakota DOT GIS Coordinator

The mission of the South Dakota Department of Transportation (SDDOT) is to efficiently provide a safe and effective public transportation system and GIS plays a big role in decision making throughout the department. In order to improve our data quality and availability, the GIS staff at SDDOT started a data migration from Microsoft SQL 2008 R2 to Microsoft SQL 2014 in late 2016. Not only does MSSQL 2014 provide security enhancements for administrators, but it also gives the GIS staff freedom to control the data. Here are our top three reasons for migrating:

1. Support for autocomplete and suggestions
2. Editor tracking
3. Server security and editing privileges

Autocomplete is a great improvement for our online applications. It enhances the experience on our mapping applications by filtering choices in the search box as the user inputs characters. Users can avoid spelling mistakes and save keystrokes by choosing from the suggestions. This is a small change with a big impact.

Microsoft SQL 2014 allows editor tracking. This is an important improvement for several of our inventories and displays not only the last edited user and date, but also the user who created the record and when it was created. There are disadvantages to the editor tracking feature, especially during mass calculations where every record gets a new last edited date whether the record was changed or not. However, in the case of our road and intersection inventories, the pros outweigh the cons when it comes to knowing when an arc moved or when an attribute changed.

Figure 1: Illustration of SDDOT Autocomplete

The most important reason for updating and migrating is the enhanced security and editing privileges. In the past, SDDOT data on the SQL server was either read only or full access. With MSSQL 2014, administrators are now able to control editors with Active Directory and Windows authentication. Data owners and editors have a single account to manage and see only what they have privileges to edit. There’s no possibility of “accidentally” editing the wrong data.

Prior SQL servers were set up as backup and restore versions. When we changed Microsoft SQL versions, the new server was blank so we were able to move our data as needed and set up feature datasets. This made setting up editing privileges easier and helped us, the GIS staff, clean up our data.

While many other technical aspects affected our server behind-the-scenes, our top three reasons are the most noticeable to our SDDOT staff and customers. Our migration was not seamless and we had a few headaches along the way; however, overall our upgrade to MSSQL 2014 has been a success. The enhanced functionality and security measures have improved the way our GIS staff provides data and data access.

Research Grant Helps Intermountain West Prepare for Success

Editorial by Buckeye, AZ Mayor Jackie Meck, Chair of the Maricopa Association of Governments

As the mayor of a growing city in the greater Phoenix region, I know that understanding the characteristics of my community can help me serve it better. The same holds true for the region I serve through the Maricopa Association of Governments (MAG) and beyond, including the entire Intermountain West. Knowing where growth, demographic changes and employment clusters exist can inform important transportation planning decisions. MAG recently worked with the states of Arizona, Colorado, Idaho, Nevada, New Mexico, Utah, and Washington to look at our connected transportation network. We also explored ways to share data to enhance our competitiveness in the global economy.

There are nationally significant corridors in these states. It is important for us to link the movement of people and freight in a strategic way. Through the Strategic Highway Research Program (SHRP2) Expediting Project Delivery Grant, the Maricopa Association of Governments was able to work with the Intermountain West states on a project that resulted in three important tools:

1. Identifying transportation and GIS-related resources for the Intermountain West.
2. A “Story Map,” an interactive tool for transportation planners with numerous data layers
3. A model “risk register” to document various levels of risks that can prevent achieving the goals of an activity. Risks may include cost increases, unexpected archeological findings, and insufficient funding.

Figure 2: Maricopa Association of Governments Logo

These tools are a great first step in faster delivery of transportation projects.

The Intermountain West region is critical to the long-term economic success of this nation. Through the grant, agencies reached out to transportation management areas and state departments of transportation to identify needs and potential gaps in transportation and data resources. It also helped develop a shared vision and operating platform for easier data information sharing.

The goal of SHRP2 is to deliver projects faster, reduce congestion and improve safety, even as the number of motorists grows. I believe this project not only has laid the foundation for these outcomes, but also has built an important bridge for communication among the Intermountain West states that will serve us for many years to come.

Other News

Reports Available

Geospatial Information System Capability Maturity Models: Case Studies of Select Transportation Agencies
A new case study report highlighting 4 State DOTs and how they use the URISA Capability Maturity Model to assess their organizational maturity is now available on the Federal Highway Administration’s website. To read this, visit this link: https://www.gis.fhwa.dot.gov/reports/GIS_Capability_Maturity_Models_Case_Studies.aspx

Webinar Summary for Washington State DOT’s Demonstration of a Bridge Geospatial Location and Vertical Clearance Data Project
This webinar featured a team from WSDOT walking participants through the development process of creating a web app that helps travelers identify the vertical clearance of highway overpasses.

A written summary and a recording of the webinar, which was broadcast on October 5^th, is available on the Federal Highway Administration’s website. To view the summary, visit this link: https://www.gis.fhwa.dot.gov/webinars/webinar34_WSDOT_Bridge_Location_Vertical_Clearance.aspx

Coming Soon

Peer Exchange on State-DOT-Focused Capability Maturity Model
In September, Ohio DOT hosted a Peer Exchange organized by the Federal Highway Administration. The group worked to modify an existing Capability Maturity Model (CMM) to reflect the organizational structure of State DOTs more closely. A summary of this peer exchange will be made available on this page: https://www.gis.fhwa.dot.gov/reports.aspx.

New Capability Maturity Model
The peer exchange mentioned above resulted in a new version of the Slimgim CMM. FHWA, in conjunction with a working group, will be piloting the new tool with a select group of State DOTs. If you work with GIS at a State DOT and are interested in piloting this new tool, email Mark Sarmiento at Mark.Sarmiento@dot.gov.

Keep an eye out for a presentation on this topic and the results of the pilot at GIS-T in March, 2018 in Little Rock, AR.

For more information about this newsletter, please contact Mark Sarmiento, FHWA Office of Planning, at Mark.Sarmiento@dot.gov.