January « 2010 « OUseful.Info, the blog…

Archive for January 2010

Skim.it – Like Digress.it, But With Ratings Rather than Comments?

A couple of weeks ago, whilst dozing to the ITConversations podcast channel, I started daydreaming around the conversation that was going on in the Mitch Ratcliffe /Booksahead.com episode of Phil Windley’s Technometria podcast.

The discussion was on the topic of the future of the book, particularly with respect to annotating books and ebooks (in a manner similar to the way we support paragraph level comments in WriteToReply).

Annotating text with text (such as comments) requires quite a lot of effort on the part of the reader/annotator, and is perhaps one reason why it can be quite hard getting folk to engage with commenting static documents (I’m sure there are lots of other factors, too! ;-)

So if we think of things like the Community Engagement Pyramid:

Yahoo Engagement pyramid

or the Social Technographics Ladder of Participation:

Social Technographics Ladder

then we see that there are various levels of engagement by – and participatory effort required from – visitors to a web site.

If we consider documents published on WriteToReply, one of the things we hope to facilitate is discussion around particular areas of the document. Lively discussions – lots of comments on a particular paragraph, or section – is one way of generating a signal that highlights “interesting” areas of a document. Web traffic analytics showing large amounts of traffic to, and reasonable dwell times on, particular pages provides another source of “interestingness” information; and so on.

But are we missing a trick?

Way back in the days when I used to print out lots of reading material, I used to skim read documents (even then!) and mark paragraphs that were somehow important with a vertical line in the margin so that I could easily return to them, or fold a page corner to “bookmark” a particular page or section. Occasionally, I would also scrawl notes in the margin, or underline particular paragraphs. But the turned page corners and the lines in the margin were the most efficient ways (for me) of marking the important parts of a text so that I could then refer to them in detail at a later time.

The commenting came later…

So what might a corollary be in WriteToReply? Each paragraph has a unique URI, so it would be possible to bookmark interesting paragraphs either within the browser, or using a social bookmarking tool such as delicious. Hovering over the linked paragraph number raises a pop up containing the text of the paragraph and a link to it (Note to self: clicking in the link box should automatically select all the text???)

Clicking through on a bookmarked link takes you to the page the paragraph exists on with the bookmarked paragrah highlighted:

If single item RSS/JSON feeds for each uniquely identified paragraph are enabled, it is straightforward (in Javascript at least) to render a page containing just the content from a list of the bookmarked paragraphs.

But what other low effort routes to engagement are there that might help an individual keep track of areas of a document they may want to return to, or that might allow the crowd sourced discovery of “interesting” areas of a document? How about ratings? How about a complement to the paragraph level commenting that the digress.it WordPress theme we use on WriteToReply offers that offers paragraph level ratings?

And in the same way that digress.it is capable of generating comment streams for each commenter, how about a similar facility that would allow me to look at all the paragraphs, sections or pages that I have commented, sorted either in the order they appear in the document, or additionally by the number of stars I have rated them?

When I read long documents, I do it in an iterative fashion. At the moment, we don’t necessarily make that very easy to do – or obvious how to do it. Maybe a ratings based approach would help?

PS the source code for the digress.it theme is available from the digress.it: WordPress plugins page under a GPL version 2 license. If you fancy creating a complementary “skim.it” theme using ratings rather that comments, post a comment here ;-)

Written by Tony Hirst

January 28, 2010 at 10:43 am

Posted in Thinkses, WriteToReply

Tagged with ratings

Two Variants of Google Blogsearch?

Creating Database Query Forms in Google Spreadsheets – Sort Of

with 9 comments

It’s all very well using a Google spreadsheet as a database, but sometimes you just want to provide a simple form to let people run a particular query. Here’s a quick way of doing that within a Spreadsheet…

So for example: Can you help me crowd source a solution?. The problem is as follows:

Students will make five choices from a list of over 200 projects that have been anonymised… We will give each project a code, and have already entered all the details into an excel sheet so we can tie the project code to the supervisor.

We need a solution that will enable students to enter their project code and then have the title of the project displayed as a check to make sure they have entered the code correctly. The list of projects is just too long for a drop down list, even when split by department (around 50 in each).

Does anyone have any suggestions of tools that we can use for students to submit this type of information, so that we get it in a format that we can use, and they get confirmation of the project titles they have chosen? A simple google form isn’t going to hack it!

Here’s one way…

Create a “form” – the text entry cell can be highlighted by setting the background colour from the spreadsheet toolbar:

Construct a query. In this case, I need to select three results columns (H, I and J) from another sheet (‘Sheet1′, the one that acts as the database and contains the project codes) so the query will be of the form “select H,I,J where H contains “BIOCHEM”; the search term (“BIOCHEM”) is pulled in from the query form show above:

=concatenate(“select H,I,J where H contains ‘”,B2,”‘”)

(As a rule of thumb, if you want your query to select cells A, D, AC, the range set in the first part of the query that defines the database should span the first to the last column in the select range (Sheet1!A:AC, for example).)

By using the contains relation, this query will generate a set of results that are, in effect, a list of auto-complete suggestions as the result of a searching on a partially stated query term.

Assuming I have placed the query in cell A4, I can automatically get the results from the query as follows:

Note that it would be possible to hide the query generator (the contents of cell A4) in another sheet and just have the search box and the results displayed in the user interface sheet.

Another approach is to query the spreadsheet via its API.

So for example, if the original spreadsheet database was published as a public document, we could also grab the results as an HTML table via an API using a URI of the form:

http://spreadsheets.google.com/tq?tqx=out:html &tq=select%20H%2CI%2CJ%20where%20H%20contains%20%22SEARCHTERM%22 &key=SPREADSHEETKEY

Setting out:csv would return the results in comma separated variable format, so we could create a Yahoo pipes interface to query the form, for example:

Here’s how:

What would be really useful would be if the Google/Yahoo widget options for the feed respected the form elements, rather than just generating a widget that displays the feed corresponding to the current Run of the pipe with the provided search terms.

Building such a widget is something I need to put on my to do list, I guess?! Sigh…

Written by Tony Hirst

January 26, 2010 at 10:50 am

Posted in newsrw, Pipework

Tagged with Google spreadsheet

Open University Adopts Google Apps For Education

with 10 comments

…
And so it came to pass that The Open University announced that it was going to adopt Google Apps for Education, and in one fell swoop sign up over 150,000 students to the platform.

And what bounteous riches would those students henceforth be able to benefit from, with “a service level agreement with higher levels of availability than [the OU] could achieve itself”:

email: “students will be offered their own Gmail accounts with addresses ending in @my.open.ac.uk”
calendar: when the OU’s student calendaring team held a consultation about future plans a couple of years or so ago, I lobbied hard for iCal/ics feed support, as well as tentatively suggesting that we might be use calendar feeds to transport payloads (documents, or audio files for example) either to students or within the context of a feed powered VLE. (I think I also suggested that they just not bother and embed Google calendars instead, and did a working demo to show what it could look like). So here’s hoping that course calendars also get put into Google Calendar… But what’s gonna happen to all the effort that was put into the Moodle Calendaring support? I guess that if they’re making feeds available, it’ll be low overhead to just pipe the info into student’s personal calendars?
online document creation and space for shared documents: I wonder – could this be used as a way of getting students to submit their assessments? By sharing them with markers, or Exams and Assessment? As for online documents – Google Gears and (in the fullness of time) full support for HTML5 local storage means that docs can also be edited offline; and even if Google docs don’t work natively in smartphone browsers, the app economy will probably have produced something equivalent;-) As far as storage goes, I have no idea what the current state of the OU eportfolio is…? I always favoured using a service like Box.net (OU Open Sources Moodle ePortfolio – But What Flavour is the API?), which as it happens opened up a comprehensive in browser document preview service this week (Reinventing how Businesses Share with Cloud Content Management).
instant messaging and contact management: I don’t think our current VLE or StudentHome pages support contacts, presence indicators, or chat? (So for example, in the last presentation of T151, and at the request of students, I embedded a Meebo chat room into a wiki page in the “Course Labs” area of the course I set up to do dangerous and unapproved by the system type things. The chat wasn’t used much though, because it was: a) hard to find; b) not obvious when other people might be using it.) As far as Contacts go, I personally think we should view users with OUCUs as the population of a social network, and develop internal apps like Course Profiles and My OU Story to service that network. IMVHO, of course…

