Kind: captions
Language: en
there's a lot of power in mapping data
whether it's to visualize findings for a
story help with your research or just
map your favorite surfing or hiking
spots
but what about grabbing data from sites
that you might see in a table
like what we regularly see on wikipedia
and opening it up in a spreadsheet
and how would we even take that a step
further and map that information
well over the next few minutes in this
session i'm going to take you through
three things
how to pull data from sites
automatically
how to sort it in a sheet
and how to get that data on a map
to visualize what you found
[Music]
hi everyone and welcome back to this
series on how to do open source
investigations from home i'm ben and
this is part 15 so let's get started
[Music]
i'm going to start this session off with
what i find is a really interesting
place
it's the airport in cots in greece
and if we zoom in a little bit closer
there's something interesting that we
can see
the entire airport or most of it is very
heavily pixelated you can see some of
the roads and the trees and even
buildings around it are clear and yet
when we cross over the airport it's
completely pixelated and small cubes
this is really interesting and i don't
really know what's happening here but i
can tell you it's probably pixelated for
a reason
are there any places like this on google
maps or any other map applications
maybe we can have a search for a map of
blurred paces on google maps and we can
see that there's maybe some articles
25 places you can't see on google maps
or why
for example answering that crucial
question
but i don't really see a map application
on here what if i wanted to create a map
to see where those things are
well
i can't really find one
but what i can find is something
interesting
it's a wikipedia piece about a list of
map images with missing or unclear data
now i could probably scour through the
rest of google earth and google maps and
maybe have a look for more sites like
this and have a look on twitter and see
if anyone else has found any what
wikipedia has done
is created a list for us with
coordinates now this is pretty handy and
what i could do is go one by one
and add each of these into
maybe google maps or google earth and
start pinning them but why don't we just
have a look at exporting this table into
a spreadsheet first of all
well let's do that the way we can export
data that looks like this into a simple
spreadsheet something as simple as using
auto functions in google spreadsheets
what i've done here is i've started up a
brand new google spreadsheet and i'm
going to type in a very simple term and
i'm going to use that term to pull this
data from this wikipedia site
now what i'm going to do is take my url
from up here
and i'm going to pop it in this sheet
but i'm not going to do that yet because
what i want to do is have a look for an
import function
the function that i'm looking for starts
with the word import and you can see a
number of options that pop up when i
write that import xml data feed what i
want is html
imports data from a table or list in a
html page
let's click on that it's going to open
up a little bracket i'm going to press
quotation mark to open up my quotation
i've pasted my url from the wikipedia
page
i'm going to close my quotation mark
i want to create a table as well
so i'm going to type in table
quotation mark and then pop a little
zero in there and close my bracket i'm
going to hit enter
and let's see what happens
great
what this has just done is pulled all of
this information
into this spreadsheet so you can see all
of that data has been pulled into my
nice little spreadsheet here
now this is what i like to call dirty
data it's not very well sorted yes of
course it's sorted in the manner that
wikipedia had it
but i want to maybe start splitting some
of these columns
so we can use a simple function
like splitting columns
and really split up this information
because you can see some of these have
multiple sections in there separated by
forward slash
the coordinates maybe together maybe we
want to split them up from
latitude and longitude and things like
that
so let's clean up that data just a
little bit to split some of these
columns what i'm going to do is click on
data and split text to columns
now the separator for some of these
seems to be a forward slash so i might
do that one
so i'm just going to replace that one so
now you can see i have a list of
coordinates in here which is really
quite useful to use and i also have
decimal coordinates over here
although there seem to be spaces where
the enter has been hit as well what we
can even do is split this again and i'll
go through this process and fast forward
it just to try and clean up the data
before we start exporting to a map
[Music]
great so what i've done is i've cleaned
up the data and now i have my latitude
and longitude in these columns so what
i'm going to do is write lat
for latitude and long for longitude what
i've been able to do is through google
sheets extensions
there's a lot of add-ons that you can
get through google sheets and one of the
add-ons is called map my sheet this is a
really useful one because it basically
will create a map automatically for you
based on coordinates
so if i just click start here
what i can do is click create map i'm
going to create a new map select tab one
i'll call it
uh
map
of
blurred
places
and click next what i want to do is
rather than choosing address and this is
a cool option with this this add-on is
you can actually choose an address so if
you don't have coordinates but you have
an address like something straight you
can do address and that will also give
it coordinates as well but what we have
is latitude and longitude since some of
these aren't necessarily addresses but
might be different areas in the middle
of the ocean
or different islands or military bases
that might not have a specific address
so we can just go to geo coordinates
what i want to do is select my latitude
column
so that will be my lat my longitude
column
long and i'll click on next skip the
filter you can add a filter it's kind of
cool for countries or anything like that
i would usually clear out some of these
columns so that i don't have as much
data that i'm not really using you can
control who views the map as well so if
you wanted to make this publicly
shareable or not you can do that what
i'm going to do is click on view map
since i've now generated my map out of
these coordinates
and we'll open that up
and what you can see is i've
successfully mapped that data out now
that would have taken quite some time to
map all of this
i can also click on my satellite imagery
here
and let's maybe check out some of these
spots so we can zoom in
on specific locations and see what's
what's going on in some of these places
that one seems to be blurred as well
that's the toulon arsenal in france
and now we have a map of a number of
blurred places as listed in that
wikipedia
article there wasn't a map before that
we had on google but what we could do is
make this into
a publicly viewable google maps
sheet and publish that online for
something for people to see there's lots
of different ways you can do that sort
of stuff and lots of different data you
can have access to the reason why using
a sheet
whether you do
google sheets or if you prefer to use
excel sheets which also has a similar
function using these sheets means you
can add more data in so if i wanted to
make a sheet of football stadiums but
there were only certain football
stadiums for say australia
but i wanted to also include football
stadiums for indonesia and thailand as
well then i'd be able to add different
data sets to show that and this is just
a really cool way to visualize data but
also a very useful way to visualize it
without having to do that manual work of
going through each one of these and
collecting that what we've successfully
done is we've identified this site we've
pulled all the data from this table
that's on wikipedia straight
automatically into this sheet we've
sorted to the data into further rows to
clean it up a bit so that we can really
get that clean latitude and longitude
and we've successfully mapped that just
in the space of a few minutes
very simple easy mechanism and and very
useful to show a visualization
of where some things are on the earth's
surface i hope you found this tutorial
useful
i'll be doing a few more of these sorts
of mapping ones as we go through with
different ways of collecting data
different ways of converting it into
visual format ways to get into google
earth so don't forget to hit that
subscribe button thank you so much for
tuning in and i'll see you in the next
session
[Music]
you