5 ms·

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 t

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

lostapathy 8y ago
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.
- bpchaps 8y ago
  You should check out muckrock.com!
mikestew 8y ago
I’m rusty, can you remind me of the SQL statement to run against the master tables to give me the schema? :-)
ir0nic 8y ago
Now wouldn't it be funny if you got them to drop a table this way?
- arenaninja 8y ago
  Wouldn't you assign readonly permissions to the account used to fulfill these requests?
  - johndavidback 8y ago
    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.
  - xapata 8y ago
    Yes, but if they're not sanitizing inputs, they might not have set permissions wisely either.
- deleted 8y ago
  [deleted]
jrumbut 8y ago
This is one of my all time favorite HN posts, a really good deed done with Unix. So inspiring!
myself248 8y ago
So first you submit a FOIA request for the database schema...
- bpchaps 8y ago
  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 ;).
  - gok 8y ago
    Can we do crowdfunding to get that started again?
    - bpchaps 8y ago
      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_button_id=8LLQBQBPS72BJ 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-government-owned-databases-308/ https://www.muckrock.com/project/tables-and-columns-from-gov... (I'll add more requests to this list tomorrow)
  - 8y ago
koolba 8y ago
If that’s possible then the first two requests should be: SELECT * FROM information_schema.tables And SELECT * FROM information_schema.columns
- anticensor 8y ago
  Not all SQL products keep their schema searchable, but good try. Your information request has been declined due to unreasonable effort required to answer.
8bitsrule 8y ago
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.
ssdd 8y ago
It's rare to see someone go above and beyond a benevolent hobby to build a complete solution to a people's problem.
paulie_a 8y ago
Actually Chicago has an extensive open data access platform. Although it's not SQL but it is pretty interesting. https://data.cityofchicago.org https://data.cityofchicago.org
- ssewell 8y ago
  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 https://data.austintexas.gov
mkobit 8y ago
This is awesome and inspiring. Any thoughts on how to approach something similar for red light camera violations and accident/police data in Illinois?

lostapathy 8y ago

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.

bpchaps 8y ago
You should check out muckrock.com!

mikestew 8y ago

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

ir0nic 8y ago

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

arenaninja 8y ago
Wouldn't you assign readonly permissions to the account used to fulfill these requests?
- johndavidback 8y ago
  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.
- xapata 8y ago
  Yes, but if they're not sanitizing inputs, they might not have set permissions wisely either.
deleted 8y ago
[deleted]

jrumbut 8y ago

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

myself248 8y ago

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

bpchaps 8y ago
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 ;).
- gok 8y ago
  Can we do crowdfunding to get that started again?
  - bpchaps 8y ago
    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_button_id=8LLQBQBPS72BJ 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-government-owned-databases-308/ https://www.muckrock.com/project/tables-and-columns-from-gov... (I'll add more requests to this list tomorrow)
- 8y ago

koolba 8y ago

If that’s possible then the first two requests should be: SELECT * FROM information_schema.tables And SELECT * FROM information_schema.columns

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

8bitsrule 8y ago

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.

ssdd 8y ago

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

paulie_a 8y ago

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

ssewell 8y ago
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 https://data.austintexas.gov

mkobit 8y ago

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