This, to me, is so neat.

Hundreds of thousands of records a month. I ended up importing them into Excel(1) and then using... what was that called? An MS/Windows library that came with IE 5 and/or a few other things, that provided regex support (with a few quirks) that was accessible via VBA.

The point was, I could programmatically mine it -- including regex pattern matching and replacement of and within cell contents -- while also having a flexible UI within which to find and handle one-off cases. When the one-off's demonstrated a repeating pattern, I could quickly iterate to add that to the programmatic mining logic.

This included adding color cueing for items of particular interest, manual follow-up. Excel's sorting capabilities to bring potentially related instances into visually displayed groups. And the like.

It ended up working quite well. I might have preferred something else to VBA, and I did use Perl and other stuff, elsewhere (something that also gave me both power and the flexibility to rapidly iterate).

But the point is, with such data, I found it very useful to combine regex and rapid programmatic manipulation, together with a good visual interface (including visual cues, the ability to comment upon instances -- Excel cell-level comments -- etc.) and manual manipulation.

As a final aside, the extensive set of Excel keyboard shortcuts greatly aided in rapidly and effectively navigating and massaging the imported data.

--

1. This was back when Excel had... I think it was a 64K (or a bit less) limit on the number of rows in a sheet.

P.S. I tended to retain the originally imported data in its columns, and to produce my mining of it in other columns. That way, I could always and immediately see what I started with, for any particular record. (And, if things visually started to be "too many columns", well, Excel lets you hide a range of columns from the view. As one example of how its features really helped, on the visual front while doing this work.)

I still had to learn and allow for some quirks Excel exhibited with respect to importing text data. That included making sure the cells/columns being imported into carried the correct/needed formatting designation before importing into them (usually, "Text").

That's some small-government activism I can get behind!

The likely reason there are many tickets there is that there are many bars there, and great crowds of people who have had a bit too much to drink. There are also great crowds of cops there every weekend.

Without looking at the data, I'd expect that many of the tickets are getting written in the middle of the night, when people are too inebriated, or too distracted, to read signs carefully.

I guess I just learned I half expected each person who wanted FOIA data to have to request it themselves, for their own personal use.

In this case I can see reusing this for interesting reasons (the plates in the .txt.gz have not been removed), so...

