Category Archives: maps

Fusion tables and maps: Post office map revisited

A few years ago I wrote a post about mapping post office closures using google maps and yahoo pipes.  I used that combination because of an issue with google maps and post codes. I needed to convert the posts codes in to a lat, long format – yahoo pipes did that job.

I noted that @patrickolszo mentioned that post on twitter today and I realised it was a bit long in the tooth and, of course, things have moved on considerably.  So here is how I’d do that now using googledocs and google fusion tables. 

I’ll use the data from the original map which came from publicservice.co.uk which is not current but at least it keeps the link across the posts!

Importing the data

In the previous example I noted that I did a fair bit of manipulation on the information splitting the text up to make columns I could use.

If you look at the sheet you can see it is actually a bit of a mess in places but it’s raw data. Truth be told I went through excel to do some column splitting and then combining to get the postcode out of the address.

Now I guess you could explore tools like Google Refine to help with that kind of thing. But I’ve shared a version of the spreadsheet on Google docs which you will need to save in to your own google docs account. Once you have the spreadsheet, you can import it in to a fusion table. In google docs

  • Click Create new and select Table
  • Click Google Spreadsheets from the Import new table options
  • Find your saved version of the post office spreadsheet and click Select

A version of the spreadsheet will be loaded. At this point you can make changes to the column headers – I changed Address three to complete address for example. The last screen prompts you to add contextual information. It’s really worth doing this.

Always add context to your data when given the chance

When its loaded in the data is not that different from the spreadsheet. Fusion tables splits it across a number of pages, 100 rows at a time but that’s all that’s really obvious. I’m not going to go in to the functionality etc. of fusion tables here though. I’ll just do the basics to get us to the map.

Mapping the data

The first thing we need to do is tell fusion tables what data we are going to map each row.

  • Select Edit > Modify columns
  • Select the Post Code column and change the Type to Location
  • Select File > Geocode and select Post Code from the drop down
  • Click Geocode

Changing the post code column type to location

Fusion tables will then generate location data based on the post codes in the table. Much easier than the Yahoo pipes solution! You can also try geocoding more generic information. You could, for example, set the Address two column type to location as well and geocode that. But if you have more than one post office in the same town that might cause problems.

Generating the map

No you have the location information

  • Select Visualize > Map

Fusion tables will take the data and put it on the map. You’ll need to zoom in and move around to see the points in detail. Clicking on a point will open a standard speech bubble with more data. But overall it’s looking pretty good. Well, almost. One of our post offices seems to have moved from Sevenoakes to Northern France!

The basic map but with a problem

Checking the data, it seems that the post code is TN13 IHZ when it should be TN13 1HZ. It isn’t the only one but I’ll just correct that postcode for now :

  • Click Visualize > Table to switch back to table view
  • Click the Address two header
  • Select Sort Desc
  • Find Sevenoaks and change the appropriate Post Code Entry
  • Click Visualize > Map to go to the map view
Sorted.

The updated map

Editing the map
Changing the data on the map is simply a case of updating the table. But you can also edit the content of the bubble using the Configure info window link and even the marker by using the Configure styles link.
One nice trick here is to add an extra column to your spreadsheet and add a custom icon to use on your map. In the example below I added a column to my spreadsheet called logo. I added ‘post_office’ to each entry. When I import that in to Fusion tables I can use the Configure styles link to choose the logo column as the icon.

Using a custom icon on the map

Sharing the map.
When you’re happy with the map you can share it with others in a similar way to other google documents.
  • Click on the share button in the top-right of the page
  • Change the settings to suit

The share settings in fusion tables

Once that is done you’re good to go. Here’s an embedded version of the map.

Note: The embed works using an iframe so if you’re using WordPress.com, sorry, you’re out of luck when it comes to adding the map to a post.

Conclusions.

The process of mapping is obviously a lot simpler with something like Fusion tables and I think that underlines just how far we’ve moved. That’s not just in terms of the underlying technology which, when you think about it, is pretty amazing. It also shows just how far and how quickly data, geocoded or otherwise, has become mainstream.

