eWEEK content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
You dont have to be Sherlock Holmes to deduce that the most important tool in fighting crime is information. Still, there was a time at the Metropolitan Police Department in Washington that when officers and detectives needed information, the immediate bad guys were the database administrators, who acted as information gatekeepers for more than 20 databases on information ranging from crime statistics to detectives notes.
Officers would routinely submit a question and then wait several days for the administrators to retrieve the information from the correct database and distribute the results on a printout.
Then came Sept. 11, 2001, and it became clear to department officials that they needed a more efficient system, one that provided line personnel with direct access to important information.
Walter Collier, deputy CIO at the MPD, said this initiative came from the top down right after the 9/11 terrorist attacks.
“One of the things the chief wanted was to provide some level of crime analysis at the officer level. [He wanted] to deliver this information—traditionally available only to people who could hack databases—and make it available for people to make crime inquiries. [The chief wanted] regular line personnel to have [direct] access to data and to have the ability to produce maps and get photos [from a central data source],” Collier said.
The system that developed as a result of this request became known internally as Columbo (named for the TV detective of the same name). Collier said that today Columbo provides a Web-based front end to more than 20 databases, which were previously inaccessible to most officers and detectives.
In true Sherlock Holmes fashion, it was a simple act of observation that put the department on the right track to solving this dilemma.
In October 2001, just after the chief articulated this vision, Michael Schader, founding partner and CEO at Washington consultants Yellow House Associates LLC, was working with MPD police, and he said he immediately recognized the difficulty in retrieving information.
“MPD brought Yellow House Associates in soon after 9/11 to maintain and improve the detectives case management system. Once we stabilized it, we prototyped a user-friendly Web application that rode on top of [a] case database, intending to propose it as a replacement front end,” Schader said.
When Yellow House proposed building this new system, it found a receptive audience in DC Metro Police administrators, not only for the proposed system but also for a more in-depth project that would tie together the departments databases for homeland security purposes.
“When we demonstrated the prototype, MPD officials saw the potential to solve a much broader and more pressing issue: the need to provide a single point of access to the broad array of data sources throughout the department in support of the homeland security mission. Using a Department of Homeland Security grant, MPD was able to keep Yellow House on staff, and we kept developing Columbo,” Schader said.
Schader and his Yellow House colleagues were comfortable using open-source tools to design custom solutions, and they proposed building a front end to access case data, rather than building a new system.
“Our approach was, Rather than looking at a vast project to replace systems that were already working, lets look at how to better exploit data the department already has, and lets do it right now, not three years from now,” Schader said. “Our main focus is assembling components and writing custom software around [these components]. The vast majority of [the system] used open-source components, and this has turned [out] to be most effective for our purposes.”
Schader said Columbo was built on open-source technology using a Java front end, accessible through the departments intranet, providing easy access to the information stored on the back end. Users of the system dont care that its built on open-source components so long as it is easy to use and it works, he said.
“Before we came on board, MPD detectives had to use a mainframe application to look up arrests, another tool to check mug shots and a third to search for suspects,” Schader said. “They had to call the U.S. Attorneys Office [for the District of Columbia] to get gang intelligence, visit the Firearms Examination Section to look at gun-recovery data and search through paper case files to look for names. When they needed a map with all this information plotted, they had to request a hard copy from [the] Crime Analysis [unit]. Since our open-source implementation of Columbo went operational, they can do all these things at their desktop in seconds.”
Heres Columbo
.”>
Collier said that the cost and flexibility (not being tied to a single vendors solution) were big factors for MPD for moving with open-source components. Of course, for the decision makers among department brass, the fact that they could use open-source components without taking a big budget hit was, by far, the overriding factor for them.
“Any time you can spend less money, it would be attractive to people in charge,” Collier said. “I think because its open source, and you are not necessarily committed to an Oracle [Corp.] or a [Microsoft Corp.] SQL Server, you have some flexibility, and the fact it costs you less is definitely a plus.”
When Schader presented the initial idea of building the Columbo system, Collier had been working on a prototype of a mapping system (a system that he said was abandoned when they began developing Columbo).
About this time, the command staff was asked to present daily briefings on crime statistics in their area in a similar fashion to the system implemented in New York City following 9/11. Without a simple data retrieval system, it would have been very difficult to pull this information together each day.
Schader said he began building Columbo by finding a way to search detectives notes, which were in a database but had never been indexed to make them searchable. After conducting a search and examination of open-source indexing tools, Schader and his associates selected The Apache Software Foundations Apache Lucene.
“Indexing was one of our best and first open-source component successes,” Schader said. “Very early on in the development of Columbo, it was clear that one of the big wins would be to provide full-text searching against detective case narratives, [which are] just a trove of information. The department had them all stored electronically, but there was no way to [search] across them and find case data you need. So we looked around and found an open-source package called [Apache] Lucene, a free text-indexing component.”
After Schaders team got the initial detective notes project up and running in early 2002, he said that within six months, they added nine other systems. They have built on that to bring in more of the departments databases and have added mapping technology to make it easy for officers and detectives to visualize the data on a map of the city. They can mix and match information types such as mug shots, detectives notes and crime statistics and overlay this information on a map.
According to Schader, Columbo data gets regular updates. For example, dispatch data from the 911 system is updated every 15 minutes, and the case management system is updated every 24 hours.
“Information is readily available. Rather than having to go through and pull together different bits and pieces of information, they can actually produce information during the briefing and respond to changes in information,” Collier said. It has not yet gotten to the point that officers can access the system on their patrol car laptops, he said, but the department expects to bring that functionality when they improve their wireless bandwidth.
“We started looking at making [wireless access] a reality. Weve tested it … [and] we are preparing to move to a higher bandwidth like EvDO [Evolution Data Optimized] or 1X, and were looking to make Columbo data available in the squad car,” Collier said.
Ron Miller is a freelance writer in Amherst, Mass. You can contact him at ronsmiller@ronsmiller.com.
Check out eWEEK.coms for the latest database news, reviews and analysis.