Yahoo YQL: find city WOEID by coordinates

Question

How to get city's WOEID if I know latitude and longitude of city using YQL?

Lohrman

Answer 1

Using the YQL console, you can search for the nearest place with a 'placeTypeName.code' of 7

http://developer.yahoo.com/yql/console/

SELECT * FROM geo.places WHERE text="{$seedLat}, {$seedLong}" AND placeTypeName.code = 7

The REST query for this query would look something like;

http://query.yahooapis.com/v1/public/yql?q=SELECT%20*%20FROM%20geo.places%20WHERE%20text%3D%22{$seedLat}%2C%20{$seedLong}%22%20AND%20placeTypeName.code%20%3D%207&format=json&diagnostics=true&callback=

Obviously replacing $seedLat and $seedLong with your own co-ordinates :)

Stanfield

Would you show us with a sample of actual URL that's actually working query.yahooapis.com/v1/public/yql?q=SELECT%20*%20FROM%20geo.places%20WHERE%20text%3D%225%2C%20106%22%20AND%20placeTypeName.code%20%3D%207 doesn't work. – Hennahane

Try using decimals instead of integers as parameters, YQL doesn't seem to like both being passed as ints. – Stanfield

nothing shows up either. Tell me what's the WOEID of SELECT * FROM geo.places WHERE text="6.0,105.0" AND placeTypeName.code = 7 – Hennahane

First if there's nothing in their database at that lat/long there will be no result, check the result count first. Second I've run that query many thousands of times via the api, it was working fine. However I've just run the script for same for 30 mins and it's always returning 0 results, looks like Yahoo has turned it off. Lastly you should do your own homework, build a testing script yourself and try the REST query with a range of lat/long pairs. – Stanfield

This answer is no longer applicable as Yahoo has made changes. The answers below by shshaw and Chuanyung Lin are now the correct answers. – Stanfield

Answer 2

For those still trying to look up the WOEID by coordinates, the other answers are now outdated. Use the geo.placefinder datatable to look up the latitude and longitude, and the WOEID will be in the returned results.

Via YQL:

SELECT * FROM geo.placefinder WHERE text="{$seedLat}, {$seedLon}" and gflags="R"

(Test it out on the YQL Console.)

Via REST:

http://query.yahooapis.com/v1/public/yql?q=SELECT%20*%20FROM%20geo.placefinder%20WHERE%20text%3D%22{$seedLat}%2C{$seedLon}%22%20and%20gflags%3D%22R%22

Staples

when I use this query it is giving result as null select * from weather.forecast where woeid in (select woeid from geo.places(1) where text="{$17.4186947},{$78.5425238}" and gflags="R") – Treat

@Navakanth: Your query should be SELECT * FROM weather.forecast WHERE woeid IN (SELECT woeid FROM geo.placefinder WHERE text="17.4186947,78.5425238" AND gflags="R") – Staples

This is no longer working. I believe YQL have shut dwon access to the placefinder table. Tenant 'query_yahooapis_com' access to 'Resource [tenantName=query_yahooapis_com, type=TABLE, name=geo.placefinder, locatorType=FILE, url=/home/y/share/manhattan/application/tenantBundles/yql_query_yahooapis_com_manhattan_v2/YQL-INF/restdefs/geo.placefinder.xml, useUrl=false]' is denied. – Ecdysiast

Answer 3

You can reference the official web page: http://developer.yahoo.com/boss/geo/docs/free_YQL.html

Here is an exmpale: select * from geo.placefinder where text="37.416275,-122.025092" and gflags="R"

Baumgartner

Answer 4

It looks like Yahoo has made the geo.placefinder table non-public, which means access requires a valid application ID and OAuth authentication (see Free Non-Commercial YQL Table Usage). If you just want the WOEID and you know the city and state, you can use the geo.places table instead:

select * from geo.places where text="Hanover, PA" AND placeTypeName.code = 7

YQL Console

If a result is found, the WOEID will be returned as data.query.results.place.woeid.

However, this isn't perfect. In Pennsylvania we have (at least) three places that can show up when searching for "Hanover" (Hanover in York County, Township of Hanover in Beaver County, and Hanover Township in Luzerne County). This may or may not affect your results when searching using this method. Using a postal code should increase the accuracy of the results, and it is possible to filter the results by the known latitude/longitude using the boundingBox property:

select * from geo.places where text="Eagle, Wisconsin" AND
  boundingBox.southWest.latitude <= 42.9147280 AND
  boundingBox.southWest.longitude <= -88.4426340 AND
  boundingBox.northEast.latitude >= 42.9147280 AND
  boundingBox.northEast.longitude >= -88.4426340 AND
  placeTypeName.code = 7

YQL Console

You will still need to provide one of several required keys (text is a valid required key), so this doesn't qualify as searching solely by geographical coordinates, but hopefully this counts as second best.

Nunci

Answer 5

None of the previous answers work anymore, but this does.

You need to combined the flicker table in the query, like so: select place.woeid from flickr.places where lat="" and lon="" and api_key=""

Getting a API KEY from Flickr is instant: https://www.flickr.com/services/apps/create/

Pottery

Yahoo YQL no more? – Sloan