Anyway, it’s a simple little example and there is a tonne more that you can do with fusion tables but I hope it’s a useful insight.

Old and new Belfast Telegraph

I found myself on the Belfast Telegraph website yesterday looking at their rather nice crime map. A nice mashup but the ability to compare years visually would have really made it for me.

The Belfast Telegraph Crime map

The Belfast Telegraph Crime map

Still, whilst exploring I noticed a slideshow of the recent homecoming parade of The Royal Irish Regiment in Belfast. It’s shame that the slideshow was the the bog standard html click and link job (very old school next to a sexy mashup). An audio slideshow with soeme sound from the event would have been really evocative.

Especially when you have such fantastic shots like this one

Great pictures still sell a story

Great pictures still sell a story

Turning dog poo in to stories

I’ve been spending a lot of time doing prep for teaching and training that I’m doing at the moment. So expect the slow appearance of a backlog of posts on video and other related issues. But I thought I would share something that has been in my radar for a few days.

I’ve been doing a lot of talking (shocking for me I know) about using the web for research – journalism toolbox stuff. And one of the things I have been stressing is that the web will very rarely just ‘give’ you a story. It will give you lots of data and information but the story is in the way you, as the journalist, put the things together. The phrase that I heard last week that best summed that up journalists are sense makers. Phil Trippenbach has a nice post on this so I won’t labour the point.

But whilst I was browsing for resources and examples to show my students and delegates I came across a site that made me wonder if I had to re-think that position – fixmystreet.co.uk

Fixmystreet.co.uk is a site by the fantastic Mysociety group who specialize in socially aware, achingly web2.0 sites. Top stuff on a number of levels and their other sites are worth a visit. Anyway, here is how they describe fixmystreet:

A site where people can report, view, or discuss local problems like graffiti, fly tipping, broken paving slabs, or street lighting.

Nearly 25,000 problems have now been reported across the UK, with our users following up many thousands with updates, news and notifications that problems have been fixed

Here’s an example of an ongoing problem with litter.

Fixmystreet.co.uk - tracking local problems

Fixmystreet.co.uk - tracking local problems

You can also sign up for an RSS feed or email alerts for a location. Told you it was brilliant.

So I’m showing my students the site today as we discussed ways that you can get a handle on a patch. They enjoyed it, not least because it offers, what must be, the most accurate geographic mapping of poo that I have yet to see on the web. With pictures! Anything scatological is a hit with students it seem.

I made the point that it shouldn’t replace physically getting out on the patch but it could provide some insight and a conversation opener when wandering around. But it wouldn’t throw up a story. Then we came across this entry.

Is this just about dog poo?

Is this just about dog poo?

Take a look and ask yourself if there is a story in that or not and if it’s a story about dog poo.

Locating the meaning

In terms of the way you would work a beat to get a story to pitch to an editor this site serves up a hell of a lot in just a few lines of comment. Perhaps it’s the fact that the story is located that adds the context you need. Maybe it did take my eyes on the story to make the connection. But one thing is for sure, fixmystreet proves that locally focused geo-mashups work.

So if the embryonic geotagging of your content or the occasional attempts at mapping this kind of thing have fallen of the radar or been dismissed as gimmicks, maybe it’s worth looking again.

Take more of a healthy interest in your audiences poo.

UPDATE:Because I am that plugged in at the moment I didn’t see this great interview with one of My society’s developers Francis Irving on Journalism.co.uk/ (thanks the Alex Lockward for the nudge)

Visualisation tools: Many Eyes

Why am I always playing catch up? Not much chance to post over the last few days but I have been browsing around as usual and one of the things that caught my eye was something I wanted to give more space too than a simple Delicious bookmark

Mark S. Luckie posted an article on various Presidential candidate speeches analyzed using tag clouds on his 10,000 words blog (thanks Mark). A nice article about visualisation, with some interesting points.