No Google Reader though? Because that’s not really a producer/creator tool, it’s mor of being just a consumer tool?

Here’s what the Goog has to say about Google Apps for Edu:

One thing that interests me in the short to medium term is the extent to which course teams will be encouraged to integrate content that is pulled from Google Apps into the VLE. Around about this time last year, I set up a quick survey using Google Forms for a course. Issues around the need for Google authentication to view the results meant I need to come up with a workaround for producing results charts (Creating Your Own Results Charts for Surveys Created with Google Forms). It’d be easy enough to mbd this charts in th VLE, but that would be competing with the VLE’s own survey tool (which I assume it has got?). So a question I have is to what extent we should drink the Google Kool Aid and spend effort not on developing Moodle apps, features and extensions (that is, apps that use Moodle datastores) and instead focus on apps that use Google as the backend. As for what widget containers to us (bespoke Moodle widgets or Google gadgets, for example), a standardisd intermediate container such as Wookie might be a better bet for a couple of reasons: 1) if we dump Moodle, we can take the widgets with us; 2) if we drop a particular back end service for a widget and replace it with another, we can retain the WIdget UI and just replace the service.

Anyway – enough. As and when this rolls out, it’ll be interesting to see what students do with it, and to what extent OU developers start developing around the apps. As far as the VLE goes, I’d be interested to know whether the powers that be are keen for us to look at ways of integrating Google services into our course delivery. Or maybe they haven’t thought about that yet? ;-)

PS to complement this, see also @andypowe11′s post On the use of Microsoft SharePoint in UK universities

Written by Tony Hirst

January 22, 2010 at 1:40 pm

Posted in OU2.0

Tagged with googl apps for edu, open university

Using Google Spreadsheets Like a Database – The QUERY Formula

with 25 comments

In this year’s student satisfaction tables, which universities have a good teaching score but low employment prospects? How would you find out? In this post, you’ll find out…

Whether or not it was one of my resolutions, one of the things I want to do more this year is try to try to make more use of stuff that’s already out there, and come up with recipes that hopefully demonstrate to others how to make use of those resources.

So today’s trick is prompted by a request from @paulbradshaw about “how to turn a spreadsheet into a form-searchable database for users” within a Google spreadsheet (compared to querying a google spreadsheet via a URI, as described in Using Google Spreadsheets as a Database with the Google Visualisation API Query Language).

I’m not going to get as far as the form bit, but here’s how to grab details from a Google spreadsheet, such as one of the spreadsheets posted to the Guardian Datastore, and query it as if it was a database in the context of one of your own Google spreadsheets.

This trick actually relies on the original Google spreadsheet being shared in “the right way”, which for the purposes of this post we’ll take to mean – it can be viewed using a URL of the form:

http://spreadsheets.google.com/ccc?key=SPREADSHEETKEY&hl=en

(The &hl=en on the end is superfluous – it doesn’t matter if it’s not there…) The Guardian Datastore folks sometimes qualify this link with a statement of the form Link (if you have a Google Docs account).

If the link is of the form:
http://spreadsheets.google.com/pub?key=SPREADSHEETKEY
just change pub to ccc

So for example, take the case of the 2010-2011 Higher Education tables (described here):

http://spreadsheets.google.com/ccc?key=reBYenfrJHIRd4voZfiSmuw

The first thing to do is to grab a copy of the data into our own spreadsheet. So go to Google Docs, create a new spreadsheet, and in cell A1 enter the formula:
=ImportRange(“reBYenfrJHIRd4voZfiSmuw”,”Institutional Table!A1:K118″)

When you hit return, the spreadsheet should be populated with data from the Guardian Datastore spreadsheet.

So let’s see how that formula is put together.
=ImportRange(“reBYenfrJHIRd4voZfiSmuw”,”Institutional Table!A1:K118″)

Firstly, we use the =ImportRange() formula, which has the form:
=ImportRange(SPREADSHEETKEY, SHEET!RANGE)

