Make RSS with an SQL query

Lots of people have asked for it to be easier to get data out of ScraperWiki as RSS feeds.

The Julian has made it so.

The Web API now has an option to make RSS feeds as a format (i.e. instead of JSON, CSV or HTML tables).

For example, Anna made a scraper that gets alocohol licensing applications for Islington in London. She wanted an RSS feed to keep track of new applications using Google Reader.

To make it, she went to the Web API explorer page for the scraper, chose “rss2” for the format, and entered this SQL into the query box.

select licence_for as description,
       applicant as title,
       url as link,
       date_scraped as date
from swdata order by date_scraped desc limit 10

The clever part is the SQL “as” clauses. They let you select exactly what appears in the title and description and so on of the feed. The help that appears next to the “format” drop down when you choose “rss2” explains which fields need setting.

Since SQL is a general purpose language, you can do complicated things like concatenate strings if you need to. For most simple cases though, it is just a remapping of fields.

This is Anna’s final RSS feed of Islington alcohol license applications. There’s a thread on the ScraperWiki Google Group with more details, including how Anna made the date_scraped column.

Meanwhile, pezholio decided to use the new RSS feeds to track food safety inspections in Walsall. He used the new ScraperWiki RSS SQL API to make that scraper into an RSS feed.

Of course, these days RSS isn’t enough, he used the wonderful ifttt to map that RSS feed to Twitter. Now anyone can keep track of how safe restaurants in Walsall are by simply following @EatSafeWalsall.

Let us know if you ScraperWiki anything with RSS feeds!

P.S. Islington’s licensing system is run by Northgate, as are lots of others. It is likely that Anna’s scraper can easily be made to run for some other councils…

This entry was posted in developer and tagged , , . Bookmark the permalink.

3 Responses to Make RSS with an SQL query

  1. Pingback: Scraperwiki now makes it easier to ask questions of data | Online Journalism Blog

  2. Pingback: Scraperwiki now makes it easier to ask questions of data - Data Journalism Blog | Data Journalism Blog

  3. Pingback: Huge list of data journalism resources | clairemiller.net

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s