The Client

The Utah Department of Technology Services (DTS) and the Utah Department of Transportation (UDOT)

The Objective

In early 2009 when the UPlan prototype began to receive nationwide attention as a regional collaboration platform for transportation planning professionals, UDOT management quietly initiated the UGate project, an LRS-based spatial data warehouse, which would serve as the infrastructure upon which UPlan would thrive. Gistic Research was contracted for its Linear Referencing System (LRS) expertise and its hands-on experience with Oracle® and Esri®.

The Challenge

There were three main roadblocks. First, the LRS geometry was either out-of-date or did not conform to UDOT’s LRS standards. Second, assets or events defined in various operational systems lacked consistent location definitions. Lastly, an agile and sustainable ETL (Extract, Translate, Load) process, driven by the customer and defined by the data stewards, did not exist.

How Gistic Helped

To overcome the LRS geometry roadblock, Gistic developed LRS QA/QC modules using Oracle Spatial and Esri ArcSDE APIs, which provided feedback to the system or users on 18 different error or warning states. These modules improved the efficiency and standard conformance of the LRS route building process, and became a key component of UDOT’s route maintenance program. Gistic further designed and implemented an Oracle sub-system to capture spatiotemporal changes of the LRS resulting from the agencies’ resolutions.

While most assets or events managed by UDOT had spatial components, a significant amount of spatial data in various systems was unpopulated or existed in inconsistent states. Gistic and the UDOT team designed and implemented iMap, a JavaScript plugin featuring GeoServer as the middle tier and Oracle at the backend. iMap could be integrated into any asset management system, enabling users of those systems to correct or enhance assets’ spatial attributes with a consistent and friendly user-experience. Figure 1 shows the architecture of the iMap system; Figure 2 is a screen capture of the application.

ETL processes are critical to a data warehouse project. In UDOT’s case, this was particularly true for several reasons:

+ There were large, diverse and dynamic data requests from internal and external customers;

+ There were over a dozen source data systems, each with its own data steward responsible for controlling access to the data;

+ There were very limited resources to handle the technical and coordination aspects of the ETL process.

It was determined that an agile ETL process, with clear definition of roles and responsibilities in various states of the ETL process, was needed. Hence the birth of UGate Manager, a web-based, workflow control application used by the UGate Administrator to manage the publication of business data to the spatial data warehouse. Figure 3 shows the role of UGate Manager in the overall architecture.

Figure 4 shows the life cycle of a data request managed by UGate Manager. UGate Manager defines, reports, and monitors the status of each data request, such as the state of the request, the current owner of the state, the length of time the state is stagnant, etc.

The Solution

Together, UPlan and the UGate Data Warehouse system comprise UDOT’s open data portal services. UGate provides the data behind the dynamic maps and analytical tools found in UPlan, making it available to business, UDOT partners, and the public at large. In 2013, UGate and UPlan won a NASCIO 2013 Open Government Initiatives award.

Why Gistic?

The Gistic Team is a renowned LRS domain expert, coupled with strong application development capabilities and extensive GIS expertise.

Technologies

The following technologies were utilized in the UGate/UPlan project:

+ Oracle database with SDO and ArcSDE to handle spatial data

+ Oracle SQL, Oracle Apex and JavaScript as the development tools

+ ArcGIS for Desktop, for most map making and GIS data editing

+ ArcGIS Online, on which UPlan is hosted

+ GeoServer for publishing WFS-T services for UGate

+ ArcGIS Server and GeoServer for map services

+ FME for spatial data manipulation