This says that we want to import a range of cells from a sheet in another spreadsheet/workbook that we have access to (such as one we own, one that is shared with us in an appropriate way, or a public one). The KEY is the key value from the URL of the spreadsheet we want to import data from. The SHEET is the name of the sheet the data is on:

The RANGE is the range of the cells we want to copy over from the external spreadsheet.

Enter the formula into a single cell in your spreadsheet and the whole range of cells identified in the specified sheet of the original spreadsheet will be imported to your spreadsheet.

Give the sheet a name (I called mine ‘Institutional Table 2010-2011′; the default would be ‘Sheet1′).

Now we’re going to treat that imported data as if it was in a database, using the =QUERY() formula.

Create a new sheet, call it “My Queries” or something similar and in cell A1 enter the formula:

=QUERY(‘Institutional Table 2010-2011′!A1:K118,”Select A”)

What happens? Column A is pulled into the spreadsheet is what. So how does that work?

The =QUERY() formula, which has the basic form =QUERY(RANGE,DATAQUERY), allows us to run a special sort of query against the data specified in the RANGE. That is, you can think of =QUERY(RANGE,) as specifying a database; and DATAQUERY as a database query language query (sic) over that database.

So what sorts of DATAQUERY can we ask?

The simplest queries are not really queries at all, they just copy whole columns from the “database” range into our “query” spreadsheet.

So things like:

=QUERY(‘Institutional Table 2010-2011′!A1:K118,“Select C”) to select column C;
=QUERY(‘Institutional Table 2010-2011′!A1:K118,“Select C,D,G,H”) to select columns C, D, G and H;

So looking at copy of the data in our spreadsheet, import the columns relating to the Institution, Average Teaching Score, Expenditure per Student and Career Prospects, I’d select columns C, D, F and H:

like this:
=QUERY(‘Institutional Table 2010-2011′!A1:K118,“Select C,D, F,H”)
to give this:

(Remember that the column labels in the query refer to the spreadsheet we are treating as a database, not the columns in the query results sheet shown above.)

All well and good. But suppose we only want to look at institutions with a poor teaching score (column D), less than 40? Can we do that too? Well, yes, we can, with a query of the form:

“Select C,D, F,H where D < 40"

(The spaces around the less than sign are important… if you don’t include them, the query may not work.)

Here’s the result:

(Remember, column D in the query is actually the second selected column, which is placed into column B in the figure shown above.)

Note that we can order the results according to other columns to. So for example, to order the results according to increasing expenditure (column F), we can write:

“Select C,D, F,H where D < 40 order by F asc"

(For decreasing order, use desc.)

Note that we can run more complex queries too. So for example, if we want to find institutions with a high average teaching score (column D) but low career prospects (column H) we might ask:

“Select C,D, F,H where D > 70 and H < 70"

And so on…

Over the nect week or two, I’ll post a few more examples of how to write spreadsheet queries, as well as showing you a trick or two about how to build a simple form like interface within the spreadsheet for constructing queries automatically; but for now, why try having a quick play with the =QUERY() formula yourself?

Written by Tony Hirst

January 19, 2010 at 2:21 pm

Posted in Data, newsrw, Tinkering

Tagged with datajourn, datastore, news:rewired, newsrw

Visualising Traffic Count Data from transport.data.gov.uk

with 6 comments

In a couple of posts last year (Hackable SPARQL Queries: Parameter Spotting Tutorial and First Dabblings With Pipelinked Linked Data) I started to explore how it might be possible to use Yahoo Pipes as an environment for sharing – and chaining together (albeit inefficiently) – queries to the data.gov.uk open transport data datastore.

Those posts concentrated on querying the datastore in order to find the location of traffic monitoring points according to various search criteria. In this post, I’ll show you one way of visualising traffic count data from a traffic count point using Many Eyes Wikified.

