Christian Heilmann

Posts Tagged ‘conversion’

Converting a data table on the web to an autocomplete translator with YQL and YUI

Monday, August 31st, 2009

During the Summer of Widgets hack event last weekend, Tomas Caspers, Nina Wieland and Jens Grochdreis had the idea of creating a translation tool to translate from the local Cologne accent to German and back.

For this, they found a pretty impressive data source on the web, namely this web site by Reinhard Kaaden. The task was now to turn this into a fancy interface to make it easy for people to enter a “Kölsch” term and get the German equivalent and vice versa. For this, I proposed YQL und YUI and here is a step-by-step explanation of how you can do it.

You can see the final outcome here: Deutsch-Kölsch übersetzer
or by clicking the screenshot:

Deutsch-Koelsch Uebersetzer by  you.

Step 1: Retrieve and convert the data

A very easy way to get data from the web is using YQL. In order to get the whole HTML of the source page all we had to do is select * from html where url=''. That gave us the whole data though and we only wanted to get the content of the tables.

Using Firebug and looking up some XPATH we came up with the following statement that would give us the language pairs as German-Koelsch inside paragraphs: //table[1]/tr/td/p[not(a)]. The not(a) statement is needed to filter out the A-Z navigation table cells. We chose JSON as the output format in YQL and dktrans as the callback function name.

All in all this gave us a URL that would load the data we wanted and send it to the function dktrans once it has been pulled:

All that had to go in there to create the Autocomplete controls was more or less 100% copied from the simple Autocomplete example on the YUI site.
First thing is to get some handlers to the input fields I want to populate with the translation data:

var di = YAHOO.util.Dom.get(‘deutschinput’);
var ci = YAHOO.util.Dom.get(‘koelschinput’);

Then you need to instantiate the data source for the autocomplete and give it the language array. As a responseSchema you can define a field called term:

