![[philiptellis]](https://en.gravatar.com/userimage/3079306/f13167d69aa4e1cb5e5f131af4e38e3e.jpeg)
First, the easy way. Rasmus Lerdorf has a web service that takes in an IP address and based on the MaxMind data, returns a bunch of information including the country and state/region code. I initially decided to use this. His example page is pretty self-explanatory, so I won't re-document it here. The problem is that this service was really slow and increased page load time a lot, so I scrapped the idea.
I then started looking through YQL. YQL has a whole bunch of geo stuff, but nothing that specifically turns an IP address into a WoEID or a country/state code. I then looked at the community supported tables and found the
ip.location table that uses the ipinfodb.com wrapper around the MaxMind database. This returned everything I needed, but the only problem was that the state was returned as a string rather than a two character code. This is the query:SELECT * From ip.location Where ip=@ipThe output looks like this:
{
"query":{
"count":"1",
"created":"2010-02-28T01:24:30Z",
"lang":"en-US",
"updated":"2010-02-28T01:24:30Z",
"uri":"http://query.yahooapis.com/v1/yql?q=select+*+from+ip.location+where+ip%3D%27209.117.47.253%27",
"results":{
"Response":{
"Ip":"209.117.47.253",
"Status":"OK",
"CountryCode":"US",
"CountryName":"United States",
"RegionCode":null,
"RegionName":null,
"City":null,
"ZipPostalCode":null,
"Latitude":"38",
"Longitude":"-97",
"Timezone":"-6",
"Gmtoffset":"-6",
"Dstoffset":"-5"
}
}
}
}
Now it's pretty trivial to build an array that maps from state name to state code, but I'd have to keep growing that as I added support for more countries, so I decided against that route. Instead I started looking at how I could use the geo APIs to turn this information into what I wanted. Among other things, the data returned also contained the latitude and longitude of the location that the IP was in. I decided to do a reverse geo map from the lat/lon to the geo information. The only problem is that the geo API itself doesn't do this for you.Tom Croucher then told me that the
flickr.places API could turn a latitude and longitude pair into a WoEID, so I decided to explore that. This is the query that does it:SELECT place.woeid From flickr.places Where lat=@lat And lon=@lonNow I could tied the two queries together and get a single one that turns an IP address to a WoEID:
SELECT place.woeid From flickr.places
Where (lat, lon) IN
(
SELECT Latitude, Longitude From ip.location
Where ip=@ip
)
This is what the output looks like:{
"query":{
"count":"1",
"created":"2010-02-28T01:25:34Z",
"lang":"en-US",
"updated":"2010-02-28T01:25:34Z",
"uri":"http://query.yahooapis.com/v1/yql?q=SELECT+place.woeid+From+flickr.places%0A+Where+%28lat%2C+lon%29+IN%0A+++%28%0A++++++SELECT+Latitude%2C+Longitude+From+ip.location%0A+++++++Where+ip%3D%27209.117.47.253%27%0A+++%29",
"results":{
"places":{
"place":{
"woeid":"12588378"
}
}
}
}
}
The last step of the puzzle was to turn this WoEID into a country and state code. This I already knew how to do:SELECT country.code, admin1.code From geo.places Where woeid=@woeid
country.code gets us the two letter ISO3166 country code while admin1.code gets us a code for the local administrative region. For the US and Canada, this is simply the country code followed by a hyphen, followed by the two letter state code. Once I got this information, I could strip out the country code and the hyphen from admin1.code and get the two letter state code.My final query looks like this:
SELECT country.code, admin1.code From geo.places
Where woeid IN
(
SELECT place.woeid From flickr.places
Where (lat, lon) IN
(
SELECT Latitude, Longitude From ip.location
Where ip=@ip
)
)
And the output is:{
"query":{
"count":"1",
"created":"2010-02-28T01:26:32Z",
"lang":"en-US",
"updated":"2010-02-28T01:26:32Z",
"uri":"http://query.yahooapis.com/v1/yql?q=SELECT+country.code%2C+admin1.code+From+geo.places%0A+Where+woeid+IN%0A%28SELECT+place.woeid+From+flickr.places%0A+Where+%28lat%2C+lon%29+IN%0A+++%28%0A++++++SELECT+Latitude%2C+Longitude+From+ip.location%0A+++++++Where+ip%3D%27209.117.47.253%27%0A+++%29%29",
"results":{
"place":{
"country":{
"code":"US"
},
"admin1":{
"code":"US-KS"
}
}
}
}
}
Paste this code into the YQL console, make sure you've selected "Show community tables" and get the REST API from there. It's a terribly roundabout way to get something that should be a single API call, but at least from my application's point of view, I only need to call a single web service. Now if only we could convince the guys at missingkidsmap.com to use WoEIDs instead of state codes, that would make this all a lot easier.Have I mentioned how much I like YQL?