The first thing we need to do is come up with a query that will pull traffic monitoring data back from the transport datastore. My first point of call for finding a query to get me started is usually to search over the data.gov.uk Google group archive in my mailbox. As ever, @jenit had posted a ‘getting started’ solution:-)
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX traffic: <http://transport.data.gov.uk/0/ontology/traffic#> PREFIX geo: <http://www.w3.org/2003/01/geo/wgs84_pos#> PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?direction (SUM(?value) AS ?total) WHERE { <http://transport.data.gov.uk/0/id/traffic-count-point/7422> traffic:count ?count . ?count a traffic:Count ; traffic:category <http://transport.data.gov.uk/0/category/bus> ; traffic:direction ?direction ; traffic:hour ?hour ; rdf:value ?value . FILTER ( REGEX(str(?hour), "^2008-") ) } GROUP BY ?direction

I tweaked this a little (using guesswork and pattern matching, rather than understanding, Chinese Room style;-) to come up with a tweaked query that appears to pull out traffic count data for different categories of vehicle on a particular day from a particular monitoring point:

SELECT ?vehicle ?direction ?hour (SUM(?value) AS ?total) WHERE { <http://transport.data.gov.uk/0/id/traffic-count-point/7422> traffic:count ?count . ?count a traffic:Count ; traffic:category ?vehicle ; traffic:direction ?direction ; traffic:hour ?hour ; rdf:value ?value . FILTER ( REGEX(str(?hour), "^2008-05-02T") ) } GROUP BY ?vehicle ?direction ?hour

I then turned this into a Yahoo Pipes query block, using the date and traffic monitoring point as input parameters:

Here’s how to do that:

Having got the query into the pipes ecosystem, I knew I should be able to get the data out of the pipe as JSON data, or as a CSV feed, which could then be wired into other web pages or web applications. However, to get the CSV output working, it seemed like I needed to force some column headings by defining attributes within each feed item:

To tidy up the output a little bit more, we can sort it according to time and day and traffic count by vehicle type:

It’s then easy enough to grab the CSV output of the pipe (grab the RSS or JSON URI and just change the &_render=rss or &_render=json part of the URI to &_render=csv) and wire it into somewhere else – such as into Many Eyes WIkified:

Doing a couple of quick views over the data in Many Eyes wikified, it seemed as if there was some duplication of counting, in that the numbner of motor vehicles appeared to be the sum of a number of more specific vehicle types:

Data in Many Eyes WIkified

Looking at the car, van, bus, HGV and motorbike figures we get:

SUmmed totals

So I made the judgement call to remove the possibly duplicate motor-vehicle data from the data feed and reimported the data into Many Eyes WIkified (by adding some nonsense characters (e.g. &bleurghh to the feed URI so that Many Eyes thought it was a new feed.)

It was then easy enough to create some interactive visualisations around the traffic data point. SO for example, here we have a bubble chart:

Do you spot anything about traffic flow going North versus South at 8am compared to 5pm?

Lets explore that in a little more detail with a matrix chart:

This shows us the relative counts for different vehicle types, again by time of day. Notice the different distribution of van traffic compared to car traffic over the course of the day.

A treemap gives a slightly different take on the same data – again, we can see how there is a difference between North and South flowing volumes at different times of day within each category:

One thing that jumps out at me from the treemap is how symmetrical everything seems to be at noon?!

All the above visualisations are interactive, so click through on any of the images to get to the interactibve version (Java required).

As to how to find traffic monitoring point IDs – try this.

PS a disadvantage of the above recipe is that to generate a visualisation for a different traffic point, I’d need to use the desired parameters when grabbing the CSV feed from the pipe, and then create new Many Eyes Wikified data pages and visualisation pages. However, using nothing more than a couple of web tools, I have managed to prototype a working mockup of a visualisation dashboard for traffic count data that could be given to a developer as a reference specification for a “proper” application. And in the meantime, it’s still useful as a recipe… maybe?

PPS While I was playing with this over the weekend, it struck me that if school geography projects ever do traffic monitoring surveys, it’s now possible for them to get hold of “real” data. If there are any school geography teachers out there who’d like to bounce around ways of making this data useful in a school context, please get in touch via a comment below :-)

Written by Tony Hirst

January 18, 2010 at 11:29 am

Posted in Data, Pipework, Tinkering, Visualisation

Tagged with data.gov.uk, Many Eyes

Reusable Presentations, On the Road

with 4 comments

A couple of years ago I dabbled with a simple web page application that would allow a user to run a presentation based around a set of web pages (FeedShow Link Presenter (I have no idea if it still works…!)). I’ve been thinking about presentations again over the last couple of weeks, in particular:

- pondering how nice it would be to have a site that combined some of the functionality of Slideshare and flickr, allowing folk to upload their presentation to a site, at which point it would automatically be disaggregated into component slides-as-images, each with it’s own URI, so that individual slides could be easily discovered, shared and reused; Slideshare lets you get a permalink for a particular slide within a slide deck (just add “/N” to the end of the presentation URL to view slide N in the deck (example), but I also want to be able to take and reuse individual slides natively, rather than just screengrabbing them;

- looking for ways of remotely controlling a presentation on my laptop using an iPod Touch or Android phone; things like the Keynote remote, for example, which I really need to try out, the iClickr Powerpoint remote or the pitchr Android app which appears to let you stream presentations from your phone to a computer connected to a big screen/projector (Pitchr video; I can’t help thinking there’s an opportunity for “hot screens” that users can tie their own devices to, e.g. in planes and trains; I donlt know if the Camvine have given any thought to that sort of screen use?):

It seems that other folk have been thinking about presentations too, as a couple more presentation related technologies have appeared across my feeds in just the last few days.

So for example – a couple of new to me Slideshare features are personal playlists (feed powered slideshows and documents – because Slideshare now lets you upload docs, much like Scribd does), and presentation packs (feed powered collections of slideshows):

I don’t think Slideshare yet offers WordPress friendly embed codes for presentation packs (one of the downsides of using WordPress.com for blog hosting is that there is a restrictive policy on what can be embedded in a blog), but this is what they look like:

On the portable presentation front, MightyMeeting provides a way of controlling a web based presentation from your phone:

This looks like it could be a really handy app, not only as a remote but also as tool for giving presentations while on the hoof, assuming your phone can support calls and a web browser/app at the same time?

I’m not sure if they’ve considered telephony integration using something like Ribbit? Voice/telephony integration with web apps seems to be something that hasn’t yet taken off, possibly because things like Ribbit use Flash, which is still hard to find on many mobile devices, possibly because of the policies of the carriers, who still rely on voice traffic as a revenue generator. But give it a year or two, and I think voice supplemented apps could be a growth area?

PS don’t forget you can also embed Youtube videos within slideshare presentations, as this example shows:

I think this could be a really good way of providing context around small collections of short Youtube videos and an effective way of creating short lessons around video based OERs. Anyone know of any examples of such an approach being taken anywhere?

Written by Tony Hirst

January 16, 2010 at 1:59 pm

Posted in Thinkses

Tagged with mobile presentations

My Presentation for News:Rewired – Doing the Data Mash

with one comment

For once, I didn’t put links into a presentation, so here instead are the link resources for my News:Rewired presentation:

(If I get a chance over the next week or so, I may even try to make a slidecast out of the above…)

The link story for the presentation goes something like this:

If there’s something “dataflow” related you’d like see explored here, please leave a request as a comment and I’ll see what I can do :-) I’ve also started a newsrw) category (view it here) which I’ll start posting relevant content to; (see also the datajourn tag).

Written by Tony Hirst

January 14, 2010 at 3:19 pm

Posted in newsrw, Presentation

Tagged with news:rewired, newsrw

OUseful.Info, the blog…

Archive for January 2010

Skim.it – Like Digress.it, But With Ratings Rather than Comments?

More Link Pollution – This Time from WordPress.com

Two Variants of Google Blogsearch?

Creating Database Query Forms in Google Spreadsheets – Sort Of

Open University Adopts Google Apps For Education

Using Google Spreadsheets Like a Database – The QUERY Formula

Visualising Traffic Count Data from transport.data.gov.uk

Reusable Presentations, On the Road

My Presentation for News:Rewired – Doing the Data Mash

Virtualisation and the Chances of a Google Chrome (Virtual) App(liance) Store

Contact

Email Subscription

My Other Blogs

Custom Search Engines

Page Hacks

OUseful feedthru bookmarks...

OUseful tweets

Recent Posts

Top Posts

Recent OU/BBC programmes

OUseful Video favourites

Archives

Follow “OUseful.Info, the blog...”