Map layers Multiple data sources - Tableau 2021.4 - I mapped every road & bus stop in Central London

0:00Hey it's Tim here. In 21.4 you can now use

0:03multiple data sources for map players. Now

0:05that

0:05doesn't sound like an incredibly new

0:07feature but up until now if you wanted to

0:09use two spatial

0:10data sets with map players you had to do

0:12some data mapping essentially to get it all

0:15into one data

0:16set either using relationships, doing some

0:18data prep in something like ortrix or doing

0:20some

0:20modeling elsewhere. Essentially you don't

0:22need to do that anymore you can see here

0:24that I've actually

0:25got a map it consists of red dots and what

0:28is essentially blue and yellow lines these

0:31actually

0:31represent roads in London so the map I'm

0:33looking at is actually London. On the top

0:36left hand side

0:37you can see that I've got my bus data

0:38extract and then on the second data set you

0:40can see I have

0:41something called TQ road link this actually

0:43comes from a company called Ordnance Survey

0:46. If I swipe

0:47over here to the left hand side and

0:48actually go to this first tab you can see

0:50that they have this

0:51open data capability where you can download

0:54data sets from their website here in the UK

0:57. What I've

0:57done is I've downloaded the road data so I

0:59actually downloaded the road network for

1:01the entire UK

1:02and I know having worked with this data set

1:05that TQ actually represents the the grid

1:07square that is

1:08actually London essentially. I've then also

1:11gone and found the TFL bus stop locations

1:14in London so

1:14I've downloaded this open data set and I've

1:16also used that. In this video I'm going to

1:19show you how

1:19to bring these two data sets together to

1:21make this map without having to do any sort

1:24of data prep

1:24essentially keeping them as two separate

1:26data sources. Let's get stuck in. Okay so

1:29here I am,

1:30I'm in tableau nothing's been created

1:32everything's pretty much good to go. What I

1:35'm going to do is

1:35I'm going to connect to my bus data because

1:37I want to highlight a small problem that I

1:39stumbled

1:39across at least in the early version of

1:41this release. If I go over to the

1:43particular file

1:44in question it's actually a text file and

1:47if I go to the 21.4 demos if I actually go

1:50to my desktop

1:50it's actually there the bus stop data. If I

1:52open it up you can see that it's just a

1:54standard csv

1:55file there's nothing sort of sensational

1:57about this. Now in the UK we have a

1:59different sort of

2:00grid system it's called I don't actually

2:02know what it's called but it uses something

2:04called

2:04Eastings and Northings okay and this sort

2:07of confuses me all the time. In essence

2:10Northings

2:11refer to latitude and Eastings refer to

2:13longitude essentially but if I actually try

2:16and use tableau's

2:18built-in make point capability to convert

2:20this to latitude and longitude which is

2:22what I can then

2:23use to visualize it because it's a really

2:25large text file the performance of this

2:27just sort of

2:28goes through the floor. So I'm just going

2:29to show you this function working first

2:31with a filter data

2:32set so you can see that it does work but

2:34then I'm going to show you how I actually

2:35resolve this

2:36using altrix. So here we go we're going to

2:38go in here and what I'm going to do is I'm

2:40going to grab

2:40stop area and I'm immediately going to

2:42filter it to maybe just two pacific areas

2:44hit apply hit ok.

2:46The next thing I'm going to do is create

2:47the make point function so I'm going to go

2:50here and create

2:51calculated field and you can see that I

2:52have the Easting here I actually need the

2:54Northern first

2:55but what I'll do is I'll go ahead and I'll

2:58sort of make this bigger to read as well as

3:01just making it

3:01larger so we can see it on the screen here

3:04and then I'll open this little arrow here

3:06on the left

3:07hand side and the right hand side go to the

3:09spatial functions which are just there and

3:12you

3:12can see that there's this make point

3:14function that I can use to convert this

3:16into the relevant

3:17sort of latitude and longitude that I need

3:19because we don't have that what we need to

3:21do is specify

3:22the code for our coordinate system. So let

3:24's use this function so it's going to be a

3:27make point

3:27and once we've done make point you want the

3:30northing northing represents latitude I

3:33believe

3:34so let's just first go with northing then a

