Ann Arbor police service calls for Jan. 3, 2010. This map was built by The Chronicle in about 15 minutes using data from the city's online catalog. (Image links to fully interactive map hosted at http://www.batchgeocode.com)

At the same meeting, the budget committee also continued its discussion about the content of the monthly financial reports that the city charter requires the city administrator to provide to the council.

What ties these issues together is the idea that there’s information the city will be routinely pushing out, without anyone needing to make a special request for it.

In the case of the data catalog, it appears at first glance that the project is a kind of bonus for the citizens of Ann Arbor. That is, it could be thought of as something the city is not required by law to do, but which it’s doing anyway in the interest of transparent government.

That’s different from the monthly financial statement, which the charter explicitly requires. That issue came to the surface during the budget committee meeting, during a verbal exchange between Christopher Taylor (Ward 3) and the city’s chief financial officer, Tom Crawford. The exchange found Taylor appealing to an English word only rarely deployed as a verb: “I guess I’d stickle.”

On Stickling

CFO Tom Crawford is required by the city charter to provide to the council a monthly statement via the city administrator, Roger Fraser:

SECTION 5.6. The Controller shall be the chief accounting officer of the City. The Controller shall:

(6) Submit to the Council, through the City Administrator, by the tenth working day of each month, a statement showing the balances at the close of the preceding month, in all funds and budget items, the amount of the City’s known liabilities and budget items to which the same are to be charged, and all other information necessary to show the City’s financial condition;

After a period during which the city council apparently did not expect such a monthly report, the council’s budget committee has now begun to talk about what information should be contained in the monthly financial statement.

At the Feb. 16 budget committee meeting, Crawford said that he’d provided to councilmembers in that month’s statement the information that he interpreted the charter to require. It included encumbrances – funds that are committed for a specific use. But Crawford suggested not including encumbrances in future statements. In context it was clear that the concern was based on clean formatting and readability of the document, not a desire to shield that information from the public.

Christopher Taylor (Ward 3), however, wanted to know on what basis that information could be stricken from the monthly statement. Hadn’t Crawford just told the committee that he interpreted the charter to require its inclusion? Crawford suggested that the content of the report could reflect a consensus from council about what they wanted to see in the report: “It depends on how much of a stickler you’ll be.”

And at that, Taylor allowed: “I guess I’d stickle.” And Taylor is right to stickle. As I wrote in a previous  column on the city charter:

That is, the city council cannot waive a charter requirement. And any citizen has legal standing to file suit on a charter violation to demand relief.

The ensuing discussion among the budget committee touched on the idea that the monthly statements would be made available online. Perhaps formatting and readability issues could be addressed through linking to a separate document – as opposed to embedding the information on encumbrances directly in a document.

That seems like a practical approach to take: Focus on providing the information underpinning the statement, not so much on the formatting of the statement document. Can the statement “show” the encumbrances, if there is only a link from the statement to a separate document containing the encumbrances? Probably so – that’s the kind of issue the council can work out with city staff.

But it’s not up to city staff and the council to agree to strike the encumbrances from the set of information that’s required by the charter to be provided.

Data Catalog: Records and the City Charter

At the Feb. 16 meeting, the budget committee agreed that once they are satisfied with the format of the monthly statement, the statement will be conveyed directly to councilmembers, without intermediation by the committee. Conveying it to the council – and the public – by posting it online would be a good approach.

It’s a good approach because it would easily satisfy a charter requirement that all city records, like the monthly financial statement, be public:

City Records to be Public
SECTION 18.2. All records of the City shall be public, shall be kept in City offices except when required for official reasons or for purposes of safekeeping to be elsewhere, and shall be available for inspection at all reasonable times. No person shall dispose of, mutilate, or destroy any record of the City, except as provided by law, and any person who shall do so contrary to law shall be guilty of a violation of this charter.

Of course, the city could also be compelled to produce those monthly financial statements under the Freedom of Information Act (FOIA) to anyone who asks.

