An OpenEI development teaser: finding companies related to carrots

OpenEI offers linked open energy data to the world. OpenEI engineers have made a section on OpenEI to showcase some SPARQL queries, but even a skilled web developer or analyst may be daunted by this new query language, and not know what to do with it. Today, I'll show how to take advantage of this from a developer or analyst's point of view. It's a little known fact that OpenEI has DSIREusa.org incentive database available in a structured, open format - so let's get our feet wet playing with that.

If you don't already have a Yahoo account, get one and go to the YQL console (or read more about YQL). Then paste this in to "Your YQL statement" in the console:

use "http://triplr.org/sparyql/sparql.xml" as sparql;
select * from local.search where location in (select result.place_name.value from sparql where query="
PREFIX rdf: 
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX swivt: <http://semantic-mediawiki.org/swivt/1.0#>
PREFIX resource: <http://openei.org/resources/>
PREFIX category: <http://openei.org/resources/Category-3A>
PREFIX property: <http://openei.org/resources/Property-3A>
SELECT ?place_name
FROM <http://openei.org>
WHERE {
?incentive swivt:page ?openei_url.
?incentive rdf:type category:Financial_Incentive_Programs.
?incentive rdfs:label ?name.
OPTIONAL {
?incentive property:Place ?place.
?place rdfs:label ?place_name.
}.
?incentive property:Incentive-2FTechDsc resource:Photovoltaics.
?incentive property:Incentive-2FType resource:State_Loan_Program.
}
ORDER BY DESC(?place_name)" and service="http://en.openei.org/sparql") and query='Photovoltaics' and radius=300

Here's the first part of the response:

<?xml version="1.0" encoding="UTF-8"?>
<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng" yahoo:count="121" yahoo:created="2010-02-03T11:47:37Z" yahoo:lang="en-US" yahoo:updated="2010-02-03T11:47:37Z" yahoo:uri="http://query.yahooapis.com/v1/yql?q=use+%22http%3A%2F%2Ftriplr.org%2Fsparyql%2Fsparql.xml%22+as+sparql%3B%0Aselect+*+from+local.search+where+location+in+%28select+result.place_name.value+from+sparql+where+query%3D%22%0APREFIX+rdf%3A+%3Chttp%3A%2F%2Fwww.w3.org%2F1999%2F02%2F22-rdf-syntax-ns%23%3E%0APREFIX+rdfs%3A+%3Chttp%3A%2F%2Fwww.w3.org%2F2000%2F01%2Frdf-schema%23%3E%0APREFIX+swivt%3A+%3Chttp%3A%2F%2Fsemantic-mediawiki.org%2Fswivt%2F1.0%23%3E%0APREFIX+resource%3A+%3Chttp%3A%2F%2Fopenei.org%2Fresources%2F%3E%0APREFIX+category%3A+%3Chttp%3A%2F%2Fopenei.org%2Fresources%2FCategory-3A%3E%0APREFIX+property%3A+%3Chttp%3A%2F%2Fopenei.org%2Fresources%2FProperty-3A%3E%0ASELECT+%3Fplace_name%0AFROM+%3Chttp%3A%2F%2Fopenei.org%3E%0AWHERE+%7B%0A++%3Fincentive+swivt%3Apage+%3Fopenei_url.%0A++%3Fincentive+rdf%3Atype+category%3AFinancial_Incentive_Programs.%0A++%3Fincentive+rdfs%3Alabel+%3Fname.%0A++OPTIONAL+%7B%0A++++%3Fincentive+property%3APlace+%3Fplace.%0A++++%3Fplace+rdfs%3Alabel+%3Fplace_name.%0A++%7D.%0A++%3Fincentive+property%3AIncentive-2FTechDsc+resource%3APhotovoltaics.%0A++%3Fincentive+property%3AIncentive-2FType+resource%3AState_Loan_Program.%0A%7D%0AORDER+BY+DESC%28%3Fplace_name%29%22+and+service%3D%22http%3A%2F%2Fen.openei.org%2Fsparql%22%29+and+query%3D%27Photovoltaics%27+and+radius%3D300">
<results>
  <Result xmlns="urn:yahoo:lcl" id="10462184">
      <Title>Eco Sage Corporation</Title>
      <Address>39 Beacon Hl</Address>
      <City>Chelsea</City>
      <State>VT</State>
      <Phone>(802) 685-3450</Phone>
      <Latitude>43.984395</Latitude>
      <Longitude>-72.450817</Longitude>
      <Rating>
          <AverageRating>NaN</AverageRating>
          <TotalRatings>0</TotalRatings>
          <TotalReviews>0</TotalReviews>
          <LastReviewDate/>
          <LastReviewIntro/>
      </Rating>
      <Distance>7.80</Distance>
      <Url>http://local.yahoo.com/info-10462184-eco-sage-corporation-chelsea</Url>
      <ClickUrl>http://local.yahoo.com/info-10462184-eco-sage-corporation-chelsea</ClickUrl>
      <MapUrl>http://maps.yahoo.com/maps_result?q1=39+Beacon+Hl+Chelsea+VT&gid1=10462184</MapUrl>
      <BusinessUrl>http://inetnews.org/</BusinessUrl>
      <BusinessClickUrl>http://inetnews.org/</BusinessClickUrl>
      <Categories>
          <Category id="96930971">Educational Consulting</Category>
      </Categories>
  </Result>
  ...

OK, now that I've blown you away with code, I'll tell you what this code does:

1) Asks OpenEI for the place name of every Financial Incentive Program that is a State Loan Program relating to Photovoltaics; place names are currently state names, though they could feasibly be a country name also. Sample return from this query would be Wisconsin, Virginia {...} Alaska.
2) Asks the local.search web service to find any company related to Photovoltaics within a 300-mile radius of the center of each place name.

What good does this do us? Well, this would be a starting framework for understanding what companies may derive value or be interested in incentive programs in their geographic region.

Want just the name of the company and a telephone number? That's easy, change select * from local.search... to select Title, Phone from local.search... in the YQL query above. Voilà, you have a call list of organizations that may have benefited from or be interested in their photovoltaic state loan program.

And that's just the start.

When you draw on a community-edited platform such as OpenEI, you get the "latest data" that organizations and individuals input and edit. A similarly interesting query would be finding what organizations have been incorporated since a loan incentive program was released in the organization's state. The possibilities for mashups are endless, and I hope I whetted some developer and analyst appetites to get in and get messy deriving value from this rich community-editable, linked open data platform.

While it's quite a long URL, YQL will create the RESTful query URL for this - so you can easily reuse the data set and query on your own site. As OpenEI derives new data from sources and the community, your web service feed gets automatically updated. The RESTful URL for the query above is here.

Note: If you are interested in getting more involved with OpenEI, I encourage you to check out our Google Group. As a bonus, I'll share some of the logic behind my SPARQL query above in the group, so you can get started making your own web services or mashups from OpenEI.