3:37comma Easting and then what I now need is

3:41the code now

3:41I never remember the code there's a website

3:43that has all of this but in essence I've

3:45got this blog

3:46here which is the first thing I googled

3:48from the data school in the UK it's called

3:51the EPSG27700.

3:54There's actually a really good website that

3:56has this if I just grab this and I just

3:57type in

3:58EPSG you can see that it actually comes up

4:01and you can actually search these codes for

4:04pretty much

4:04any country in the world so if you come

4:06across the coordinate systems that you're

4:08not sure about

4:08you can actually just type in the code hit

4:10search and it will come up with the

4:12actually that I did

4:13the wrong thing there if I just type in the

4:15code itself and you can see that it comes

4:17up with a

4:17relevant coordinate system but I know this

4:20is the right code what you can also do is

4:21just type in

4:22the United Kingdom and it will search for

4:24all the coordinate systems that might exist

4:27that are being

4:28used by different people in the United

4:30Kingdom of course the one we want here

4:31comes up at the top

4:33and what you need is this code here 27700

4:36so we're going to copy that we're going to

4:38go back to our

4:38calculation and that is the number we're

4:40going to paste right at the end of this

4:42makepoint function

4:43and if I just call this a point because

4:45essentially what the makepoint does is it

4:47takes the x and the

4:48y coordinate and creates a point on a

4:50spatial map so we can go ahead hit apply

4:53hit ok and if we now

4:54visualize these points you'll see that we

4:57get one two three four five six points here

4:59and we can

5:00separate these out by giving them a name

5:03and putting that on detail so now each stop

5:06here

5:06will have its own detail and you can kind

5:08of select that now the challenge we've got

5:10here

5:10if I just zoom out here and go to map

5:12layers and we can see that the map layer

5:15here is not really

5:16helpful so let's go to streets and you can

5:18see that it's it's it's actually I think it

5:20's put us

5:20in the middle of nowhere let's just zoom

5:22out and you can see this is just completely

5:24wrong isn't it

5:25it's put us in I don't know that I can't

5:28believe there's a bus stop in the middle of

5:31the ocean so

5:32this this looks suspicious to me um when I

5:35tried this earlier it was not there I

5:37wonder if I've got

5:38the easting and northing the wrong way

5:40around let me just double check so location

5:42northing location

5:43easting this should be let's check the make

5:46point function so latitude and longitude

5:49let's go over

5:50here and let's just look at this um I'm

5:52just going to google this make sure that I

5:55've got this right

5:56easting northing latitude longitude google

6:00is the answer for pretty much everything

6:03here so

6:03latitude is denoted by northing and okay I

6:06think I did put them the wrong way around

6:08so latitude

6:10is denoted by northing no I put them the

6:12correct way around and longitude is denoted

6:14by easting

6:15so uh and longitude is made by easting x

6:17yeah so I've got these the right way around

6:20I don't know

6:20what's happening we'll just carry on and

6:23assume this is okay um the long and story

6:25short of it is

6:26if I then remove this filter and I try and

6:29run this on this entire data set it grinds

6:31to a halt

6:32everything sort of freezes up the main

6:34reason is is that function's got to run on

6:36this text file

6:37even if I take an extract from the csv it

6:38takes a while so that's not going to be

6:40great for this

6:41video so what I did is I actually put the

6:43same data set into altrix so this is altrix

6:46you can

6:46see this is um ready set up I've got my

6:49same bus stop data I've got a um create

6:51point function here

6:53and all I've done with the create point

6:55function is I've put in the location of the

6:57easting location

6:58of the northing and you can see that I've

7:00actually got these um I've got these set up

7:02differently

7:02actually to how I had them in tableau so

7:04that might explain why things are going the

7:06wrong way

7:07around I can never remember it but if you

7:08're stuck just google it and then in altrix

7:10you've got this

7:11capability to select a projection I've

7:13selected the correct projection if you're

7:16not sure you

7:16can always search it yourself but you can

7:19see here it says epsg27700 and then when I

7:22run the

7:22flow it basically writes out a hyper file

7:24and you can actually see the output all the

7:26data points

7:27are now here available and there's 19,801

7:30records so there's 19,801 bus stops in the