By posting the monthly financial statements online, the city reduces the resources that would otherwise be required to respond to requests under the FOIA, or the city charter, that those records be produced.

The same principle applies to the information the city is providing as part of its new data catalog.

Last month, the city council’s budget committee had been told the data catalog would be coming online soon.  [Chronicle coverage: "Ann Arbor's Budget Data to Go Online"] So last week’s implementation of this first draft of the data catalog – which contains much more than just financial transactional data – was expected and welcome news. In addition to financial data, for example, the catalog also contains mapping data and public safety information.

Washtenaw County government is moving toward a similar goal. Commissioners discussed their “transparency of government initiative” at a Feb. 18 working session.

The city’s data catalog is a tremendous stride forward for transparency of our local government – but it should not be analyzed as an “extra” or a “bonus” for citizens. The FOIA requires that city documents – with few exceptions – be produced on request. And independently of the FOIA, the city charter requires that city records be available for inspection at all reasonable times.

By providing availability 24/7 on the Internet, the city would certainly cover “all reasonable times.”

The data catalog, then, should be seen as a way for the city to use technology efficiently to respond globally to potential requests for access to information under the FOIA or the city charter – which citizens could already legally demand on an individual basis.

As the city looks to add to the data catalog, then, one guiding question should be: Is this information record required to be public under the charter or the FOIA? If the answer is yes, then the information is a candidate for inclusion in the data catalog. Otherwise put, everything is fair game for inclusion in the data catalog.

Is there any reason why some city records shouldn’t be prioritized for inclusion in the data catalog? Absolutely. Those data sets that would require intensive ongoing staff resources for production of the data should be a lower priority.

As head of the city’s information technology, Dan Rainey, told the budget committee on Feb. 16, the data sets that are included in this initial phase are those that can be produced in automated fashion. They’ll be on a production schedule, with no human intervention required beyond the initial setup.

Data versus Records/Reports

What’s included in the data catalog are data sets, not reports/records. The idea is that by providing information in a relatively raw state, individuals – citizens or councilmembers – who are interested in building their own reports can do so fairly easily, without introducing an additional burden to city staff.

Kevin Eyer, senior applications specialists in the IT department with the city of Ann Arbor, gives the city council budget committee a quick tour through the data catalog. (Photo by the writer.)

For example, the city’s data catalog includes a comma-delimited file containing police service calls. There are data fields for date, location, type of call, and the street address.

Why don’t they provide that data presented as a map? It’s partly because you can make your own map out of the data, if you need a map. Besides, whatever kind of map the city might create, there will always be someone who’d prefer a different kind of map – maybe someone  wants to see the police calls for a specific day, like Jan. 3, 2010.

That’s what I wanted – for demonstration purposes – so I took the city’s data, and headed over to http://www.batchgeocode.com and within about 15 minutes generated the map shown at the top of this column. A few tips on preparing the police call data for mapping at batchgeocode.com:

• Add columns for city and state and fill each cell with “Ann Arbor” and “MI,” respectively
• In the street address field, replace the word “block” with a blank – the city provides the address information by block, not the specific address.
• In the street address field, insert spaces around the “&” for addresses specified by intersection.
• The batchgeocode.com process takes whatever column you name “group” and assigns the colors of the map push pins based on that. Clicking on the pushpins in the map legend causes just those color push pins to appear.
• Read through the documentation at batchgeocode.com
• If you’ve never done this before, it’ll take longer than 15 minutes.

It’s not that the city doesn’t like maps, though. Among the data sets in the catalog are various KML files that open in Google Earth. For example, there’s a perfectly drawn map of Ann Arbor’s city boundaries, which can be used as a layer in other maps that people might be interested in creating.

The demonstration of the data catalog at the budget committee meeting prompted an exclamation from Marcia Higgins (Ward 4): “It seems really user friendly!”

It is.

But the city will be looking for feedback on the data catalog – the email address DataCatalog@A2gov.org is one way to do that. And as Chronicle readers provide that feedback, I’d encourage you to bear in mind the difference between data and reports – as well as the idea that the data catalog provides information that could legally already be demanded from the city.

