Providing More Value
City turns necessity to its advantage
By Bob Blackwelder, City of Chula Vista, California
This article as a PDF.
A Southern California city of slightly more than 250,000 is using the latest GIS technology to help it implement a management philosophy that eliminates waste and tightens processes to deliver better service more efficiently.
Photo courtesy of Mark eggrole/Flickr (cc)
Like many local governments around the country, the City of Chula Vista has experienced a reduction in city staffing, resources, and funding during the recent economic downturn. To help counteract this loss of manpower and resources, in 2012 the city adopted the Lean/Continuous Improvement (CI) program, which was derived from the Toyota Production System as a way to improve overall productivity and efficiency.
A number of city employees were trained in the Lean/CI concepts that focused on reviewing current work processes and eliminating wasteful steps and unnecessary tasks that do not provide value to the organization. With the reduction in staffing, all employees have been asked to take a closer look at how they do things and find more creative ways to do business to maintain quality service to the citizens of Chula Vista. "Doing more with less" is the new motto throughout City Hall.
Lean and GIS
At the same time, GIS has continued evolving. Recent changes in GIS data types and the database technologies underlying them must be learned and implemented. The Lean training encouraged GIS staff to focus on leveraging the latest technology whenever possible to create more dynamic, self-sustaining GIS layers and applications while eliminating additional maintenance work and duplicate data entry.
Chula Vista's GIS section recently upgraded its citywide GIS Viewer (commonly referred to as CVMapper). Although the older version was widely used by city staff in almost every department, the viewer used ArcIMS technology. It could not utilize published map services and other technology advancements available with ArcGIS for Server.
An updated version of CVMapper, developed by WebGIS-Solutions, utilizes ArcGIS API for Silverlight and provides a number of enhancements to the mapping system including custom map themes and the ability to add web-based map services and layers on the fly.
In addition to the out-of-the-box tools, WebGIS-Solutions also developed a number of modules to extend the viewer's capabilities. Chula Vista's Public Works staff immediately expressed interest in the work order module, which can display current fieldwork performed on city-maintained assets. This add-on module, including an intuitive, dashboard-like interface, has tools that display fieldwork based on asset type, status of work, and date the work was performed.
Seizing an Opportunity
The work order module would be easy to add to the CVMapper interface, but it required that all work orders be mapped as a single point layer before being published as a map service. Although Public Works staff have streamlined how work orders are attached to the city GIS, at the time there was a lack of consistency in how work was entered into the city's Work Management System (LuCity). In certain cases, Public Works crews entered work locations into LuCity as x,y coordinates, while other crews were attaching work directly to GIS features such as street segments, storm mains, and sewer mains. The GIS team had to find a process for aggregating all work orders into a single point feature class, regardless of asset type or the method used to enter the work in LuCity.
Chula Vista, which maintains its GIS data in an enterprise geodatabase, had recently upgraded to Microsoft SQL Server version 2008/R2. With the upgrade, the GIS team also migrated to the new SQL Server geometry spatial type. Staff viewed this as a great opportunity to explore query layers and leverage the new spatial functions that are available in SQL Server.
Implementing the Latest Technology
The first step in building the query layer needed for the work order module was to compile all the fieldwork to be mapped. With the help of Chula Vista's LuCity administrator, Claudia Block, a SQL Server database view was created containing all fieldwork for 2013. This view contained not only all work associated with each asset type (e.g., storm main, wastewater main, pump stations) but also the date and type of work performed, field crew that was assigned, and location of work performed (provided as a GIS asset ID or an x,y location).
After identifying the information needed from LuCity and compiling this data into a SQL Server view, the next step involved spatially enabling this view to create a unique point feature for each work order. With a basic understanding of Structured Query Language (SQL), GIS staff members were able use Esri's online samples to write a SQL statement creating point features showing all fieldwork with an associated x,y location.
Example of a SQL statement that creates point features for all fieldwork associated with an x,y location.
The team set out to create point features that showed the work orders associated with linear assets like street segments or sewer mains. In these cases, only the associated GIS Asset ID was stored in the work management system, so staff members had to somehow create point features to represent the approximate midpoint of these linear assets.
To resolve this issue, staff downloaded and installed SQLSpatialTools, a SQL Server spatial toolset available from CodePlex, Microsoft's open-source project hosting website. Included in this toolset is a spatial function called LocateAlongGeom(shape, distance) that will generate a point feature at a certain distance along a line segment. This allowed staff to merge the work order data with the appropriate GIS layer and generate points for all fieldwork attached to linear assets such as sewer and storm mains.
The LocateAlongGeom (shape, distance) function was used to generate a point feature at a certain distance along a line segment.
The SQL function UNION ALL was then used to group the different asset types into a single SQL statement before copying the SQL query to the Add Query Layer tool in ArcMap and publishing the point layer as a map service.
The UNION ALL function was used to group different asset types into a single SQL statement.
Doing More with Less
Once the new GIS work order layer was published and added to CVMapper, it was a great information resource for Public Works managers and engineers. The module provides unique symbols for different asset types. It allows users to toggle between open and closed work orders so they can quickly assess and investigate the status of work orders performed in the field and identify possible patterns or trends. Being able to visualize this data can allow them to quickly see assets that need further attention.
"The work order module has allowed our crews to search for property information and access the mapping features from the field to better prioritize which work orders to respond to, allowing us to be more efficient with our limited resources," explained Michael Lengyel, senior management analyst with the Chula Vista Public Works Department.
More Ways to Leverage Existing Data
Chula Vista's city staff, like many local governments, needs access to GIS layers that have authoritative attribute data stored in nonspatial databases such as work management systems, business license databases, or land management/permitting systems. City staff is now looking at GIS layers like day-care facilities and historic homes that can be can leveraged using spatial functions and query layers.
For example, when a new day-care facility is approved by planning staff, the data is entered into the city's land management system (Accela). A hard-copy approval sheet is sent to GIS staff who reenter the same attribute information into the GIS. The GIS team can take advantage of query layers and database views to merge spatial (GIS) data and nonspatial data and provide city staff with more accurate, dynamic GIS layers. In the future, when a new day-care facility is approved and entered into Accela, the spatial location and attribute information of the new day-care facility will automatically be added to CVMapper without additional work by GIS staff. As a result, end users will have more confidence in CVMapper, because they will know the underlying data is coming directly from the system or database that maintains or owns that data.
This solution also fits into Chula Vista's long-term goal of eliminating data silos and duplicate data entry into key city databases. Data will be entered one time and maintained in the most appropriate system.
The idea behind Lean is to eliminate waste and ensure there is value in everything the city does and the services it provides including GIS. During the next few months, the GIS team plans to meet with staff members from every city department to review each of their GIS layers and attribute information to determine where they can leverage the latest technology, such as query layers, to provide more dynamic data; minimize or eliminate duplicate data entry; and, in the end, do more with less.
For more information, contact
Bob Blackwelder, GIS Manager
City of Chula Vista
t: 619 585 5765
About the Author
Bob Blackwelder is the GIS manager for the City of Chula Vista and past chairperson of the San Diego Regional GIS Council. He has a bachelor's degree in city and regional planning from the University of Louisiana-Lafayette.