example mashups – part II


image by schwarz published under CC BY-NC-ND 2.0

Now, last time when we left off, the site was in what you’d call a colossal mess. Trashed wordpress install, and still no interesting visualisation. So, how to improve things?

We’ll start by doing what I suggested last time – mapping off with a less ambitious dataset, based on LGAs (Local Government Areas) instead of suburbs. Here it is:

income by LGA

I even made a little chart. And look, since I am no longer quite as bored of CDATA as I was last week, I made another other spreadsheet based on percentage of people doing volunteer labour, by LGA.

Now, I’m keen to graph these data sets against each other. And I can, using google spreadsheets “Chart” function.

google chart

But first I need to tidy up those names. What’s with the ABS sticking “(C)” and “(A)” all over the place? FFS. Right, a new spreadsheet formula to trim off that crap. This one is a bit ugly:

gnarly name tidying formula

But it works.

Now… about that chart… Well, we could do one in google spreadsheets, but that’s basically like doing it in excel and about as much fun. So let’s not.

Instead, let’s upload it to ManyEyes. Have you seen this thing? Fun, collaborative mashup, and you do it by copying and pasting. There are a couple of tricks. Like I can’t have dollar signs in the spreadsheet values, but I have to put the dollar sign in the column header. Then I have to select just the rows I want. Here a screenshot fo the spreadsheet after I’ve done that:

select the rows

Note that I don’t know that I have to do that just because I’m some kind of web genius, in case you were wondering. In fact, I just read those instructions off the manyeyes upload page: (click through to see it yourself – you’ll have to sign on, mind)

upload to manyeyes

Wham. There it is. I did the same for the volunteers doohickey. And then I made a merged version of those two and uploaded it, because it’s more interesting that way. (“Interest” is also why I spelled it “voolunteerism, ok?)
E8ea740c-b8a9-11de-9809-000255111976 Blog_this_caption

Now this ain’t a statistics class, but, er, there you go, a possibly informative data visualisation. Well, it might be if you click through to a less microscopic version. So it turns out that rich local government areas tend to volunteer an amount close to average, and poor ones volunteer over a wider range. Hm. Damn those rich people, and their… lack of variability? That could have been more interesting. Messing around with statistics until a more fascinating one pops out is left as an exercise, however. Let’s power on through and work out what we can do with the tools at hand.

We’re gonna map this thing.

Now, first I try that yahoo pipes idea the obvious way, and repeat last time – i just send the output of my spreadsheet into the yahoo pipe, and hope the location extractor will do something useful:

pipes geocode first try

Look at that. Dang, no location information in the output. Lame! I might have to read the manual.

Location Extractor can glean location data from some URLs, such as those from maps.yahoo.com, maps.google.com, and mapquest.com.

A wide range of location mark-up is recognized, including GML (Geography Markup Language), Abbreviated GML, W3C Basic Geo, Abbreviated W3C Basic Geo, Simple GeoRSS, Yahoo! Local format, and KML LookAt and Point tags.

Right. Well, clearly, our feed doesn’t look like yahoo local or whatever the crap that is. But I reckon getting our data format in google maps link format should be fine, because it’s easy. I know from playing around, that you can get google maps to link to, say, Armidale, by using a URL like this:

http://maps.google.com/?q=Armidale, New South Wales

I guess that might be documented somewhere, but I just figured it out by typing things into the URL bar until it worked.

Anyway, we could use Google Spreadsheets to turn all those “Armidale”-type cells into “http://maps.google.com/?q=Armidale, New South Wales” type cells, but it’s late in the day, so I’ll show you a little hack to do it without using Yahoo Pipes to save more fiddling around:

unholy geocoding mess