dktransdata.cologneDS = new YAHOO.util.LocalDataSource(
dktransdata.cologneDS.responseSchema = {fields:[‘term’]};

Next you need to instantiate the AutoComplete widget. This one gets three parameters: the input element, the output container and the data source. You can set useShadow to get a small dropshadow on the container:

dktransdata.cologneAC = new YAHOO.widget.AutoComplete(
dktransdata.cologneAC.useShadow = true;

This turns the input of the Cologne language into an Autocomplete, but it doesn’t yet populate the other field. For this we need to subscribe to the itemSelectEvent of the AutoComplete widget. The event handler of that event gets a few parameters, the text content of the chosen element is the first element of the third element in the second parameter (this is explained in detail on the YUI site). All you need to do is set the value of the other field to the corresponding element of the translation maps we defined:

function cologneHandler(s,a){
di.value =[a[2][0]];

All that is left is to do the same for the German to Cologne field:

dktransdata.germanDS = new YAHOO.util.LocalDataSource(
dktransdata.germanDS.responseSchema = {fields:[‘term’]};
dktransdata.germanAC = new YAHOO.widget.AutoComplete(
dktransdata.germanAC.useShadow = true;
function germanHandler(s,a){
ci.value = dktransdata.kd[a[2][0]];

Step 5:Putting it all together

You can see the full source of the translation tool on GitHub and can download it there, too.
Of course we are not really finished here as this only works in JavaScript environments. As the translator was meant to be a widget though, this was not an issue. That the autocomplete does not seem to work on mobiles is one, though :).

Making this work without JavaScript would be pretty easy, too. As the data is returned in JSON we can also use this in PHP and write a simple form script If wanted, I can do that later.

Postcode from latitude and longitude or even IP – fun with Geo APIs and YQL

Tuesday, June 9th, 2009

One of the more complex things about GeoFill was to get postcode information from an IP. However with a collection of APIs and a collated YQL statement even this was possible.

The first thing I needed to get was the IP of the user. This is done with the GeoIP API based on the GeoLite API from MaxMind. This is available as an open table in YQL and can be used thus:

select * from ip.location where ip=”“

Try the lookup in the console or check the lookup result

Response”: {
“Ip”: “”,
“Status”: “OK”,
“CountryCode”: “US”,
“CountryName”: “United States”,
“RegionCode”: “06”,
“RegionName”: “California”,
“City”: “Sunnyvale”,
“ZipPostalCode”: “94089”,
“Latitude”: “37.4249”,
“Longitude”: “-122.007”,
“Gmtoffset”: “-8.0”,
“Dstoffset”: “-7.0”

This gives us a lot of information. What’s really important here is latitude and longitude, as this can be used in the flickr.places API to get a where on earth ID which is a much more defined identifier:

select * from flickr.places where (lat,lon) in (
select Latitude,Longitude from ip.location where ip=””

Try the flickr places call in the console or check the flickr result

“places”: {
“accuracy”: “16”,
“latitude”: “37.4249”,
“longitude”: “-122.007”,
“total”: “1”,
“place”: {
“latitude”: “37.371”,
“longitude”: “-122.038”,
“name”: “Sunnyvale, California, United States”,
“place_id”: “P_ls_fybBJwdHP8t”,
“place_type”: “locality”,
“place_type_id”: “7”,
“place_url”: “/United+States/California/Sunnyvale”,
“timezone”: “America/Los_Angeles”,
“woeid”: “2502265”


Here the interesting part is the woeid which we can use to dig deeper into geo.places:

select * from geo.places where woeid in (
select place.woeid from flickr.places where (lat,lon) in (
select Latitude,Longitude from ip.location where ip=””


Try the geo places call in the console or check the geo places result

The result is all the information you’d ever want.

“place”: {
“lang”: “en-US”,
“xmlns”: “”,
“yahoo”: “”,
“uri”: “”,
“woeid”: “28751237”,
“placeTypeName”: {
“code”: “22”,
“content”: “Suburb”
“name”: “Fairgrounds”,
“country”: {
“code”: “US”,
“type”: “Country”,
“content”: “United States”
“admin1”: {
“code”: “US-CA”,
“type”: “State”,
“content”: “California”
“admin2”: {
“code”: “”,
“type”: “County”,
“content”: “Santa Clara”
“admin3”: null,
“locality1”: {
“type”: “Town”,
“content”: “San Jose”
“locality2”: {
“type”: “Suburb”,
“content”: “Fairgrounds”
“postal”: {
“type”: “Zip Code”,
“content”: “95112”
“centroid”: {
“latitude”: “37.326611”,
“longitude”: “-121.878441”
“boundingBox”: {
“southWest”: {
“latitude”: “37.275379”,
“longitude”: “-121.89254”
“northEast”: {
“latitude”: “37.330879”,
“longitude”: “-121.808723”


YQL is so the bomb to get web data as XML or JSON

Friday, December 12th, 2008

Yesterday I wrote a blog post on YDN about opening the web covering curl, pipes and YQL and today I did a more detailed deep-dive on Ajaxian about how YQL can help you to convert the web to JSON.

Suffice to say, I like YQL a lot – it is the command line interface to the web (and a text version of Yahoo Pipes). Go and play with it yourself:

YQL console

As explained in the Ajaxian article, all the non-authentication web services can be accessed through a public REST API. Simply add your YQL statement to and add a format=json parameter and a callback parameter with the name of your callback function and you are set.

This would for example to allow you to search for rabbit images on the web and display them quick and dirty with a few lines of JavaScript:

YQL allows you to access any freely available data service and even scrape HTML, how cool is that?

Currency conversion API on a shoestring

Saturday, June 21st, 2008

Someone just came to our table at Mashed08 and asked if Yahoo! offers a currency conversion API. We don’t, but a few lines of PHP allows you to get the information from the Yahoo finance site:

function convert($from,$to){
$url= ‘’.$from.’&to=’.$to.’&submit=Convert’;
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$feed = curl_exec($ch);
return $cells[1][1];

echo convert(‘USD’,’GBP’);

There’s a whole list of currency codes available on oanda.

A few more lines turns this into a JSON API:

$from = $_GET[‘from’];
$to = $_GET[‘to’];
$callback = $_GET[‘callback’];
if(preg_match(“/[A-Z|a-z]{3}/”,$to) && preg_match(“/[A-Z|a-z]{3}/”,$from)){
$to = strToUpper($to);
$from = strToUpper($from);
$url= ‘’ .
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, $url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
$feed = curl_exec($ch);
$out = ‘{“from”:”’.$from.’”,”to”:”’.$to.’”,”factor”:”’.$cells[1][1].’”}’;
} else {
$out = ‘{error not convert currencies, are you sure about the names?”}’;

} else {
$out = ‘{error Currency format, must be three letters”}’;

$out = $callback.’(‘.$out.’)’;
} else {
$out = ‘{error callback method name”}’;

echo $out;

You have several parameters:

  • from (mandatory): three letter currency code (upper or lower case)
  • to (mandatory): three letter currency code (upper or lower case)
  • callback (optional): the name of the callback method that should be wrapped around the resulting object

If something goes wrong, the API will return an object with an error property, otherwise you’ll get an object with three properties:

  • from: the original currency
  • to: the target currency
  • factor: the conversion factor

Say you store this as convert.php somewhere, then you could do the following:

This is a terrible dirty hack and if Yahoo finance ever changes their HTML (and they will), this will cease to work.