How To Use Open APIs

Yesterday I gave a talk entitled “How Citizens Can Use Open APIs to Create New City Services” at Asheville Cloud Day 2011. I aimed to inspire city IT personnel to create open APIs and to show everyone else how they could leverage those APIs. You can find the slides of my talk here.

While I’ve been aware of open APIs, I had never used one until I was preparing for this talk. I learned how to use open APIs about a month ago after watching the excellent video Google Docs Unleashed from AppSumo. Open APIs are surprisingly easy to learn and use, and I encourage everyone to try out the import functions of Google Spreadsheet that allow you to access them.

In preparation for my talk, I did some research about open APIs and the “mashup” applications built on top of them. This post details the results of that research and walks through the live examples I gave during the talk for those who want to learn more.

Government Mashup Applications

Below are government mashup examples I bookmarked for my talk. The application name link takes you to the application itself, but I’ve included links to the ProgrammableWeb listing for each application so you can see which APIs were used to build the app.

• PDXster
  Allows citizens to view and comment on Portland, Oregon city council agendas. [Programmable Web Details]
• Citizen Service Requests
  Allows citizens to submit service requests to the city of Ann Arbor, MI. Built on top of their own APIs and public APIs. [Programmable Web Details]
• National Broadband Map
  Provides information about broadband deployment by location. View Asheville’s info or check out the extensive list of APIs.
• DUIMap
  Shows a map of drunk driving deaths. [Programmable Web Details]
• Risky Roads
  Shows a map of all fatal driving deaths. From the same people who did DUIMap.  [Programmable Web Details]
• Are You Safe?
  Mobile app that provides a dashboard readout of the crime level in an area. [Programmable Web Details]
• Bill Maps
  For any given bill, maps out where the yes and no votes occurred. [Programmable Web Details]

For this list, I selected only a handful of applications. For a broader list, check out the government tag on Programmable Web.

Live Examples

During my talk, I walked through two examples of how to access open APIs in Google Spreadsheet.

New Corporations Example

I wanted to give a local government example. Unfortunately, North Carolina does not yet have many open APIs. Instead, I chose to screen scrape the North Carolina Secretary of State web site for new corporations formed in Buncombe County last month, then map those corporations onto Google Maps.

The steps I took to create this example include:

1. I used Needlebase to screen scrape the data after doing a search for new corporations in July 2011. Needlebase uses machine learning algorithms to automatically detect and parse data. I used Needlebase to execute the search form, parse the results page, then link into each detail page and parse that.
2. After parsing, I published the data set so public services like Google could access it. Links at the bottom let me grab the data as plain text, CSV or JSON. For this example, I used the CSV data.
3. I created a new Google Spreadsheet and imported the data using the formula:
   =IMPORTDATA("https://pub.needlebase.com/actions/api/V2Visualizer.do?domain=NC-New-Corporations-Buncombe-Cou&render=Csv&query=company")

   I renamed the sheet containing this data “Data Feed”.

4. I added a new tab to use to stage data I wanted to import into Google Maps. Google Maps takes two columns, an address and a tool tip. So I created these two columns and then entered the following two formulas into the first two columns:
   =IF('Data Feed'!G2 = "NO ADDRESS", "", 'Data Feed'!G2)

=IF('Data Feed'!G2 = "NO ADDRESS", "", CONCATENATE("<b>", 'Data Feed'!A2, "</b><br>",A2 , "<p>Type: ", 'Data Feed'!M2, "<br>Citizenship: ", 'Data Feed'!E2, "<br>Incorporated: ",'Data Feed'!H2, "<br>Incorporated State: ",'Data Feed'!J2, "<p><u>Agent</u><br>",'Data Feed'!B2, "<br>",'Data Feed'!C2))

   I then copied these down the rest of the spreadsheet for each row.

5. Finally, I created a third tab and added a Maps gadget using the Insert > Gadget menu item. For the data range, I just used the two columns from my reformatted data sheet. Then I specified I wanted tool tips and mouse wheel zooming.
6. It took a minute or so for the Google Maps to appear, but when it did all the July incorporations for Buncombe County were mapped out geographically and you could clearly see some corporations had out-of-state offices.

You can see the final spreadsheet here.

YouTube Example

