Okay, so welcome to my presentation, why I'm here?
I'm here because I like to analyze massive data sets.
I like to play with data.
I like to experiment.
So today I will show something from my experience.
And what we will need?
We will need a nice data set,
some idea and the way to present it.
There are plenty of data sets,
plenty of open data sets,
like you can download Wikipedia and analyze it.
But we will be interested in just special data sets.
And there are a few also open,
like you can download open street map,
pre-process it and visualize something like
the number of traffic lights,
in different locations in India,
or in the Netherlands,
and where are more traffic lights,
or you can take data from crowdsource
sensors about temperature, air quality.
There are plenty of data sets like sensor community.
But today we will first look at ADSB.
Let me do a quick check.
Does everyone know what is ADSB?
Please raise your hand.
I expected something closer to 100%.
But anyway, ADS-B.
Automatic dependent surveillance broadcast.
Actually, this does not make it clearer.
But actually, it is the data that is broadcast
by transponders inside every airplane.
And on the airplane,
also inside helicopters, inside many drones.
Many drones are also required to have transponders.
And inside ground vehicles,
like snow, blow machines,
working on air tracks.
Inside even ground stations.
And this data is available to everyone.
Unencrypted.
And if you buy cheap radio receiver,
you can get this data and analyze it.
But actually, yeah,
if you look at this data,
it will look like flight radar 24,
or many similar websites that present it in real time.
And if you buy a radio station and collected,
you will get the data from like maybe 100 kilometers around you.
But there are plenty of services that are name it exchanges.
ADS-B data exchanges.
Like ADS-B.feel,
is ADS-B.1.
ADS-B.low.
I don't know why it is name it.
This way, nothing finds there.
It's a great service with daily data sets that
sets provided in public domain.
So you can download all of them.
And no one will ask you,
why what you are going to do with that airplane's life.
They provide real time data feed,
not to the public,
but they gave me a special permission to work with this data.
For ADS-B exchange,
that is commercial service,
but they provide samples that you can analyze as well.
Some of these exchanges will give you a real time feed
if you provide your data from your radio station.
Okay, so let's take a look at ADS-B.low.
They publish all these data sets on GitHub,
and every day,
it's about 1.5 gigabytes of data.
It is available for several years.
So if you terabytes maybe like 5,
10 terabytes,
it's sizable,
but it's available for a single machine,
for a simple enthusiasm to take
and analyze all of this data.
I'll do the easiest part.
It's probably the most boring part of my presentation,
but the easiest part is I will load all of this into Clickhouse.
Clickhouse, what is this?
It's my favorite database.
But just in case, if you don't know,
it's an open source,
analytics database management system,
available under Apache 2.0 license.
It is used by thousands of companies,
including the largest ones,
like every API company,
oh, sorry, every AI company,
every AI company,
every UI company you hear,
every day,
every company you do not hear,
often it's available since 2016,
and developed since 2009.
You can download it as a single binary distribution.
You can use it on your laptop,
whatever laptop.
Linux, Mac, freebies,
the ARM machine 686,
even risk five boards,
clickhouse will work there.
Clickhouse is just a popular database,
like Postgres,
but for analytics.
So let's take a look at the data.
It is the result of
producing of this binary
code of a radio signal
with the tool,
name it the RHSB.
And it generates JSON,
and JSON looks like this.
It contains metadata about particular
airplane,
like this,
the type of airplane
description,
timestamp,
and set of traces.
Where this thing
where at a particular moment of time?
What was the hiding velocity
pressure?
And for hiding,
there are different,
and for velocity,
there are different kinds of velocity,
like through airspeed,
or speed,
relative to the ground,
so plenty of data.
And we'll do,
like this.
We'll just split this into data file,
then we will join these files using
Clickhouse local tool.
What is Clickhouse local?
It is a common line tool
that lets you run SQL queries.
On top of local files,
or remote files,
or external databases,
or any kind of data sets.
It's like a database,
but inside the common line.
So let's join it to denormalize
and let's create a table.
Here is the table schema,
and there are a lot of,
like usual stuff.
Usually columns,
like time, date,
metadata,
ground, speed, and so on.
And there are just a few additional stuff.
Additional stuff looks like this.
This column, Mercator X,
it is a generated column.
You can see it like materialized,
using some expression.
And the expression involves some
logarithm,
tangent,
pi,
some arithmetic.
And the question for you,
what is it?
What is this magic?
What does it do?
What?
It's a conversion of latitude and longitude
into web, Mercator projection,
mapped into the range of you in 32 data type.
So we will have numbers from zero to four billion something.
And if you take a square of these numbers,
it will represent web, Mercator,
so you can just visualize it.
And we created two indexes,
minimum and maximum of this web,
Mercator coordinates.
And at the end,
you can see some additional magic.
And here is this additional magic.
Order by,
Morton and code of Mercator X and Mercator Y.
So we take two,
you insert the two numbers,
and put them into a function,
name it Morton and code.
And another simple question,
there should be at least one person in the audience,
aware of what I'm going to do with this.
Yeah, exactly.
Can't know it's two numbers into one number.
Using Morton and code,
Morton curve.
What is Morton?
Let's imagine you have a square with two coordinates.
And you create a single number by mixing this coordinates.
So you split this plane into four quadrants.
And numbers, this quadrant, like one, two, three, four, like zigzag.
And inside each quadrant,
you do the same split.
And if you look at the bits of this number,
it will be identical to taking the first bit of the first number,
the first bit of the second number, the highest bit.
Then the second bit of the first number,
the second bit of the second number,
and you like mix this bits together.
And you get this space feeling curve.
It's that I don't have a picture for this,
but I hope you imagine this picture right now.
Or you're thinking, I'm just talking some gibberish.
But okay.
No, it's worse.
And it's worse than a Hilbert curve.
It is like conceptually simpler.
And when I created this service,
we only had Morton curve support.
Now we have both Morton and Hilbert, but it's a really good question.
Hilbert curve provides continuous properties,
and it's a little bit more locality.
Okay, now I have a table,
and I created this script.
And after a day, it has loaded 95 billion records,
around 3.1 terabytes of compressed data.
And the question is,
what to do with all this data?
We need to do something beautiful, right?
And I want to visualize it in the browser
to aggregate and generate records.
And let's take a look at the result first,
and we will explain it later.
So I created the website.
Here is the website.
I hope my mobile connection will work,
yes it works.
And it represents all the tracks of airplanes.
The density of these tracks visualized,
and I can do any sort of stuff.
I can like visualize them by airline.
Interesting if my mobile internet will work with all these pictures.
And to the top, as the top,
you can see that we have different data sets,
like planes, places, doors, photos, and you.
And let me go.
What?
Yeah, there is the one mask.
Let's load Elon Musk.
Interesting why do we have time out.
But I hope with Elon Musk, we will get to this beta.
Where is Elon Musk?
Where is his airplane?
He's trying different things.
And actually, I do have mobile connection.
And there are some tracks from Elon Musk.
And he did some stuff in Italy.
But much more in London.
And interesting, which airports he used in London.
I don't know.
But also, yeah, I explored this data set,
so I know where it is.
Elon Musk.
And about Wi-Fi, the interesting thing,
if we have time for changing Wi-Fi.
But let me say that Elon Musk also visited Florida quite frequently.
Found each airport and taxes for some reason.
OK.
About Wi-Fi.
Let's try to keep it like this.
And also, in addition to airplanes, we have a data set of doors.
And if we look at the just density of doors,
it's uninteresting.
There are some doors, doors, observations.
By the way, the data is from E-Bird project,
which is a crowdsource project for doors watchers.
I'm not a door watcher.
I almost became a door watcher while doing this project.
But I'm not.
And we can visualize, by, for example, the family,
the family of doors.
And if we use this rectangular selection tool,
we can do something, something really interesting.
Let me go to places like closer to Antarctica.
And on Antarctica, closer to Antarctica, we have some tracks.
That look like airplane tracks.
But now airplanes play there.
These are doors flying between different interesting islands.
And if we use this rectangular selection, we can check particular types of doors.
Like this penguin, is it penguin?
No.
The pictures are from Wikipedia.
So some pictures are automatically selected.
Giant patrol.
But also interesting, if we click on some of these,
we will have the same map.
But filter it by this particular door.
And we can look at the distribution across all over the world.
Like in New Zealand, there is also such a door.
And more over, we can click here.
And here we will see a SQL query.
And this SQL query is running basically in real time across all of this data set.
And you can change it, you can do your own visualizations.
But also, interesting, we have a data set of photos.
From flicker, flicker provides many photos with permissive licenses.
At least some of them.
And let's take a look at a few photos from Belgium.
Where is Belgium, like here is Brussels?
Yeah, here it is.
And again, the density is not so interesting.
We can look at the coloring based on the hash of text.
But it is also not so interesting as if we click on this button.
And this button will do something unusual.
It will take every tile.
And for every tile, it will do a SQL query in Clickhouse.
And the SQL query will select the best photo from this particular location.
That means what is the best photo?
There are about 20 of interesting stuff.
I don't know, it's public data based on Clicker.
I don't know, maybe we should just do something like this.
And this selection to it should provide us a report about this particular photos.
Okay, and there is also one interesting data set name at you.
I will not show it to you.
It will be at the end of the presentation.
Not now, let's go back.
And let me explain how all of this, how is all of this implemented.
I also have another data set, David Ships.
For Ships, there is a similar original protocol name at AIS.
Automatic identification system.
It is used for Ships, so they don't have less chance to collide with each other and so on.
Either to navigate.
And again, you can buy a radio station.
There is a data exchanges.
And we did exactly this.
We bought a radio station installed.
It's done not by me, but my colleague.
And we feed this data into AIS Hub.
And they provide us all the real time data from all over the world.
We did not have an open source tool to process this data.
So we end up writing a small script in Rust.
Why in Rust?
Because if you know some language where you are professional, you use this language for real stuff.
If you want to write some draw a script or just have a pan you write in Rust.
Now this basically just single library, that is already available as a crate.
And the result looks like this.
It's pretty cool.
So this is a near router, I'm certain.
An open.
And interesting, what is this?
Maybe you can guess, maybe you can just read the map.
What?
No, it's not a router.
A router that was on the previous slide.
This is something like, yes, really cool.
Unfortunately, I like these small circles.
And these small circles.
And this is some port.
And it is a pretty big port.
Maybe one of the biggest.
Maybe anyone can guess?
Yeah, it's Singapore.
Okay.
So what is the implementation of all of this?
And what makes it possible?
Let me just quickly introduce it.
So the first is built in Rust API.
Clickhouse, provide an HTTP API.
It is included.
You can just do a fetch from JavaScript.
And that's it.
It provides parameterized queries.
And not like in other databases when you just write a question mark.
It does it slightly differently.
And the difference is all of this is type save.
This is type save parameter.
And you can create parameterized use as in this example.
It also provides materialized use.
So you don't have to always query all the data.
You can create tables that will represent just aggregations or samples from the original dataset.
And I use this samples for my service.
It creates materialized use.
That will sample 10% and sample 1% of the data.
It provides access control restrictions.
Including crawl, base access control, roll, roll, I will secure this and so on.
You create users.
You restrict them.
Nothing, nothing particularly different but it is just a match for data base.
You can configure all of this stuff.
You can also configure quotas.
Like how many requests from a single IP address can be done in a period of time.
From a single IP network.
So the service will at least not be easily overloaded.
It will be protected from simple dos attack spot.
Not necessarily from D does but anyway.
Functional indexes.
Including sorting keys by function.
Materialized columns.
Friendly SQL language which allows you to do a lot of interesting stuff.
Like unrestricted use of alias parameterized table identifiers.
Or stuff like order by with fill.
Which lets you not just order by some condition.
But also make it continuous.
It requires a support for all data formats.
You can imagine like this.
We see us with text based formats binary formats.
Protobuff Apache Parked.
Arrow native.
Some structure data like JSON.
Not just JSON like message pack as well.
So a lot of stuff built in.
Compression out of the box and not on the internal compression inside database.
But also the ability to process compressive data sets.
On the fly.
Without any additional tools.
It supports objects storage.
If you use it with the cloud like AWSS3.
GCS.
Azure hybrid storage is also possible.
You can use external tables with data lakes.
Iceberg.
Delta lake and so on.
Aquarius are parallel parallelized across CPU course.
And distributed across money machines.
You can create a cluster with multiple starts and
Treblecas.
It has query result cache and call so quite useful for this project.
And there are money modes.
Money modes of operation.
You download a single binary.
And you can run it as a server.
As a common plane tool.
And there is also a Python model for a clickhouse inside your scripts.
It can even replace pandas with pandas compatible API.
And this is specifically useful for data pre-processing.
You just install a clickhouse locally to CLI.
And you pipe something like JSON in.
CSV out or park it out with any types of queries.
So in my opinion, my honest opinion.
Clickhouse is the best database for analytic applications.
It is fast, scalable, resource efficient.
Easy to use.
It is pleasant to work with.
Robust reliable.
And let me say that friends and you are my friends.
Friends, tell friends to use clickhouse.
OK.
And before we will continue with that data set,
I would also like to invite you to the dinner that we organized today
in just like two hours.
So you are invited.
Now let's go to that service and try to find out something interesting.
So do we continue to look at the photos?
No, no.
We have one interesting data set, name it you.
And the question is, what does it show?
But how do I know your location?
How can I obtain your location legally?
Not a data from what?
No, photos is another data set.
And yes, this data set about you.
But how exactly?
What does it show?
It looks quite similar to many previous data sets, but it is kind of special.
Sorry?
How do you translate this data?
It's my question.
Sorry, I would like it to guess.
Please.
Not quite.
At least there are no so many antennas.
Even on the most popular ideas, the exchanges there are just thousands of antennas.
Here we have many more dots.
Any more hypothesis?
Yep, please.
Copy and solve the by IP location for post project.
Not quite.
But it's a good hypothesis.
If I collect the IP and IP geolocation, probably I should not store it for a long time.
Yep, it's more hypothesis.
But if you're not actually store location, but it's just light pollution.
The cities are here, so people are there.
Yeah, that's interesting.
But it is not light pollution.
And let me explain why.
Why isn't it look like light pollution?
Let me zoom out.
For example, what is?
What is here?
Any more hypothesis?
What?
GPS art?
Not quite.
Actually, it is not related to GPS art at all.
Let me zoom in to some locations where there is almost nothing.
Should I change my file?
Because I don't like it doesn't look good.
Because it's a good file for you.
Does it require any else?
Okay, I hope it will work.
I hope.
I feel it was the Wi-Fi works, no?
Was them dwells?
Actually, I trust my little bit more.
So let's zoom in to a certain location where there is almost nothing.
Well, you still have a few minutes to guess.
At least try.
Some weird hypothesis.
Yeah, yes.
Well, let's make people guess first.
Because I really want to make sure people have a chance to guess.
Can you guess?
It's no people looking at this website, right?
We've said it's not.
Exactly.
This website also records where exactly people look at the map.
Yeah, and this place is as a density of points.
This is basically the whole idea.
And now let's go to your questions.
Yeah, questions.
Questions about this website?
It's not mental at all when a particle or a location or something.
I think I'm happy to.
Thanks, this is really cool.
So for you, it looks like it's more like an experiment, right?
It's not your main focus of work.
It still looks very impressive.
What you've been interested in building some kind of tool, which would kind of make it easier for people to have some data visualized using this approach.
But like, supply and their data.
Yes, a really good question.
Yeah, this is basically a hobby project.
It is also open source.
So we can press on this link about.
And we will see a GitHub repository.
I don't have any particular expectations about this project.
But it is fairly generic.
It is also quite simple.
So all the source is basically a single HTML page and a few JavaScript files.
A few and one source scripts.
Basically three files.
Let me solve the configuration.
There is conflict.js.
And it basically describes the data set and everything we sort of visualize.
Looks like like this.
We have a license.
A set of you make it bigger.
Sorry?
You make it bigger.
So this is a description of the planes data set.
The license set of endpoints to the database, HTTP endpoints.
Data levels of detail.
Here are three levels of detail.
But it could be just one.
And the template query.
Here it is.
It's a template query for the record.
And the description of a separate set of SQL queries.
And if we look at other data sets like the data set name it you.
Here it is.
Instead of the license, we just use this website.
A single data source.
A single level of detail.
A parameterized query for the report.
And the parameterized query for the styles.
The query will return RGB, RGBA binary data.
Which is put on kind of HTML kind of.
And this is basically just at the order data set set.
It's done.
I think we have time for questions.
Thank you.