Under Florida public record law, source code produced by state employees is, in very narrow circumstances, a non-exempt public record (the code can't process sensitive data, etc.). I'm considering a future endeavor where I periodically request the code to such projects until the I.T. department decides it's worth the effort to open source it.

I like to think this is a step towards consolidating publicly funded code and reducing duplicate effort. Ahh, imagine making a pull request to your city's website! But I'm getting ahead of myself...

"This'll be my first blog post on the internet, ever. Hopefully it's interesting and accurate. Please point out any mistakes if you see any!"

KEEP IT UP MATT! and data munging, not sure if it's a word, but it sounds nice :D

If it is annually, they got 17m tickets over 7 years so for 10 years, assuming they issue just over 19m tickets, that means each parking ticket needs to be at least $10 to cover the cost, even at $100 per ticket, IBM is banking on 10% share? That seems excessive to me but I never worked in government so could someone enlighten me on this?

By any chance there's a conflict of interest for government to be willing to make improvement and cut down parking tickets or any other similar source of income? Or maybe that's what public audit is for?

Did you give more thought into the address cleaning bit? Or does anyone have an idea how to go about transforming mangled addresses into coordinates?

I have a problem that's been bothering me for months, similar to what you have here: people from an emergency service call-center are inputting the addresses of the emergencies. For emergencies that happen on the public domain, there is often not a specific address, but rather names of landmarks. Something like "Street StreetName / Opposite Train Station Y", which can be written like "st stName / opp tr st y" or some other infinite variations.

I don't have any after-data to corroborate, but I do have previous instances where the operator inputted the same address better. If I can extract the correct landmarks, I think I can do a Google Places search for them, with a cleaned query, like "Store Amazon, Best Street, Ohio" to get coordinates that can fall into an acceptable area.

PS: in the example you gave with Lake Shore Drive, I think you could easily correct the names with an algorithm based on the Levenshtein distance

https://easy-a.net/

I wrote a blog post about it, because it requires a ton of work to get FOIA requested data - this I'm assuming was done in the same painstaking way:

https://austingwalters.com/foia-requesting-100-universities/

I give this props. I'm sure it required a ton of work

The footer indicates that the web page was generated using bashblog [1] – looks like it might be worth checking out.

[1] https://github.com/cfenollosa/bashblog

His hobbies make me feel bad about my own...

[Edit: on the other hand, if the ticket is unfair (eg. confusing signage as in this example), then you have a valid point; I just wanted to point out the other side of the coin]

(random example: https://www.syracuse.com/news/index.ssf/2015/01/private_comp... )

I thought so to until recently and was honestly kind of surprised they actually gave it to me. They rejected giving license plate info at first, but they've given it out in other, similar, FOIA requests.

Specifically in FOIA's statute, it says:

    (c-5) "Private information" means unique identifiers, including a person's social security number, driver's license number, employee identification number, biometric identifiers, personal financial information, passwords or other access codes, medical records, home or personal telephone numbers, and personal email addresses. Private information also includes home address and personal license plates, *except as otherwise provided by law or when compiled without possibility of attribution to any person.*

Interesting note about getting data like this - Illinois FOIA allows a requester to submit a SQL as part of their request.. so long as they know the tables and columns within the database ;)

You're correct that a simple DB with some forms would be cheap.

But integration tends to be crazy expense. For this sort of system, other things that also need to be covered: 1. Billing integration. Including changes to billing codes, bill (fine) printing, testing. 2. Audit integration. Because whenever money is handled, audit follows. 3. Customer support integration. Including UI for customer service, training, testing. This is often a very complex item because customer service already have a zillion systems they have to use and their training requirements are ongoing and expensive, so they want you to integrate with their existing systems instead of giving them a brand new thing, and integrate with their existing training processes, etc etc. 4. Integrate with all those hand-held readers. inc vendor compliance, testing etc. 5. Contract management. You have a contract with the government and they'd like to know that you did what you claim you did. So there's teams of people to deal with on an ongoing basis. 6. Project management. There's more than one person working on this, and a lot of complex integration requiring changes in other systems => extensive project management. 7. Ongoing changes to requirements, often conflicting. All the integration points above are moving targets, so expect that they'll have to be re-done a few times both before and after launch. 8. Arse covering. You now have a large contract with the US Government. You will sued and they will get sued (typically by whomever didn't win the contract). Vast amounts of documentation covering _everything_, including documenting the process by which documents are written => tech writers galore, plus lawyers plus lawyers.

Honestly, this is barely scratching the surface. I haven't even touched the (expensive) work before the contract is even signed.

$190M doesn't go very far!

Near where I work in Bellevue WA, they recently restriped the road to have a brightly painted bike lane, with double-white lines to make it abundantly clear that you were not supposed to drive in it. Bright red "no stopping" signs were placed on the curb. People still parked right in the bike lane.

It wasn't until they added a concrete barrier that the lane cleared up enough that bikes could use it. And of course, right where the barrier ends, people start parking there instead. The West side seems to have less difficulty understanding this.

https://www.wbez.org/shows/wbez-news/where-chicago-tickets-f...

Though, during last mayoral election, some of the mayoral candidates wanted to use parking tickets as part of their campaign, and through some connections I found my way into Bob Fioretti's campaign manager's office to discuss parking tickets, alongside an ex-candidate, Amara Enyia's campaign manager. They were super, super interested - Fioretti's CM calling the work "fucking golden". But.. they both went silent after that, despite Fioretti started using parking tickets as a major part of his campaign. Go figure.

There's a lot more to that story - I'll end up write about sometime later :).

https://en.wikipedia.org/wiki/Windows_Script_Host

(Not all of my post came through)

Great example of value being in data. One that ordinary people can connect the dots on and encourage.

On a technical basis, it's trivial - you already have the data stream that's going to be sent to the printer, generating a PDF wasn't going to be an enormous roadblock (though it wouldn't have been completely trivial as the source data was PCL not PS - did you know that there was handling for that in Ghostscript, at least on the commercially-licensed side?). Encryption of PDFs also possible, either with separately-licensed open source tools or with some closed-source commercial alternatives. Even ignoring the possibility of email being intercepted in transit, encryption would have been a requirement due to the risk of someone walking up to an unattended desk and simply checking that attached PDF for someone's pay info.

The killer? The infrastructure required to assign and allow people to change their passwords including management, training, etc.. By the time you've built that, you're a chunk of the way to simply providing the payroll information within an online HR system instead.

Like the old trope about the first man on Mars being a technician for an unreliable rover, the bulk of the work and cost isn't always where you'd think it would be.

It's a C library with gigabytes of data, so it isn't light weight, but it attacks the problem aggressively.

My current stack is:

1. Send addresses to https://smartystreets.com/ - They gave me a year's worth of unlimited geocoding for free. They also tokenize the addresses, but I had about a 50% success rate with them.

2. Tokenization raw addresses with https://github.com/datamade/usaddress.

3. Use a normalized levenstein distance algo to get ratio of difference.

4. Compare all of the addresses' levenstein distances with each other.

5. Apply logistical regression/gradient ascent algo to tickets by chaining heavilytypo'd addresses to less-typo'd and eventually to a static list of verified-correct addresses.

It works surprisingly well, but there are still a lot of problems that can't easily be solved:

1. Street types (st/ave/blvd/etc) are missing. So, when two addresses have the same street name, it's difficult to pair the two. It's still possible with some probability stuffs and matching the ticketers' paths to the nearest street.

2. Addresses have a LOT of one-off situations. For example, there's a street name called "Avenue A". The street name here is "Avenue", and the street type (usually st/ave/etc) is "A".

3. Lots of four letter streets make levenstein distance very difficult.

Glad you enjoyed it!

I'm surprised he asked after license plates, though. I don't know if that is different in the USA, but in Europe that certainly wouldn't fly because of privacy. I wouldn't even have asked because I shouldn't want to have such data. Perhaps one could get an anonymized version to be able to correlate how often a certain plate got a ticket, but not which plate that was. Anyway, the general concept of a FOIA request is the same. (Edit: Oh, someone else remarked this as well: https://news.ycombinator.com/item?id=17754396)

I have a lot of experience in making public records requests and would be happy to help.

Not saying the signage was clear, but that is a very very weak excuse to not understand them.