Since the government example didn’t use a true API, I wanted to an example that demonstrated mashing up multiple services to get information using actual API calls. I chose to use the YouTube API to retrieve the first 10 videos in Asheville, North Carolina and then to see how often they are shared on Facebook. Since YouTube requires latitude and longitude coordinates, I used another API to translate from a zip code to latitude and longitude.

The steps I took to create this example include:

1. I created a new Google Spreadsheet and decided the first sheet was going to be my main “application” sheet. At the top I added the label “Zip Code:” and in the cell next to it entered “28801”.
2. I added another sheet, calling it Lookups. In the first row I called the Geocoder.us API to translate from the zip code entered on my main sheet into a latitude and longitude using:
   =IMPORTDATA(CONCATENATE("http://geocoder.us/service/csv/geocode?zip=",Main!B1))
3. I looked up the syntax for the YouTube API call for searching videos. I grabbed their example link, modified it to use the “location” and “location-radius” parameters, removed the keyword query parameter and tested it with the Asheville latitude and longitude:
   https://gdata.youtube.com/feeds/api/videos?orderby=published&start-index=11&max-results=10&v=2&prettyprint=true&location=35.595661,-82.55632&location-radius=10km
4. I converted this to a formula with the latitude and longitude coming from the result of my API call on the first row:
   =CONCATENATE("https://gdata.youtube.com/feeds/api/videos?orderby=published&start-index=11&max-results=10&v=2&prettyprint=true&location=", Lookups!B1, ",", Lookups!C1, "&location-radius=10km")
5. I switched back to my main sheet and underneath the zip code line, used the ImportFeed function to pull in the data from the YouTube feed URL defined on the Lookup sheet:
   =ImportFeed(Lookups!B3)
6. This gave me a listing of the most recent Asheville videos with links I could click to see each one. Unfortunately, most of them were advertising videos for car dealerships or real estate (not surprising, since only videos that are geocoded for Asheville appear in this listing).
7. To demonstrate also pulling data from a JSON API, I wrote a script I learned in Google Docs Unleashed. I started by opening the script editor using Tools > Script editor. Then I entered:
   function FacebookShares(url) {
    var service  = "http://graph.facebook.com/" + escape(url);
    var jsondata = UrlFetchApp.fetch(service);
    var object   = Utilities.jsonParse(jsondata.getContentText());

    return object.shares != null ? object.shares : 0;
}

   This defined a user function for my spreadsheet called FacebookShares which retrieves the number of times a given URL is shared on Facebook using the JSON format from the Facebook API.

8. I used this function to retrieve the number of shares for each link I retrieved using:
   =FacebookShares(C3)
9. Not surprisingly, none of the advertising videos had been shared on Facebook, so I created another sheet called “Facebook Shares” to confirm my function was working and put in some popular web sites. Sure enough, I got the number of times these links were shared on Facebook.

You can see the final spreadsheet here.

Resources

Resources I found while researching my talk include:

• API Directories
  • ProgrammableWeb: Directory of over 3,000 open APIs. Search by popularity, keyword, etc.
  • USGovXML: Government web services by department
  • Sunlight Labs APIs: Open government APIs
  • Small Business Administration (SBA): APIs provided by the SBA
  • Data.gov: Data feeds for the U.S. Federal government
  • OpenData: Public open data feeds hosted by Socrata
• Screen Scraping
  • Needlebase: Scrape web data using machine learning algorithms
  • ScraperWiki: Share and extend custom screen scraping code
  • InfoExtractor: Text and attribute extraction from limited sources
  • Web Scraping: The Wikipedia article on screen scaping
• Mashup Services
  • WSO2 Mashup Server: Open source enterprise mashup hosting server
  • Yahoo Pipes: Combine and remix data feeds to use in widgets
  • Google Docs: Use Import functions and scripting to build mashups
• Creating APIs
  • How to Create an API: List of 10 tutorials for creating an API
  • How To Create a Simple API with PHP & MySQL: Step-by-step guide
  • Socrata: Sells software to governments to create open APIs
  • Open Government Data Initiative: Publish open data using Azure
  • Web API Best Practices: Short blog post on best practices
  • Good APIs Project: Longer guide to developing good APIs

Have you used open APIs or are thinking about it? Comment below.

Credits: The photo used in this article was taken by Jeff Keyzer.