Open Data API: Socrata Queries
Chicago's open data is managed on the Socrata open data platform. The Chicago portal offers very helpful filtering and downloading capabilities through its web interface but applications need API access. Socrata provides an API called SODA or the Socrata Open Data API but the format for queries is not easy to learn and use. The most up-to-date description of SODA here gives a lot of information to get you started.Socrata provides a Ruby gem called socrata-ruby to access the API but as far as I can tell it does not run queries other than text searches.Chicago has provided a Ruby gem called windy that wraps SODA with a much simpler interface. Unfortunately windy doesn't permit any selection of rows so it loads a complete dataset. That makes it very handy for exploring small datasets but not for large ones like the one my app uses unless you create a small view from the web interface and explore that with the gem.(Note: There's something for PHP, too, but I don't know much about it. See this blog post.) I have written an app to write filtered SODA queries which none of the existing tools do. An app to write queries sounds like overkill, but check this format for a fairly simple query filter:{ "originalViewId"=>"qnrb-dui6","name"=>"not used but must be specified",
"query"=>{
"filterCondition"=>
{"type"=>"operator",
"value"=>"AND",
"children"=>
[{"type"=>"operator",
"value"=>"EQUALS",
"children"=>[
{"columnId"=>3850119, "type"=>"column"},
{"type"=>"literal", "value"=>"2011"}]},
{"type"=>"operator",
"value"=>"GREATER_THAN_OR_EQUALS",
"children"=>[
{"columnId"=>3850106, "type"=>"column"},
{"type"=>"literal", "value"=>"2011-11-26T00:00:00"}]},
{"type"=>"operator",
"value"=>"BETWEEN",
"children"=>[
{"columnId"=>3850105, "type"=>"column"},
{"type"=>"literal", "value"=>"HT606237"},
{"type"=>"literal", "value"=>"HT606284"}]}
]
}
}
}The query this specifies is "all 2011 crime records where date is >= 11/26/2011 and HT606237 >= case number >= HT606284" so you see why a generator is needed for this!Another complication is that fields are specified by number, not by name, and those field numbers can and do change over the life a Socrata view.Illinois open data is also served with Socrata and this query app can be used there as well.