Speech visualization is not just for politics, but for any great talk or monologue that could benefit from a quick analysis. Just for kicks I analyzed the last seven 10,000 words posts and the most frequent words were audio, colors, multimedia, news, olympic, online, political, site and users. Sounds right to me.

A number of people are trying this kind of thing (including me) and I was interested in the visualization tools he used. Turns out he used a tool site called Many Eyes which I hadn’t seen before.

Many Eyes is a bet on the power of human visual intelligence to find patterns. Our goal is to “democratize” visualization and to enable a new social kind of data analysis. Jump right to our visualizations now, take a tour, or read on for a leisurely explanation of the project.

They have some great tools which make some nice graphics. These include those nice bubble graphs that are cropping up all over the place. (I see a lot in the Guardian)

Below is a nice example of one users mapping of the satisfaction rates for UK police forces.

You can also embed visualizations using a nifty link feature.

Worth a look

Times online and google maps

Times online map

I got a very nice mention on the Times website today. The Times’ Communities editor Tom Whitwell (thanks Tom) has added to a survey they did for the upcoming budget with a “now legally-required Google maps mashup

The map was built using Yahoo pipes and google maps, based on a post of mine with a little google forms magic built in. (Later: Just to clarify, that’s about the sum of my advice here. My boss just asked me when I started working with the Times!)

I mentioned in my post that, combined with Google forms, turning surveys in to ‘geotagged’ surveys is pretty darn easy.

As Journalism.co.uk reports

“The Times has a long history of commissioning opinion polls,” wrote Tom Whitwell, Communities Editor, Times Online, about the origin of the survey.

“These are scientifically rigorous, using a carefully selected panel of maybe 1,000 people. At Times Online, we can do things very differently. We can throw out questions to our readers and capture their mood quickly, cheaply and easily.

He admits the online poll isn’t that rigorous but it offers a cheap and easy way to add depth to your content.

Great stuff and it gives me a great example to show the students.

Using google maps for sports journalism

As you can see from previous posts, google maps has been high on my radar this last few weeks. I finished the last of the maps workshps on Friday with two groups of sports journalists. Now mapping a news story is interesting but from a sports point of view it’s, on first thought, perhaps less interesting. So I wracked my brain for a way to make it a bit more relevant and interesting.

Whilst I was blundering around yahoo pipes I came across a mashup that mapped the location of football stadiums which I thought might be fun. Obviously they would need to do this the long way but it was worth a look. So I tried a few out just to make sure I could find them (I’m not a football fan). On a side note here the age of Google maps satellite imagery was highlighted by the lack of pitch at the Emirates stadium.

One of the things you can do with Google maps is change the icon from the default lollypop style. So changing the marker to offer a little footballer marker for each stadium seemed appropriate. Here’s an example:


View Larger Map

But the little green guys gave me an idea. What about placing them on the pitch of the stadium, something like this.


View Larger Map

Click the goal keeper and you get a picture of Manchester City goalkeeper Joe Hart. (one of the students told me it was him)

This seemed to catch the students imagination and created some good results. Including this great effort by Sean Molyneux for the Man Utd. starting lineup.


View Larger Map

Each marker has been replaced by a team mug shot. Nice touch.

Other uses

One of the other ideas that came up was using the ability to embed a map in a page and still update it. We thought this may work to plot the front runners in a marathon or other race. There is already a Google map of the route (via this page) that uses the line tool to map the route. You could have the key runners marked with logos or pictures. Change the position every so often and also map any other events. Refresh the page* and hey presto! Lo-fi dynamic map.

*if you aren’t doing this on a blog but on your own site then you could add some javascript that refreshes the google map iframe automatically

Maps, spreadsheets, yahoo pipes and post offices

NOTE: I’ve updated this post to show how to do the same thing using Fusion tables rather than pipes 

Over the last few weeks I have been teaching second year students a number of digital tools. This week it was Google maps and I thought I would share the process I went through to create a map/mash up example to show them.

So here is the map.


