{"id":715,"date":"2020-10-14T00:12:57","date_gmt":"2020-10-14T04:12:57","guid":{"rendered":"https:\/\/www.mcgurrin.info\/robots\/?p=715"},"modified":"2020-10-14T00:14:51","modified_gmt":"2020-10-14T04:14:51","slug":"socrata-open-data-network-great-concept-terrible-error-messages","status":"publish","type":"post","link":"https:\/\/www.mcgurrin.info\/robots\/715\/","title":{"rendered":"Socrata Open Data Network: Great concept, terrible error messages"},"content":{"rendered":"<p>The<a href=\"https:\/\/dev.socrata.com\/\"> Socrata<\/a> API combines a RESTful API with an SQL-like query language, and their open data network encourages public agencies to publish their data sets. It&#8217;s really powerful, and Virginia uses it for many things, including publishing multiple <a href=\"https:\/\/data.virginia.gov\/browse?tags=covid-19\">COVID datasets<\/a>.<\/p>\n<p>In using if for the first time, I&#8217;ve found two things lacking: 1) anything but the simplest of examples. Maybe they are out there somewhere, but I couldn&#8217;t find much. It&#8217;s true that the syntax is ALMOST SQL, and looking at SQL info helped me, but it&#8217;s not quite the same. 2) The error messages are horrible, even worse than old DOS error messages like &#8220;error -2876&#8221;. They often convey VERY little, if any information. Here&#8217;s a real example I just ran into. My broken query was:<\/p>\n<pre>https:\/\/data.virginia.gov\/resource\/bre9-aqqr.json?$query= SELECT report_date, sum(total_cases) WHERE report_date between \"2020-10-05T00:00:00:000\" and \"2020-10-13T00:00:00:000\"AND vdh_health_district IN (\"Alexandria\", \"Fairfax\", \"Arlington\", \"Loudoun\", \"Prince William\") GROUP BY report_date<\/pre>\n<p>The error message I received was:<\/p>\n<pre>{'message': 'Invalid SoQL query', 'errorCode': 'query.soql.invalid', 'data': {}}<\/pre>\n<p>No kidding! But where is it going wrong? Any hints??? Clues???\u00a0Here&#8217;s the correct query:<\/p>\n<pre>https:\/\/data.virginia.gov\/resource\/bre9-aqqr.json?$query= SELECT report_date, sum(total_cases) WHERE report_date between \"2020-10-05T00:00:00.000\" and \"2020-10-13T00:00:00.000\"AND vdh_health_district IN (\"Alexandria\", \"Fairfax\", \"Arlington\", \"Loudoun\", \"Prince William\") GROUP BY report_date<\/pre>\n<p>Spot the differences? I couldn&#8217;t until I actually ran a little script to compare a very similar query that was working to the broken one to find them. I had mistakenly had &#8220;:&#8221; rather than &#8220;.&#8221; as the delimiter before the milliseconds field in the datetime values.<\/p>\n<p>Oh well, frustration out of my system. I need to create one more query for a different dataset, then right the small amount of processing logic on the data.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>The Socrata API combines a RESTful API with an SQL-like query language, and their open data network encourages public agencies to publish their data sets. It&#8217;s really powerful, and Virginia uses it for many things, including publishing multiple COVID datasets. &hellip; <a href=\"https:\/\/www.mcgurrin.info\/robots\/715\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[91],"_links":{"self":[{"href":"https:\/\/www.mcgurrin.info\/robots\/wp-json\/wp\/v2\/posts\/715"}],"collection":[{"href":"https:\/\/www.mcgurrin.info\/robots\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mcgurrin.info\/robots\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mcgurrin.info\/robots\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mcgurrin.info\/robots\/wp-json\/wp\/v2\/comments?post=715"}],"version-history":[{"count":4,"href":"https:\/\/www.mcgurrin.info\/robots\/wp-json\/wp\/v2\/posts\/715\/revisions"}],"predecessor-version":[{"id":719,"href":"https:\/\/www.mcgurrin.info\/robots\/wp-json\/wp\/v2\/posts\/715\/revisions\/719"}],"wp:attachment":[{"href":"https:\/\/www.mcgurrin.info\/robots\/wp-json\/wp\/v2\/media?parent=715"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mcgurrin.info\/robots\/wp-json\/wp\/v2\/categories?post=715"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mcgurrin.info\/robots\/wp-json\/wp\/v2\/tags?post=715"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}