The Utah Department of Technology Services (DTS) and the Utah Department of Transportation (UDOT)
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®.
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.
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.
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.
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.
The Gistic Team is a renowned LRS domain expert, coupled with strong application development capabilities and extensive GIS expertise.
The following technologies were utilized in the UGate/UPlan project:
+ Oracle database with SDO and ArcSDE to handle spatial data
+ 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