2

I am new to this site and this is my first question. I am trying to query the "Seattle Real Time Fire 911 Calls" database from the Socrata Seattle Open Data site: https://data.seattle.gov/Public-Safety/Seattle-Real-Time-Fire-911-Calls/kzjm-xkqj. I'm not an expert at using the SODA API, and I'm having difficulty figuring out how to query the most recent entries in the database. All attempts to use the "order" or "where" SoQL statements give me data from 2010 or 2011, and I cannot figure out how to query the most recent 300 entries. Querying the "top" rows yields the oldest entries. Using a full OData feed pull yields data as recent as today, but I need to use a fast json or csv SODA API query.

Note: The datetime field does not respond to any "where" statements that I use.

Thank you!

Open Data Bits
  • 23
  • 1
  • 3

1 Answers1

2

OK, a few tips to get started:

  • The $order parameter sorts by default in ascending (ASC) order, so you'll want to actually order by datetime DESC to get the latest records first
  • Unfortunately Seattle has a number of crimes that are listed with no datetime, so you'll also want to filter with a $where query to only retrieve results in a date range. $where=datetime > '2014-07-01' works for me, for example
  • To only get the top 300 results, you'll want to pass a $limit=300 parameter as well.

Here's a sample request in Runscope for you to try out.

chrismetcalf
  • 1,556
  • 1
  • 8
  • 7
  • The example was very helpful and I've got this figured out. Thank you for your assistance. – Open Data Bits Jul 28 '14 at 19:38
  • 1
    Has something changed in the implementation of SoQL? This example no longer works for me. If I `curl -v 'https://data.seattle.gov/resource/kzjm-xkqj.csv?$where=datetime%20%3E%20'2014-07-01''` I get back the following error message: ` "SoQL does not currently support conversion between date and number."` – Chris Kolodin Mar 10 '15 at 02:11
  • What's between the `0` and the `7` in your URL?: `2014-0‌​7-01` – chrismetcalf Mar 10 '15 at 05:18
  • Having the same issue as @ChrisKolodin, the following http request returns the same error: `https://data.cityofnewyork.us/resource/erm2-nwe9.json?$where=created_date>='2014-01-01'` – clhenrick Jun 01 '15 at 20:44
  • 1
    It looks like your query should work just fine, but double-check you're not URL encoding the date portion (`2014-01-01`) as we seem to take issue when that portion is encoded: https://data.cityofnewyork.us/resource/erm2-nwe9.json?$where=created_date%3E=%272014-01-01%27 – chrismetcalf Jun 03 '15 at 21:10
  • By the way, you guys probably should migrate to the new version of that dataset - you'll see higher performance and some improved query options: http://dev.socrata.com/foundry/#/data.cityofnewyork.us/fhrw-4uyv – chrismetcalf Jun 03 '15 at 21:16