UK-House-Prices.com is a web site to see how the prices in a certain area changed over the years using a data set released by the UK government as part of the data.gov.uk initiative.

Here’s a screencast showing the app:

The first step was to get the right data. I was lucky enough to be invited to the initial “hack day” and pre-release of the data and looked around for something to mash up. Initially I wanted to do something with environmental data but I found a lot of it to be very old. Therefore I just did a search for “2009″ at data.gov.uk and found that the house prices data from 1996 to now in England and Wales is available. The plan was set. This was it:

• I wanted to build an interface to show this information that was very fast, very portable and show a nice map of the area next to the numbers.
• I wanted to build this as a web app and as an application for the Yahoo homepage (as I needed to build one as a demo anyways)
• Traffic and speed was the most important issue – as this might get huge.

Cleaning and converting data

I got the spreadsheet and was confronted with my old nemesis: Excel. After saving the sheet as CSV and spending some fun time regular expressions and split() I had the data in a cleaner, and more usable version (JSON, specifically). One fun part is that when there was no data available for a certain area the field was either “..”, “n/a” or just empty. Something to work around. The numbers were also formatted like 100,312 which is nice on the eye but needs un-doing when you want to sort them outside Excel.

Once I had the list of locations and their numbers I wanted to turn them into geographical locations to display maps of the area. For this I used Yahoo Placemaker, especially the YQL table (see an example for Rugby in the YQL console). This is the script I ran over the list of locations:

$out = '';
for($i=0;$i<sizeof($lines);$i++){
  $select = preg_replace('/,.*/','',$lines[$i]);
  $select = preg_replace('/ UA/','',$select);
  $url = 'http://query.yahooapis.com/v1/public/yql?q=select%20match.place.woeId%2Cmatch.place.centroid%20from%20geo.placemaker%20where%20documentContent%20%3D%20%22'.urlencode($select.',uk').'%22%20AND%20documentType%3D%22text%2Fplain%22%20and%20appid%20%3D%20%22%22%20limit%201&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys';
  $ch = curl_init(); 
  curl_setopt($ch, CURLOPT_URL, $url); 
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); 
  $output = curl_exec($ch); 
  curl_close($ch);
  $data = json_decode($output);
  echo '{"place":"'.$select.'",';
  echo '"w":"'.$data->query->results->matches->match->place->woeId.'",';
  echo '"lat":"'.$data->query->results->matches->match->place->centroid->latitude.'",';
  echo '"lon":"'.$data->query->results->matches->match->place->centroid->longitude.'"'."},\n";
  ;
}

That was that – I had a data set I can work with.

Adding more information

The next thing I wanted to add was some more information about the area which meant using maps. As both Yahoo and Google maps have static map versions but are rate limited I wondered if there is a free version of that. And there is. Openstreetmap was the answer, especially the somewhat unofficial API I found with Google. To play safe, I wrote a script that gets the images and I cache it on my server to avoid killing this API.

I also wanted to show currently available houses in the area in case you are looking to buy. For this the natural choice for me was to use Nestoria as they also have an open YQL table (see the Nestoria table in the YQL console). So I used YQL and sorted the results by date:

select * from nestoria.search where place_name="Rugby" | sort(field='updated_in_days')

Using this I can get offers in the area live:

$url = 'http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20nestoria.search%20where%20place_name%3D%22'.urlencode($city).'%22%20|%20sort%28field%3D%27updated_in_days%27%29&format=json&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys&diagnostics=false';
$ch = curl_init(); 
curl_setopt($ch, CURLOPT_URL, $url); 
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); 
$output = curl_exec($ch); 
curl_close($ch);
$data = json_decode($output);
if($data->query->results){
  $i=0;
  $results = array_slice($data->query->results->listings,0,5);
  if(sizeof($results)>0){
    echo '<div class="listings"><h3>Current property listings (powered by <a href="http://www.nestoria.co.uk/">Nestoria</a>) <input type="button" class="hide" value="hide"></h3><ul>';
    foreach($results as $r){
      echo '<li><img src="'.($r->thumb_url).'" alt="">';
      echo '<h4><a href="'.($r->lister_url).'">'.($r->title).'</a></h4>';
      echo '<p> Price: '.($r->price_formatted).', Type of property: '.ucfirst($r->property_type).', Updated: '.($r->updated_in_days_formatted).' ('.($r->updated_in_days).' days)</p>';
      echo '<p>Listed at: '.($r->datasource_name).' by '.($r->lister_name).'.</p>';
      echo '</li>';
    }
    echo '</ul></div>';
  }
}

Finding a charting solution

Adding interactive charts was the next step. I had a few issues with that:

• While Google charts are full of win, they are rate-limited and I didn’t want to pull images. As the app was also meant to become a Yahoo application every image would have to be run through Caja for safety reasons which slowed it down.
• Canvas and Flash solutions like YUI charts or Raphael were also not possible because of the performance of the YAP app.

So I wrote my own pure CSS bar charts to work around that issue.

Building the API

I put all these solutions together and built a small API that will give me the search results with three parameters: the location as an id and the start and end of the time range.

http://uk-house-prices.com/graphs.php?loc=1&start=10&end=20

Building the interface

To build the interface, I went all-out YUI. I took the YUI grids builder to create the main layout, the AutoComplete demo, the dual slider demo and the button and put them all together. Add an Ajax call to the form, and you are done. OK, I admit, there was quite a bit of cleaning up to be done :)

Notice that I am using progressive enhancement all the way. Without JavaScript you get dropdowns:

That’s it

The next thing I had to do is move the app over to the Yahoo Application Platform which was easy as I based it on an API - but this is another blog post :)

Right now I am a very happy bunny:

The reason is that more and more great data sources and mashup competitions are cropping up in my surroundings right now. One of the things that got me most excited is that the UK government is opening its data and calls for a mashup competition. I’ve been at the Guardian last week before leaving for Vienna and Prague to look at the data sources and plans in place and there is a lot of cool data to play with. I found the data format of RDF and SPARQL a bit on the confusing side (yes, it is open, but why make it hard, SPARQL is not the most intuitive of languages) and it seems there will be easier ways to get to the data. If you are interested in the data and the competition, check out the Government open data google group.

Me pimping that on Twitter also brought the Australians out of the undergrowth pointing out the Australian government data competition. Sweet stuff, but zipped excel sheets are not really usable data sources. Maybe hosting them on Google Docs and writing a YQL table to point to them would be a more successful approach?

The next thing I got involved in is the Science Museum of London’s Cosmos and Culture mashup competition. There’s a blog post with more details and I’ve done an interview with Mia Ridge, Lead Developer at the Science Museum where she points out that there is a wiki to collaborate in teams to hack something for the competition. Mia also talked about a wiki of all kind of Museum APIs.

As mentioned before here and on the YDN blog, the National Maritime Museum in London still has a call for developers to astrotag photos and mashup that data. The driving force behind this is Jim O’Donnell who uses YQL extensively to make this data very easy to use.

It is on! Get your keyboards ready, give YQL some thought and let’s show the government and museums what stories geeks can find in data sets.

Things that made me happy this morning:

• 8 bit trip is a 1500 hour work stop-motion movie with lego bricks.
• James Gibbons collected a great list of assistive technology videos
• Labuat has a wonderful interactive song and ink thing in Flash
• If you need a UK postcode API there is Ernest Maples
• SVGWeb is a wrapper library for SVG that renders MSIE results in Flash
• The boy who heard too much is a very cool story about the blind phreaker “Lil’ hacker”
• The enigma machine in Java is fun to play with
• An attempt at version control for HTML using ins and del elements
• Codepad is much like pastebin.com but also executes and validates the code you share in it

I just thought my laptop was broken as it was smelling of burning plastic in the office, but it turned out to be a massive fire some streets down in Soho, London (that is not Soho,NYC).

I am very impressed with the job the firefighters have done to stop the fire from spreading. It also shows once again that it is great to have a camera and a fast internet connection handy. As the above video and all the other photos of the Soho fire I’ve taken are creative commons, the Metro newspaper used them on their site.

One thing web developers who do not work in large corporations or with the public sector or education often forget is that there’s a lot of red-tape and checkbox ticking to be done before you even start a line of code. This get worse once there has been a decision made or a guideline in place, as replacing or upgrading those slips far down the list of need-to-do’s.

The web is a large and confusing place and the fact that you just cannot control or demand the setup your visitors use to come to your site and consume what is there can be frustrating. To me, it is what the web is about and I love the challenge of the unknown. Official sites, however, do not revel in unknowns and challenges and try to help webmasters to release quickly by cutting down on things to support.

Last friday, the UK government’s Central Office of Information (COI) published a public consultation on browser standards for public sector websites which misses the mark of good advice by quite a bit.

Bruce Lawson checked the guidelines in detail and responded to them on the WaSP blog

I agree with all that is said there, and humbly point the COI to the graded browser support my employer applies to steer the wild web into easier supportable channels.

There’s a comment form on the bottom of the page on the guidance site that gives you a chance to react to this. It might not mean much, but let’s not forget that if we can have an impact on the public service, it’ll mean a lot more web sites out there that do the right thing. These are the areas we should concentrate on – if your blog doesn’t render properly that is much less of an issue than you not being able to pay a parking ticket or sign up your kids for school.