RACHEL DEACON-SMITH: Today we're going
to give you a very quick talk on BigQuery GIS.
So, looking at the agenda, we'll give you
a very brief introduction into BigQuery GIS So,
what exactly is it?
And some of the data types around it.
And then we will spend the bulk of the time
giving you guys a demo, so showcasing the features
and showing you some of the example use cases.
You have three speakers today, Kannappan, Thinh,
and myself, Rachel.
We are all a strategic cloud engineers
based in the London office.
OK, so BigQuery.
For those that do not know, BigQuery is Google Cloud's data
enterprise data warehouse.
It can manage large data.
So, we manage storage on the petabyte scale
and you can run analysis on up to billions and billions
of growths.
You can interact with BigQuery using SQL.
And it's fully managed and serverless,
so you don't have to worry about spinning up clusters
and managing resources.
That's all done for you.
The idea that you load the data, run your analysis.
It's super big, it's super quick.
So then we move on to BigQuery GIS.
So GIS is Geospatial Information Systems.
And BigQuery is one of the first cloud providers
to offer an MPP, massively parallel processing data
warehouse that supports geospatial functions and data
types.
So we have the concept of a geography data type,
and that's essentially a point set on the Earth's surface.
And when you combine that with your location data
in your BigQuery table--
so, for example, if you have longitude
and latitude or country codes--
you can essentially derive really powerful insight.
It makes your data lot more relatable.
Underpinning this all is the same library
used in Google Maps.
And we also have the BigQuery visualization tool
which is a lightweight tool that allows
you to view your analysis against a live map.
So I mentioned the geography data type.
And I said this represents a point set
on the Earth's surface.
So by points set this can be an actual like point
on the surface or a set of points, so a line
string or a polygon.
And we'll see this in action in the demo.
You also have GIS functions and types.
And if you're familiar with PostGIS
you'll be familiar with this.
And these essentially just operate
on the geography data type.
Enough talking, I think.
Let's hand over to Kanna, who will do a demo
and show you some of these features in action.
KANNAPPAN SIRCHABESAN: OK.
Hi, welcome everyone.
So I'm doing a demo of, like the data
types and the functions that are available within BigQuery GIS.
So this, like we are starting with a blank slate.
So on the left side, you have the query and on the right side
you have the map.
So this is like a visualization tool as part of BigQuery.
So it's got query Geo Viz.
So what we are doing now is, basically, we
are starting from scratch.
So we are drawing a point of where we are.
So we are in Moscone Center.
So, the function we use is s t underscore geogpoint
and we provide the lat and long.
So, as you can see, we have the Moscone Center on the right.
So, yeah, this is one point.
Now we are going to draw one more point.
So this is the baseball stadium, Oracle Park.
So, as you can see, like, the s t
underscore geogpoint takes like two parameters.
The longitude first and then the latitude, just the convention.
So what you see now is, basically, you
have, like both the points using the union hall method.
And now we are going to connect both the points using
s t underscore makeline.
And I do yonan all again so that I display the line
on the dot on the two points.
OK.
Now we are going to draw like a polygon of three points,
using Google SF office as the third point.
And what we use here is s t underscore makepolygon.
And we use the job from text function.
And then we draw a polygon.
OK, once we have the polygon, now we
can do all the other measures like distance perimeter,
and so on.
So what I've shown here is that centroid.
What you're seeing now is basically
the public data sets that are available within BigQuery.
So the one that is shown here is basically the bike data set,
the Ford GoBike.
So these are the bike stations that are available.
So as we can see, there are like three clusters.
So this is the cluster in San Francisco.
So these are all the bike stations.
Now let's filter to the bike stations
that are within that specific polygon, using the function
s t within.
S t stands for spatial types which is
a common terminology in geog.
So, as you can see, there also like a street view.
So I just clicked on one of the points.
And as you can see, you can see the Ford
go bike station with the bikes.
So this is like a complex polygon.
So it's basically the zip code.
So you can get the zip code data set, your zip code data set,
from the BigQuery public data sets.
So what we see now here is--
So this is like, all the bike stations.
So yeah, this is like the cluster of points.
So what this cluster represents is the size of the circle,
represents the number of bikes that
are available within that specific bike station.
So, if the circle is bigger, which
means that there are a lot of bikes, and if it's smaller
it means it's a less number of bikes.
And what you're going to see now is basically from Moscone
within a 500 meter radius what other bike stations
that are available.
So these are some of the bike stations
that are available within a 500 meter distance.
Find a meter radius.
So, as you can see, that is one very close by,
in front of like the Whole Foods just right outside.
OK?
So, as you can see, it's like very
easy to actually do all this like basic methods and data
types.
So you can start using it to do very complex
geospatial functionalities.
Thank you.
THINH HA: Thanks, Kanna.
So, for my demo, I'm going to talk
about how you can use some of the functionalities we
discussed for analysis, for fun and profit.
Keeping with the cycle higher data set,
I'm going to move across the Atlantic
to London where we're based.
I'm going to also take the view of the operator of the cycle
highway service.
So, as operator, I want to know what do people use the cycle
service for.
And I like my drawing the 50 most common path
that's taken by my service.
I can see that most of the path is happening around Hyde Park.
So Hyde Park is a big station.
It's a big park in central London
that's very popular with tourists.
What this tells me is that people are using this service
quite a lot for tourism.
So, looking at the previous analysis,
I can see that some stations are more frequently used
as a destination i.e. more bikes arrive
at that attestation than no bike starting from that station.
So this may cause some capacity problems.
If I'm a tourist arriving at the station that's
very frequently used, I might not find a docking station.
So I want to do some analysis of this trend.
What I've done here is I've counted the number of trips
that started at the station and subtracted it
from the number of trips that end at that station,
and plotted on a map.
Red means there are more trips starting
from that station and blue means there
are more trips ending at that station.
Looking at the station with the biggest disparity,
I can see that corresponds to hop exchange
to Borough, which is where I know
there's a big farmer's market that's also
very popular with tourists.
So, with this knowledge, I can start
making some changes to accommodate for this trend.
This also make me ask the question,
do people use this cycle service for commute at all?
So, by isolating the trips to hours
in the morning versus evening commute,
I can immediately see a trend where in the morning
most people are using the service to commute
into central London and vise versa in the evening.
This is most pronounced at King's Cross Station,
which you might have heard from Harry Potter.
It's also a big commute spot where
for everybody outside of London commuting in.
So, personally, I also like to go to Borough Market
in the weekend.
I know that I won't be able to dock my bike.
That's a very common destination.
So what I can do is I can find the 10 closest station nearby
and immediately I can see that the closest
station is Park Street bank side, which
is about 164 meters away.
Thank you.
RACHEL DEACON-SMITH: Thank you.
Just to quickly wrap up, hopefully you guys
have seen how you can harness big data with GIS
to derive insights and make your data bit more relational.
Don't take our word for it.
We have a few links on the slides where
you can go and have a play.
It's currently in beta, but, as you've just seen,
there's a lot you can play with and familiarize yourself with.
Thank you so much for attending and for listening.