Hacker News Reader

Comment by bpchaps | Hacker News Reader

Hey! Thanks for posting this, and the kind words.

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 ;)

View on HN

If that’s possible then the first two requests should be:

    SELECT *
    FROM information_schema.tables

And

    SELECT *
    FROM information_schema.columns

Not all SQL products keep their schema searchable, but good try. Your information request has been declined due to unreasonable effort required to answer.

I’m rusty, can you remind me of the SQL statement to run against the master tables to give me the schema? :-)

Not only are you a certified urban hero, you don't even need a costume!

Not only did you save drivers $$$thousands, but you freed up at least one of 'Chicago's Finest' to fight more serious crime.

So first you submit a FOIA request for the database schema...

Funny enough, I submitted one of those to all of the biggest cities in each state for a list of their databases and the columns/tables within each. Pretty much universal rejection.

The ones that didn't send me a rejection letter all told me that the cost would be somewhere between $400 and $2,000 to complete the work. While it's nice, and I've spent similar on requests before, it's not something I'm exactly interested in doing repeatedly, since this is all coming from my personal savings.

That said... I'm still fighting that fight in Chicago. Chicago used to post a "data dictionary" of 111 of their databases, which had a list of the tables and columns. Problem is... it isn't running anymore since it's no longer being funded by a $300k (!) grant. So, I'm working on requesting a database dump of that. Last request for it was rejected saying that Chicago's Department of Innovation and Technology (DoIT) doesn't mave any of those records. I'm thinking it's because I requested a "copy" of the database dump, rather than just explicitly asking for the data within it.

That opens up a pretty nifty door to work with for future requests ;).

Can we do crowdfunding to get that started again?

Yep - I have two ways open now: A donation to my non-profit, Free Our Info, NFP - which is the company I'm submitting requests for DBs' info through (and many other requests): https://www.paypal.com/cgi-bin/webscr?cmd=_s-xclick&hosted_b...

The other's through Muckrock, for specific requests: https://www.muckrock.com/project/tables-and-columns-from-gov... (I'll add more requests to this list tomorrow)

I'm reasonably sure that in UK FOIA requests have to be handled at cost? It's that true in Chicago.

Mind you the company responsible probably said "the database schema will cost $2k to put in a PDF".

It varies by state in the US (what doesn't?), but most states do have a clause in their FOIA law that says something along those lines as well. It may be completely ambiguous ("at cost", fees must be "reasonable", etc.) or it can be more specific ("may not exceed the hourly salary of the lowest paid employee who can do the work", etc.).

Of course the former is pretty worthless, especially if the state doesn't outline any formal appeals process - you'd have to actually sue them for the records, which is rarely going to end up being cheaper. It also doesn't mean that the actual cost couldn't end up being insane. If no one has access to perform that particular query or knows how to get that data it could involve requesting a custom report from whomever developed and/or maintains the system. No one is reasonably going to pay IBM's hourly rate to write a report for them...

And don't forget if their DBA team is all consultants, "at cost" for the state just included that DBA's time, putting together the scope of work for the client, writing the proposal, management approval, etc. Generating a PDF of all tables and all columns in a presentation-level format could definitely cost the state $2k.

I work for a company that has a lot of state consultants (>50% of the workforce) and our salaries are typically 2-2.5x our state counterparts, and most items of work that would take more than an hour or two will require at least one level of management approval on both sides.

Do you have any other chicago-related datasets available?

Now wouldn't it be funny if you got them to drop a table this way?

Wouldn't you assign readonly permissions to the account used to fulfill these requests?

Ideally, but until someone asks for it the first time, they probably don't even have an account for the purpose. So, an irritated DBA who has myriad other backlog tasks probably runs it on a copy if smart, and production if lazy or in a hurry. Would not surprise me in the slightest if an important table was dropped in this way.

Yes, but if they're not sanitizing inputs, they might not have set permissions wisely either.

This is one of my all time favorite HN posts, a really good deed done with Unix.

So inspiring!

Do you have any specific information about this or the relevant regulations?

Perhaps we need a meta-site with best practices and how best to attack open records requests in general.

You should check out muckrock.com!

It's rare to see someone go above and beyond a benevolent hobby to build a complete solution to a people's problem.

Actually Chicago has an extensive open data access platform. Although it's not SQL but it is pretty interesting.

https://data.cityofchicago.org

Thanks for this! It inspired me to see if Austin has one too. It does, and it appears to be using the same application to serve up the data: https://data.austintexas.gov

This is awesome and inspiring. Any thoughts on how to approach something similar for red light camera violations and accident/police data in Illinois?

Reply on news.ycombinator.com