Tip:
Highlight text to annotate it
X
KATHRYN HURLEY: Hey everyone.
Welcome to I/O.
I'm so excited to be here.
I hope you are too.
My name's Kathryn Hurley.
I'm a developer programs engineer at Google.
Specifically, I work on Fusion Tables.
And I'm here today along with James McGill, an engineer on
the Maps API team, and Simon Rogers, from the UK
Guardian data blog.
And today we're going to be talking to you about to how to
use Fusion Tables to manage and visualize geospatial data.
So this is the agenda for the talk.
I'm going to give you an introduction to Fusion Tables.
Then I'll run through a quick demo of how
to use Fusion tables.
Then I'll talk to you about how you can build an
application using the Fusion Tables API.
Then James is going to talk about Google Maps API and how
you can you Google Maps API to display your Fusion Table data
on a Google Map.
And finally, Simon Rogers is going to show you some
real-world examples of how the Guardian is using Fusion
Tables to display their data on Google Maps.
And we're really excited to see how journalists are using
Fusion Tables.
All right, so intro to Fusion Tables.
When people ask me what is Google Fusion Tables, I like
to say that it's a database in the cloud that you can use to
store, host, collaborate on, and visualize your data.
Here's a nice example of a visualization that you can
create using Fusion Tables.
This is a map.
And this map was actually created by Simon Rogers.
It's showing deprivation in England.
You can see he did some nice styling of the
polygons on the map.
You can also see that the info windows were customized and
this was all built using Fusion Tables.
All right, here's another example of a visualization you
can create using Fusion Tables.
This is a bar chart created using the Google
Visualization API.
Fusion Tables acts as a data source in the
Visualization API.
I'm not going to go into too much detail here about how to
use the Visualization API, but I encourage you go to the
session "Using Chart Tools to Create Interactive Dashboards"
that's occurring right after this one in room one.
And you can learn more about the Visualization API there.
So now I just want to actually jump into a quick demo of how
to use Fusion Tables.
I can show you a lot of screen shots of what the product
looks like, but I think it's more interesting if I actually
show you a real-life demo of the product.
And there's also some really cool things that you can do in
the Fusion Tables web application that I just wanted
to show you how to do.
And so, for example, you can merge two tables together in
the Fusion Tables web application.
Merging is basically a left out or join if you're familiar
with databases.
You can also manage the permissions and visibility of
the tables that you own in Fusion Tables using the web
application.
And you can also create a map and customize that map with
custom info windows and map styles.
So I'm just going to get out of full-screen mode right now
and go to Fusion Tables.
And this is probably a little bit small, so I'm going to
increase outside here.
All right, so I go to google.com/Fusiontables, I've
signed in with my gmail account, and I'll see a list
of the tables that I own.
I'm going to work with these two tables down here at the
bottom: Ecoregion Boundaries and Ecoregion Attributes.
So let's look at the Ecoregion Boundaries table.
So this table contains the boundaries of various
ecoregions around the world.
Ecoregions are basically areas that have the same climate and
plant and animal species within them.
So we got this data from The Nature Conservancy.
They were very nice and shared this data
with us for this demo.
The Nature Conservancy is concerned with preserving
Earth's natural resources, so they have a lot of data about
the ecoregions around the world.
And this table was created from a KML file that they had
containing all the ecoregion boundaries
throughout the world.
And this is just a subset of that data, it's the ecoregions
in Australia.
So let's take a look and see what this table
looks like on a map.
So Fusion Tables maps all the polygons that were in that KML
file and places it on it a Google Map.
When you click on one of these features, you can see what's
displayed in the info window.
You can see that the data from the table is displayed in the
info window.
OK, let me go over to the other table that I have the
ecoregion attributes.
Now this data was also donated to us from The Nature
Conservancy.
And the cool thing about The Nature Conservancy is that
they work with a lot of different organizations.
So, for example, they work with the World Wildlife
Foundation.
And all these organizations have specific data about the
ecoregions around the world.
So, for example, The Nature Conservancy has information
about the percent forest area in ecoregions, and the World
Wildlife Foundation has information about maybe the
number of lizards and snakes in each of these ecoregions.
So Fusion Tables allow a lot of these organizations to work
together to collaborate on this data.
So The Nature Conservancy can upload their own table with
their own data so that the WWF can upload their table and
have their own data up in Fusion Tables.
And then they can merge these tables together to create one
huge Table or merge Table.
So I'm going to show you how to merge two tables
together right now.
I'm going to merge the attribute table with the
boundary table.
So I just copied the URL from the attribute table.
I'll go back to the boundary table and select Merge.
I'll paste in the URL from the other Table, click that.
Now at this point I want to select the two columns that
have the same values in it.
So this is similar to keys and databases, both columns have
to have the same value.
And the two columns in my table are the object ID
column, so I'll select both of those, and I'll called this
new merged table that I'm creating Ecoregions Merged.
When I'm done here, I click Merge Tables, and Fusion
Tables will work and it creates this new merged table.
And you can see that the data from both tables are in this
merged table.
So I'll scroll over so you can see more columns here.
And the nice thing about this merged table is that if any of
the data in the base tables that were used to create the
merged table are updated, then those updates are immediately
reflected in the merged table.
So let's say that the WWF finds a new lizard species,
they can go ahead and update that data in their table and
it'll be reflected in this merged table.
Now let's see what this looks like on the map.
Select visualized map and occasionally might see a
loading message.
It takes a little bit of time for the data to load.
I'll just refresh the page and let's see
that on the map again.
And there you can see our nice boundaries on the map.
And I'll click on one of these polygons so you can see the
information that's being displayed in the info window
and you can see it displaying all the information from my
table in this info window.
But maybe I don't want to show all this information.
Maybe I just want to show the name of the ecoregion and the
forest area, so I'm going to update the contents of the
info to just show the information that I want.
In order to do that, I can click Configure Info Window
and I can select and deselect which columns I want displayed
in the Info window.
Or I can also go to Custom and edit the HTML.
So I can really customize the contents of the
Info window even more.
So I'm going to delete that label, maybe give this nice
color blue, and add the closing tag here.
And save. And let's see what happened to our info.
There you can see now the name of the ecoregion is bold and
blue and shows just the percent forest area in the
Info window.
So an all-red map maybe not that useful.
Let's actually change the styles of these features
according to the information that's showing in my Info
window, basically the percent forest area.
So in order to do that, I can configure the
styles for the table.
And you can see there are several options for changing
the style of the features on the map.
If you have points, you can change the marker icon.
Or you can change the fill color of the
polygons, et cetera.
Since my data is polygons, I'm going to
change the fill color.
And again, you can see there's several options for changing
the fill color of the polygons.
I'm going to go ahead and select a gradient.
I'm going to show a gradient on percent forest area--
that's the data I'm showing in my Info window.
And I can customize the colors of the gradient.
I'll make it a nice teal color.
And I'll set the min and max for the range of the data in
the Percent Forest Area column.
I'll click Save. And you can see our map is immediately
updated with the new styles.
And I'll click on one of these regions and you can see that
the Info window content is the same.
All right, this is really awesome.
And at this point, I'll show you how to share the table
with others, because after you've put all this hard work
into your map, you probably want to share
it with other people.
So these permissions are similar to Google Docs
permissions.
You can invite specific people to view or edit the table, or
you can set the visibility of the entire table to public,
unlisted, or private.
I will set this as unlisted so people that have the
link can view it.
And at this point, I could be done.
I could click Get an Embeddable Link.
I grab this HTML, I'll copy it, paste it in my own HTML
page, and I'll have this map directly in my website.
So it's how I styled it in Fusion Tables, how I set the
Info window contents, that'll be available in my website.
So you can do a lot of cool things in the UI.
All right, go back.
So I showed you how to merge two tables, I showed you how
to manage the permissions and the ability of the table, I
showed you how to create a map and customize that map.
Now how might you build an application
using Fusion Tables?
Here are a couple of application
ideas I came up with.
Some of these are actually being developed right now.
One developer is mapping out newspapers around the world.
It's a really cool application.
He's styling the markers according to the language of
the newspaper.
He's updated the contents of the Info windows to provide
links to the online content for these newspapers in
various translations so you can read it
in English or Spanish.
And he's allowing his users to search for newspapers around
their area.
Another cool application I've heard of Fusion Tables is a
popular game manufacturer was interested in finding out
which servers their users were connecting to.
And using Fusion Tables, they were easily
able to map this out.
And they were surprised to find that a lot of their users
in North America were connecting
to servers in Europe.
And they really wanted those users to connect to servers in
North America.
And they were easily able to figure that out
using Fusion Tables.
So they uploaded the data and they could immediately map it
out on a map.
So let's just go through a work flow of what one of these
applications might look like.
So let's go through the mapping newspapers around the
world application.
So let's say you wanted to create this application and
you want users to be able to use your application and enter
information about their own local newspapers into your
application.
So you could create a web form and or Android or mobile
application, and this application would enter data
directly into Fusion Tables.
And at this point, it's really easy to create a visualization
of the information that's being submitted to your table.
You just simply visualize it on a map.
And here's an example of a map that you can
create with this data.
You can color the markers according to the language or
some other attribute of the newspaper.
I'll click on one of these.
You can see that it has name of newspaper-- and these are
all just fake newspapers I made up--
and you can provide a link directly to the newspaper.
Alright, so now let's go through the details of the API
so you know how to use the API to build an
application like this.
The API is a REST API, so basically that means you can
make HTTP requests to Fusion Tables.
With these requests, you send SQL-like queries.
In order to create table inserts, collect to update
data, et cetera.
And here's just a few examples of the queries
that you can send.
You can see where creating a new newspaper table with
columns for the name of the newspaper, location of the
newspaper, and a link to the newspaper.
And we're inserting data, selecting, et cetera.
And OAuth is used for authorization.
And here's just some sample code.
This is written in Thai Puan, but you can write your code in
any language that you want as long as it
can make HTTP requests.
So you can see I'm creating a query that's creating a new
table under the newspaper table.
And it's sending the query along with the request. And
depending on the query that I'm sending, you'll get
different data back.
So if I send the Create Table query, then I'll get this
Table ID back if the create was successful.
OK, so now I just want to jump over to a demo of the API.
So let me just go over to a terminal real quick.
And this looks a little bit small.
And something happened to my [UNINTELLIGIBLE].
Sorry, let me just find where it is real quick.
Sorry about this.
I should have prepared this a little bit better.
OK, so I have a Python file.
And let me just initiate Python here.
And it's a little bit small.
Let me increase the font so I can see this better and
increase the window.
OK.
So I have a Python file and exchange a
Fusion Tables class.
And in this class, I can make requests to Fusion Tables and
I'll send various queries to Fusion Tables.
So I have a Show Table query, Create Table query, et cetera.
And so I'm just going to go ahead and import that Fusion
Tables class.
And I'll create a new instance of that.
And I'll run the Show Tables query.
And you can see the tables that I own.
You might recognize some of these tables from earlier.
It has the Ecoregion table.
And let me go ahead and create a new table.
So I'm running the Create Table query.
I'm creating a new newspapers table and initializing the
three columns, the name, location, and link.
OK, so let's just run a describe on this.
And you can see the results of the describe.
It's describing the table, showing the columns and the
data types for each of these columns.
OK.
And this is sort of mimicking the stuff that you would do if
you wanted to write your newspapers application using
Fusion Tables.
So let's enter some data into this table because right now
it's just an empty table.
And so this step is going to mimic how your users would be
able to submit information to a Fusion Table.
So you run insert statements when somebody submitted a form
on your web application or in your Android app.
And so we're inserting information about various
newspapers around Bay Area: San Jose Mercury News,
Mountain View Voice, et cetera.
So let's run a Select query on the table.
And now you can see that the data is in my table.
We have information about San Jose Mercury News, et cetera,
and it might be hard to see--
maybe I'll increase this one more time-- but the link at
the bottom is actually incorrect.
The San Francisco Chronicle's URL is not SGATE, it's SFGATE,
and this is pretty common if you're writing an application
that does crowd sourcing.
Maybe your users are submitting erroneous data, or
maybe changes to the data are occurring.
Maybe the San Francisco Chronicle has changed the URL
to their website.
So you want to be able to update that data.
And so we have an Update Statements.
We've sent the Update Statement, we've set the new
link to the new value and specified the row ID that we
want to update.
And you can see that.
Now our data is being updated and let's run a Select one
more time so you can see the results.
Now the URL for SFGATE has been updated.
Also an interesting addition to this might be adding a
Spatial query.
So Fusion Tables for Spatial queries.
This will allow your users to find newspapers
that are near them.
So you can find the N nearest features or you can find all
the features that fall within a circle or a bounding box.
So you can find the closest newspapers to your user.
All right, so that's the API demo just showing you how you
might use Fusion Tables to build a newspaper application.
All right, so now I just wanted to announce a couple
new features.
We're very excited about these new features.
We are now releasing to trusted testers map feature
styling and Info window customization, all available
in the API.
So I showed you how to do these steps in the UI earlier.
I showed you how to style the features is on the map of the
ecoregions and also update the Info windows.
Now you can do this in the API.
So this is really exciting.
I just want to mention real quick how to
become a trusted tester.
Just send an email to this mailing list. This email will
show up later in the presentation, so don't worry
if you don't copy it down right now.
We'll show it again.
And how do these new features work?
This is a rest API similar to the S2L API that
I showed you earlier.
So you can make HTP requests to insert, delete, update the
styles and Info window templates.
JSON objects represent the styles and the Info window
template for your table.
This is an example a JSON object representing the styles
of table one, two, three, four, five.
It's setting all the markers to small blue icons.
And so why is this awesome?
This is really great if you're creating lots of tables.
So I was recently working with the census data, and there's a
ton of census data.
I think I created around 200 different tables.
And if I wanted to go into the UI and update the styles for
all these tables, it would take a very, very long time.
So what I can do instead now is use the API to create the
table and then immediately apply a style or custom Info
window to this table.
Why?
It's just in the code, so it saves a lot of
time in these cases.
These new features are also really cool because you can
find out how public cables are being styled.
So if you really like how the Guardian styled their
deprivation map, you can use the API to find out the style
of that table and potentially reuse it for your own table.
Also a cool new feature of these new features of the API
is that it's part of the Google API console, and with
the API console you get a lot of nice things.
For example, you see your quote and you can also get
analytics about your API usage.
So if you're getting close to your quota, then you can
adjust your code.
Also, these new features are ported in the Google client
libraries, and that makes requests to the APIs much
easier and it makes it much easier as well.
And again, I just want to reiterate that trusted testers
are needed.
Here's the email list. And we'll show this later in the
presentation as well.
OK.
Now, at this point, I've shown you how to do the back end of
Fusion Tables.
James is going to take it away and show you how to use map
API to show your Fusion Table data on a map.
And he's flown all the way from Sydney, by the way, to
talk to you guys.
He's excited to be here.
JAMES MCGILL: Good afternoon, everybody.
Thank you very much for coming to our presentation today, as
Kathryn mentioned, I have flown in all the way from
Sydney, but it's been absolutely worth it, I'm
having just a great day.
And I'm excited to be talking to you today because what I'm
really interested in as an API programmer is making it easy
for developers to show large amounts of geographic
information on a map in their own websites in a way to easy
for them and useful to the people visiting their website.
And I'm really excited about Fusion Tables, because Fusion
Tables makes this very simple.
As I mentioned before, I'll be talking about the Google Maps
JavaScript API.
Now this is a JavaScript API used by developers all over
the internet.
It makes it simple to add an interactive SlippyMap like you
might have seen on maps.google.com to any
third-party site with just a few lines of JavaScript.
But what developers may struggle with a little bit
more is getting their data onto that map.
Now, I'm kind of a crime buff, and I happen to have some data
that shows the locations of crimes all over Chicago.
And I have a map of Chicago.
Using Fusion Tables, I can combine these two things with
only five lines of code.
All I need is the name of a column in a Fusion Table that
represents my geographic data.
Now this column should contain features like latitude and
longitude points or polygons or polylines.
I also need the ID of the title.
If you're not sure how to get the ID, that's contained
within the URL.
So you can just copy that out of the URL, highlighted in
blue in these slides, and paste it in the layout.
And with those five lines of code, I get a map
that looks like this.
Right?
Very simple.
And I can convey a large amount of information to the
people who view my site.
Now of course I can also filter that information.
So, for example, I may only want to display crimes that
have occurred where the victim was over 50 years old.
I use those same five lines as before, but I dynamically,
when I click this button, add an additional property to
filter them out.
And the map updates instantly.
And now we're only showing the subset of the information that
the person viewing our site is interested in.
Of course, you can set this programmatically as well.
So you can let the people visiting your site decide
exactly what subset of data they're
interested in looking at.
So why use Fusion Tables Layer?
Both of the things I just showed you are possible using
the Fusion Tables UI.
Kathryn demonstrated before that you can extract an Embed
link that you can use to embed an interactive map in your
site without having to write any JavaScript.
But as developers, we often want to customize the behavior
of the sites that we build.
We want them to stand apart from the crowd.
And a Google Maps API allows you to do this.
In the next couple of examples, I'm going to use a
table I found publicly available in Fusion Tables
about coffee production around the world for various
countries over the last five years.
Using that code I showed you before, it's very simple to I
add this data to a map.
By default, if you click one of these features, what will
happen is that the default Google Maps Info window is
displayed and it's filled with whatever Info window content
was configured through the UI.
However, through the UI you're effectively
limited to static HTML.
Now if we want to do something just a little bit more fancy,
because the Maps API has additional options on the
Layer and allows us to customize behavior, we can
achieve that.
So in this example, I set Suppress Info Windows to true,
which tells the API to no longer display the default
Info window.
And I also add a Click Handle.
Now the Click Handle function will be called whenever a user
clicks on a feature in the map.
And this allows me to inject my own customized code to deal
with this event.
So let's see what happens when we click this time.
It's just a little bit different.
And what's particularly cool here is that when you click on
a feature, it not only gives you back the location of that
feature, but it also gives you back the contents of the row
in the table associated with that feature.
In this case, I get back an array containing integer
values about coffee production.
And I can process that in JavaScript, pass it off to the
Google Charts API, and create my dynamic chart.
Which makes it easier for people viewing this site to
interpret this data.
Of course, you can also combine this with other
features in the Maps API.
For example, Styled Maps, which I've used to here to
customize the appearance of the base map, remove a lot of
the saturation, make it all very simple, and make the data
that I'm showing to the user really stand out.
Let's flip things around a little bit and talk about why
you would use Fusion Tables in the Maps API instead of some
of the all the built-in functionality.
Now, if you've used the Google Maps API before, you'd know
that you can use JavaScript to create markers.
And markers can be placed on the surface of the Earth to
annotate a point.
However, there's one problem with markers.
Even though we've worked extremely hard to ensure that
you can display between 100 and 1,000 markers very, very
quickly, they still must be created in a client.
This means creating JavaScript objects and the responsibility
of rendering these markers most be
performed by a browser.
Now these are some measurements I've taken using
a relatively modern machine and a modern browser sometime
in the last week.
As you go from 1,000 to 100,000 markers, the
performance of the client-side marker degrades significantly.
And it can take up to 40 seconds to display all those
markers on a map.
Now, if you use Fusion Tables Layer, you can display the
same number of markers in under a second.
And that's really compelling, especially on mobile devices
with slower APUs and less RAM.
The user of your site is going to notice the performance
difference.
This is achieved with tiled rendering.
The data in the Fusion Tables database is rendered in the
cloud by Google servers.
It renders transparent image overlays, which is sent down
and put on the top of your map.
The net result is that we need only 40 tiles or so to display
any map regardless of the number of features shown.
So your map can scale from 100 features to 1,000 features to
100,000 features without you needing to think through the
performance implications.
And this is, I think, particularly compelling for
developers.
Now there's one more thing I want to talk about.
I want to talk about adding a little bit of
style to your map.
Now this is something Kathryn touched on before.
Adding styling information to your map is not only important
to make your map look good but also to convey important
semantic information about the data represented on your map.
Here's a map where I've gone into the Fusion Tables UI and
I've styled the map to show areas within Australia that
have fewer than 250 unique species of birds in green and
areas with more than 250 unique species in blue.
But as a developer, I know that I'm not always in the
best position to decide what people visiting my site are
interested in.
The next person to view my site may not care about the
difference between 250 species but maybe the split between
300 species or 350 species, which is why I'm excited today
to announce that you can now dynamically update the styles
on a Fusion Tables Layer using the JavaScript API.
So if I drag the slider here to 300, and that will be
restyled to represent this information.
Now this is great, because you can now have users interact
with your site, tell you what they're interested in knowing
about your map, and restyle your map to convey that
information in a way that's very easy for them to see.
So how is this done?
It's very basic.
We've added a style property to the Fusion Tables Layer.
This takes in an array of styling objects which can be
used to customize the appearance of your map.
We'll start with a simple example.
This one is a simple styling object that sets all polygons
to have a green fill.
If you put this on your map, you get something
that looks like this.
But we can combine this with other styling objects.
So let's add another element to our array.
This one is a restricted style.
We've added a Where clause, which means that this style
will only apply to elements that match the condition of
having more than 300 unique species of birds
associated with them.
In this case, we're going to set the fill color to blue.
Now if we combine this in the array with our previous style,
both styles will be applied in order with later styles taking
precedence where they conflict.
In this case, on the definition of the fill color
for a polygon.
So now we get something like what I showed you before.
But we can do something even cooler.
Styles cascade in the same way that you would
expect CSS styles to.
So if you don't provide a full definition for a style, it
will inherit elements of that style from
previously matched features.
So let's restrict this to features that have a
population of more than five people per square kilometer.
And in this case we're going to set the full opacity to
one, where it previously was about half.
Now this is going to cascade with our previous style, so
we're going to see a couple of distinct types of features.
Some features are green with 50 percent opacity, these are
where there are fewer than five people per square
kilometer and also fewer than 250 species of birds.
Some features are blue with full opacity, and this is
where there are more than five people per square kilometer
and more than 300 unique species of birds.
And already this map is conveying useful
information to me.
Everybody loves the east coast of Australia, right?
Just by glancing at this map, we can tell the highest
concentrations of both people and unique species of birds is
along the east coast of Australia.
So by combining these styles, we can represent multiple
different strata of information on a single map.
I think that's really cool.
And the final point is that this can
be dynamically updated.
The styles, arrays, and NDC property, which means in the
Maps API JavaScript, if you update that property at any
time, the map will automatically update to
represent the new state.
So let's have a look at a little example.
Maybe I'm a little bit over birds now; I'm more
interested in plants.
So we'll create a new style that colors the map red where
we have more than 1,000 unique species of plants.
We're also going to create a function called Update Style,
which is going to read back our previous definition of the
style, which is this array we parsed in.
It's going to override the first style and array with
this new style about coloring the map red
where we have plants.
And we're just going to hook that up to a
Button on our map.
Every time someone clicks that button, this code is going to
run, the property will be updated, and the map will
automatically update to reflect this change.
So click this button, three colors.
Right?
This is great, because as I said before, you're now no
longer limited to deciding exactly what you're going to
show your users in advance, but instead allow them to sift
through the information that you can make available
publicly on your website and extract exactly what they're
interested in.
Couple of pitfalls.
As I showed you before, you can restrict what data is
shown by parsing in a Where clause as
part of a query object.
In this example, I've restricted the data to only
show elements that have fewer than 100
unique species of birds.
But I've also very cleverly decided to add a style that
only applies when there's more than 100 hundred unique
species of birds.
Obviously that style isn't going to take effect.
Just something to watch out for.
Of course, in all these demos, I've shown polygon styles.
You can also style other elements of the map.
You can control the border thickness, color, and opacity
for polygons.
You can control the thickness, color, and opacity for
polylines, which you could use, for example, to represent
routes, so driving routes.
And you can also control the appearance of markers or
points by selecting from the set of icons
offered by Fusion Tables.
Couple of things worth knowing.
Styles can be applied to any table, not
just tables you own.
So you can take someone else's data and, without owning that
table and Fusion Tables or needing to clone it, restyle
it on your own website to represent what you're
interested in.
And you don't have to worry about the styles on that
changing underneath you because you can always ensure
that you control the default appearance.
With Fusion Tables Layer, you can have up to
five styles per layer.
And you can have up to five different layers per map, one
of which can contains styling information.
And this is still part of the Maps API
experimental features.
We wanted to push this out so that you as developers can get
started playing with it.
There might be a few bumps in the road, but we're working
hard to ensure that this works as stably as possible.
And even more exciting, this is live today.
The reference should have gone out sometime
in the lost 10 minutes.
So you can start building website that use this feature
as soon as you leave this room.
[APPLAUSE]
And now I'm going to pass over to our guest, Simon, from the
Guardian data blog.
He's going to talk about how he's been using Fusion Tables
out there in the real world to help build his sites.
[APPLAUSE]
SIMON ROGERS: Hi.
I feel a bit of a fraud here because all you people here
actually make things, and I'm not one of the people that
makes things, I just take advantage of things that other
people make.
But we do use Fusion Tables quite a lot, and we're just
scratching the surface of what's possible.
I'm personally aware of that.
I'll explain a bit about what we do and then how we use it.
So I edit something called the Datablog and the Datastore,
and what we do is every day we just publish raw
data around the news.
So, for instance, this is the site today, so we just had
this UN report that projects the population to 2100.
And we'll take that data, we'll make it more accessible.
We publish it using Google spreadsheets because that's
such a good way to share data.
And we'll show visualizations, we use Fusion Tables quite a
lot for that.
And these guys did this stuff for us.
So we put our data out there.
And we put it out there so other people can then do stuff
with it and give it back to us.
And I suppose the reason for that is because nowadays
nobody trusts journalists.
And they rarely trust what we're telling them.
So we can say, well, this is the data behind the story.
We can make that story real and we can bring it to life.
I suppose it came out of the fact that we were just
accumulating all this data.
And there's so much data, raw information out there.
So much of it is available in totally inaccessible formats:
in PDFs or badly formatted documents.
We've got something called the Office of National Statistics
in the UK, which publishes tons of data, but they're used
to publishing books.
So the published data as it was in the book was all
formatted preferably with lots of lines and merged cells and
stuff, which is no good if you actually want to do
anything with it.
So I guess the reason we came across Fusion Tables was
because like a lot of places, we have a real
kind of time famine.
We have a lot of developers who are really busy on
creating enormous things.
Actually, if you want something built, you have to
really justify it.
And I can't really justify my getting my map built compared
so building the iPad for the Guardian, or whatever.
So you've got to justify maps, then.
So we just came across Fusion Tables.
And at first, when somebody told me about Fusion Tables, I
didn't really get it.
I didn't understand whether it was a spreadsheet.
It wasn't really a spreadsheet and it wasn't really--
so I didn't understand it until somebody said, oh it's a
really good way for making maps.
And that's how we got into it.
And this is one of the first things we did,
which was the blitzes.
people and you code that's with the Second World War some
in general in the blitzes, in particular, was a big attack
on London in the year of 1940.
And it turns out that basically the London fire
brigades kept these records during the blitz, which are
all kept in the London Metropolitan Archives in a
great big book, obviously.
Neatly tied records.
Every time the fire brigade was called out to a bomb,
somebody would go back and tie a record after.
It was pretty amazing, considering what were were
going through at the time.
And we thought well, we should just take our data--
and there was too much data to record for every day.
There was 800 days of this.
But what there was was we thought, let's take the first
night of the blitz and we created this list here.
And all we had for this list was we had a time, we had a
location with a street address.
And that street address would be a street address that might
not exist anymore or would be a partial post code.
Every street, you have zip codes here.
Every street has a post code in the UK.
And you might just get a partial address.
That street there, Suffolk Street, that might not exist.
So what we did was we did this table, we put it in, then we
thought let's try a Fusion Table, and see what it does.
And because it works like Google Maps works, it
basically found 95 percent of those addresses just by having
the first name of the address, and we added London, UK
because it seemed to just default to America otherwise.
And it would collect 95 percent of the addresses and
then we could correct the ones that were missing.
So of the 300 places, you could then find the other ones
that were missing and easily adjust them.
The fact that we could do that.
And you create something, then, which brings
that data to life.
And it means that you can see what happened.
And people were going on there and exploring the data and
saying that's why there's a new building at
the end of my street.
That's what happened there.
It's a really nice way for us to be able to display that
data and make it real for people.
And do something online that takes advantage of the
features that you've got there.
This is something else where we've got a lot of data.
The other thing that we'll discuss in the UK are school
results, especially on parents obsessed with where my
daughter is going to go in five years' time.
And the government every year releases
secondary school results.
And they release it with the school name and a post code
and an ID for that school.
So if she's got an ID for that school, something we can start
mashing that data together and bring it in with other data.
And they also release data on how much you spend for each
pupil in each school with the same ID codes on separate
sheets So we can bring that stuff together.
And we can do stuff like this, which it what goes in the
paper which shows the spread by local authority across the
UK of how much is spent on these pupils.
But the really cool thing about bringing all of those
schools into a Fusion Table it really brings it to life to
people online.
Again in a way that--
I could do that--
in a way that we wouldn't be able to do otherwise.
To be able to have each of these schools that you can
then click on and you can find your school.
And we started zooming into where I live,
which is down here.
Let's see if I can zoom in to that.
So each of these dots is a secondary school.
And secondary school in the UK is where you go to from 10 to
18, and you do A levels there and so on.
And the fact that you can get really close there-- let me
just find our school.
And for each school you've got, you can pull all that
stuff up and you can say how much-- so that school there is
8,000 pounds per pupil.
And you've got the results there and you can start to
compare real-life data and actually tell you something in
a useful way for us to be able to make that data come alive
for people.
But then to just use it in a standard way every day is
really useful too.
So when they had the Fukushima accident in Japan, being able
to pair our data and then use it.
To So you can highlight different events-- let's just
go to the full-screen one, but that's too small--
but the fact that they store this stuff up and they pick on
different places is just a really useful thing for us.
And we can update this and add to it and it
will display on it.
Because traditionally--
and this might sound what you guys want--
every time we've done a map online, has meant somebody in
Flash manually drawing each place or drawing each border.
So it's quite new for us to be able to have stuff ready on
spreadsheets and to be able to do it without involving
developer time is just a really good thing.
And then here's another example of big data.
So this is every--
the government in the UK is obsessed with open data mode
because it's a very easy way to appear transparent.
And so what they start doing is publishing things like
every public building in England and Wales and what its
carbon emissions are.
The problem was, they wouldn't release the full data.
So somebody put in a free information request and they
got this and then they sent us the data.
And we use Fusion Tables to map that.
And what you've e there is you've got
thousands of public buildings.
And the fact that we can show those all.
We can color code them all up by
different types of emissions.
And you can see by how much.
You've got a lot of buildings there.
The red are the worst ones, of course.
So let's see if we can go into some of these.
And you can see where all the public buildings are as well.
So the type of building is on the side.
And you can show the information quite quickly, and
creating something like that took about 20 minutes.
And again, that's really, really useful for us.
And then we've got the Wikileaks data.
Now obviously, Wikileaks was a lot, a lot of data.
There were three releases.
There was Afghanistan, Iraq, and the big cables
release at the end.
With the Afghanistan data, that was
about 90,000 instances.
The Iraq data was 391,000 instances, which makes the
Iraq war the most documented war in history.
It was in the Pentagon papers, which came out about Vietnam
was 7,000 documents.
So I think 400,000 documents, we know everything there is to
know about that war.
And it became obvious quite quickly the big story was this
huge numbers of people that died.
Even in this database, where 109,000 people died.
That covers everything from coalition troops through to
Iraqi civilians.
And we did two things for this.
We did this thing here, which is quite beautiful.
Took a lot of technical time up.
It's basically just presenting one day in
the life of the war.
We had a Guardian correspondent writing this
lovely piece to go with it.
You could click on different events and you could walk
through it and so on.
And we did that.
And then I had this dataset, which I've been using to do
some analysis on, which was basically every instance in
which somebody had died.
And there was 66,000 instances in which at
least one person died.
And we thought, maybe we should just put that on a
Fusion Table to see what it shows us.
It might give us something we can show on the site.
It wasn't something, to be honest, we spent much time on.
And this is what we got back.
And this is the most popular interactive
map we've ever done.
Basically, each of these dots here is an instance where at
least one person died.
And because there are so many, it gives you a real
sense of the war.
And it tells you more than you could write 15,000 words on
how many people died in Iraq.
But to actually see them like that totally
brings it to life.
Now anybody got any thoughts on which was the most popular
out of those two interactives?
It was this one.
And I think the reason is was this one, the one that we
haven't spent all that technical time on, was because
it allows people to explore the data for themselves.
A lot of the interest in data at the moment is about people
feeling empowered by data, of being able to find out about
things they didn't understand about before
and to discover things.
And that's exactly what this allows us to do.
It gives you a way to explore the data yourself.
And you can zoom in and out.
But the great thing about zooming out is it gives you a
sense of the war in terms of patterns.
And you see, you're already starting to get these
groupings as you pull out.
See these roads along here where there
were lots of IED attacks.
And you get a real sense of what's
happening across the country.
Now, if you're driving down that road every day, you might
feel a little bit nervous, for instance.
And that tells you the story of the war in a way that
thousands of words wouldn't necessarily do.
And then one thing we've been experimenting a lot with
recently is the auto-map countries.
Now again, for us to draw a map, traditionally it's been
our Flash guy drawing it, or we have some stuff where our
developers have done stuff, but for me to be able to
create a map like this is a new thing.
And what this is is that our Foreign Commerce Office, which
is the equivalent of the State Department here, they issue
travel warnings about countries that
are safe to go to.
And we thought well, this is just after Egypt and Libya
kicked off and so on.
We thought, why don't we just take snapshots of the Foreign
Commerce Office's travel warnings?
So we scraped the site and we graded the travel warnings
from don't go here at all to you can go here, it's fine.
And this is what we got.
And the great thing, actually, in terms of our style, besides
being a million miles away from how we would start all
that math anyway, we would use those kind of borders, we
would use 100 percent opacity anyway.
And the great thing is you click on that and you convert
the HTML and that tells you don't go to Libya.
Or maybe don't go to Afghanistan either, but you
can go to Iraq or Pakistan partial.
And so it just gives you--
it's just a useful, yeah, it's all
troubled, don't go to Iraq.
Just ignore the what's on the map.
So for us to be able to create something like that and to put
a story up quite quickly is just really, really good thing
and this is really what we're about.
And then there's this one here, which
Kathryn mentioned earlier.
Now what happens is, the government every four years
they do this thing called deprivation indices.
And they're really important in the UK because they're how
the government hands out all its funding.
So it's basically a measure of how poor people are in
different parts of the country.
But of course they don't use a boundary that's recognized in
any other way possible.
In the UK we have counties, which are quite big.
They're a lot bigger than the counties that you have here.
We also have wards, which may be similar to the counties you
have here in the States.
Our counties are more like states,
just to confuse things.
But they also have these little areas, which are only
used for the states, they're called lowest input-output
areas, which are basically small things that maybe
10,000 people go.
So for us to manually map that would be impossible.
But it's not that we can embed the official version.
This is the official website.
And if you want to find out about any of these places,
it's slow, it's clunky, you don't know what
these places are like.
It's trying to load here.
It's pretty poor.
And it doesn't give you a picture of the whole country
as a whole, you can't see the whole country at all.
It doesn't rank it.
It just allows you to zoom down eventually, see how slow
it is there.
OK, we're getting somewhere.
Anyway, it's pretty poor.
So we thought, we've got all that data.
It'd be great if we could do something with it.
So firstly, we had to find the polygon data, which is a bit
of a pain there.
Eventually we found an academic.
So each of those little areas is super out there, but
imagine having data at that granular
level is really useful.
But we had to get the polygon data, which was difficult, but
once we had that data, it meant we could create
something like this.
Click on it and go to full screen.
So basically, that's everywhere in England and
Wales, and the deeper the reds get, the poorer it is and the
deeper the blue get, the richer it is.
So that's London, it's kicking off of London, and
[UNINTELLIGIBLE]
is the poorest place in Britain.
At least now I know where it is.
But you see, these areas out here are quite wealthy.
And here you got the score that was
covered up by the school.
You've got very explanatory data there, and see over here,
it's quite wealthy at all.
So the great thing is to be able to show that kind of data
and be able to illustrate it is really, really a
new thing for us.
And it gives you a picture of the country you can't get in
any other way.
And it's better than the official site, so what's not
to like about that?
I'm going to show you one last thing we did last week.
We're running out of time, aren't we?
OK.
So last week, we had three major elections
in the UK on Thursday.
There were two devolved parliaments, and Wales and
Scotland elected their members.
But we had to vote in our voting system.
At the moment in the UK, we vote first past the post,
which basically the person who wins the most votes in the
seat wins, that's how it works.
And but they wanted to change to a more proportional
system called AV.
It didn't win, it was a disaster for the campaigners.
But basically we wanted to create a live results map.
We don't have much time, so we thought how are
we going to do this?
So we basically created a merged table.
We merged together one map which showed-- and the problem
with this, the tricky thing about it is that in England we
had local authorities, and in Scotland we had Parliament
seats, and in Wales we all had Parliament seats.
So we managed to put this all together using the Fusion
Tables merge thing.
And then we had the data in separate sheets, merge that
together too.
And basically all we had to do to change this map, to make it
live, was every 10 minutes just update the data on the
data sheets.
And that's all we had to do, just load a new spreadsheet.
We were getting a spreadsheet download of the results.
Just had to upload that.
And so to turn that map into a live map.
And people were using it during the night.
Every time it was like a 30-second job.
So for us to update that, the great thing about the
turnaround quite quickly.
And we used the same colors and styles for the newspaper.
It just made it consistent.
And you can do this nice stuff.
Everybody voted no apart from Glasgow.
There you go.
These purples were the only places that voted yes.
Edinburgh and Glasgow and a couple of places in London.
There you go.
I suppose I should say that what we're about as
journalists is that telling stories and making
data come to life.
And all Fusion Tables have done is give us
a tool to do that.
Thanks a lot.
[APPLAUSE]
KATHRYN HURLEY: All right.
So just want to go through a quick summary of what we
showed you today.
So I talked about new features for trusted testers in the
Fusion Tables API.
These include map feature styling and Info window
customization.
And James McGill talked about the new feature in the Google
Maps API and its dynamic styling.
And I just want to provide a few links for everyone.
Links to Fusion Tables, Fusion Table APIs, et cetera.
I'll leave this screen up here while we take some questions.
So James McGill, if you want to come up.
And so we have a few minutes for questions.
And you can also ask questions of Roger as well.
AUDIENCE MEMBER 1: Just want confirm the map is generated
through the styling of the cached tiles?
JAMES MCGILL: Yes, through the dynamic styling.
AUDIENCE MEMBER 1: So if you do the dynamic styling, you'd
also generate the tiles.
JAMES MCGILL: Yes, that's correct.
So you still get that performance gain, even with
the dynamic styles.
AUDIENCE MEMBER 1: In terms of tiles size of dimension, we
still only have 128x128 or--
JAMES MCGILL: It's 256x256, but it's just an
implementation.
it's not something that you need to worry about.
AUDIENCE MEMBER 1: OK, what if I want to push to the mobile.
Would you allow us to do 64x64 or--
JAMES MCGILL: We've found that the current tiles work really
well on mobile.
They come down relatively fast and you get that--
because you're not dealing with having the number of
client-side objects--
the performance is going to be better on mobile.
AUDIENCE MEMBER 1: Second really quick question.
The Fusion Tables, the geometry type you
mentioned is KML.
Any other geometry types you support?
KATHRYN HURLEY: So you can enter street addresses if you
have location data street addresses or
lat-longs or points.
What else?
What was that?
Oh, other tools.
And if you have shape files, you can import shape files to
Fusion Tables using a tool called Shape Escape.
So it's shpescape.com.
AUDIENCE MEMBER 1: All right.
Thanks.
KATHRYN HURLEY: I think over there.
AUDIENCE MEMBER 2: Yeah, I just have a question.
You showed in the API on how to enter data one at a time
into the Fusion Tables.
Is there a bulk API for loading
gigabytes worth of data?
KATHRYN HURLEY: You can send up to 500 inserts at the same
time, just separate them with semicolons.
AUDIENCE MEMBER 2: Do you have to then check
and resend the 500?
I can obviously see network timeout or success-failure.
That being if you had to insert, let's say, 10,000 data
points into a Fusion Table, so 500 at a time.
Something times out, check for a response,
have to resend those?
Is there just a bulk upload type of facility available?
KATHRYN HURLEY: So if the request is successful, it
returns the row IDs.
If not, then the inserts did fail and you have to resend
all those inserts.
AUDIENCE MEMBER 2: Right.
But 500 is the maximum at a time that you can upload.
KATHRYN HURLEY: At this point.
But It would make a great feature request to be able to
allow more.
Yes.
AUDIENCE MEMBER 3: Question to James.
In Fusion Tables Layer, if you have point data, and lots of
it, and you're using tiling, how do you manage to generate
tiles that don't split the points and are able to show
them as whole.
JAMES MCGILL: Across tile boundaries?
AUDIENCE MEMBER 3: Yeah.
JAMES MCGILL: So we use a method that ensure that if a
point is selected in one tile, it's also selected in all
other tiles.
So that guarantees that if that point crosses the border
of multiple tiles because it would be present in both tiles
or all four tiles, it'll render correctly.
So people viewing your site, with the exception of the fact
that Fusion Tables Layer doesn't let you drag your
markers, for example, they shouldn't be able to tell the
difference between Fusion Tables Layer and you having
created these points manually.
AUDIENCE MEMBER 4: Hi.
I have KML that is using the time line feature, modeling
311 data, pot holes, graffiti, abandoned cars,
that type of stuff.
I have about 10 years of that model for
showing in Google Earth.
Can I upload that to Fusion Tables and still have the time
line feature?
KATHRYN HURLEY: We do have a story line, but is this
something that you want to show on a map?
AUDIENCE MEMBER 4: Yeah, I want them just like they do in
Google Earth.
I want to be able to throw all those 10 years and close and
open up when the things were going on, abandoned cars,
graffiti, where they were at and all the rest of that.
Is Fusion Tables supporting the time line?
KATHRYN HURLEY: No, you can't do anything like that in
Fusion Tables.
JAMES MCGILL: But you could use, for example, some
JavaScript Maps API to achieve, I
think, the same effect.
AUDIENCE MEMBER 5: The political boundaries you
showed, is it something that Google offers for all the
countries or something you expect the user to create
those political boundaries, different
states for each country?
JAMES MCGILL: So Google provides Fusion Tables, but
the data is generally uploaded by users.
The advantage is that you can mark that as public, so you
could upload some political boundaries and allow other
people to fuse that data with their own data.
And we've seen a lot of people have uploaded their state
borders, country borders, political borders and other
people making good use of this, combining it with other
information they have to make these maps.
AUDIENCE MEMBER 5: I see.
And one more question.
If our Fusion Table, let's say I have it on 1 million rows of
data, is there a way the person who's using it, they
can only see a subset of the data?
Like I'm logging in from northeast, I can only see
northeast data.
Somebody's logging in from southeast, they can only see
southeast data.
But the data that's is the table has all the data I need.
JAMES MCGILL: So you could use the example I gave where I
restricted it only show points where age was greater than 50.
You can of course use spatial queries as well, so you can
get the current location of the user and filter it to show
only points within a certain radius.
Or if you had a bounding box for the area in which you
wanted, you could filter to only show those points, so
yeah, I think that's possible.
AUDIENCE MEMBER 5: Thank you.
Good presentation.
JAMES MCGILL: Thank you.
AUDIENCE MEMBER 6: Does Fusion Tables--
can I optimize any KML that I put into it?
Let me rephrase.
If I have highly detailed KML, normally huge files, if I put
them into Fusion Tables, will that be optimized when I try
to build a map?
Or do I need to strip that KML and simplify it?
JAMES MCGILL: So we're talking about simplifying polygons?
AUDIENCE MEMBER 6: Correct.
JAMES MCGILL: Yes.
So, the idea is that when that data is uploaded to Fusion
Tables, we do offline simplification to make sure
that it shows efficiently on a map.
So you don't need to worry about that as long as you can
keep the KML within a size that can be
uploaded to Fusion Tables.
AUDIENCE MEMBER 6: Yeah, I'm not sure I can, but that's
outstanding either way.
JAMES MCGILL: Or consider splitting it into multiple
files, adding multiple layers, and that's another way to
aggregate that.
AUDIENCE MEMBER 6: But still, that sounds
like a great feature.
AUDIENCE MEMBER 7: So for rendering point data using the
Fusion Table Layer, do you have any plans to allow for
custom images like custom marker images?
Because currently you can show dots of different colors and
different sizes, but if we could upload--
JAMES MCGILL: I think that would be compelling to file a
feature request. And if that's something enough people are
interested in, then maybe it's something we'll look at.
We're actually out of time, but think Kathryn and I will
be hanging around I/O.
We'll be at the party tonight.
So if you have any more questions, come up to us after
and feel free to ask those questions.
Thank you.