The other side of the moon

For my missing kids hack, I needed to convert an IP address to a US or Canadian 2 letter state code. This should have been pretty straightforward, but it turned out to require a little more effort than I initially wanted to put in.

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=@ip

The 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=@lon

Now 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?

After my post about converting the geo microformat to a google map, this one adds support for Yahoo! Maps. What I found cool was how alike the two APIs were.

I changed my map creation code to this:

             if(mapAPI == 'G')
             {
                point = new GPoint(lon,lat);
                map = new GMap(adr[i]);
                map.addControl(new GSmallMapControl());
                map.addControl(new GMapTypeControl());
                map.centerAndZoom(point, 0);
             }
             else
             {
                point = new YGeoPoint(lat,lon);
                map = new  YMap(adr[i]);
                map.addPanControl();
                map.addZoomShort();
                map.drawZoomAndCenter(point, 1);
             }
             addMarkerToMap(map, point, html);

And the marker addition code to this:

       if(mapAPI == 'G')
       {
          var marker = new GMarker(point);
          map.addOverlay(marker);
          if(html)
          {
             marker.openInfoWindowHtml(html)
             GEvent.addListener(marker, 'click', function() {marker.openInfoWindowHtml(html);});
          }
       }
       else
       {
          var marker = new YMarker(point);
          map.addOverlay(marker);
          if(html)
          {
             html = "<div class='map-marker'>" + html + "</div>";
             marker.openSmartWindow(html);
             YEvent.Capture(marker, EventsList.MouseClick, function() {marker.openSmartWindow(html);});
          }
       }

Which creates as close an experience as possible in both maps.

The map provider should be selected at random, but if you'd like to force a particular map, add #G or #Y to the url. They can be found on the california reviews page.

Let me know what you think.

I'd been toying with the idea of adding geo data to my restaurant reviews. I thought it would be nice to have a map pointer right below the instructions for getting there. I started looking around for well estabilished methods to markup geo data in a blog post.

I came upon an article in linux journal that spoke about geotagging and geocoding for websites. It talks about ICBM and meta tags, which are great except for one thing. It goes into the page header, so can't be different for different sections of the page. It also talks about embedding the information in comments - which means that users and javascript can't easily read it, and about RDF/RSS feeds - which would be useful for my blog feed, but not for my blog itself.

I decided to try my own minimalistic markup, and came up with this:

   <address class="gmap" lat="yy" lon="xx" zoom="z">Some text</address>

Of course, I went through a couple of iterations to settle on this, and it was based largely on what the google maps api accepts.

A side note before I go on. The reason I chose google maps was because they provided aerial photos of a few major Indian cities, which is where most of my reviews are based. This turned out to be of no use though, because the aerial photos are not provided via the API. One has to visit Google Local to see them.

Ok, so this gave me the ability to easily add geo information to a post - just a single line. If I wanted to be really cool, I'd need to translate that to a map, so I started studying the google maps API, and after several iterations, came up with this:

   //! Add a marker with a callout containing the specified html
   function addMarkerToMap(map, point, html)
   {
      var marker = new GMarker(point);
      map.addOverlay(marker);
      if(html)
      {
         marker.openInfoWindowHtml(html)
         GEvent.addListener(marker, 'click', function() {marker.openInfoWindowHtml(html);});
      }
   }

   window.onload=function()
   {
      var adr = document.getElementsByTagName("address");
      for(var i=0; i<adr.length; i++)
      {
         if(adr[i].className == 'gmap')
         {
            // Grab HTML to put into callout
            var html=adr[i].innerHTML;
            var lat, lon, zoom;
            lat=1*adr[i].getAttribute('lat');
            lon=1*adr[i].getAttribute('lon');
            zoom=parseInt(adr[i].getAttribute('zoom'));
            adr[i].innerHTML = "";

            // Build map and center on lat/lon
            var map = new GMap(adr[i]);
            map.addControl(new GSmallMapControl());
            map.addControl(new GMapTypeControl());
            var point = new GPoint(lon,lat);
            map.centerAndZoom(point, zoom);
            addMarkerToMap(map, point, html);
         }
      }
   }

What it does is quite simple. After page load, it iterates through all the divs in the page, looking for divs with class 'gmap'. When it finds such a div, it looks at the lat, lon and zoom attributes (which are not standard HTML btw) of the div and uses that to draw the map.

I soon realised that I was only using zoom level 0, so dropped that attribute and hard coded it in.

I showed it to Nate a little later, and he mentioned that there was a microformat for geocoding. Had a look at it, and while it was slightly more verbose than my format, it achieved a little more, and wouldn't be much harder to parse.

Changed the div to this:

<address class="geo">
Some text<br>
<abbr class="latitude" title="37.801324">N 37° 48.079</abbr> <br>
<abbr class="longitude" title="-122.424903">W 122° 25.494</abbr>
</address>

This shows up neatly, and I could change my javascript to accept both classes, 'gmap' as well as 'geo', and change the parsing to this:

             if(adr[i].className == 'geo')
             {
                var ab = adr[i].getElementsByTagName('abbr');
                for(var j=0; j<ab.length; j++)
                {
                   if(ab[j].className == 'latitude')
                      lat = 1*ab[j].title;
                   else if(ab[j].className == 'longitude')
                      lon = 1*ab[j].title;
                }
             }
             else
             {
                lat=1*adr[i].getAttribute('lat');
                lon=1*adr[i].getAttribute('lon');
             }

The rest of the code remains the same.

It isn't too hard to replace Google maps with Yahoo! maps in this implementation. The parsing of the microformat is the heart of it all, after that it's just a matter of using your API of choice.

You can see both formats in action on my reviews of US restaurants. Note that one of the restaurants uses the old format that I've described above, and the other two use the geo microformat. I'll add more in time, and a few from the UK as well.

Update: Calvin Yu has written a Yahoo! Maps implementation of geo.

Update: Switched to use the <address> tag instead of a <div>. Just seems more semantic.

Update: I have a Yahoo! Maps version as well.