7:33London area

7:34I'm not sure how many of them are active

7:35but nonetheless we're going to be able to

7:37visualize

7:37them very soon so here we are we have the

7:39data and I'm outputting it to a hyper file

7:42and just for

7:43reference what I'll do is I'll disconnect

7:44this particular file because I don't need

7:46it to to

7:46write again um I just disconnect this all

7:48together because I don't need this to write

7:50but what I'll

7:50do if I run that you can see how quickly

7:53altrix run that file it's super fast so you

7:55know in some

7:56cases you're going to be better off doing

7:58some data prep outside of tableau or

7:59outside something else

8:01before you get to the next step okay let's

8:03jump back to tableau and carry on making

8:05this map

8:06so what I've done is I've gone back to

8:08tableau here what I'm going to do is I'm

8:10going to close

8:10this I'm going to delete the sheet first so

8:12just we don't get like an error it's not

8:14going to let

8:15me because there's no other sheet so what I

8:16'll do is I'll create a new sheet then I'll

8:18go in and

8:18delete it and that will just clear it and

8:20now we can close this connection to the csv

8:23and instead

8:23we can connect to this hyper file so let's

8:25go ahead and connect to this bus data you

8:27can see

8:28that it's been created there and now we're

8:30in everything opens really really quickly

8:32and if we

8:32go to sheet number two you can see that we

8:34now have this centroid which if I just

8:36double click it

8:37loads instantly so you can see it works

8:39much faster now that the data has been pre-

8:41processed

8:41and I'm working off an extract even though

8:44the icon doesn't say I'm actually reading

8:46live from

8:47an extract so that's why it looks like that

8:49now we've done this we need to bring in our

8:52second

8:52data set and for this we need to go and get

8:54the open road street roadmaps essentially

8:57now

8:57if I go back to my ordnance server website

8:59you can download lots of different things

9:01the data

9:02that I specifically downloaded was os open

9:04roads and they've got like a standard terms

9:07of usage

9:08so I think it's important that I mentioned

9:10that I'm using this data set as part of

9:11that

9:11particular standard use but nonetheless now

9:14that I've downloaded everything I can just

9:16go ahead

9:17and connect to the relevant shape file so

9:19for this I need to go to tableau select

9:21spatial file

9:22and now that I'm here in 21.4 demos I've

9:25got the folder that came from open ordnance

9:28survey even

9:29and I can go to the data folder and I

9:31personally know that tq represents the

9:33London area so let's

9:34go ahead and find the tq shape files now

9:37there's actually several types of shape

9:40files for the road

9:40network the one I'm interested in is the

9:43road link and not the road node the road

9:46node just I

9:47think represents the points at which sort

9:49of the roads connect but the road link

9:50actually shows you

9:51the road so if I go ahead and open that you

9:53'll see that tableau reads their shape file

9:55and because

9:56it's a shape file actually shows me all the

9:57other shape files in here so I could bring

9:59more shape

10:00files in I don't need to do that I don't

10:01need to sort of union these and get

10:03something crazy going

10:04on you could do that I wonder maybe that's

10:06another video how many shape files can

10:08tableau handle

10:09we'll try and test it out but nonetheless

10:12if I go to sheet two you'll see that I now

10:14have two

10:14data sources here on the top left connected

10:17what I'll do is I'll go to the second sheet

10:20the third

10:20sheet in this case and I'll just show you

10:22the geometry so we'll double click the

10:23geometry and

10:24we'll sort of push tableau a bit to its

10:26limit and make it load all these roads this

10:29actually works

10:30it shouldn't take too long my computer is

10:32fairly powerful so it shouldn't have any

10:34issues doing

10:35this so we'll just give it some time and

10:37there you go it's loaded the entire road

10:39network for the

10:40southeast this is the tq zone of the entire

10:42uk now this is not a manageable data set if

10:45you think

10:46about how many roads are represented in

10:47this data set it's actually crazy so what

10:49we're going to do

10:50is we're going to filter this down a little

10:52bit just so we can work with it so let's go

10:54ahead and

10:55put the function filter in the filters and

10:57we're just going to select a and b roads

10:59these are the

10:59major roads here in the uk hit apply and