Woo! It worked! What does it do? Well, I’m not going into too much detail with this one, since, as I said, you can do it all in google spreadsheets… but the regex tool is a handy thingy for doing translation of strings of letters into other strings of letters (in this case, into strings of letters with “New South Wales” stuck on the end, and the URL builder just shoves stuff into a URL – in this case, the google maps one. Feel free to play around with it. The only trick with this one was I noticed that Yahoo Pipes was occasionally breaking when I tried to use “Local Government Area” as a field name in the CSV import thingy, so I renamed it to “LGA” in the CSV import widget, and then it worked OK. I guess it doesn’t like spaces in the name. Anyway, watch out for that.

Long story short: It works.

yahoo pipes success

But lets get that stuff off the pipes page, by getting the KML export URL:

yahoo pipes success ouput

Now we’ve copied that url, we paste it in to google spreadsheets, right there in the search box.
yahoo kml in google maps

And the result?

yahoo gecode win

Win. we have a google map of the yahoo pipe of the google spreadsheet of the ABS data.

Not that it’s perfect. I meant, as far as visualisations go, this could be way more interesting – this is more a map of where local governments are than it is of the stats that started the whole thing off. We could pup pop-up speech bubble with the ABS stats into the yahoo pipe output if we wanted, which might be fun. Or we could try to import THIS data set into wordpress. Or maybe we could try to map it differently. If you’re not afraid to get your feet wet in javascript, the handy javascript mapping library Openlayers javascript examples page includes on of resizable map markers. So we could build a map that had, maybe, big markers for really rich LGAs, and really small ones for poor ones. Or colourise them. Or… Lots of options. However, there are a few steps along that path that were a little hairy, so I’m going to give you an alternative geocoding method instead – since more of you seem to want to map specific locations rather than census statistics, it might pay to stick to close to what the class needs.

Basically, it was too much work to get it to geocode properly (it might have been a bit more fun if we controlled the source of data, and say, had a wordpress blog outputting GeoRSS or something).

Fortunately, Pamela Fox has blogged a nice way to geocode your spreadsheets. (She also has some awesome tips for designing maps.) Let’s follow her instructions. First, we add “New South Wales” to the end of all our LGA names, so that google can find ’em better.

name stating

Nice. The rest is all following Pamela’s instructions.

You whack her gadget in there:


set it up:

fox instructions

Then you press the go button (small correction – up above I showed myself geocoding 150 records at once, but it only for 99. If you find it stops half-way through, that’s why. But it’s easy enough to restart.)

fox near success

And then it goes. Not completely smoothly – it Turn out google has no idea where “Greater Hume Shire” is. And nor do I. I could have fixed that manually (possibly using the plain old paper street directory), but decided to be lazy. I copy and paste and then:geocoded

From the same blog post, Pamela also gives links to a handy spreadsheet mapping tool or two. There are, as always, too many alternatives. You might want to reverse geocode, or try this other hand bulk geocoder, or the GeoCommons open source geographic database, and there are semi-commerical services to automate part of it for you, such as umapper. Bah!

The real idea that I’m trying to get across here is that none of this is magical secret powers (well, except regexs), but there is a lot of muddling through. The list of tools out there is really large, and they change. No-one is a ninja at most of them, but be being happy to just try things and see if they work you can get a long way with a lot of them. There are bugs and annoyances. But then you usually get something at the end of the day.

Here are some links for lists of other directions to explore with mashups. I’ve mentioned some before, and some are new.

  1. HackU
  2. ToyChest
  3. Simon Willison’s roundup for the Guardian
  4. Freebase appears to have a general purpose mashup engine too.
  5. YQL is hot right now, if you want to get just a shade geekier. It supports a massive number of the services we are looking at here and more I haven’t heard of, and if those aren’t enough, you can turn anything, almost, into a YQL data source with a little bit of code. Very handy.
  6. google has a Javascript playground! (although I reckon the Openlayers examples page, mentioned above, is cooler)

Now, that’s enough writing from me for now. I’m may have time to do another walk-through of things for this semester… If anyone wants to suggest the topic in the comments, fire away.


7 Responses to “example mashups – part II”

  1. 3 Jian-Long Ooi October 24, 2009 at 18:41

    Hi Dan,

    I successfully mapped my data using some of the stuff you used in this post, but mainly http://gmaps-samples.googlecode.com/svn/trunk/spreadsheetsmapwizard/makecustommap.htm, but when I copy the code into WordPress it doesn’t work. I tried copying the code into Dreamweaver in a blank html file in the body section, and that works, but when I copy the code and paste it into a wordpress post and when i preview it, all i see is basically the outline of where the map should be, but it doesn’t appear. Do we need to do something different when we put code into a wordpress post? and yes, i pasted it in the html section, not the visual section. you can check out what appears by going to my site. And i forgot the site to use to dump my code, but the code seemed to work fine in a blank Dreamweaver html file, so is it something more to do with WordPress rather than the code? Hope this makes sense!


    • 4 netcultures October 29, 2009 at 16:40

      Oops, sorry J. I missed your comment there.

      YEs, that sounds like a wordpress issue, of sorts. I can embed a rich flash object in my test blog (e.g.) with no problems.

      I would guess that in this case, the problem is to do with some kind of javascript clash. Can you post me a link to the page and let me know what you expect to be happening ?

      • 5 J October 29, 2009 at 22:30

        Hey Dan,

        it’s the one that you go to when you click on my name, or here: http://j.map-netcultures.net/ i pretty much followed the instructions and copied and pasted the html stuff i was given and added in the googlemaps api key, but it doesn’t appear….

        • 6 J October 29, 2009 at 22:32

          I should also note that i got it working with yahoo pipes and then mapping the kml into googlemaps, but apparently googlemaps won’t let you embed a map into your own site when the data is not your ‘own’…..

  2. 7 netcultures October 30, 2009 at 00:40

    Ah. So I’m doing “view source” on your code right now and I can see that wordpress has shoved some nasty <p> tags right in the middle of your javascript, which won’t work so well. I’m guessing this is simply because the javascript you are inserting is longer than the snippet we tried in class.

    The official wordpress FAQ has some nice tips – http://codex.wordpress.org/Using_Javascript#Troubleshooting_Javascript
    – in particular the idea, mentioned toward the bottom, that you might want to use the “no formatting” options to stop wordpress helpfully putting in paragraph marks where they don’t belong would sort you out.

    If you want to get a bit more full-on:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Except where otherwise attributed, Creative Commons License
Netcultures blog is by Dan Mackinlay and Chris Caines and licensed under a Creative Commons Attribution 2.5 Australia License. Content from external sites remains property of its original creator.

%d bloggers like this: