Using the Intersect Function in Tableau for Spatial Analysis | New in Tableau 2022.4

0:00Hey, it's Tim here. Let me ask you this.

0:02How many trees do you think exists within

0:04one kilometer of the Empire State Building?

0:07Pretty random question, I know. But

0:08actually, Tableau have just released a

0:10feature called the intersect function,

0:12which is going to allow us to answer this

0:14question right here in Tableau. You can see

0:15I've already set it up and I'm already

0:17using it. But I won't show you the answer

0:19until the end of the video. So stay tuned.

0:21And as ever, let's get stuck in and find

0:23out how this works. Okay, so we're here

0:26inside a Tableau cloud, I'm going to do

0:27everything in the browser. If you just want

0:29to see the

0:29function works, look at the timestamps to

0:32skip ahead to the various bits, it's

0:33important to share everyone every single

0:35step to show you how I got to the end

0:36result that I've just shown you. So let's

0:38go ahead and open up a new workbook. And we

0:42're going to do this in the browser. So

0:43everything is going to be a little bit wild

0:44. And we're actually going to upload a

0:46really large CSV file, I downloaded this, I

0:49'm going to put the website on screen. But I

0:51downloaded this from the New York open data

0:54website. It's got a tree census from 2015.

0:57And it contains all the trees in New York.

0:59During that census. So when we go ahead and

1:02select open, in fact, I can't open the file

1:05because my face is over the button, we're

1:07going to have to go up and upload the files

1:10to 225 megabyte file. Some people have

1:13asked me in the past, what is the file

1:15upload limit for Tableau cloud? Well, I've

1:18tried this, and I know this definitely

1:19works. So we'll go ahead and let this run.

1:22And then I'll just skip ahead to the end

1:26when it's just about to complete wonders of

1:28editing.

1:29Just one quick favor 82% of the people

1:32watching this video in the last year on

1:34subscribe to the channel. And just to show

1:37you some context, nearly half of my

1:38subscribers came from this last year's

1:41worth of content. And if you look at how

1:42many views and how much time people are

1:44spending, I'm sure it's creating value

1:46somewhere. So if you really appreciate the

1:48content or would like to see even more of

1:50it at a better higher quality standard, hit

1:52the sub button and enjoy the rest of the

1:54day. I have to say it's really fast though.

1:57The future is definitely in a way better.

1:59The thing I love about Tableau is it's nice

2:01and easy and quick. That experience right

2:03there slowed me right down. So anyway, we

2:05've got the Tableau file connected,

2:07everything is looking good. And we're going

2:10to now start doing a little bit of setup.

2:12Now, in order to kind of work with this

2:14data, we've got a couple of things, we've

2:16got the latitude and longitude in the data

2:18set. So we can use those for what we want

2:20to do. But the point of interest that I'm

2:22actually going to use is not going to be in

2:24the data set. I'll come to this a little

2:26later on in the video, I'll explain

2:29some quirks with this feature. But for now,

2:31let's just push ahead with the feature.

2:32What I'm going to do is I'm going to grab a

2:34point of interest from Google Maps. So let

2:37's go ahead and go to Google Maps. So we go

2:39maps.google.com. And we'll search for a

2:42building in New York, let's call it the

2:44Empire State Building in New York. The nice

2:49thing about Google when you search for a

2:50building, if you go ahead and click on the

2:52building is it does give you the latitude

2:54and longitude just up here. And this is

2:56exactly what I'm going to copy. So I'm

2:58going to copy that.

2:59And what we're going to do is go back to

3:01Tableau. And I'm going to create a

3:02parameter. And I'm going to put this into

3:05the parameter. The reason I'm doing this is

3:07because this feature has a quirk that again

3:09, I'll explain at the very end. And I'm

3:11going to create this as a list. And in the

3:13valley, I'm going to paste that latitude

3:15and longitude in there. And then we can

3:17actually call this. The reason it's not

3:20actually saving this is because I'm pasting

3:22the value that it's not recognizing that as

3:24a recognized float value, and then it's

3:26clearing it. So what we have to

3:29go do here is select string, paste it, and

3:31now it will actually let me rename this

3:33appropriately. So if we just double click

3:36that, we can call this Empire State. Okay,

3:40let's go ahead and add another location.

3:43Where else can we go to New York that's

3:45pretty famous, we can go to the Flatiron

3:47building, I think Times Square is a pretty

3:49nice one. So let's go here to Times Square.

3:52Again, it will give us the Latin long us

3:55just up here, we'll copy that. And we'll go

3:57back to our tab.

3:59And we'll add another value in here. And we

4:01'll call this Times Square. Great, so we've

4:04got two values in here. Now, what we want

4:07to do with this, you know, latitude and

4:10longitude from this URL is we want to be

4:12able to pass it in a bunch of spatial

4:15functions, the way you have to think about

4:18this is, first, we have to get our point of

4:20interest, then we have to create a sort of

4:23zone that we're going to search for this

4:26zone will allow us to see if any

4:28other spatial objects match that particular

4:31item. And so the way the insect function

4:33works is it currently lets you do three

4:35types of matches. It does points in polyg

4:38ons, lines on polygons and polygons in polyg

4:42ons. I think those are the three ones, I've

4:44got that wrong. I'll correct it up on

4:46screen. But I'm pretty sure there's the

4:47three types. And so for this one, we're

4:49going to be doing the point in polygon

4:52matching. And so to do that, the polygon we

4:54're going to create is going to be a buffer.

4:56And the point we're going to use is this

4:58point of interest.

4:58And so we need to create a point for our

5:00point of interest for this to work. So

5:02because we're doing this through the

5:04parameter, I should actually rename this to

5:06call it POI. And we're going to then create

5:08a calculation. And this calculation is

5:11super simple. We're simply going to take

5:13our parameter, and we're going to pass it

5:15now, you might think, how am I going to

5:16pass this very easily? Well, Tableau has a

5:18couple of functions built in, I've done a

5:20whole playlist and function. So go ahead

5:22and check that out. But if I go in here,

5:24you can see there's a string function. And

5:26it's called split. And what that does is it

5:28let you specify a way to split a string.

5:31And you can use a comma, whatever you want

5:34to use to do that. So for this one, because

5:36I had the comma in my latitude and long

5:38itude, I can actually do this, I can say

5:39split, you'll see it auto complete, I can

5:42then go get my POI, which is a text field.

5:46Okay, and the way I want to split this is

5:48using the comma that's currently inside of

5:52that string. And then I want to return the

5:55first thing that it catches that matches

5:58that

5:58criteria. So that will give me the latitude

6:00. Okay, and then what we're going to do is

6:02we're going to copy this. And once we've

6:04done this, and the thing is going to return

6:07is still going to be text. So we then need

6:10to actually make sure that we make this

6:12into a numerical value, which would mean we

6:14need it to be a float. The reason we need a

6:16float is because we need the decimal places

6:19, and integers are whole numbers. If I go

6:21here, and I type in float, it's not

6:23actually under string, it's under numerical

6:25. But if I clear that, and I type in float,

6:28I can't type, you'll see that it actually,

6:30if I double click that it actually puts the

6:32function here in my calculation window. And

6:34then now I can highlight that, copy it and

6:36put it in here, just make sure everything

6:38is good, then I can go ahead and delete the

6:41first one. So now that works, you'll see

6:43the calculation is valid. And we're ready

6:45to go. And now I can actually copy this,

6:47select OK. And we're going to go ahead and

6:49actually duplicate this, I was going to

6:51copy and paste it, but instead, we'll

6:53duplicate it. And we'll go ahead here and

6:55say we'd like the

6:57second part of that as the longitude. Okay,

7:02now that we've got the longitude, we can

7:04hit apply, you'll see that Oh, something's

7:06already named longitude. So we'll just call

7:08this instead of the full name, we'll just

7:10call this long and hit apply. That's also

7:13worked perfect. And the way we can check

7:15this point of interest works is we can

7:17actually just go ahead to this latitude

7:19here. And we can tell Tableau that this

7:22actually has a geographical role of

7:24latitude. And we'll go to the longitude

7:27and tell that but it has a geographical low

7:29role of longitude as well. I can't talk it

7:32's so late. And now if I double click lats,

7:35and I double click long, we should get the

7:37point of interest mapped out on the map,

7:40which is correct here in New York. Let's

7:43zoom right in. I'm doing this in the

7:44browser, don't forget. So if you see any p

7:46ugs or any sort of weird behavior, don't don

7:50't sort of

7:52well, don't judge my skills, judge the

7:54browser. I've completely done something is

7:57so let me just reset this. Let me just

7:59reset this and double click lats and double

8:01click long. I think the issue I have here

8:03is that if I go in here, and I just do this

8:06, let's just draw a nice little box there.

8:08Is it going to let me zoom? Is it going to

8:10let me zoom? What's this? Why is it? Why is

8:13it not? Oh, that's the selection. So sorry,

8:15I do need to do this. And there we go. That

8:17will let me zoom in again that. Okay, so

8:20now we've zoomed right into New York.

8:21Excellent. This is working

8:22great. I'm scared my bearings with with web

8:24edits just so confusing. Sometimes we get

8:27streets. Now this looks more like a map.

8:29Thank God. If you really want to check out

8:30some really cool things here. You can also

8:32switch over to the satellite view, which is

8:35super detailed. I mean, if you zoom right

8:37in, we can actually see that the last

8:39season long through the Google gave me

8:40slightly off. I don't know if Google and

8:43Mapbox disagree on the position of the

8:46Empire State Building. But the one I got

8:47from Google is over here. And Empire State

8:50Building can clearly see on the satellite

8:52is

8:52over there. But that's that's fine for the

8:54purpose of what we're trying to do. It

8:56might also be to do with projections,

8:58things are slightly off, but I'm not a

9:00mapping expert. So I won't pretend to know

9:01what's going on there. But nonetheless, it

9:03's good enough for the purpose of what we're

9:05trying to show. So let's go back to the

9:08streets. And now that we've done that, we

9:11can actually sort of go back out. And let's

9:14actually start trying to solve the second

9:16half of the problem. If we now want to turn

9:20our attention to the buffer,

9:22the thing we're going to match against, we

9:24need to be able to use this point. So to do

9:26that, we'll just go ahead and create a new

9:29calculation, we'll call this buffer. And

9:31for this buffer, what you need to do if we

9:34go down here to the not to my face to the

9:37drop down, let's just see if we can find

9:40spatial and buffer. The label for that is

9:45make point, the Latin long essentially, and

9:47then the distance and the kilometers. So to

9:50do that, what we're going to do just to

9:51make this super easy is we're going to go

9:54ahead and make a point using our lat long.

9:57So let's go ahead and make point. And we'll

9:59say lat comma, long, we should actually

10:02really copy this and recreate the

10:04calculation. Now that we've made a point,

10:07we can go ahead and specify the next thing,

10:10which is going to be the distance, we'll

10:12just do two kilometers from this location.

10:15And we're going to specify this in

10:16kilometers, that needs to be in text. So

10:19let's go ahead and do that. Now that we've

10:21done that,

10:21we can go ahead and say this is our buffer.

10:24We'll come back to this to change it a

10:26little bit later, you know, put a bit of a

10:28finesse on it. But I'll go ahead and

10:30actually I need to grab this make point lat

10:32long here. I think I'm going to need this

10:34calculation again. So I'm going to copy it

10:36right now just to save me some trouble. But

10:38I think that's good. We can hit apply and

10:40hit OK. And now, when we go ahead and

10:44actually try and visualize this, let's just

10:46go ahead and clear the map, clear this

10:48sheet. And we can just go ahead and

10:51visualize the

10:51buffer, you can see that that buffer is now

10:54really working nicely. So we've drawn a two

10:57kilometer radius. And what we can do is we

10:59can add one more thing, let's add another

11:01parameter. This time, we are going to keep

11:04it as an integer, we're going to give it a

11:06range. And we're going to start with a

11:08minimum of one and a maximum of 10. I think

11:1210 is a healthy number, we're going to make

11:14sure we only go up in increments of one

11:16here, and then click OK. And now that we've

11:19done that, we'll go ahead and show the

11:21parameter.

11:22And you'll notice if I give it a range

11:23table, it gives me a slider over here on

11:25the right hand side. So if you're wondering

11:27where I'm looking, I'm over here. And you

11:30can sort of move this up and down. Now the

11:32reason I've created this is because I want

11:34to put this parameter inside of my

11:36calculation to control the radius that I'm

11:38actually interested in. So if we go ahead

11:40and select OK, go back to my buffer, go in,

11:42edit that. And in here, we can actually say

11:45radius. And now we have a dynamic way of

11:47changing the buffer sizes. Now, if I go

11:49ahead and hit apply,

11:51you can see that actually shrunk. And if I

11:53go ahead and shove it right up to five, you

11:55can see the buffer region goes much larger.

11:57So now we're doing some some sort of sort

11:59of dynamic work here with parameters. This

12:02is really cool. I keep it to one because I

12:04think there's going to be a lot of trees in

12:06this area. And so now the next thing that I

12:09've got this working is to bring in our

12:11trees. Now the trees themselves are kind of

12:13interesting, because they are latitude and

12:16longitude points themselves. So you can see

12:18here, got latitude and longitude. So what I

12:20'm going to do,

12:21is I'm going to create another calculation.

12:24This time, I need to be very careful that I

12:27make point this time to specify the tree

12:29location. So we'll call this tree locations

12:32. Okay. And for these tree locations, we

12:36actually need to use the latitude. This

12:38time, we need to use the proper latitude

12:41and the proper longitude. And we'll go

12:43ahead and use that these were the ones that

12:46were in the data set, they're not the ones

12:47created dynamically from my parameter. So

12:50just just be careful,

12:51you don't get confused with everything that

12:53's called, you know, whatever it is. And so

12:55now that we have that we've got make point

12:57tree locations, that's going to give us a

12:59point for each of these. And then we can go

13:01ahead and hit apply, hit OK. And now I'm

13:04not going to visualize that because this is

13:06a really large data set. In fact, I'll show

13:08I'll just visualize it. So you can see it's

13:10if I just go ahead and just double click

13:12that tableau will try and visualize it. Now

13:15, I'm doing this in web edit. So I expect

13:16this to take time. But just so you know,

13:18this is, I think, a really

13:20large set of data. So something like 500,

13:23000 or 200,000 trees in this data set. And I

13:27'm doing this in the browser on a CSV that

13:29was uploaded, it took you know, quite a

13:31while, as you saw 256 meg, I'm essentially

13:34just talking to fill the air here. If I can

13:36't fill it, I'll just skip ahead to when

13:38this finishes. And there we go, you can see

13:41it's visualized. And this is this is sort

13:43of what I was worried about, I've got a lot

13:44of data points here, I can't really see

13:46anything useful. And the responsiveness of

13:49this whole application is just

13:50completely died. So this is what I was

13:52trying to avoid. Now, have I actually

13:54crashed my browser, this is now my new

13:56concern that I've actually completely

13:58crashed what was going on. We have made the

14:00page unresponsive. So let's wait and see

14:03what happens. I think it will come back to

14:06us. And we'll see what happens with web

14:07edit.

