Socrata Open Data Network: Great concept, terrible error messages

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’s really powerful, and Virginia uses it for many things, including publishing multiple COVID datasets.

In using if for the first time, I’ve found two things lacking: 1) anything but the simplest of examples. Maybe they are out there somewhere, but I couldn’t find much. It’s true that the syntax is ALMOST SQL, and looking at SQL info helped me, but it’s not quite the same. 2) The error messages are horrible, even worse than old DOS error messages like “error -2876”. They often convey VERY little, if any information. Here’s a real example I just ran into. My broken query was:

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

The error message I received was:

{'message': 'Invalid SoQL query', 'errorCode': 'query.soql.invalid', 'data': {}}

No kidding! But where is it going wrong? Any hints??? Clues??? Here’s the correct query:

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

Spot the differences? I couldn’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 “:” rather than “.” as the delimiter before the milliseconds field in the datetime values.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

The maximum upload file size: 512 MB. You can upload: image, audio, video, document, spreadsheet, text. Links to YouTube, Facebook, Twitter and other services inserted in the comment text will be automatically embedded. Drop files here