Kannappan Sirchabesan works at Google as a Cloud Data Engineer helping large enterprises migrate their Big Data and Machine Learning workloads from on premises to Google Cloud. Prior to Google, he has worked on applying Big Data and Data Science technologies for a variety of sectors including Telecoms, Media and Geospatial industries.View the profile
About the talk
First come first serve
This demo will execute 100 geospatial SQL queries and display them on a map using BigQuery GIS and BigQuery GeoViz. Using geo datatypes like Points, Lines and Polygons, it will showcase the various geospatial functions that are available within BigQuery by executing them to form first a simple visual and gradually build up to a very complex geographic visual. Join for a quick understanding of the simplicity and power of the geospatial capabilities available in BigQuery.
Today was going to give you a very quick to look on big caridi. Is there looking at the agenda will give you a very brief introduction into Bakery diet. So what exactly is it and around it and then we will spend the bulk of the time giving you guys a demo by showcasing of beaches and showing you some of the example you connected. Do you have three spaces today can attend I myself Rachel. We also teach it Cloud engine is by Cindy London office. Okay, so big crate for those that do not know Bakery is Google files
beta and Phi Beta White House it come out of his lot. So we manage storage on the pit bikes go and you can run analysis on up to billions and billions the first you can interact with big furry using SQL and it's fully managed. I'm fabulous. So you don't have to worry about spending up classes and managing resources. So dumb for you that there that you know if it's paper bag, Play I want to be crazy GIF or GIF is geospatial information systems and bakery is one of the first Cloud providers to offer an
MPP massively parallel processing safer Warehouse that supports geospatial functions and data type. So we have the concept of a geography and that's essentially of points that on the other stuff it and when you combine that with your location data, and you'll be crazy table if you have an attitude country code inside it makes it a lot more relatable. under the same Library used in Google maps and we also have the big Prairie allows you to view urinalysis
against so I mentioned a drunk if he died the type of points that on the other stuff is so by points that sits on the surface or set of points over lines during or in the demo. You want to have GIF functions and types and it feels right on the left hand over the tunnel to do with them. Eventually somebody's feet to connection. Height of the Apes on the functions that are blank slate you have the credit of the map. So this is like a relation to a spot of my credit. So it's obvious. So what are you doing now is basically starting from scratch.
So we're drawing a point of that. We are so very muscular Center. So the function venues is SD in the school job, and we provide the lat and long So as you can see, we have the most money center on the right. so this is one point. Now we are going to do one more point. So does the baseball stadium RV Park? So if you can see like the standard Scottsdale plane takes like two parameters the longitude first and then the latitude so what do you see now? It's
basically you have liked using the union hall. I know we're using S T underscore make line. And I know you're in all the game so that I just made the line and the dot under two points. how to draw a like a polygon of three points nothing Google SF office point and what are you serious espn.com a polygon and nvusd job from text function and then we draw a polygon. Okay, once we have the polygon now we can do all the other measures like distance between meter and so on. So what is
centroid? What do you think now is basically the public data sets that are available? So what is the one that is shown here is basically d by the Ford gobike. Do they sell the bike stations that are available so I can see there are like three clusters. So this is one of those in San Francisco. So on the bike now. 34 on the bike stations Now let's filter to one of the bike stations that are within that specific polygon using the function S T & S covid-19 types terminology. In in jail
view, so I just clicked on one of the points and I think you can see the Ford gobike station for 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 Republic. So everything out here is so this is like all the bike stations. Yeah, this is like the cluster of points. So what has gotten a present to the spice of the circle represents. The number of bikes the bike station. So if the cyclists bigger which means that there are a lot of bikes and even smaller it mean sister
and now it's basically from Moscone within a 500-mile radius. What are the bike stations that are available? These are some of my stations that are available. Find a meter radius that is one very close by in front of Flag Day. So I can see it's like very easy to actually do all this like basic geospatial functionalities. Thank you. Okay. So for my demo, I'm going to talk about how you can use some of the functionalities we discuss for analysis for Fun and Profit keeping was the cycle hire dataset. I'm gonna move across the Atlantic to London the way
bass. I'm going to also take the view of the operator of the cycle hire service. So as operator, I want to know what do people use to Cycle Service for analyte by 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. Hyde Park is a big station is a big park in Central London. That's very popular with tourists. What is tells me is that people are using the service for the law for tourism? So looking at the previous
analysis, I can say that some station some more frequently used as a destination IMO bikes arriving at station, then they'll bike starting from that station. So if this might cause some capacity problems if I am a Taurus arriving on the station, that's very frequently use. I might not find a docking station. So I want to do some analysis of this friend is of counting the number of trips that started at the station and subtracted from a number of troops are stationed and plus it on the map red means stop then come. Station in Bloomington mall trip standing at the
station looking at the station with the biggest disparity. I can see that correspond to help exchange to borrow which is where I know that's a big farmers market. That's also very popular with tourists. So with this knowledge, I can start making some changes to accommodate for this friend. This also make me ask a question do people use this Cycle Service for commute at all? So by isolating the trips 2 hours in the morning vs. Evening commute. I can immediately see a friend in the morning. Most people are using the service to commute into Central London and vice versa and evening.
This is most pronounced at Kingsport station with you might have heard from Harry Potter. It's also a spot for everybody outside of London commuting in. So passionately I also like to go to Borough Market in the weekend by now. I won't be able to. 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 say that the closest station is pop Street bankside, which is about 164 minutes away.
Plankton GIF to survive inside and make it a bit more relational. We have a few late from the slide so you can go and have a play is currently in beta but I didn't say nothing, but you can play with in 2 minutes. Thank you so much for attending. I'm listening.
Buy this talk
Access to all the recordings of the event
Buy this video
With ConferenceCast.tv, you get access to our library of the world's best conference talks.