14:10There we go. So it's come back to us has

14:12gone back to the first sheet, I'm actually

14:14going to see if I can delete that sheet.

14:16Can I delete that sheet? Yes, I can delete

14:19it without going to it. Great. So what we

14:21're now going to do is we're going to use

14:24the new function, the intersect function.

14:26So the way this works is if we go ahead and

14:30type intersect, in fact, you can see a

14:32little description of it here is it returns

14:34a Boolean when it matches to geometry. So

14:37what we can do is we can just go ahead and

14:38double click it. And we're

14:39going to see if the buffer that we created

14:43intersects with the tree locations. Okay,

14:47so if the tree locations fall within the

14:50circle, it's going to give us a true or

14:52false a Boolean response. And that means we

14:55can say okay, intersection. That means our

14:58trees are inside of this buffer area that

15:00we've drawn on the map using our dynamic

15:03slider. So we can say okay, as part of the

15:05intersection, so we can go ahead and click

15:07Apply, and then hit OK.

15:10And when we do that, you'll see it creates

15:11a new calculations just just up here, it

15:13says true or false, we can actually drag

15:15that into the filters pane. So let me just

15:17go ahead and put that in there. And you'll

15:19see we'll get this option to select true or

15:22false from this list. Anything that doesn't

15:24match will be false. We only want the true

15:26items. So let's go ahead and hit Apply. And

15:29now that we've done that, you can see that

15:31it's now sort of kicked into gear,

15:32everything's working. Now what we can do is

15:35we can go ahead and go get our trees and

15:37put this on the map.

15:39So let's go ahead and get our tree

15:40locations, bring that in and add it to a

15:43second map layer. When we do that, we're

15:46actually able to generate the trees and you

15:49can maybe just see behind this, you've got

15:51the dots that have turned up. And so you're

15:52probably wondering, well, how do I get the

15:55selection of trees? Well, we need to do a

15:56little bit of formatting here. The first

15:58thing I want to do is actually bring my

16:00tree locations to be above my buffer. And

16:02you can see that's actually already the

16:04case. If it wasn't the case, I could just

16:05move these around and swap them around. But

16:08in this case, it's actually already

16:09correct, my buffers on the bottom. The next

16:11thing I'll do is I'll go ahead and set the

16:13buffer to orange. And I'll set it to a

16:15really sort of pale color just so you can

16:17just about see it. But it doesn't sort of

16:19get in the way of the visualization. So we

16:21kind of set it a pale. In fact, we can go

16:23for something a little bit sort of easier

16:25to see. Let's go for something like red.

16:27There we go. And the border, we can do a

16:30nice dark red border. So it's just much

16:32easier to see. And then for the tree

16:34locations, what we can do here is these

16:37individual items, you can see when I

16:39select one of them, all of them get

16:41selected. And that's because we've got

16:43nothing on detail to let you specifically

16:45select each tree. So we'll go ahead and get

16:48the tree ID, the tree ID is the identifier

16:50for the tree, we'll put that on detail. And

16:53what that will let me do is now select each

16:55and every individual tree. And now that we

16:58can do that, we can go and get the health

17:00of the tree, let's go get the health and

17:02put that on color just to give us a little

17:04bit of a more interesting visualization.

17:07And you can see that it's

17:08it's codified good in red, which in some

17:11cultures is actually, you know, fair

17:14reflection of how it works. But what we'll

17:17do is we'll go ahead and use a slightly

17:19generic, you know, formatting here, we'll

17:23say fair should be pink. And we'll now know

17:26is there anything sort of dull I can use,

17:28let's just say no can be that color, poor

17:31can be black. And that's about it. And as

17:33you're making these changes, it's changing

17:34the formatting in the background. So there

17:36you go, you can see the trees and the

17:37formatting

17:38work really nicely. And if I then go ahead

17:41and change the radius, you can see that we

17:43do get a slightly bigger circle. So to