Dave Askins is editor of the Ann Arbor Chronicle.

The Chronicle was alerted to a revision to the city website’s crime data archive via the automatic email system we’ve signed up for. Anyone can subscribe to the system, and it’s customizable by topic. Readers who don’t wish to receive notifications of crime updates can simply opt out of that topic at sign up. But The Chronicle subscribes to the complete package of notifications. So we received the following note in the email yesterday:

-
From: City of Ann Arbor, MI
To: dave.askins@annarborchronicle.com
Date: Tue, Dec 23, 2008 at 3:21 PM
Subject: Ann Arbor Police Crime Statistics – Dec. 14-20, 2008

You are subscribed to Neighborhood Watch for City of Ann Arbor, MI. This information has recently been updated, and is now available.

Update your subscriptions, modify your password or e-mail address, or stop subscriptions at any time on your Subscriber Preferences Page. You will need to use your e-mail address to log in. If you have questions or problems with the subscription service, please contact support@govdelivery.com.

This service is provided to you at no charge by City of Ann Arbor, MI.

GovDelivery, Inc. sending on behalf of City of Ann Arbor, MI · 100 N. Fifth Avenue · Ann Arbor MI 48104 · 734-994-2700

The link embedded in the email leads to the city of Ann Arbor’s neighborhood watch page, which includes a PDF archive of crime data that is divided into chunks of about two weeks per PDF. The notification yesterday was the second one we’d received. Two weeks ago, we received a similar notice. To date, we hadn’t allocated time and resources at The Chronicle for opening up PDFs with crime – partly because many of the PDFs generated by the city contain scanned images of pages, as opposed to the “native text” that allows for easy use of that data. We imagined that the crime data PDFs probably fit the scanned image pattern.

However, Ed Vielmetti forwarded to us by email the contents of the PDF, which he’d copied-and-pasted from the file. From that we concluded that the PDFs contained text, not just images of pages, and from there, we were able to assemble a single file of text containing the data, delimit it, geocode it, and map it. The key concept for mapping – a KML file – came from Vielmetti and a second-degree connection to him, Andrew Turner.

If time and resources permit, in 2009 we may provide maps of crime data from the city’s website as they become available. We’d first want to develop an efficient work process for generating them, as well as discuss with the police department the categories we’ve lumped together based on a lay person’s understanding (e.g., burglary and larceny from a vehicle are given the same color marker in the map we’ve generated above).

Technical Details

Why don’t we just use Google’s MyMaps instead of GPSVisualizer? There’s an apparent limit of 200 placemarks for Google’s MyMaps, which the year’s data easily exceeded. The 2009 strategy of creating one map for each two-week span covered by the city’s PDFs, plus the ability to assign log-in privileges to others for help in doing the work, points towards adoption of Google’s MyMaps.

Where do you get the longitude and latitude for the addresses? We submitted them to a for-now free service: BatchGeoCode. GPSVisualizer provides batch geocoding as well, but not at the level of precision required for this work. For presentation of the data, however, GPSVisualizer provides a whole range of options, including the ability to present quantified data – like campaign donations displayed on a map by donor address with a marker scaled relative to the size of the donation.

How do you get from the set of geocoded crime data to a KML file? In our case, we did a mail merge using MS Word and an MS Excel table as a data source. We’ll pause for a moment while our geek readers cough up their collective lungs laughing. It does work, although it requires using text like “LEFTANGLE” and “RIGHTANGLE” for the ubiquitous left and right angled brackets of KML, so that MS Word doesn’t try to interpret the document as something it cannot read and then crash. See “develop efficient work process” above.

How do you delimit the text, once you’ve got it out of the PDF? We used a text editor called TextWrangler, which supports grep searches, making it fairly easy to deal with records with no spaces between address numbers and street names.

Files:

MS Excel Spreadsheet of 2008 Crime Data [not complete or official]

KML file with data from Excel Spreadsheet of 2008 Crime Data [not complete or official]