In the process, which is ongoing, officials in Marshfield, a city of about 20,000 in central Wisconsin, and Memphis, a city of more than 653,000 in southwest Tennessee, are redefining and streamlining their respective workflows.
Both cities had existing contracts with Redlands, Calif.-based Esri, which offers a variety of mapping technologies, and both were able to work with the company to re-examine and reshape their tools to better fit their needs.
In Memphis, GIS Program Manager Della Adams said Lead Application Developer Vijay Ganti worked on and off for about two years to create a solution that, as far as she’s aware, has never existed anywhere.
Creating a handshake between ArcGIS and Oracle
The city already utilized Esri’s ArcGIS technology, which lets cities make use and share online maps. And it has used Oracle Customer Relation Management (CRM) software for about 13 years.But despite both systems working excellently in their own right, they never “talked,” in computer-ese.
Adams praised Oracle’s robust architecture but said certain functions — like compiling a day’s workload and linking service request tickets to assets and infrastructure — were so difficult that staffers either didn’t do them or resorted to paper-based record-keeping instead.
That’s why, in about 2013, she decided Memphis needed to tighten its embrace of ArcGIS, starting with the drain maintenance department, a division of public works.
Memphis had already captured a variety of stormwater infrastructure data, from drain pipes to manholes — and had even built a mobile app for crew members that kept up the network.
GIS Analyst and Developer Jeff Jackson mapped the drain maintenance division’s entire business process and figured out its major pain points, then studied ArcGIS to figure out how to link its infrastructure to service tickets in Oracle.
The city started a pilot in late 2013 to track drain maintenance tickets, and once its process was well underway, created similar architecture for the city’s sewer and environmental maintenance departments, and for street maintenance, which handles everything from potholes to curb cuts.
Ganti worked with developers at the city to do an extract-transform-load (ETL) process — “basically, a data dump twice a day,” Adams said. But Memphis still couldn’t track services in real time.
Then, in late 2015, Ganti achieved the previously impossible: Using Web Services, he devised a way to create a handshake between ArcGIS and Oracle.
“The reason this is so phenomenal is that no one had done it ever,” Adams said. “The reason I know about it is I’ve been after a technology that allows us to talk to Oracle in real time the entire time, all 11 years I’ve been here.”
The city had been considering looking for an enterprise-level work order asset management system but didn’t have the funds it needed. Adams proposed creating a series of solutions in-house centered on Ganti’s handshake tool, called Live Link, and the city's CIO agreed — though even to Adams it seemed “a huge, huge undertaking.”
Memphis hired two additional analysts, its only real expenditure, and set out to try.
Developers worked with the engineering department to track traffic signs, so crews changing them out would know exactly which had been changed and when.
It worked with public works to circle in its bulk trash pick-up service requests, then included its recycling and regular trash carts as well as its entire inventory system; and created a solution for code enforcement that allowed inspectors to generate citations in the field, a first.
Exact numbers aren’t available but Adams said by remaining in the field, code enforcement inspectors who once needed hours can now create citations in minutes. Drain maintenance service request tickets, which once took about three weeks to close, now take around two days.
An enterprise-level work order asset management system would likely have cost around $5 million to $6 million. In contrast, Adams estimated the city has spent more than $200,000 on salaries for the analysts it hired.
Later this year, likely in June, her 12-person team will go live with a tracking system for City Hall’s own facility management crew and department, and during the next fiscal year it plans expanding its services to eight other departments and divisions.
And Adams told Government Technology that the city just learned it will receive a Special Achievement in GIS award from Esri at its annual conference in July.
Crowdsourced Reporter
Marshfield is less than one-eighth the size of Memphis, but its issues were similar. The city has a lengthy history with Esri through a $25,000 annual enterprise licensing agreement; and virtually every department uses GIS, according to Dave Buehler, Marshfield’s GIS coordinator.But before the city launched its online GeoReporting System through its website on March 14, constituent issues arrived the old-fashioned ways: by telephone or from visitors to City Hall.
“It was kind of a hodgepodge of how they got dealt with. Sometimes emails got lost or forgotten about,” Buehler said.
Internal monitoring was similarly analog, he said, with departments effectively siloing to track their own service requests.
That’s why, more than a year ago, officials asked Buehler and his group to create a system to link the city’s infrastructure and investment and ensure reports go to the right place.
Marshfield wanted analytics, Buehler told Government Technology, but it also wanted to see which departments “are getting the hits,” and how, whether by email, telephone call or alder-person.
“We’re trying to capture that younger audience of reporting, rather than calls and things like that,” he said.
Buehler and other staffers started to work, he said, with Esri’s Crowdsource Reporter application — and with a developer from the company who came in to help them get up to speed. The city handles calculations and notifications through its GeoEvent Processor, and on the back-end has a dashboard it uses for managerial purposes.
Like Memphis, Marshfield automates workloads, which greatly reduces steps for residents, who only have to fill in four fields before submitting a service request.
Once designed, the initiative debuted in data last May, and stayed there for about 10 months while the city worked with Esri to fine-tune its functionality. The city’s only additional cost was $3,000 for the GeoEvents Processor.
Only three weeks have passed since the launch, but Buehler said the city has seen a spike in reporting — though its workload hasn’t changed. The city has logged 126 reports since the beginning of the year, Buehler said, and he estimates around 24 percent, or around 30 have come in during the past two weeks.
A key way Marshfield is gauging its effectiveness is through transfers: service requests being moved from one department to another — but finding the correct place to be resolved, which might not have happened before.
One example was an outdoor burning incident reported by an alderman. It went to the city’s zoning department — but under the new system was sent quickly to the fire department which resolved it the same day.
The issue, Buehler said, was one that’s quintessentially Vermont: a maple syrup distillery on private property. This time of year, that's a not-uncommon occurrence.