View Larger Map

Google doc publishNot too interesting I know. It’s a map of post office closures in areas of the UK shown on a website called public servant daily last October. I just did a search around for the list based on a quick think about a geographically relevant story and this was the first site that popped up with data that looked copy and pasteable.

What I figured I needed was a list of postcodes that I could some how convert in to a feed that Google maps would understand. Now you can point the spreadsheet directly at a map via Google maps spreadsheet importer thingy but I didn’t want to do a lot of hand converting post codes in to lat/long information.  There are some tools that will do it, like this one, but they all seem to need some kind of preformatting. So I needed something that would do that ‘more’ automatically. That’s where yahoo pipes came in.

A quick search around pipes found me several examples of postcode conversion pipes to play with.

Setting up a spreadsheet

So to start I cut and paste the data in to a Google spreadsheet. If you look at the sheet you can see it is actually a bit of a mess in places but it’s raw data. Truth be told I went through excel to do some column splitting and then combining to get the postcode out of the address.

Next step was to publish the spreadsheet so I could use the data. You can publish in a number of formats but the pipes examples I had seen used CSV (comma separated values) and that seemed easier than trying to deliver the RSS feed.

 

Publsish the csv

publish your spreadsheet as a csv file

The publish option gives you a URL for the CSV feed. By setting the publish options to automatically update I knew it would be the most up to date.

Yahoo pipes

Yahoo pipes

The postcode to lat/long pipe

Now that I had the feed I needed to get it in to pipes. So the first block is the Fetch CSV (found in Sources) option. This essentially gets the csv feed published from Google docs and spits it out in a list based on the columns in the sheet.

Then a loop block (found in operators) takes each item in the spreadsheet and runs a Location builder operation. To combine the two, you add the loop operator and then drop the Location Builder in to the space.

 

Loop and location

Drop the location builder pipe on the loop

The location builder “converts a description of a place into geographical data.” You can see that is set to use column 4 as the location- that’s the postcode – so we can generate the lat and long information that google needs to plot the points on a map.

The output of that loop are packaged up in the item.loop.locationbuilder results. So the last thing to do is make sure that the results are in a format that Google maps can understand. As far as I understand it Google plays with the Geocode RSS format and fitting with that convention seemed to be the norm on other pipes. So the next box takes each element and renames it in to something more Geocode friendly.

I’ve used column 1, the post office name and column 5, the post office address (or a truncated version) as a description. The lat and long results from the location builder are then renamed to match the requirement for geocode RSS.

The result of that is then plugged in to the pipe output.

Get a feed

RSS from pipesWhen you save the pipe you can then run it and it spits out a fairly plain list. You can subscribe to the list of results in a number of ways including the RSS with all the geocode content. If you right-click over the link you can copy the url.

If you take that link over to Google maps and paste the RSS url in to the maps search box. Hey presto! the results are plotted.

You can then click the Link to this page option and gather the link data to embed on your site. Result!

Conclusion

This may be old hat to some. New to others. What pleased me was how easy this was made by the way pipes allows you to share and edit other peoples work. It feeds my approach of ‘oh, that’s good you could use that to do this..” hacking.

I’m also thinking that the new Google forms thing will make interesting addition to this mix. All it needs is a bit more data and more flexible pipe to make for richer content and things could get very mapalicious. Hope there is something there to play with.

Raw material

Go and play links

A few quick ‘go-and-play’ links:

  • A new version of soundslides is in Beta – Soundslides Plus
  • Google have introduced Streetlevel to their Google maps. It seems to serve no other purpose than to make you say ‘cool’ a lot. It allows you to interact with…I’m not going to explain it. Just go and have ago. You can see an overview of the features here. I’ll warn you know that you will have to brave the site of a man in an orange lycra body suit – wrong, just wrong.
  • Adobe labs have released a beta version of Visual Communicator 3. It works for 95 days and although the download and install process is a bit flakey its worth a look. Lot’s of other goodies to try there as well.

Have fun