Importance of Natural Resources

Cloud OnAir: Add rich geospatial analysis to your toolbox with BigQuery GIS


[MUSIC PLAYING] CHAD JENNINGS: Hello, everyone. Welcome to Cloud OnAir. These are live webinars
put on every Tuesday by Google Cloud Platform. My name is Chad
Jennings, and I’m a product manager for BigQuery. SOLEIL KELLEY: Thanks, Chad. My name is Soleil Kelley. I’m a product marketer on
the data analytics team here at Google Cloud. And today, we’re going to
talk about geospatial analysis and BigQuery, in particular,
doing that with BigQuery GIS. Do note, you can ask questions
at any time on the platform. We have Googlers on standby
to answer your questions. So our goal today is
really a couple-fold. We want to, A, introduce you
to the BigQuery GIS service if you’re not yet familiar
with that and, B, get you up and running so you can
add GIS into your toolbox as an analyst. And let’s jump right in. So spatial context really
matters from our perspective. Maps provide this really
unique type of context. And when we think of context,
think of the who, what, when, where, whys, and hows. In particular, that where– when you add your data,
and you put it on a map, it just instantly becomes real. It becomes relatable as a human. And that’s really powerful to
help you make better business decisions. It, second of all, gives
you this rich extra set of data at your disposal. And there’s a nice benefit
there because, when you put your data on that map,
and you join with other things, you instantly have context
in physical space, right? Those maps connect
that physical space to that various, sometimes
intangible space, of data analysis on a computer
connected to the cloud, right? And so yeah, maps really matter
from the human perspective and also from the
business perspective. So take, for example,
this map in Denver. I might be a retailer in this
particular location in Denver. And I would want to analyze
where my customers are or the different demographics
from different neighborhoods in the Denver area. And maybe I’m considering
opening up a new store in a different neighborhood. I would also want to factor in
other competitors in the area and think about different
physical geographies that are present in this space as well. And when I actually overlay
my demographic analysis on top of the map, as opposed to
just looking at tables of data, I instantly just
have more context. I can see where the
freeways are that might be gridlocked during that time
I want my customers to come to my store. I can see physical,
geographic boundaries, like bodies of water
or mountain ranges, and things of this nature. So really useful to be
able to contextualize your data in maps, for sure. Now, if you have just
a small set of data, you can imagine almost
doing this analysis by hand. If you had just a few hundred
customers, and they’re all coming from a certain
neighborhood– easy enough. But in our age of information
and the accessibility we have to just beaucoup
data from all the open data sets, from weather to census
data and other sources, you can really go pretty wild
from an analytics perspective thinking about all the fun
things you can do with space. And for that, you might
be thinking about, if you had millions
of customers– let’s just hypothetically say– you wanted to join that
with some of those bigger, bigger data sets, you might need
a little bit more horsepower to actually conduct
that GIS analysis. And for this, we’re incredibly
excited to have launched BigQuery GIS into beta a few
weeks back, here, in September. And really what this means
for analysts, especially those that are already
using BigQuery, is that, hey, we’re bringing
the GIS functionality right directly into the
data warehouse. You no longer would need to
export your data elsewhere and bringing it to a
purpose-built GIS application, right? All that core functionality
is right there on top of your data and
accessible right there. And what’s really unique and
different about this service is combining this
Cloud GIS functionality with the raw power of BigQuery,
and its Massively Parallel Processing, or MPP
architecture, to do big data– to kind of merge big data
and GIS in the cloud. And that’s super exciting. We’re really excited to
bring that to market. And Chad, being product
manager for BigQuery, is going to dive into a little
bit more details on this. CHAD JENNINGS: Yeah. And I just wanted
to say, though, that intersection
of big data and GIS is a thing that we’re
really excited to address in the marketplace. So with the launch
of this, BigQuery is the only cloud MPP
enterprise data warehouse to support GIS data
types and functions as first-class citizens. And so we’re really
proud of that. And the engineering team has
worked on this, actually, in conjunction with
the Earth Engine team. So we used the same
computational libraries under the covers that power
things like Google Maps, Earth Engine, Google Earth. So it’s really bringing a lot
of really awesome Google assets to our customers, which is fun. SOLEIL KELLEY: [INAUDIBLE]. CHAD JENNINGS: All right. But first off, you may
have a question of, what is this BigQuery thing anyway? Let’s start there. SOLEIL KELLEY: Sure. CHAD JENNINGS: So BigQuery is
Google Cloud’s enterprise data warehouse. So you’d interact
with it in SQL. And it is serverless
and fully managed. And what that
means is, you don’t have to mess with spinning up
nodes or spinning up clusters. We handle all of that for you. And so what that means
is, you bring your data, you bring your workloads,
you load them both, you press the button
that says Run Query, and we spin up all the
compute resources and storage resources that you need. That’s what fully
managed means to us. This product scans– goes
super big and super fast. Our largest customers
have hundreds of petabytes of storage with us. And our largest
queries regularly exceed 20 or almost
30 billion rows. So big data and GIS
together, right? SOLEIL KELLEY: There’s that
intangible data analytics thing in the computer
box thing again, right? CHAD JENNINGS: Yeah, really. What does 30 trillion
actually mean? SOLEIL KELLEY: I have no idea. Just a lot. CHAD JENNINGS: So here’s
what BigQuery GIS actually means, right? We’re supporting geographic data
types, geographic functions. And we’re going to go through
these in some detail in just a second, here. And then, kind of where
the payoff happens is, we have launched something
called BigQuery Geo Viz, which is a lightweight
visualization tool to put all of those cool data
that you just figured out onto a map. All right. So a little context
setting– why bother? Euclid would look
at this map and go, that’s perfectly fine, right? That is a straight line between
two points, shortest distance, that’s how you should go. Except, as we all know now– Euclid didn’t know
this, but we do– curved Earth– the shortest
distance between these particular two points– Seattle and Stuttgart,
if anyone is curious– is a great circle route. And to be honest, even though
I come from a navigation background, I never
really quite got what a straight
circle route truly meant until I looked
at it visualized on Google Earth from space. So Euclid was right. A straight line
between two points– that is the right way to go
if you want to get there fast. And that is what a straight line
looks like on the curved space. So with geographic data
types and functions, we want to honor the curvature
of the earth, right– seems like a big
thing to honor– and actually do these
calculations exactly right. So these are the data types
that we’re supporting– point, linestring, polygon,
all the way down to collection. So it’s quite a rich dataset. All right, and now,
Soleil will take us away with the functions. SOLEIL KELLEY: Yeah, for sure. So we love our SQL
verbs, and we’ve just brought in about 40 new
verbs into the BigQuery as first-class citizens again. And they conform to the post-GIS
project spatial type function convention, the ST_. And we have a number of
different functions here you can see in the
table on the right. If you are familiar
with PostGIS, this will be a walk in the park. And you can maybe grab
a super quick glass of water or something. But if you’re just getting
started, and want to just know, at a high level, what these
functions are all about and the things that you can
do on your geospatial data, we’re just going to dive
into that super quick. Constructors– as
the name suggests, these are really about
building new geographies from existing
coordinates of, say, a lat-long pair, or
existing geographies, like a couple of polygons or
lines, and making a collection. So the diagram here
demonstrates a set of five different lat-long
pairs and making a line out of those, right? Parsers and formatters–
I mean, obviously, we want some interoperability
between different formats. And so these are all about
creating or exporting geographies into
different formats. So from binary to a
polygon, from GeoJSON to text and things like this. These are the functions that
you would use to do that so that you have a
little bit more interop with other programs. Transformations–
again, so these are creating new geographies
similar to constructors. But they’re having
similar properties as their input geographies. So here, in the diagram,
we’ve highlighted the centroid function. So if you wanted to find the
center of some sort of zip code polygon, you’d use
that function to create a point, a lat-long set, out
of that existing geography and many other types
of transformations, naturally, as well. Predicates– so,
great for filtering. Is this region, or the
data within this region, existing within another region,
in this particular zip code, or something– you know,
yes/no, or true/false questions, rather– so great for filtering your
geographic data and whatnot. Accessories– so
sometimes you just need to know a little bit of the
metadata about your geography data. And so for this, we have a
number of functions here for, like, how many vertices or
how many points are there as part of this polygon? So you can ask those
types of questions. Is it a point? Is it a line? Is a Polygon? If you just get a whole
bunch of geographic data, you could ask those
types of questions there. Measures– as the name
suggests, pretty intuitive. But what’s the perimeter? What’s the area, distances
between points, et cetera? These are real
core functionality. CHAD JENNINGS: Right–
not flashy, but important. And here’s the flash. SOLEIL KELLEY: Super,
super important– I mean, these are, like,
when you immediately think about GIS data,
at least for me, you’re asking questions,
like, wait, what’s the distance between x and y? And these are questions
you often have. But yeah, joins are [INAUDIBLE]. CHAD JENNINGS: So this is
where the real magic starts. And doing joins on geographic
datasets– in the demos we’ll talk about it in a second,
we actually join on zip code. But we’re actually joining
on the zip code integer. With these functions, you
can actually– or sorry– the integer that measure or
that identifies the zip code. With these, you can actually
do joins on the geography, like, find all the points that
are in these two datasets. Join them together with
any of these predicates. So this is where the magic
really happens, here. SOLEIL KELLEY: Cool. CHAD JENNINGS: OK. But in terms of, like, eye
candy, this is the magic. So this is BigQuery Geo Viz. And you can see
from the GIF here that you can compose
a query, run a query, and then style the results
in a map, all interactively. It’s a lightweight tool, so this
isn’t going to handle millions upon millions– it’s limited to
about 2,000 points. But it solves the use case of– I’m an analyst, I wrote a query,
please let me see that on a map just to make sure that I’m
sane or that I got the results that I expected. SOLEIL KELLEY: Great for
ad-hoc exploration, yeah. CHAD JENNINGS: Exactly. And if you’ve got more
serious mapping needs, you can export a table
from BigQuery into GCS, and then import that
into Earth Engine. And then here, you
use JavaScript, and you can create maps of
arbitrary complexity and arbitrary beautifulness. Is that a word? SOLEIL KELLEY: It
should be, if it’s not. CHAD JENNINGS: There it is. Okey-doke, so we’ll dive
into a couple of demos here. And so referencing the example
that Soleil talked about earlier, we’re going to
pretend that we are retail site selectors. And so we have a store. And Soleil, the target
demographic of your store is– SOLEIL KELLEY: 25 to 34. CHAD JENNINGS: How about
25 to 44, since that’s– since that’s what I prepped? SOLEIL KELLEY: Yeah,
let’s do that one. CHAD JENNINGS:
Let’s do that one. OK. All right. So here, let’s cut
over to the demo. SOLEIL KELLEY: What
are we looking at here? CHAD JENNINGS: So this
is the BigQuery web UI. And what we see here is,
on the left panel here, there are a bunch of, basically,
asset navigators, right? You can look at your queries. You can look at saved
queries, the job history that you’ve run in this project. You can even look at datasets. Down here, I’ll double click
into the BigQuery public data. And there’s now a
whole bunch of stuff. The baseball dataset
is pretty fun. We’re not going to do
that one today, sorry. SOLEIL KELLEY: Unless my
customers are baseball folks. CHAD JENNINGS: Right. Right now it’s a retail shop. SOLEIL KELLEY: OK. CHAD JENNINGS: Any case,
the left panel here, to navigate assets– this window right here’s
the query composer window. So this is where
you write your SQL. And then you get your
results back down here in the lower pane. And so what I’m
going to do is I’m going to walk you through
this query real quick. So one thing that I
like to do a lot in SQL is use these WITH statements
to pull parameters up to the top of a query. It just means that,
if you want to share that query with somebody, or if
you want to adjust a parameter, you don’t have to go searching
through lines and lines and lines of SQL to get to it. So we’re going to
set parameters. We’re going to pick
latitude and longitude. That’s the center
of Seattle, so we’ll pretend we’re going
to put a store there. You could get this very
simply by just googling center of Seattle or
googling an address, and it will return
you the lat-long. And then, for this
one, we’re going to stipulate the
radius as 1 mile. And then, this set of code
pulls all of the zip codes within that area. So it uses this ST_DWithin. So it creates a point from
the latitude and longitude, and then makes short. And then it looks at the zip
area latitude and longitude and finds all of the zip codes
that are within 1,609 meters. SOLEIL KELLEY:
Happens to be 1 mile. CHAD JENNINGS: 1 mile,
that’s right, thank you. The next set of
this code is where we’re going to pull the stats. And so this table
that we’re looking at is actually available in public
dataset inside of BigQuery. It’s called– no surprise– Population by ZIP 2010. And so what this code
is doing is simply adding up the population
totals from these different demographic buckets. This dataset only
has age and gender. If you look at the US Census
page or the American Community Survey– the Fact Finder page
is really useful for this– they have many, many, many
more demographic buckets. But we’ll focus on these. And then, at the
end, we’re just going to pick all of those zip
codes, the zip code stats, and the zip code geometries. And we’re going to pull them
out into a single table. And so I’ve run the query. And it was cached. The 0.017 is– it
used the cache. I prepped this ahead
of time because I didn’t want to burden you
all with watching query run. But what you can see
here is this table. So here’s 98154. This is actually
a tiny little zip code that’s just for the
purposes of the US Post Office, so no people live in it. But you can see these
are the populations, and then here’s the polygon. And that polygon
string is totally parsable by human readers. And you look at that, and
you’re, like, oh, -122.333564. Yeah, that’s downtown
Seattle, right? SOLEIL KELLEY: I can see it. CHAD JENNINGS: Right, no. Nobody does that. So what we really want to do is,
we want to see that on a map. So let’s walk through how
BigQuery Geo Viz works. So I’ve actually prepopulated
this one as well. And you can see
that I’ve increased the radius to 15 miles. It’s exactly the same query. So I have copied from
the composer window and pasted into the
BigQuery Geo Viz window and run the results to
get this map over here. And what’s cool about
this tool is that you can style interactively. So the fill color
for this choropleth, I have chosen to be
population of 65-plus. But if I wanted to change
that, I could, in real time. As a matter of fact,
we’re going to dive in to the north end
of Seattle here. You know what? That’s a little bit
opaque for my taste. I’m going to lighten
the fill opacity. I’m going make it 0.5. There, that lightens
it up a little bit. It’s a little easier to see. Go back to Fill Color. And we’re going to
look at a couple of different demographics–
so demographic number one. SOLEIL KELLEY:
There’s my 25 to 44. Thank you. CHAD JENNINGS: Right on. So let’s see where your
target demographic is. We’ll change the range a
little bit, since their– SOLEIL KELLEY: Yeah. The max is 21,000. There you go. CHAD JENNINGS: And
so what you see is, if we zero in on this
zip code– so 98103– there’s a concentration here. And there is a dearth of your
target demographic in 98199. So don’t put your
store out there. SOLEIL KELLEY: No. CHAD JENNINGS: But what I
wanted to point out here was, let’s go ahead and
have a look at– well, let’s expand it. Let’s say you were looking
for college-age students. And then, this zip
code here lights up. What’s there? SOLEIL KELLEY: There happens
to be a university there. CHAD JENNINGS: Right. SOLEIL KELLEY: And we haven’t
even changed the range, but you can see it’s actually
a similar range set, there. CHAD JENNINGS: Oh, right. Yeah, I can do that. SOLEIL KELLEY: But a
very high concentration there of college-age students. CHAD JENNINGS: So no
surprise, the University of Washington lights up. And then, if we
look at the 65-plus, and I adjust the
range here to 7,000, you can see that the
population is just starting to move, not just
north, but out, right? So this is flight from the
urban center, I suppose. And folks in this
demographic are moving away from the city center. OK. So what we’ve shown
here is the ability to do geospatial analysis
and then style a map and visualize a
map in real time. If you wanted to
share this with folks, you can just take a screen
shot or share the query. SOLEIL KELLEY: You can even
make that nice and big. CHAD JENNINGS: Oh, yeah. Here. So I’m sorry. We can zoom out and
see the entire extent. Okey-doke. So geospatial
information is useful partly because it’s not focused
in any one particular area. So I ran this query
again for New York. And so again, this is
the 0 to 24 demographic. And if we just click over
to this other tab here, you can see that– sorry. This one– let’s
see, at the styling. This one is the 65-plus. I’ll switch back and
forth between these tabs. You can see that these
neighborhoods out here, in the south of New York,
get quite a bit darker. So what that means is folks are
kind of moving out to the beach as they get tired
of the city life. OK. So that’s interesting. So now we’ve given these
retail site selectors the tools to go ahead and
look at different areas and see what the
demographics are. To be honest, zip codes are
pretty coarse-grained geometry for this analysis. You’d rather use census
tracts or census blocks. Again, you can get those from
the American Community Survey page. Go to the Download tab. That’s where you can
download census tracts or zip codes for the
entire United States and bring that into BigQuery. We’re going to look
at this last query here because this kind
of the summary table. So same kind of construction–
so with stats by zip code. This is actually the
same code as before. And what we’re
going to do here is we’re going to run those stats
for a collection of radii. So we’re going to
create a summary table, like, show me the list
of people that live 1, 10, 20, 50 miles from
my chosen location. And again, we’re using
the BigQuery GIS functions to construct that filter. All right, and then here’s
the resulting table. And now, this is not GIS,
but it is super convenient. You look at this button here
called Exploring Data Studio, you can actually click
on that, and that will materialize the results
in a Data Studio session. Let’s go ahead– so let’s see– the dimension we’re
going to use is r. So that’s the Radius. We’ll get rid of that one. And then we’ll do population. SOLEIL KELLEY: While
Chad is pulling in these different
demographic groups, as well, [INAUDIBLE] is something we
just launched into GA last week, which is super exciting. And this particular
functionality– that integration between BigQuery and
Data Studio, that one-click UI experience is something that
we launched earlier this summer at our Cloud Next event. And it’s been a very
popular feature. Our customers have
been asking about it. It was really nice to
be able to deliver that. And people have been
responding well to that. It’s been fun. CHAD JENNINGS: Excellent. SOLEIL KELLEY: And it’s
just super quick for– just like we had the
BigQuery Geo Viz application to be able to quickly
explore your geospatial data, you can do the same thing
here with summary tables, with other data, to quickly
visualize it in Data Studio. CHAD JENNINGS: Yeah. And so with just a few
clicks and a little bit of clumsy dragging of
these little tickets, you can create a chart. You can then save it,
copy it to report, and share it with folks,
and they can interact with your query as well. So anyway, we wanted
to get that one out. We’ve got one more demo to
talk about, which is actually a totally different persona. So now we’re done with
being real estate moguls. SOLEIL KELLEY: Or retail moguls. CHAD JENNINGS: Or retail moguls. Now, were city
planners from Chicago. And so our customer
Geotab actually built this application. And so what you’re
seeing here is a map of hazardous driving behavior. And you might naturally
ask yourself the question, well, how does
Geotab know anything about hazardous
driving application? Great question. Thanks for asking. Geotab is an asset
tracking company and a telematics company. And so, for example, UPS– all of the UPS trucks have a box
about yea big in their truck. And that box measures location,
velocity, acceleration, plus a host of other variables. SOLEIL KELLEY: Temperature–
[INAUDIBLE],, yeah. CHAD JENNINGS: Exactly. So Geotab actually has an
incredibly rich dataset collected by 1.2
million vehicles running around the country. I think their daily intake
is about 3 billion points. All of that gets stored
inside of BigQuery, which you’ll find out soon is a
very convenient place to do it. So what the map shows
here is areas in Chicago that register hazardous
driving behavior. And that’s characterized
by extreme amounts of acceleration– either
forward and back, or lateral. And what this left
panel is going to do is, we’re actually
going to combine a few different technologies
here in just a few clicks. So we have BigQuery GIS,
which is going to call out the points from Chicago. We have, obviously, Google Maps. And we have BigQuery
ML, which is going to– they’ve actually trained a model
to predict hazardous driving behavior, i.e., those
accelerations, based on weather data. SOLEIL KELLEY: [INAUDIBLE] CHAD JENNINGS: Now,
the next question is, where did you
get the weather data? Another excellent question. He’s awesome. NOAA actually hosts
weather data in BigQuery. SOLEIL KELLEY: They do. CHAD JENNINGS: And so joining
your data with weather data is literally only a
join away because it’s all hosted in the
same backend storage. All right. So here, that’s enough
context setting. Let’s get to it. So we’re going to dial up
some weather conditions. So I’m reducing the temperature. So we’re going to
make it winter. I’m going to reduce
the visibility. I’m going to order a snow storm. And then, we’re going to
pretend it’s the holiday season, and we’re going to bump
up the traffic volume. And then I just clicked
Run Predictive Analysis. And we get a map that’s a lot
hotter than the original one. OK. That’s interesting. SOLEIL KELLEY: Make sense? Although colder
because it’s winter– it’s hotter in terms of
hazardous driving behavior. CHAD JENNINGS: I
totally get you. Any case, we’re going to
look in at one of these because, as the traffic planner
for the city of Chicago, we want to investigate these
points and see what we can do. And in particular, there’s
one that we’re going to dive into right here because– oh, here it is– because it is just down
the street from a school. So being elected
officials, we’re going to prioritize
safety of constituents. We’re going to prioritize safety
of vulnerable constituents, focus on hazardous
driving around schools. SOLEIL KELLEY: And the
efficiency through the network of streets for everything. CHAD JENNINGS: Yeah. We want to keep our kids safe. All right. So what’s going on here? So it’s interesting that
there is hazardous driving behavior in inclement weather. But what’s going on? So this is where we get
a little bit of a benefit from being in the
Google ecosystem. And we’re going to
drop the Street View avatar into this scene. And so we’re going
to turn around. So here’s the school. And then I’m going to move
just a little bit east. And look at what’s here. So I’ll make this screen
a bit bigger for you, and we’ll zoom in just a touch. It’s a bike rack. SOLEIL KELLEY: There you go. CHAD JENNINGS: Right
next to an alleyway. So agreed. I dealt up a winter day. But maybe some kids are riding
their bike down this alleyway and causing some kind of traffic
congestion or traffic issue here. Let’s spin around and
see what’s going on. I don’t see any
traffic signage, right? So just down here, at
the end of this picture is where that hazardous
driving behavior was occurring. But there’s no
traffic signage here. Maybe the remediation is
to preposition some sand. Maybe the right remediation
is to put a stop sign in here. SOLEIL KELLEY: Even
a crosswalk, yeah. CHAD JENNINGS: Or a
crosswalk, yeah, good point. But what we’ve enabled
here is, our city planner can now scan the entire city
using GIS, ML, BigQuery, Google Maps, Street View, public data– all without leaving their chair. And that is pretty darn cool. Here, I’ll take it out
of the full screen mode. All right. Anyway, so thank you for
going on that little journey. So we did that demo. We just finished this
one, and I skipped ahead in the slides a little bit. SOLEIL KELLEY: Yeah. So obviously amazing
things that you can do using this functionality. Tons more resources we
wanted into the arm you with. First of all, if you want
to get started in BigQuery, you can just go right
to the Cloud Console. That’s the first link, there– that particular BigQuery
Geo Viz application for just visualizing your
SQL queries on that map. There’s the link there
for you, as well. Tons of documentation, very
thorough– all the functions that I went through, those are
all detailed out one by one in the documentation,
which is really helpful. We also have a link for
all those public datasets that we host in BigQuery
that’s in our GCP marketplace. You can also, of course, go
find any of the open datasets out there and bring them into
your particular projects, as well. And then, too, we have a
Stack Overflow topic here on Google BigQuery with
the GIS particular tag. And this is where Chad will
post all these queries sometime in the next few days so that
you can play around with those. Again, you saw the
facility of just putting in a lat-long pair
for that central point of your retail ring study. But you could conceivably
do that at any location that you might want to explore. CHAD JENNINGS: Let me
speak specifically. If there are people
watching who are either in retail, or in
television or radio ratings, then these queries are
very readily extensible to census tracts,
census blocks, or DMAs. So we didn’t go to that
extent because we just wanted to keep things simple here. But if that’s your
industry, then use those as your template. And you can do these
analyses and show them on BigQuery Geo Viz. SOLEIL KELLEY: Great. Thanks. Well, that’s what we
had to show for today. Thank you Chad for walking
through those demos. Those were super cool. And folks, everybody
stay tuned for live Q&A. We’ll be back in just a couple
of minutes to cover those. Thank you. Great. Welcome back, everybody. So we’re here now for the live
Q&A portion of our webinar. And we got several great
questions from the audience. So we’ll just kind
of dive into those and do them one at at a time. So first off, I have a few ESRI
shapefiles I’d like to use– super, super common. Can I use them
with BigQuery GIS? The answer is yes,
although you would need to convert that
shapefile format into, either well-known text or
some of the other formats that we can then
bring in, right? CHAD JENNINGS: Well, we got
this question a lot right after we launched the alpha. And so we actually have– and
we’ll put it up in a Stack Overflow topic– we actually have a document
that one of our colleagues wrote that details
exactly how to do– what’s the right tool to use,
and how do you bring it in. But essentially,
Soleil is right. You have to convert the
shapefile into the formats that we support. And then you can look at them
just like we did in the demos. SOLEIL KELLEY:
Our next question. “Does BigQuery GIS have
geocoding capabilities?” CHAD JENNINGS: Yeah. So we rely on the Google
Maps APIs for this. So BigQuery, itself, does not. But you can, in a different
part of your program, you can call in API, and augment
the table that you’re looking at with the geocoded data. Or you can call in the external
API as part of a Dataflow job. So you can read out a BigQuery
column through a Dataflow job, then call that API,
augment the data, and then write that
back into BigQuery. SOLEIL KELLEY: Got it. I see. Great. “For visualizations and
mapping, what other BI tools can I connect BigQuery to?” So, BigQuery he has a
number of native connections to different BI tools
like Tableau and Looker and Click and things
like this specifically for mapping and connecting to
your BigQuery GIS functions. So long as those tools
support custom SQL queries, and so long as they can
render geographic data types, they should be able to
leverage this technology. And one thing, as
well, is you probably want to bring that into GeoJSON
format, right, to do that? CHAD JENNINGS: Yeah. So Tableau is an
example of a Viz tool that you’ll want to use the
custom queries to leverage BigQuery GIS. And then Looker actually
supports BigQuery GIS. SOLEIL KELLEY: OK, wonderful. Next question. “Does BigQuery GIS
support 3D geometries and measure values,
xym or xyzm?” I’ll let you take that. CHAD JENNINGS: Oh. I actually don’t know
the answer to this one. SOLEIL KELLEY: The answer is no. We don’t support
the [? z ?] measure. CHAD JENNINGS:
Thanks for that one. SOLEIL KELLEY: Hey, these
are questions that people are having, you know? “Does BigQuery GIS come with
geospatial statistical data, for instance, parcel
map data that I can use to join with my business data?” CHAD JENNINGS: Oh,
that’s a great question. So not really. So BigQuery GIS
comes with BigQuery. BigQuery comes with
Google Cloud Platform. And inside Google Cloud
Platform are a whole host of public datasets. And we went through
some of these, or I showed you a very
small subset of the list. If there is some
public datasets that have some of that geospatial
statistical data that you want, like– I do happen to know that zip
code land and water areas are there, things like that– you might be able to find
them in the public datasets. If not, then you’re
going to have to import them
using the procedure that we’ll publish in
that Stack Overflow topic. SOLEIL KELLEY:
Next question looks like we’ve already addressed
with respect to geocoding. For our final question
there, “For non-programmers working with large
amounts of data, is there a resource
for query language to facilitate pulling
or geolocating data?” So for this, if you’re
not a programmer, I would just direct
your attention to the BigQuery
documentation for GIS. There are a ton of
resources there. Again, it’s all fully outlined–
all of the different functions that are there. There are several
tutorials as well. We’ll post some links
to the Stack Overflow there as well unless
you want to add to that? CHAD JENNINGS: Yeah,
I do, definitely. So as we all know, we
work with geospatial data. And for those that
have, know that there are datasets all over the
place and in all sorts of different formats. And aside from
shapefiles, which is, I suppose, a bit of
an industry standard, but there’s just like,
GDB, MDB– there’s a whole lot of stuff out there. We don’t have a
SQL verb that says, like, go get this
dataset, and bring it in. However, it’s a
really good idea, and we’ve already
written that down. But what you will have
to do is, if you’re a non-programmer, have
a look at the resources, again, this article about
pulling in other types of data formats into BigQuery. And then the process to
copy that over requires a couple of lines of code. But you can literally copy
and paste from the article and put that into the console. And the directions
there are clear enough that even I was able to get it
right on the very first time. SOLEIL KELLEY: Amazing. CHAD JENNINGS: I was
not the author too, so I was actually testing. SOLEIL KELLEY: Great. Well, thanks everybody. That concludes our Q&A portion. Do stay tuned. We have another webinar
following this one. It’s called Visualize 2030. This is about a data
storytelling contest that Google Cloud is hosting
around the UN Sustainable Development Goals. And that will be coming up
in just a few moments live from New York City. Thanks again, everybody. I’m Soleil. CHAD JENNINGS: Outstanding. I’m Chad, and happy mapping. SOLEIL KELLEY: Woo-hoo. [MUSIC PLAYING]


Leave a Reply

Your email address will not be published. Required fields are marked *