11:02then hit ok oh actually apply is fine and

11:04now we've got a

11:05more manageable sort of set of data so you

11:07can see the two constituent sets of data

11:09that we want to

11:10work with for the record if i add the road

11:13number onto detail then each of these

11:15individual roads

11:16actually become selectable and what we can

11:18actually do is put the function on color so

11:21we

11:21can see the main roads and the b roads

11:22essentially b roads are linked between the

11:24a roads and the a

11:25roads are typically used to get you from

11:27one place to another and around london they

11:29sort of coalesce

11:30and they sort of go outwards essentially so

11:32if i had other quadrants and regions you

11:34could sort

11:34of see them uh sort of make the map but all

11:37we're really interested in here is london

11:39okay so what

11:40i'm now going to do is bring these two

11:41together you can see that i haven't done

11:43any data prep

11:44between them in a new sheet i'm going to

11:45start building that final visualization

11:47that i showed

11:48you so here i am on a new sheet um what i'm

11:50now going to do is just start working with

11:53these two

11:53data so you can actually see how this

11:55feature works so again i'll repeat that

11:57step i just made

11:58uh we're going to bring in all the roads

12:00because it's cached it's loaded a little

12:02bit it's loaded

12:03a little bit faster and what we can now do

12:06i'm going to filter this again to just the

12:08a and b

12:09road so let's just keep all those roads and

12:11remember the question we're trying to

12:13answer

12:13here is we're trying to see where the bus

12:15stops are in relation to the roads it's a

12:16very simple

12:17question there's nothing sort of too

12:19complex i'll put function on color so we

12:20can see where the main

12:21roads are and for this um if i just if i

12:24just do this let's let's uh let's just do

12:26this and if i

12:27just select uh i've just drawn a circle

12:30here and you can see it sort of see it's

12:32just around london

12:33so i'll just bring this out and if i just

12:35select those you see that it kind of does

12:37select them

12:37now that didn't work because again i haven

12:39't put the road name or road number on

12:41detail so if i

12:42put that on detail then try and draw my

12:44circle again you can see this works a lot

12:46better and

12:47now you can see it's selecting the roads

12:49now some roads go all the way into london

12:51from central

12:52london that makes sense again as i

12:54described before i'm actually going to keep

12:57all of these

12:57so i'm going to right click and keep only

12:59these roads in my visualization so you can

13:01see it's

13:01filtered out all the other roads and then

13:04what i can do is i can just sort of zoom in

13:06here

13:07and just move my map in to where i'm

13:09actually interested in analyzing so let's

13:11just sort of

13:12move it here okay so this is about right we

13:14're going to keep it like this now the next

13:16thing i

13:16want to do is bring in my bus data and this

13:18is the really cool thing here if i just go

13:20in and

13:21i essentially start dragging my centroid

13:23you'll see that the bus data allows me to

13:25add to another

13:26map layer without doing any sort of complex

13:28linking to the previous data set so it is a

13:31sort of blend in a way it is working like a

13:33blend if i go to my tq road link you'll see

13:36that there's

13:36no sort of linkage but it's just

13:38understanding that these two things are

13:40working in the same

13:41geographical context and it's just layering

13:43the two things on top of each other so what

13:45this

13:45allows you to do is essentially use spatial

13:48data as a way of adding context let's keep

13:50pushing on

13:51with the bus data so um at the moment i've

13:54got them both as maps what i want to do is

13:56make the

13:57bus data circles and make the road data

13:59just like a normal background area so um

14:02what i'll do for

14:03this is i'll go to this automatic selection

14:05here and i'll turn it to a circle and you

14:07'll see that

14:08nothing happens because essentially what i

14:10haven't done is put anything on level of

14:12detail

14:12that can distinguish all of these bus

14:14points so at the moment we just have this

14:16little gray circle

14:17you can't see it there but there it is that

14:19's basically the average of all of these

14:21places in

14:21one place there's sort of no nothing to

14:24separate the data out so what i can then do

14:26is i can then

14:27if i just go back to my bus data here i can

14:30then get the stop name and put that on

14:33detail

14:34and boom you should get all the bus stops

14:36coming out so this was the right area to

14:38sort of zoom

14:38into we can see that we did a fairly good

