-
Notifications
You must be signed in to change notification settings - Fork 0
/
data.qmd
213 lines (158 loc) · 6.84 KB
/
data.qmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
---
title: "Data"
---
The data is modeled according to the [ER diagram][ERm] in @sec-ER-model.
The `FLIGHT`, `EVENT` and `MEASUREMENT` tables are published under the **\acr{OPDI}**
in collaboration with the **[\acr{OSN}](https://opensky-network.org/)**.
The `TRAJECTORY` data comes from \acr{OSN}'s State Vector data.
The first data release contains limited flight events from `start-date` until `end-date`,
further enhancements are planned according to our [Roadmap](roadmap.html) page.
Download the data here:
| Table Reference | Description | Data Source(s) | Download Link |
|-----------------|---------------|--------------------|-----------------------------------------|
| `FLIGHT` | Flight list | [OSN]/[PRC] | [Parquet][FListpq], [CSV][FListcsv] |
| `EVENT` | Flight events | [OSN]/[PRC] | [Parquet][Eventspq], [CSV][Eventscsv] |
| `MEASUREMENT` | Measurements | [OSN]/[PRC] | [Parquet][Measpq], [CSV][Meascsv] |
| `AIRPORT` | Airports | [OurAirports][OA] | [CSV][OAapt] |
| `RUNWAY` | Runways | [OurAirports][OA] | [CSV][OArwy] |
[OSN]: <https://opensky-network.org/> "OpsnSky Network"
[PRC]: <https://ansperformance.eu/about/prc/> "Performance Review Commission"
[OA]: <https://ourairports.com/>
[OAapt]: <https://davidmegginson.github.io/ourairports-data/airports.csv>
[OArwy]: <https://davidmegginson.github.io/ourairports-data/runways.csv>
[ERm]: <https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model>
[FListpq]: <https://ansperformance.eu/data/download/OPDI/flight_table_v001.parquet>
[FListcsv]: <https://ansperformance.eu/data/download/OPDI/flight_table_v001.csv.gz>
[Eventspq]: <https://ansperformance.eu/data/download/OPDI/flight_events_v001.parquet>
[Eventscsv]: <https://ansperformance.eu/data/download/OPDI/flight_events_v001.csv.gz>
[Measpq]: <https://ansperformance.eu/data/download/OPDI/measurements_v001.parquet>
[Meascsv]: <https://ansperformance.eu/data/download/OPDI/measurements_v001.csv.gz>
[404]: <https://en.wikipedia.org/wiki/HTTP_404>
::: {.callout-note appearance="simple" collapse=true}
## Future releases (click to open/close)
Further data will be made available in upcoming iterations.
These updates will cover an extended time frame, additional flight event and
measurement types, and
additional flight events and measurements from different trajectory sources.
See the [Roadmap](roadmap.html) page for our plans.
:::
## Retrieving Data on Trajectories
Given the `FLIGHT` table, one can, when the data `source` is `OpenSky Network (OSN)` fetch the trajectory from the publicly available OpenSky Network history database if needed. Based on the available `icao24` or `callsign`, `first_seen` and `last_seen` one can perform a query to fetch the data.
The following tools are available for such queries: [https://opensky-network.org/data/data-tools](https://opensky-network.org/data/data-tools).
::: {.callout-note appearance="simple" collapse=true}
## Future releases (click to open/close)
\acr{OSN} kindly provided State Vector data at 5-sec granularity for a bounding box covering (wider) Europe.
![Bounding box for OPDI state vector coverage, SW=[-25.86653, 26.74617] NE=[49.65699, 70.25976] ](media/bbox-opdi.png "Title: a map of the bounding box for OPDI state vector coverage"){width="80%" fig-alt="Bounding box for OPDI state vector coverage."}
We intend to make these trajectories available for easy download, see [Roadmap](roadmap.html) page.
:::
# Relational Model {#sec-ER-model}
The diagram below describes the relationship between the different tables/entities
in the OPDI dataset.
```{mermaid}
%%| label: fig-er-diagram
%%| fig-cap: "Entity-Relationship diagram for Milestone and relevant concepts."
erDiagram
EVENT {
int id PK
int flight_id FK
string type
timestamp event_time
float longitude
float latitude
float altitude
string source
string version
string info
}
MEASUREMENT {
int id PK
int event_id FK
string type
float value
string version
}
FLIGHT {
int id PK
int ADEP FK
int ADES FK
string ICAO24
string FLT_ID
timestamp first_seen
timestamp last_seen
date DOF
str version
}
AIRPORT {
int id PK
string ident
string type
string name
double latitude_deg
double longitude_deg
int elevation_ft
string continent
string iso_country
string iso_region
string municipality
string scheduled_service
string gps_code
string iata_code
string local_code
string home_link
string wikipedia_link
string keywords
}
RUNWAY {
int id PK
int airport_ref FK
string airport_ident
int length_ft
int width_ft
string surface
boolean lighted
boolean closed
string le_ident
double le_latitude_deg
double le_longitude_deg
int le_elevation_ft
double le_heading_degt
int le_displaced_threshold_ft
string he_ident
double he_latitude_deg
double he_longitude_deg
int he_elevation_ft
double he_heading_degt
int he_displaced_threshold_ft
}
EVENT }|--|| FLIGHT : has
FLIGHT }o--|{ AIRPORT : has
AIRPORT }|--|{ RUNWAY : has
FLIGHT ||--|| TRAJECTORY : has
MEASUREMENT ||--|{ EVENT : has
```
### `FLIGHT`
The `FLIGHT` table models a flight list. It provides high level information about
a flight such as origin/destination airports, day of flight, callsign and
\acr{ICAO} 24-bit address.
This table is useful to reduce the scope of your analysis to a certain airport,
and/or country and/or time period.
### `AIRPORT`
The `AIRPORT` table contains details about an airport such as \acr{ICAO}/\acr{IATA}
identification code, the coordinated of the
[\acr{ARP}](https://en.wikipedia.org/wiki/Airport_reference_point),
ISO contry and region...
### `RUNWAY`
The `RUNWAY` table provides details about each available \acr{RWY} at an aerodrome, i.e.
their orientations, width, length, the threshold locations, ...
### `EVENT`
The `EVENT` table provide 4D milestones that characterize a flight.
Events allow to condense a full trajectory into a reduced set of milestones
that are still useful for a specific analysis.
For example a flight could be (simplistically) summarized by take-off,
top-of-climb, top-of-descent and landing.
For more on Events see the [Concepts](concepts.html) page.
### `MEASUREMENT`
The `MEASUREMENT` table provides measurements associated to events.
Some examples of measurements are cumulative distance flown or
cumulative CO2 emissions.
For more on Measurements see the [Concepts](concepts.html) page.