Aircraft trajectory analysis using PostGIS
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 | 266 | |
| Author | ||
| License | CC Attribution 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 purpose as long as the work is attributed to the author in the manner specified by the author or licensor. | |
| Identifiers | 10.5446/66344 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
FOSS4G Prizren Kosovo 2023250 / 266
10
17
23
44
45
46
47
48
49
50
52
53
80
84
85
91
110
116
129
148
164
167
169
173
174
181
182
183
186
187
199
202
204
206
209
215
241
248
265
00:00
TrajectoryObject (grammar)QuicksortOpen setSet (mathematics)Mathematical analysisInformationProcess (computing)Computer animation
00:53
User profileNumberWeb pageMultiplication signNumberComputer animation
01:16
Sign (mathematics)Multiplication signQuery languageDistanceCalculationPoint (geometry)PlanningSet (mathematics)System callString (computer science)Different (Kate Ryan album)Line (geometry)InformationComputer animation
02:04
Point (geometry)PlanningData modelMathematicsTable (information)Computer animation
02:26
Point (geometry)MathematicsQuery languageDistanceThree-dimensional spaceVertex (graph theory)Table (information)Computer animation
03:00
PlanningGoodness of fitThermal fluctuationsBitEndliche ModelltheorieLine (geometry)Vertex (graph theory)Computer animationDiagram
03:22
MeasurementPlanningPoint (geometry)Multiplication signNumberVertex (graph theory)Table (information)Computer animation
03:52
TrajectoryFunctional (mathematics)Point (geometry)MeasurementString (computer science)Line (geometry)Multiplication signTable (information)Computer animation
04:31
Table (information)InformationCodierung <Programmierung>IdentifiabilityVertex (graph theory)TimestampRight angleSign (mathematics)System callTouch typingAddress spaceComputer animation
06:03
InformationGoodness of fitSet (mathematics)Group actionTerm (mathematics)Open setWebsiteSoftwareCondition numberInformationPlanningComputer animation
06:56
System callElement (mathematics)Electronic mailing listQuantum stateMassSoftwareOpen setMereologyImage registrationState of matterInformationSign (mathematics)Dependent and independent variablesParameter (computer programming)Position operatorOcean currentBitAddress spaceCuboidSource code
08:13
File formatTable (information)BitFlow separationInformationQuery languageConnected spaceParameter (computer programming)Database normalizationPlanningCuboidDatabaseLatent heatDifferent (Kate Ryan album)CASE <Informatik>System callNormal (geometry)Source codeAddress spaceFunctional (mathematics)Server (computing)Point (geometry)Computer animation
10:29
Gamma functionDemo (music)DatabaseDisk read-and-write headTable (information)PlanningRight angleBitSymbol tableDot productRotationPosition operatorDirection (geometry)Multiplication signInternetworkingOcean currentPoint (geometry)Moment (mathematics)TrailLevel (video gaming)Computer animation
12:10
Multiplication signTrajectorySingle-precision floating-point formatLoginRight anglePoint (geometry)Moment (mathematics)Computer animation
12:29
Parameter (computer programming)Multiplication signTrajectoryBitMoment (mathematics)Dependent and independent variablesMetadataComputer animation
12:59
View (database)Function (mathematics)Formal languageCodeTable (information)Dependent and independent variablesTable (information)Address spaceFunctional (mathematics)CodeParameter (computer programming)Multiplication signTimestampDependent and independent variablesLoginBitGeometryModule (mathematics)CASE <Informatik>System callPoint (geometry)Electronic mailing listMereologyFile formatTrailOpen setDimensional analysisAreaField (computer science)MetadataState observerInsertion lossSource codeSlide ruleString (computer science)Line (geometry)SpacetimeSheaf (mathematics)Key (cryptography)Repository (publishing)Computer animation
16:45
GeometryPolygonSet (mathematics)Line (geometry)Dimensional analysisPlanningCASE <Informatik>Computer animation
17:31
PlanningMereologyDifferent (Kate Ryan album)Point (geometry)Multiplication signObject (grammar)Dimensional analysisTouchscreenInformationTrajectoryFunctional (mathematics)
18:33
GeometryFlow separationPoint (geometry)TrajectoryMetrePlane (geometry)Multiplication signFunctional (mathematics)NumberCASE <Informatik>MetrePairwise comparisonTrajectoryPlanningFlow separationRow (database)CalculationSet (mathematics)DistanceQuery languageCrash (computing)Term (mathematics)ResultantPoint (geometry)CollisionGoodness of fitComputer animation
21:25
Software repositoryLink (knot theory)Expert systemOpen setMultiplication signFunctional (mathematics)Different (Kate Ryan album)Computer animation
Transcript: English(auto-generated)
00:08
Alright, so yeah, I'm gonna talk about aircraft trajectories in PostGIS. I guess a couple things before I start. First of all, this is not my day job, this is just something I sort of discovered recently
00:22
and I thought it was an excuse to explore some capabilities of PostGIS that I thought were interesting. So really, I'm just sort of sharing this journey with you and I think I have kind of like three objectives. One is to show where you can get this data. It's an open data set, so I think that's valuable information. Then I want to show some tricks on how to get that data set into PostGIS and then finally
00:42
to use some native PostGIS tools to actually do analysis on these aircraft trajectories. So that's what I set out to do today. So let's go. First things first, I, like probably many of you, flew into Pristina Airport on my way here. So I took a screenshot of Pristina Airport's Arrivals and Departures page and just quick
01:05
look at this, it's pretty straightforward. You've got, you know, the flight numbers, you've got the airline, you've got where it's coming from, date, time, pretty standard things. So my first question is gonna be, how would we model this kind of data set inside PostGIS?
01:22
Very easy question and a very easy answer would be something like this. We would have the call sign, we saw, was it TK 1019 was the first one, where it's coming from, where it's going to, time of departure, time of arrival, and yep, come on in. And then of course we would have a line string to represent the path that that plane
01:44
took between those two points. So here's an example of putting some data in, very straightforward, and because it's PostGIS, we can do spatial queries on it, we can do simple stuff like this, we can do the distance calculations, we can even do spatial, spatial joins on the two different
02:02
data sets to get information out of it. So my question then is, is this a good way of representing the path of a flight between two points, what I just showed you? And I would say no, unless you're doing this, because what do planes do? They fly in the air, not on the ground.
02:23
So can we update that proposed data model? Yes, we can make a very small change to our PostGIS table as we create it, we can add of course elevation data. Everything else, very straightforward, now we have three dimensional points for each vertex on that path, and then we can do three dimensional queries in PostGIS.
02:45
So we can get how high are each of those flights ever flew, or we can do something like a three dimensional distance between a fixed point and that three dimensional path. Good.
03:01
Can we do better? Yes, we can do better, because planes, they change altitude as they fly, but they also change the speed during their flight. So at the beginning maybe they're a bit slower, at the end they're slower, in the middle they have fluctuating speeds, and this means they do not travel through each of the vertices in that line in a constant speed.
03:21
So how do we model this in PostGIS? Again it's very easy, we use something which is quite straightforward and probably known to many of you, we add a measure value to each of the vertices, which captures the time at which the plane passed through that individual point. So once again if we insert data into this table, we have the altitude, but now we
03:43
also have that m value, and here I'm representing it as a as Unix time, as epoch time, you could use anything as long as it's a number. And this means we now have something called trajectories. But let's nail down that definition of what a trajectory is. If you go to the PostGIS documentation, there is a function called st is valid trajectory,
04:05
and it defines a trajectory as this. So a valid trajectory is a line string with measures m value, which we saw, we have an m value in our table that we were creating, and of course the measure values must increase
04:20
from point to point, right? And that makes sense, time is linear, it's going forward, so the first point must have a smaller value than the next point, and so on and so forth. Good, so now I've given you a table we can work with, we've set up PostGIS in a way that can accommodate the data, so the question is, where do we get all of this aircraft
04:43
data? And I'm going to dive very briefly into the world of something called ADS-B. So as it turns out, aircraft are continually transmitting packets of data as they travel. These packets are unencrypted, and you can buy a relatively cheap device that will plug
05:03
into the USB port of your laptop, and that can receive this data and decode it, and you can get interesting information about the aircraft as they travel. The packets contain information such as the call sign, so we saw that in the table that we created, TK1019, they also have longitudes, they have latitude, they have altitude,
05:26
and they have a timestamp, and of course, you'll recognize that as the four things that we have for each of the vertices in our path, X, Y, Z, and M. One other thing that's going to be important that I'll just briefly touch on is that
05:43
each of these packets also has something called an ICAO24 address, and this is a unique identifier that every airframe, that every aircraft has, and there's an example, 36444, these are also transmitted in the ADS-B packets, so you can uniquely identify which aircraft
06:01
is traveling. Good. What if you don't have an ADS-B receiver? I do not have an ADS-B receiver, so I also can't go and just get this information out of the sky whenever I want it. Fortunately, there's a lot of people who do this as a hobby, and these people have
06:21
these receivers around the world, they're collecting this information, and then they are sending it to this website, to this group of people called the Open Sky Network. They aggregate all of this ADS-B data, and they make it available to anybody under certain terms and conditions, which you should read, by the way, through a RESTful set of APIs.
06:43
So we can go to their website, we can sign up, you can sign up by the end of this talk, probably, you'll have an account, and you will be able to access live and historical plain data to go and play with. So I'm going to talk about their API a little bit, because this is kind of like the key
07:01
part of how do we get this data into PostGIS. The first endpoint that I'm going to talk about is called the states endpoint, and this basically, when you hit the states endpoint with no parameters, you'll get a massive JSON list back, which has effectively the current position, the current information, let's
07:21
say, the most recent ADS-B packet of every aircraft that Open Sky Network is currently aware of. You can also add some optional parameters, as you see here. So you can add an ICAO24 parameter, which we all know what that means, and that would be only give me the information for one particular aircraft.
07:40
And of course, you can give a bounding box, you can say only give me the aircraft in this particular region. So that's great, very easy to use. Here's an example response. This is what we would call a state vector. You can kind of read it even without knowing what each of the individual elements is, like the first one is the ICAO24 address, of course, the next one is the current call
08:03
sign that that aircraft is using, then there's the registration country, and so on and so forth, more data that you can get from this endpoint. Okay, so now let's get this data into PostGIS, and we're going to do this in an interesting way. We're going to do this in a real-time fashion.
08:21
And the first thing we're going to do is create a new table. So just ignore the one I already showed you, and think about this new one here. It's not, or sorry, there's an interesting difference between this table and a normal Postgres table, and that's that this one is a foreign table. And when you create a foreign table, you do not put any data in the database. Instead, when you query this table, you actually go to an external data source, pull
08:46
the data in, and then expose it as though there were data in the database. And of course, what is our external source? It's that endpoint that I just showed you. So when we query this table, we're actually going to be making a call to an external endpoint and be getting live data from aircraft and returning it as though we just queried
09:06
a normal database. And yes, you do need to define what that server is. There's another separate Postgres command that you would have to run first, actually. And in case you're wondering, it does require a little bit of glue to connect Postgres and
09:21
that external data source. In this case, I wrote a very simple Python function, which will query the data, query the API, return the data in a format that Postgres understands so it can be exposed as a table. Here's an example. We query the table, and we put a where clause, and we say this specific ICAO24 address, and
09:45
it will return the information about that particular aircraft. In this case, it's United Airlines 2619. It's at this particular point, just as we expected. The cool thing is that this foreign data table is actually smart.
10:00
And when you put a where clause saying only give me this particular airframe, it knows to put the correct parameter inside the query out to the API. So you don't get a whole bunch of redundant data back, you just get what you asked for. You can also do a bounding box query in Postgres, and you get back all the planes in that particular bounding box.
10:20
And yes, it's smart. When you put that bounding box in, it creates the external API query with the correct parameters in it. Would you like to see a live demo of live aircraft right now? Yeah, I thought you would. So fingers crossed. This is just Fugis with a base map.
10:42
I've got a database running in a Docker container right now, and here's the live aircraft table that I just showed you. And I'll just drag it in here, and it'll take a moment because of the internet. But in a second, if everything goes well, we'll see a bunch of points. So these are live aircraft. They're exact current positions.
11:02
I don't think red dots looks all that cool, so I'm going to change this. I love how Q just comes with this little aircraft symbol. So you take that, looks better. It does look a little bit weird that they're all flying direct north though. You would probably agree. Hey, I've got a solution for that though.
11:21
Rotation. You know what's in an ADS-B packet? The current heading that the plane is flying in. So if we just change this to true track, and we do it, there. There's our aircraft, current live aircraft in the direct location, or sorry, facing the direct heading. And of course, the thing is, whenever I refresh this, we'll give it a moment.
11:44
It's a little bit hard to see up there, but when we refresh this, you'll actually see the planes move a little bit because it's live data. Again, every time I hit this button, it's querying the API endpoint and returning it through PostGIS. Well, they don't seem to be moving very much right now, but trust me, they did.
12:05
Well, like I said, it's hard to see, but we'll come back later and check to make sure that they moved. It does. I can even show you the Docker logs, and you'll see exactly that. It's querying just the extent that you're seeing in QGIS.
12:20
Okay. So, you're probably asking yourself, Ben, you said we're going to be talking about trajectories, and these are not trajectories. These are single points in time. And you're right, but we are going to get to trajectories in a moment. But we need two more endpoints, and the first one is the aircraft endpoint, and this is how you get historical data. So you say, for this particular aircraft, give me all of the flights that it has taken
12:44
between this start time and this end time. The response is quite straightforward. It's just a bit of metadata about that particular flight, and here's an example of it right there. So what we want to do is we want to get some historical data for a particular aircraft and put it into our flights table, which we created earlier.
13:02
And I'm modifying the flights table only to put in the ICAO24 address in, because that's a useful piece of data. But otherwise, it's exactly what we saw before. So, I like writing Python functions inside Postgres. It's very convenient, sorry.
13:21
And what I did here, just created a simple Python function that queries that endpoint. You can see that the parameters for this PL Python function are exactly the same as the parameters to the API endpoint that I showed you before. You have to imagine that the Python code to actually make the call is here.
13:40
There's not enough space to put it on the slide, but just pretend that we're calling the endpoint there, and we get some response back. And as long as it's a good response, we take that data and we return it in the same format as the flights table that we created earlier. So I can do something as simple as this. I say, insert into flights.
14:01
We select all of the columns, and we call this function as the source for it. And you can see there, the parameters are just the CO5, FO1 aircraft, and the start time and the end time. And then we get a table, which we can query, and we query this table, a very simple call
14:21
sign in geometry, and we get back the responses. Now, if you're observant, you'll notice that there's no geometries here. And that's because the flights endpoint does not return geometries. It only returns that really simple metadata. So how do we populate the geometries field? We have another endpoint. We have something called the tracks endpoint on OpenSky. And you call the tracks endpoint with a specific aircraft and a time that it was traveling
14:45
and it will get you back a list of all the points that it went through in this particular format. Again, I think you can probably read what this is, but it's a timestamp, latitude, longitude, altitude, and then two things which are not part of our schema,
15:01
but it's there for in case you're interested. So now we've got all the tools, almost all of the tools we need to get or to populate that geometry column in the table. But we do need to write a little bit more Python in Postgres. And what we'll do here, again, is we'll make a call to that endpoint, we'll get the data back, and then we'll be using this Python module,
15:24
which you can find in my repository, that basically lets you interchange geometries between PostGIS and, sorry, between Python and PostGIS. We query it, and then we create the line string, which is the path that it's traveled through based on the response from the API.
15:44
So that flights table with no geometries, we can very, very easily update it to have geometries by doing this. We just call the OpenSky getTracks function, which I just showed you, with the address and the time that it was traveling, and it will populate it.
16:02
Simple. So as soon as we've done this, we can clear the table and you can see that there's three-dimensional points in there for, sorry, four-dimensional points in the line string geometry now. So we've got the data from OpenSky into PostGIS
16:23
in that table format that I showed at the beginning of the talk. So I know I'm going to run out of time. I had to remove several, I think, interesting sections from this talk, but I think the most, maybe the key interest area that, yeah, the key interest area that we have is looking for spatial relationships inside this table.
16:43
So a classic spatial relationship in two dimensions is like, when did two lines cross or when does a polygon intersect with another polygon? And, you know, these are lines, right? Those planes are traveling in a linear fashion. So we can look for cases where in this data set,
17:00
I just imagine I have another data set of aircraft that we're working with right now, we can find when two aircraft crossed over one another. And this is a two-dimensional intersection, which is great, but we already know that we're not working in two dimensions because planes fly in three dimensions. So we would probably want to do a three-dimensional intersection.
17:21
And we can see in this particular data set, there was one case where two planes exactly crossed over in three dimensions or crossed paths in three dimensions. We are working in four dimensions. These are trajectories. It's not super interesting to find when two historical craft happened to cross through the same point at different times.
17:42
What we're really interested in is the relationship between two moving objects. And it's cool because post just can give you a lot of that information. And I'm just going to introduce one more notion as part of this talk, and that is the notion of a closest point of approach.
18:01
Okay, again, a concept that really works well or really is useful when you're dealing with trajectories. And I define the closest point of approach is the point in time at which two trajectories are closest to one another. So if I asked you when is the yellow plane and the blue plane,
18:20
when were they closest to one another, you cannot answer this question just by looking at this screen. You would need to know the timing associated with those two trajectories as they were moving past. So postgres or postgis has a function that lets you find the closest time that two trajectories...
18:42
Sorry, the time at which two trajectories were closest to one another. And all I did in this case is I just said for this particular flight, CAI 6KA, when was it closest to all of the other flights who were happening at the same time?
19:01
And we can see at these various points in time, this plane was close to some other planes in the data set. That doesn't quite tell us if there was an intersection though, right? It just tells us what time they were closest. Maybe they were 100 miles apart. Maybe, you know, who knows, one meter apart.
19:21
But of course, postgis has an answer. And it's called ST distance CPA. And it's basically saying what you would expect. What was the distance between two trajectories when they were closest to one another? And in this particular case, all I'm saying is if you look at all the trajectories,
19:41
was there ever a time when two of them had a distance of zero? And a distance of zero would imply, of course, an intersection or in layman's terms, yes, a mid-air collision. Okay, so it is good that there are no results when I make this query, because if there were results in this query, that would imply, of course, a plane crash.
20:01
Yeah, good. So I'm going to wrap this up really quickly here. It is possible also to find what the actual distance was between them in, let's say, in a way that's useful for doing comparisons. So again, I cleared the data set and I just said, when was it that any two aircraft were within 1,000 meters of one another,
20:23
just taking an arbitrary number? And I found that there was two cases where there were aircraft within 1,000 meters of one another. And the separation here, I did a calculation. There's a horizontal separation and there's a vertical separation. And we can see that the bottom row, they were 500 meters apart.
20:42
And in the horizontal, they were only 600 meters apart. Okay, and let's look at this case. So in the case that I found, basically, it was a plane flying north from Pristina and another plane flying south from Pristina. And they crossed quite close to one another. Again, 500 meters in a vertical separation and about 600 meters in a horizontal separation.
21:06
And the other one, you're probably wondering about, where they had a vertical separation of zero and a horizontal separation of only 142. It sounds pretty close. They were just at Zurich Airport. So, you know, nobody knew. This goes to show like ADBS data also works on the ground sometimes.
21:23
And like I said, there was a bunch more that I cut from this just because I knew we were going to run out of time. These are the easy, photographable links for you that gives you most of the functions. Most of the stuff you saw in this talk is directly inside the Docker container here.
21:40
Some links to the GitHub repos and, of course, OpenSky if you want to sign up for an account and do something like what I did. I'll just wrap up by saying, like, look, I am not an expert in any of this stuff. There's probably some things here that could be improved upon if you have any ideas. Like I come to Phosphorgy to learn from people. So, you know, please tell me if there's something could have done better,
22:01
could have done differently. I want to hear from you just as much as you heard from me today. So thank you.