14:40job of that what i'll do is i'll make it

14:42red just to make

14:43it easier to see and i'll put the size down

14:45a little bit so we can see that these are

14:47really

14:48really tiny points now of course what i

14:50haven't done in here is mapped the bus

14:53stops that are

14:54only on a and b roads remember that's what

14:56my sort of filter here is doing but

14:58nonetheless you

14:59can start to see how this is working and if

15:01i start to zoom in go right into central l

15:04ondon

15:04and just get the little cursor here i could

15:06use a spacebar to do this but it's kind of

15:09hard when

15:09you're recording a video and then we have

15:11this essentially so we have the bus stop

15:13sort of

15:14plotted over the different things now what

15:16we could do is we could start putting some

15:19of the

15:19road information back now that we've sort

15:21of narrowed in on what we want to see

15:23and what i'll do is i'll just actually

15:25remove this filter here at the top and what

15:27we should see is

15:29that the road functions come back so let's

15:31just give it a moment and boom there we go

15:34that's the

15:34whole entire road network that's come back

15:36and it's now a little bit hard to see where

15:38the bus

15:39stops actually are because it's the road

15:41network is crazy here in tableau it's just

15:43it's amazing

15:44but how amazing is it that tableau is doing

15:46this like this is just nuts i'm working

15:48with an

15:49incredible amount of data and obviously the

15:51interaction is a little bit slow but it's

15:54not

15:55my computer is also very very capable for

15:57the record so it's it's more than able to

15:59sort of

15:59keep up with sort of the demands here but

16:02if you sort of narrow in on a very small

16:04part of london

16:05you can get this incredible level of

16:08fidelity and map out what is essentially

16:11really really complex

16:12networks here using open data as well this

16:14is a really sort of powerful thing now the

16:17last thing

16:17i want to do is just dial down the road

16:19maps a little bit just make them a little

16:21bit more faded

16:23so they go off into the background there

16:25you go and then our bus stops stay on top

16:27and then what

16:27i'm going to do because i don't want i don

16:29't want to select the whole entire map every

16:31time i move

16:32over a single road so what i'll do because

16:34i'm just using it as a background context

16:37is i'll

16:37actually go to my map layers function so if

16:39i just hover over the map options you'll

16:41see that it kind

16:41of activates the map in the background but

16:44if i select control map layers i can

16:46actually lock

16:47these down so that they don't do that so

16:49what i'm going to do is lock the road

16:50network so that now

16:52when i hover over them you see it doesn't

16:53activate the map it becomes something sort

16:56of nice and easy

16:57to use and then i can go back to my centro

16:59ids here for the bus stops and bump up the

17:02size a little

17:02bit and we can make these a little bit more

17:05easier to see okay so this is pretty much

17:07it this is what

17:08i was trying to build and this is what the

17:10new feature in tableau enables it's a

17:12really really

17:13awesome little example what i'll do is i'll

17:15publish this up to tableau public so check

17:18it out in the

17:18description below so you can have a play

17:20with it download it fair warning this is a

17:22really large

17:23data set i've got a fairly capable computer

17:26so if you do download this on a desktop and

17:29you're using

17:30live connections make sure you take

17:32extracts of these or the package workbook

17:33will have that in

17:34there anyway and you can play around with

17:36this and just try and see how this works

17:38but maybe this has

17:39given you an idea maybe you've got several

17:42data sets that can add context to your map

17:44and then you

17:45can use your main data set as the top layer

17:47and you can disable all of that context as

17:49you see

17:49i've done here i can hover over this dot

17:51here and i can see the names of the bus

17:53stop maybe i've got

17:54information about each of these bus stops

17:56for example the average wait time so when

17:58you hover

17:59over this you get a nice little chart

18:01showing you the average wait time at each

18:02of these stops and

18:03you can start to tell stories in a really

18:05interesting way anyway i think this is

18:07really

18:07cool let me know what you think in this uh

18:09uh video in the comments below in the

18:10description

18:11it's a bit of a long one but it took a

18:13while to set everything up and i just

18:14wanted to make sure

18:15that i covered all the steps thank you for

18:17watching and check out some more videos on

18:1921.4 that i'll be releasing in due course

18:24take care