SBTB 2019: Michael Entin, Introduction to geospatial analysis for uninitiated SQL Data Engineer
[Music] Hey so we will be talking how to add just special data analysis to your data analysis top box so I hope you all do some kind of data analysis but if you are not using spatial data right now we will learn how to do it and why I'm decided to do the talk I am NOT Jess or geospatial engineer that world was completely foreign to me I was sicko guy building various data platforms at Microsoft and then at Google but recently I had and currently I'm working on bigquery which is Google's data warehouse I think one of the most capable and easy-to-use service and soul but recently I worked on adding geospatial analytics to that data analysis to Google bigquery and of course we had drilled J's people from our central helping us who have expertise in the data and they realized that those walls shouldn't be distinct all the engineers should know how to learn just be sure to use geospatial data because it's everywhere you probably have it even if you don't know that you have the data or maybe you're not collecting it but you should be collecting it so we will learn and not hopefully we all be less scared so once you start learning about jas didn't dress papers and learning their tools it initially looks like everything is completely different they are talking about some strange words like layers base maps and features and I didn't understand what those means but don't worry those are like sequel Walt Python volt and art world they look different they use different tools but we all process the same data we all use the same processing techniques and if you understand data if you understand how the data might be useful to you it doesn't matter if you're a civil engineer or our engineer you probably would find the way to use your data and get meaningful insights from the data so let's learn the terminology and jargon of just geospatial data guys and understand how to use those tools in your normal data workflow so it's all data we learn what kind of data is there what data formats what standards are there what can I do with the data what functions are available in my tools and what tools do I need and how do i optimize performance so if you understand that those questions about any data you can use it so let's talk about types of geospatial data and although spatial data is described in something on the earth something physical something Krios that happens around us but there are different ways to describe the data and the first that comes to mind are probably those Maps like those maps you see that measure something like temperature on the surface of the earth or something like that and that's called raster data and think about it as you think about bitmaps it's 2d grid with some measure or some multiple measures that are assigned to every single pixel that's all that's it so it's an example of that measure can be elevation temperature participation population density or whatever and you might find what cell it's the same as pixel in jazz darken Bend is a measure so if you have RGB band it has three measures red green and blue so they will called three band bitmap don't be scared and they call now they called no data otherwise it's just a bitmap and he used it in any image processing to you know what the data look like unlike in image processing tools they started with bitmap data but they gradually realized that for many tasks it's much more convenient to use vector data so we switched from bitmaps to SVG the same things happened in just a tional analysis so vector data describes the same things but instead of describing every single pixel you describe a region over the surface of the earth or specific point at the surface of the earth so for example here we have like five areas north best Bay East Bay and so on so they might be described as five different rows in the table and each row has a shape that describes the boundary of each region and again you might meet weird jargon like instead of calling the data a table jazz people will call it layer or future collection and they will call each week each row feature don't be scared it's the same kind of data that you are using in any two you're currently using so property if you see property just account and typically in geospatial data there is a one special data which contains geometry which describes the boundary of polygroup the boundary of the region like North Bay or coordinates of specific point like Auckland or a line that describes a road additional types of geospatial data you might need is a road network just like the vector data we saw before that also describes lines but here think about graphs in addition to line we also describe connectivity like what turns are allowed which turns are not allowed which directions are allowed and various geocoding to enjoy location those are just mapping between names and coordinates on the earth see a names can be anything think about postal addresses think about IP addresses so what data should you use if you are just regular data analysis doing data analysis for business or anything and raster data is mostly imagery and if your environmental group evaluating deforestation in Amazon that the data you will use you have a lot of satellite images but you probably are using specialized jazz tools so I will not talk much about using raster data today same with road networks if you are using the red called network we are probably building navigational up or you're building lyft or uber but vector data I think should be used by everyone and luckily for us it's available in most databases so if you use passe chasse passe gray a sequel it has Padres Express extensions if you use my sequel it has my sequel spatial and the same for if you are using panda there is a Geo Panda that is a excellent package and the same for art almost all the tools can support data and while they're different they use about the same representation and better data is essential points just location on the Earth's like our coordinates here lines and arbitrary shapes or you might be describing collections of those of those when we move to computer we can describe arbitrary curves so instead of doing other records we just define specific points and replace curved lines with straight lines connected in sequences and the same thing happens with polygons like with shapes how do you describe the data describing is just the format I see so here is wkt but it doesn't matter like all different formats are very similar except different encoding they just provide coordinates if you have a single point coordinates of that point is what your data contains if you have a line then the sequence of lines of points defines that line if you have a polygon that's more complex we can have a complex shape and it can't have holes so the most common approach to describe that is by giving a sequence of loops and there is always at least one loop the first loop defines the boundary all additional loops define holes so here there is one boundary and there is a hole that doesn't belong to our shape so that the second loop here and there might be collect like any mix of those using collections Multi polygons and so on so a puzzle like about polygons I said that there is one shell and it might have holes what do we do with that thingy when we have an island inside of a hole well hold it with just a multi polygon so instead of just describing it as a simple polygon we have to describe this two polygons one is red polygons with two holes and one is a simple chain copolymer to lie within the whole of the first one it might also encounter D and M data where in addition to coordinates each point has additional data it might be elevation which is called Z because the first one x and y or it might be arbitrary measure so it might be convenient when you travel along the line to the shade for example time with each point next question is we described up with abstract coordinates but once we work with the real Earth's where are those coordinates and what are those numbers and there might be two major answers one is you are working on a flat coordinate system of some specific map map projection and that's typically called geometry type in post J's for example in my sicko it's called projected coordinate system and the key here is you're working on a flat map in 2d another answer is you are working on spherical earth ours is not flat so whenever you're working on 2d you might be doing some approximations and some assumptions that might be not always correct and when you're working on the sphere that's called geography type in most of the systems and my sequel is slightly different has a single type but that one works with geographic coordinate systems when do you use one or another of course it's simpler to work on 2d your computer is doing less computations but you need some experience in how to do it correctly because all projections distort some of the data so you do need to know some math to know which projections should I use for each location it's also very becomes hard when you're working on the whole globe so the easiest thing is to work with geography type that one much easier so that's one of the problem with working on 2d like the shortest path between Europe and the United States is through Greenland but if you do draw a straight line on a flat map that goes through friends that's not the real shortest line another problem when you're working with 2d is intimidation so again if you try to find the shortest distance between Japan and Alaska if you are working on 2d you might get incorrect answer if you don't working on spherical earth you will get the real short distance there are some buddy some complications with working with 3d the biggest problem is that some systems supported less than 2d because the mass is simply simply more complex but if your system does support 3d I highly recommend one thing to be aware of when you're working on the earth is inverted polygons so if I describe a boundary between all the oceans and all the continents on the earth so I draw a line but what does that line describe does it describe oceans or does it describe continents how do you know so you might get inverted polygons but all the systems support resolving that ambiguity by using some orientation rule okay so that's a single geometry value and think of it as any data type so you use in 60 integer stream float data types so your system even if you don't know it most likely has another data type called geometry or geography and you use it just like any data type you create a table with the data type and you put some data into it just like any other data so here we have a table with geometry like first row describe North Bend North Bay so it has a complex polygon second row defines Auckland so it has a single point so that what your Joe spatial data might look like there is absolutely nothing different from any other type okay so now we know what data we have what operations can be run with it and it's the same answer as any other type we can construct it like just like you can construct string you can construct geometry value just like you can parse drink from hex encoded string or any other encoding you can convert geography type from common formats and common formats are WKT the ones that are shown or Joe Jason and so or you can format to external representations and you can measure so you can measure just like you can measure the length of the string you can measure area of the geometry ography length of the string you can measure distance between two different geography and you can transform geographies you can union geographies you can subtract them and get a new one and you can calculate relationship between geographies do they intersect do they have any common point does one of them contains another and so on so if you want to use find for example all points in your data set that belong to California you will use st contains function giving coordinate like polygon of california and all the points so know that typically all those functions start with st underscore prefix that's the convention that comes from a standard there was standard body that was very ambitious and wanted to define all the space and time functions that S stands for space and T stands for time unfortunately they never came and did nothing with time so all st functions are just based on functions okay so that's I already slept so you can use find relationship between different geometries and that's the most common thing that you will be doing in your geospatial analysis so you want to find all the for example if you are tracking your employer's and you have a table with coordinates of employers and you want to alert all of them who are near California fire so you might have another table which has coordinates of all the fires going on and you want to find out who of them your employees are within some distance from the fire whom should I notify so I call a ste distance function between employee and polygon that describes a fire and find out is the distance with less than five miles then I need to call them okay so that's an example let's find census tracks and that's very common when you do any location analysis you want to use census data because that's the most detailed data about population that you have so our table might have for example geography ID the just government string that defines it by the point name of the truck and geometry of the truck and geometry is a polygon that describes particular area statistical area that has aggregated statistical data about population so that's actually subset of data from public table in bigquery and we want to find all the trucks that are near Oakland so we start the same select track name and geography and then we need to find which tracks are within one mile of Oakland so our condition will be a stiff distance between trunk geometry and point that describes Oakland is less than 10 miles so that distance 10,000 meters mod 10,000 and that miles to kilometers multiplier one thing to note that argument of your point in geography world you usually pass latitude first and wanted you to second in most ecosystems you pass wanted to third because that's your x-coordinates how does your database execute that query just like with any other data in this is important and different databases implement indices in different way there are what trees there are our trees bigquery uses hilbert curves but that those indices allow the database to partition geospatial data and quickly find locations that are near these five points for example and previous query we needed to calculate distance between a complex region and particular point that would be very expensive to do but Oh most of the systems implement some kind of index it doesn't really matter which one your system implements just make sure that if you're doing those kind of queries and your system requires explicit indices create those sentences and you'll be fine and just like any other system it's very common to do joints like you're doing joints on some ideas or strings in with your integers or strings it's very common to do joins in unjust facial data and here is those that example of finding employees who are affected by the fires you might have a huge table of employees and you might have a table with the list of fires so you are joining two tables and the joint you are writing the same query that you normally do with only condition here will be st intersects for example do those regions have any common point or another most common condition is STD within and STD within is the same as distance between two geographies is less or equal than the third parameter distance just on many systems using STD waiting will be faster okay let's do some demo okay so let's construct that waiting about I want to learn something about Auckland so so I'll use find census data about California so that is sends a list of sensors talk that's bigquery UI don't be alarmed if you don't know but it's just sicko editor so we'll find we need to find something from California sensors trucks where we are close to Oakland yep okay so then we find Oakland in California and wikipedia has coordinates we we copy those Cardinals mmm that's more and paste them here and as I said we need to reverse order of the arguments because sequel systems yep so okay and it should the less than let's say one mile so that's the most simple just a tional query you have unfortunately it returns just tons of data so what can we do with it and you usually find some visualization tool and here I'll use visualization tool that comes from the with bigquery but most other systems have similar more complicated and better tools so that the query I have here also joins it with more census tracts with actual data and I might be able to color it so that's where color comes but let's I think stop here and us take questions alrighty does anybody have any questions so are there any limitations like with the geo support you'd get out of you know bigquery or other databases like when you compare that to I guess they are gif like I've never used any of those systems so it might be a silly question but like is there any are we limited in some way with the out of the box support databases yes our dress is I don't know 20 or 30 year company they have way more extensive support for various just special functions in bigquery we edit the special support it was released about a year ago it doesn't implement the long tail of functionality we think we implemented the 80% that most data analysts need but definitely if you are cold like if you're a hardcore jazz user you might find that some of the things you are used to are not present there we think for those 80% we get much better performance much better cost efficiency and so on but yes it's nowhere as complete as the whole functionality that exists on our JS already and this will be our last question so you mentioned in bigquery it is using hilbert curves so can you please explain how does this a next thing work for these queries for bigquery yes so the question is was how does bigquery uses Hilbert curves let's return to that slide so although synthesis allow you to find points that are near specific point and hilbert curve for Hilbert curve we use s2 which is open source code released by Google and used by most of the Google special products like Maps or Centron and so on it supports that Hilbert curve and Hilbert curve allows you to map every car coordinate region on the earth to a single integer so any position on the earth within couple centimeters can be mapped to a single 64-bit integer so that turns a 2d search space like they think why we needed quadtree or why we needed r3 is because we have multiple coordinates so hilbert curve curve turns 2d index into one the index instead of searching in 2d space we need to find all the intersections of linear range all righty and that is the end of the session [Applause] [Music]