17:45answer the original question, how many

17:48trees are within one kilometer of the

17:50Empire State Building? The answer is 2286.

17:54According to the 2015 tree census of New

17:57York, how amazing is that tableau letting

17:59us do that kind of analysis without having

18:01to go off into another tool. Now, the other

18:03thing I can do is don't forget, I had other

18:06elements in my point of interest, if I

18:08end up showing the parameter here, you can

18:10see that I can switch the point of interest

18:12from Empire State to Times Square, or not

18:14to that, let's put it over here, let's put

18:16my face over there, let's go to Times

18:18Square. And you can see that everything

18:20gets redrawn, because I've built the

18:23parameter around this sort of dynamism. So

18:25now I can go to another point of interest,

18:27I can bump it up, I can bump it down. And

18:30you can see that the radius will get bigger

18:32, bring in more trees. And again, I'm doing

18:33all of this in web edits. So it's super

18:35dynamic. And we even interestingly, I

18:38don't think they included trees in Central

18:40Park, because Central Park probably has its

18:42own tree census. So these are just trees

18:45not inside of central Park. And you can see

18:47it even catching some of the trees over in

18:49Long Island as well. So how cool is that?

18:51So really, really kind of cool feature set.

18:54Now, there's a couple of quirks here. When

18:56I first started using this, I'll put some

18:58sort of overlays on screen. The first thing

19:00I tried to do was I tried to bring in two

19:01data sets together that weren't related.

19:04And I tried to see if I could intersect

19:07data points in

19:07geometries from two separate data sets.

19:09That's actually not how this works. There's

19:11a sort of a limitation with the capability.

19:13And I think it's intended, and it's

19:15actually makes a lot of sense once you get

19:17into it. And the reason is this, when you

19:19have two separate data sets, this

19:21calculation is working at a row level. And

19:23so when you're working with two data sets,

19:25you can't really get those things to work

19:27correctly. And then there's also an

19:29aggregation challenge that comes up, you

19:31might see this error message that says,

19:33look, you can't mix the aggregation levels.

19:35And that again, is because you can't really

19:37sort of define to Tableau that these geomet

19:39ries are at different levels of detail,

19:42everything needs to be on the same level of

19:44detail. And so that's why I've loaded

19:46everything into the parameter, because in

19:48the parameter, I can specify the points of

19:51interest. And I can move those points

19:53around in space, the parameter is its own

19:56separate data set, but it can be

19:57dynamically fed into the visualization. And

20:00that allows me to then work with this. And

20:01so what you could do is you could use this

20:04as a way of sort of setting up a workbook,

20:06you could have maybe

20:0715 locations, I think that's enough to be

20:09able to type those in code those into a

20:11parameter. And once you've done it once you

20:12don't have to do it again. And the other

20:14thing I'm hoping they might do in the

20:16future is give you the way to sort of load

20:18a parameter with these locations,

20:20specifically for this use case, because I

20:22think it's quite nice. And but what I'll do

20:24is I'll put a link to a forum post that

20:26discusses this in a little bit more detail.

20:28I personally had help from other members in

20:30the community, specifically Jonathan Drummy

20:33, who actually explained this to me in a

20:35private thread that I can't really share

20:37here. But I'll link to a forum post that is

20:39public that pretty much explains the same

20:40thing. And you can get involved as well. So

20:42that's it. That's the feature. That's the

20:44intersects function. It's allowing us to

20:46build some really cool capabilities. I'd

20:49love to see what you guys do with it. And

20:51another interesting thing, this wasn't

20:52actually included in the main release notes

20:54. So it's another example of capabilities

20:58that you know, get sneaked into the product

20:59, they make it into the marketing but are

21:01pretty much game changing. As ever, I hope

21:04you've enjoyed this video, and I'll catch

21:05you in the next one.

21:06Transcribed by https://otter.ai

21:12[ Silence ]