Dynamic dashboards with D3.js and CartoDB
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 183 | |
| Author | ||
| License | CC Attribution - NonCommercial - ShareAlike 3.0 Germany: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this | |
| Identifiers | 10.5446/32051 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language | ||
| Producer | ||
| Production Year | 2015 | |
| Production Place | Seoul, South Korea |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
FOSS4G Seoul 201590 / 183
7
8
47
53
54
65
73
74
79
82
84
92
102
103
105
124
126
127
130
141
142
143
156
161
162
170
176
178
181
183
00:00
Server (computing)Query languageOrder (biology)Key (cryptography)Row (database)Medical imagingType theoryMessage passingMappingSeries (mathematics)DatabaseDifferent (Kate Ryan album)Visualization (computer graphics)BitGoodness of fitView (database)Presentation of a groupTesselationNumberGeometryMultiplication signTrailPhysical systemComputer fileSource codeField (computer science)Network topologyWeb browserLevel (video gaming)CASE <Informatik>Stress (mechanics)MetreSystem administratorCodeLine (geometry)Price indexNumeral (linguistics)Neighbourhood (graph theory)Demo (music)Flow separationCategory of beingMathematicsQuantileProcess (computing)DialectTable (information)WordWebsiteData miningFunctional (mathematics)Group actionMassTask (computing)Electric generatorError messagePolygonLattice (order)Service (economics)CodePerspective (visual)Video game consoleDynamical systemElectronic visual displayPoint (geometry)RootWave packetLogicTunisEvent horizonGraph coloringRight angleState of matterStapeldateiFilm editingOnline helpExecution unitTerm (mathematics)Client (computing)TwitterRevision controlResultantEndliche ModelltheorieSequelSoftware developerVideo gameAreaComputer configurationMoment (mathematics)Hydraulic jumpProgrammer (hardware)MereologyXMLComputer animation
10:02
Link (knot theory)Computer configurationSheaf (mathematics)Computer animation
10:31
Level (video gaming)Multiplication signDemo (music)Clique-widthGraph coloringQuery languageTwitterGeometryPiCASE <Informatik>Pie chartQuicksortDifferent (Kate Ryan album)AreaSystem callLibrary (computing)Row (database)Hardy spaceComputer fileLaptopRange (statistics)InformationElement (mathematics)1 (number)Temporal logicView (database)Bit rateWeb pageMappingKey (cryptography)Functional (mathematics)Dimensional analysisJunction (traffic)Endliche ModelltheorieDisk read-and-write headForm (programming)Sheaf (mathematics)RectangleDrop (liquid)File formatScripting languageProcess (computing)Kritischer Punkt <Mathematik>Population densityUniform resource locatorState of matterCuboidWordGradient descentType theoryNumberCovering spaceDesign by contractGroup actionMathematical analysisRight angleWeb browserSpacetimeCategory of beingLimit (category theory)Medical imagingComputer animation
17:11
Functional (mathematics)Dependent and independent variablesNetwork topologyLevel (video gaming)SequelUniform resource locatorGraph coloringPoint cloudArithmetic progressionWordExpert systemElement (mathematics)Read-only memoryBoiling pointComputer programmingIncidence algebraNormal (geometry)Moment (mathematics)Visualization (computer graphics)ProteinMultiplication signInjektivitätSound effectGroup actionCodierung <Programmierung>Sampling (statistics)AdditionComputing platformInformation securityKey (cryptography)Food energyInstance (computer science)Source codeRoutingComputer configurationFront and back endsSmoothingDefault (computer science)Social classMereologyQuery languagePasswordAreaString (computer science)Table (information)IterationBlogSimilarity (geometry)Operator (mathematics)Directed graphGene clusterSummierbarkeitPoint (geometry)Line (geometry)Demo (music)State of matterParity (mathematics)Product (business)Reading (process)Self-organizationTracing (software)Beta functionRight angleVirtualizationPlastikkarteEnterprise architectureHypermediaBitDifferenz <Mathematik>Clique-widthAmerican Vacuum SocietyCodeDesign by contractKeyboard shortcutPiPie chartLoginSoftwareComputer animation
23:48
AreaSource codeGoodness of fitPerfect groupLecture/Conference
24:27
Computer animation
Transcript: English(auto-generated)
00:04
Welcome to session 4.2 on new business, I think it's called. I'm John Powell, I'm the session chair, I'm also the first speaker. I work for a small company in the UK, data reseller essentially. I'm mostly a database programmer,
00:22
but I'm gonna present on two JavaScript libraries, CartaDB, which I'm sure some people use, yes? Anyone use CartaDB? No, good. And D3JS, which together, in combination, I hope to show you can use to make some interesting visualizations.
00:41
So, I spend a lot of my time dealing with data, which is, I don't know, a lot of people probably work with data, it's messy, large, I spend a lot of time cleaning it, using one of the favorite tools we see there. It's actually horrifying how bad data can be. Had a tree set recently,
01:01
which had trees that were 1,000 meters tall in it, which is true but unbelievable, no one had tested it. So, for this demonstration, I'm actually gonna show you some crime data from the UK. This is freely available, and it looks like this, which is, you have basically, you have an ID,
01:24
which is ridiculously long, and half the time doesn't exist anyway. You have latitude and longitude field, this is data for London, quite a lot of this data was in France and Scotland, so that was cleaning step number two, and then you have outcomes
01:41
and various other things, and those suspects identified, this is sadly very common, and there's about 20 different crime types, everything from antisocial behavior to burglary to one I particularly hate being a cyclist, bicycle theft, and as you will see, this is not sole, there's a lot of crime in London.
02:04
So, what I did is I took all this data into Postgres and ran some queries, the details of which are not very interesting, but essentially some spatial joins, doing a bit of waiting across regions, waiting somewhat by the population of the neighborhoods
02:22
because they try and anonymize the crime somewhat, so it's not, black marks certain neighborhoods too much, and what came out after running this query is this, which is for one crime type just for London, five years, 369,000 rows in Excel, nasty,
02:42
you look at that and you think, my colleague says that's the deliverable, and I said you have to be kidding me, how can anyone make any sense of that, it's just a row of millions of numbers, so, and then, what I did, each of these numbers here is a postcode, a particularly bad UK system
03:01
involving numbers and letters, which again is another source of huge error, so if you ever design a system, use zip codes, they're much better, it's five numbers, you can't get it wrong, so these are postcodes, I did a join and brought in the geometry, and so that I could then load it into CartiDB
03:21
and actually get a look at it, so before I show you CartiDB, I'll say why, no one uses CartiDB, a couple of people, okay, this might be a bit boring for you, it's cloud based, I can't stress how important this is, it means if you don't have to,
03:43
you upload your data and it's there, you have a console, you can run queries against it, just like you would on your desktop, but you don't have to have a Postgres installation, you make a change, it's live, I'm still waiting, we're still on Postgres 9.1 at work, which is a nightmare, been waiting for an upgrade for years
04:01
so it gets ran sysadmin essentially, and I don't know if you work with a lot of other developers, you want to change one line of code and it might take three days to get it live, so it avoids all of that, Postgres under the hood, which is great, numerous visualization options, which I'll show in a moment, that's the key side of this, on the server side, it's actually WMS,
04:22
so the images that come back are WMS, this is really important because you have, the data I'm about to upload is many megabytes, and if you try and send that to the client, and generate it on the client, you send 50 meg files to the browser and it's just gonna die on you, whereas CartiDB actually generates them on the server
04:43
and sends it back as WMS, so it makes it very responsive, and finally, there are many others, but it's extendable with CartiDB, JS, which is a wraparound leaflet, and I ask you to pull back the visualization and do more interesting things with it in the browser.
05:03
Finally, before we get to the CartiDB, this is what my data looks like now, I've got a crime type, some sort of number, I don't know what that actually means, but it's a crime indicator for that area, there's postcode, and a year, so the year's repeated, the postcode's repeated, so what that means is when you have the geometry,
05:20
the geometry's been repeated multiple times, which is useless for the purposes of CartiDB, you need to have each column be a separate category and you only want the geometry once, otherwise, you're uploading megabytes of data and you'll get annoying messages from CartiDB saying, please upgrade your plan, because there's a free version,
05:41
and then there's paid for versions, and the more megabytes you store on the servers, the more they'll charge you for it, which is fair enough. So, in order to, well actually, let's go to CartiDB now. So, CartiDB looks like this, this is the data, there's a data and a maps dashboard,
06:01
so these are the data sets, and if you look at one of these, you'll see there's essentially just a view of a SQL table, and so here we have, this one is actually bird tracking routes, this is something called Torque, which is one of the CartiDB visualization types,
06:21
this one's great for showing dynamic moving data, and this has become really popular with the ornithological crowd, because now they can suddenly show birds flying all over the world, so we have a bird name here, there's seven of these, a geometry column, which is the point, and a date time. On the right here, this is the CartiDB dashboard essentially, so there you can see you can run a,
06:42
SQL query straight in there, and then, if I go to the map view, okay, so it zooms in on the map, so now, you have these wizards, these are how you define the views on the data, and in the case of Torque, you have a time column, the date time,
07:02
and bird name, which is a category, so you see here you have Eric, Nico, and Santa, these seagulls flying around, and CartiDB just iterates through it for you, and gives you a nice, gives you a nice real-time demo. Unfortunately, my crime data is not like this,
07:20
so going back to that, what I did, in order to get it from columns with repeated postcards in years, I ran this crosstab query, anyone familiar with crosstab queries in Postgres? From TableFunk, so basically what this does is it pivots on the year, you've got generate series down here, and that will basically lay it out
07:40
so that each crime year is in a row, and you've only had the geometries once. So having done that, and go back to CartiDB, data view, okay, so here are my crimes.
08:04
Sorry? No, not mine, no, no. Although my bike was stolen three times in London, but I don't think it figures, because I never reported it, it's pointless. Anyway, so there we go. I only put three kinds up, antisocial, the years, burglary, and here you see the geometry,
08:20
just as polygon, but obviously there's an actual Postgres geometry behind that, and then this wonderful postcode field here. So if I go to a map view, okay, so there we have it. Back to the wizards, this is obviously a chloropleth map, which is mapping an intensity of some event,
08:41
in this case crime, to a color, and you have various clustering techniques, Jenks is one that does better separation that actually takes longer to calculate. If I change that, you'll see it will regenerate on the fly. You can change the number of buckets.
09:01
Seven is more interesting, because then you get more, and you'll see also that the legend down here changes, and then finally, let's choose a different antisocial behavior, for example. This comes back. Not very good colors, actually.
09:21
This is central London, that's Chinatown, so there's a few things going on in there, and I promised to show this is, this really is a WMS. So we go in here, you'll see that these are all actually generated, these are tiles that are generated on the server, so that's what means that you can display
09:41
huge amounts of data very quickly like this. So that's all well and good. What if you want to have something a bit more dynamic? So what I did was, you can add extra layers in here, which I did before.
10:03
You see add layers, you can show crime for five different years for the same category, and then you click visualize, and it will create a map, and once you've done this, without geography, no way, you click publish, and this actually gives you
10:21
three options, it gives you a link, which you can just send to someone and they'll get a view, you've just seen. Much more interesting, what I'm gonna show, is you get this car2db.js, this is essentially a JSON file, doesn't look that great, but that encodes the information about the layers that I've just generated.
10:41
So putting that into a JavaScript page generates this, and now, I've got a click handler, and that will just switch between these predefined views. I use different colors just to make it more obvious. So that gives you, it would've been nice maybe to have a year and a crime category,
11:04
different dropdowns, so it could've shown all of them, but just for demonstration purposes, there's three of them which are pre-canned. The JavaScript for this is incredibly simple. You create a map, you can see this is leaflet, essentially, and then you add in this layer URL, which is the JSON file I've just shown,
11:23
and then you have a callback, basically, once you've added it, and you can do, there's a done function you can do things with, and that's why I've put this layer selector, this adds a search box, if you want to search on the postcode, it'll take you there. That's essentially that, that's all that's necessary
11:41
to get that view. So, at this point, I thought, well, that's great, but how do I get an idea of, say, trends over time? So, for that, I looked into quite a few things and came up with D3JS, which is a phenomenal library.
12:00
Anyone familiar with anyone using it? Yeah, it's incredible, supports all sorts of formats, but what's great about it is it's data-driven, so basically, it manipulates HTML elements for you, and you just throw data at it, and it redraws once you find the view.
12:22
So, it covers Spatial as well, and you can do Chloropleth maps quite easily in D3JS, but again, when you're dealing with megabytes of data, as is the case here, it isn't such a great thing to send to the browser, and it's got a really beautiful style, it's functional and chainable. Every method that sends this, you can just chain together,
12:41
which I'll show in a second. So, how we get data, how we get data back from what I'm gonna do is add a click handler so that when I click on one of these postcards, it will send back data based on a geometry intersection, and this is essentially what the CartiDB
13:01
JavaScript API looks like, sorry, SQL API looks like. You can either use an Ajax get JSON or a D3 JSON, and then that's my URL and the key, and then this is the query string, select a bunch of crime types, that's gonna be a year for a particular crime type, whichever one I've chosen in the view, and that will send back the data.
13:21
So now if I uncomment that, live demo always fun, reload. So now, when I click on one of these crimes, I should now see, yeah, great. So now we've got a yearly trend, so I can see actually that area
13:41
has been really bad for antisocial behavior for forever, basically. That's something that people do in the UK, which is drink way too much, and then go out and shout in the street and fight each other and all this, so this is central London, you can see there's a lot of that going on. Look at vehicle, same thing, bad area for vehicle theft.
14:01
Burglary, same again, okay? So you get a time trend along with, you get a spatial overview of how that postcode looks related to the ones around it, and you get a temporal trend of what's happened at a time in that area.
14:20
So, one more thing to show is, this is not very good for the British police, but crime clear-up rates. So I'm gonna add a pie chart, and another lovely query coming up.
14:41
So now, reload this again, okay, we'll go to this area. So now, when you roll over these, it's gonna do a call back, call the data from Carter DB again, and draw a pie chart showing the clear-up rate. None, unfortunately, is the commonest category, that means the police didn't even enter
15:01
anything about those crimes, it just, nothing happened at all. If you're really lucky, ah, great, red, okay, caught red-handed, so that's charged in some level. None, and not prosecuted. As you can see, crime detection in London is not doing that great, and in Seoul, you can leave your laptop in a cafe
15:22
and go off for two hours, and it will still be there, but not in London. To show what this looks like, because this data is, it's, you've got three rows coming back, so you need to line it up so that,
15:41
sorry, we go back to D3. D3 works essentially by, you define some dimensions, and you have Y and an X axis, which basically the range of the dimensions of your chart, and the domain, are the values that have been bound
16:02
from the JSON that's coming back from the SQL API call, so there you have this anonymous inner function here that just returns the value in this case, which is the value of the crimes, and the key here is the year, range, band, band, so that creates an X and Y axis, and that redraws,
16:21
this puts on the labels, and then the actual elements themselves, the way D3 works is, you basically, you have a div, I've got a chart div, you append SVG to it, you set the size, append a group, transform it to put it into place, and then, because we're talking bar charts here,
16:40
we append a rectangle, and the X and the Y again, are just functions dependent on the key and the value, that's where they're placed, and the width and the height, as you can see, the height minus the value, so that's what draws it, and finally, there's the mouse over, which calls the pie chart, and the pie, the thing I had to do for the pie chart
17:01
is this awful looking query here, array to JSON, array out row to JSON, what that takes is a bunch of rows, which are the chromatic types, and the numbers, and the pie chart, and it bonds it up into one row, so you can iterate through the keys and values, and the pie chart is very similar, you create an SVG element,
17:21
but this time you bind the data to this pie function, and what this pie function does is by JSON, the pie function basically calculates the art widths from the data, and then it creates a path, the SVG path element, and again, we've got a function for the color,
17:41
which is based on the data, a legend, the legend is simply an HTML table, so that's essentially how I'll actually show you the generated elements, so the chart, element tab to the chart,
18:00
you can see there's a diff called chart, it's got the width, it's a group, and then each of the bars is a class width, an X and Y, and a width and a height, and that's it, the pie, basically the same, there's the legend, HTML, there you go,
18:21
there's a path, which is one of these, each one of these is a path element. Right, that's basically my time up,
18:41
so any questions? I'm sorry, I'd like to point out, I'm not a JavaScript programmer, so this is a work in progress about techniques rather than a beautiful, no, I probably have more security questions,
19:03
you were showing, I'm sorry. Anyone from CardzDV here? No, I'm not a security expert, but you were showing the source code of the HTML, and I saw there are a couple of things, first you have the SQL query in there,
19:20
you have that with string concatenation, that's SQL injection, you have an URL for an SQL API, including the API key, and I'm looking at that and I'm thinking, okay, well, that's nice to play with, if you're holding it for yourself, but how are you going to put something
19:40
like this in production? You can secure these tables, so any people with a login can access your CardzDV account. Ah, I was hoping, just the man, thank you. Yeah, so the SQL injection thing is not an issue, the reason it's called the SQL API is because you're allowed to hand SQL to it,
20:00
and we run it at a permission level, that makes sense. It's a read-only permission level by default, if you provide your API key, then you can get a higher level of permission. So the problem isn't the SQL injection, the problem is the API key. You don't want to put your API key into your code so people can see it, because basically you're showing them your password, which then allows them to do write operations
20:22
on your table, which you don't want. Thank you. This was a demo. That was the one question I was hoping I wouldn't be asked. Well done. It's supposed to be about the graphics, but thank you, Paul.
20:40
We've started using CardoDB to visualize some fire department responses, so we've got the location of the incident, and we've got the location of the vehicles when they're dispatched. So we've got A and we've got B, and we can infer what the route is. We want to visualize a nice, smooth travel path
21:03
like the bird data, and so we go through these iterations of interpolating the points along the line and trying to do that in a smart fashion so that it makes sense. Have you done anything in that regard? No, but that would be more using something like PG routing, I'd have thought,
21:20
or matching it up to an OSM network or something along those lines. That's not something I don't think you'd do within CardoDB. Yeah, I mean, it would boil down to more of a post-GIS. I would say that's more of a post-GIS thing, yeah. Thank you. Any comments from the CardoDB expert at the back?
21:40
No. No, so no, I don't think that's not built in CardoDB functionality at the moment. I still think it's essentially a visualization tool. Obviously, you could do anything you would do in Postgres on the back end, but no more questions.
22:11
You said it's in clouds. Which cloud, and can I use my own cloud? If I am, for instance, using Amazon RDS
22:21
with some Postgres back there, would that be an option to plug that into? There is a way of syncing, no?
22:40
Yeah, we basically have our own contracts with AVS, and we have our own clusters and parts of AVS which are dedicated to us, all this kind of virtual private cloud and all this kind of stuff, so we are not going to put our data into the cloud, which is not secured by our experts,
23:02
and so on and so on, that's a corporate policy. Would that be something that we could arrange? He works for CardoDB. I am from John Railways. If you're going to use CardoDB as sold by CardoDB in the cloud, you could arrange it,
23:22
although it would be fairly expensive because it's fairly custom. We're really about selling access to a platform. Our platform runs both on the AWS cloud and the Google cloud. Bigger organizations can get it on premise and run it on their own infrastructure, and they can set up special enterprise agreements
23:43
to run their own bits and pieces. If you wanted to roll your own, you certainly could by taking the CardoDB source code and deploying it yourself, but that would, of course, be a lot of work and expertise, which is why CardoDB exists, to get all that effort out of the way.
24:05
Perfect. Any more questions for me or Paul? No, okay, good.