Why does this Yahoo weather API query not work?

Question

This query to get the forecast from Ottawa does not work:

SELECT * FROM weather.forecast WHERE woeid IN (SELECT woeid FROM geo.places WHERE text="Ottawa, Canada" limit 1)

Result:

"query": {
  "count": 1,
  "created": "2015-07-02T13:06:45Z",
  "lang": "en-US",
  "results": {
   "channel": {
    "title": "Yahoo! Weather - Error",
    "description": "Yahoo! Weather Error",
    "item": {
     "title": "City not found",
     "description": "\nInvalid Input /forecastrss?w=91982014\n"
    }
   }
  }
 }

Executed on its own, the sub query gets the correct result:

SELECT woeid FROM geo.places WHERE text="Ottawa, Canada" limit 1

Result:

"query": {
  "count": 1,
  "created": "2015-07-02T13:00:47Z",
  "lang": "en-US",
  "results": {
   "place": {
    "woeid": "91982014"
   }
  }
 }

This works for all other cities I tried and when I leave out "limit 1" it also works for Ottawa, but returns too many results. Am I doing something wrong or is the API at fault?

score 0 · Accepted Answer · answered Jul 09 '15 at 17:52

I think the problem is that for Ottawa, there are multiple results (multiple WOEIDs), and not all of those WOEIDs have corresponding entries in the weather.forecast table. And I think that is because some of those entries are at levels other than the levels for which we take forecasts.

The query SELECT woeid FROM geo.places WHERE text="Ottawa, Canada" yields the following result:

"results": {
 "place": [
  {
   "woeid": "91982014"
  },
  {
   "woeid": "29375164"
  },
  {
   "woeid": "12483153"
  }
 ]
}

If you modify your query to include the type of the place SELECT woeid, placeTypeName.content FROM geo.places WHERE text="Ottawa, Canada", you can see that the results include a Town, a County/District, and an Island, and the town is the first item in the results:

"results": {
 "place": [
  {
   "woeid": "91982014",
   "placeTypeName": "Town"
  },
  {
   "woeid": "29375164",
   "placeTypeName": "County/District"
  },
  {
   "woeid": "12483153",
   "placeTypeName": "Island"
  }
 ]
}

In the results of the full query that returns too many results SELECT * FROM weather.forecast WHERE woeid IN (SELECT woeid FROM geo.places WHERE text="Ottawa, Canada"), you can see a section called diagnostics that has an array called url that gives the weather API URLs that were called as a result of your query:

http://weather.yahooapis.com/forecastrss?w=12483153
http://weather.yahooapis.com/forecastrss?w=29375164
http://weather.yahooapis.com/forecastrss?w=91982014

and that the result for 91982014 is an error:

You can remove the error case from your results SELECT * FROM weather.forecast WHERE woeid IN (SELECT woeid FROM geo.places WHERE text="Ottawa, Canada") AND title NOT LIKE "%Error%", which still gives two results.

It seems that what you really want to do is realize that there are three places in Canada with "Ottawa" in the name: the capital city Ottawa in the province of Ontario, the county/district Ottawa in the province of Ontario, and a collection of islands called Ottawa Islands in the territory of Nunavut. So you need to be more specific with your query.

SELECT * FROM weather.forecast WHERE woeid IN (SELECT woeid FROM geo.places WHERE text="Ottawa, Ontario, Canada") AND title NOT LIKE "%Error%"

feels kludgey, but it gives a single result.

Thank you for the detailed answer! The problem with being more specific is that I only get the city and country name from another API, but with your advise I can get a working solution. Thanks again. — user923487, Jul 14 '15 at 10:34

Why does this Yahoo weather API query not work?

1 Answers1