Introduction
In the previous post I described a path search problem and provided results of experiments with a randomly generated data.
In this post I will describe experiments with the real data.
Real data source
The data I made experiment with is from the „Aviation Edge“ company’s API. I bought a developer license to access API for 7USD per month. I took all European airports and extracted scheduled data for period 2026-04-01 to 2026-05-01. The amount of airports, that „Aviation Edge“ provides and has at least one flight for a period 2026-04-01 to 2026-05-01 is 525. Though the total amount of European airports is 10287, only 525 airports has flights information at the „Aviation Edge“.
The total number of flights in the given period (2026-04-01 to 2026-05-01) is 1056849 ( 1.05 million ).
Data specifics.
The real data is different from the generated ones. The main difference – more flights for a time period and more dense coverage between airports. There are several very large hubs in Europe that access almost every other airport in Europe.
Largest hubs:
- United Kingdom,Heathrow, LHR – 73518 flights for April
- Netherlands,Schiphol, AMS – 63675 flights for April
- Spain,Barajas, MAD – 62419 flights for April
- Germany,Frankfurt International Airport, FRA – 60268 flights for April.
The dense coverage makes a problem a bit different: now you must not only to find a path, but you must select the best path from the list. Sometimes list may be hundreds of thousands wide and this makes the selecting of the best variant the hardest problem.
To see the density of the airports coverage by flights I have calculated a shortest path length (ignoring arrival and departure times ) to each other airport from the VNO ( Vilnius Airport ).
Amounts are following:
Airports accessible from VNO by
- 1 flight: 42
- 2 flights: 278
- 3 flights: 104
- 4 flights: 76
- 5 flights: 23
- 6 flights: 1
I have removed the ‘order by‘ sql part to make the sql work faster.
Some of experiments results:
The source airport is always VNO, and the destination airport is different. The destination airport is selected using analysis made previously – the one with the least distance path must be equal to the searched distance.
Distance 5
| Destination | Found paths | Connection time (h) | Search time (s) |
|---|---|---|---|
| EGS | 0 | 64 | 0.1 |
| GDX | 0 | 64 | 2.5 |
| HTA | 0 | 64 | 1.6 |
| IFJ | 100 | 64 | 0.2 |
| IFJ | 0 | 32 | 0.2 |
| NNM | 14 | 32 | 0.5 |
| NNM | 0 | 64 | 0.2 |
| TLK | 6 | 64 | 3 |
| USK | 40 | 64 | 0.2 |
| UUS | 0 | 64 | 1.4 |
Distance 4
| Destination | Found paths | Connection time (h) | Search time (s) |
|---|---|---|---|
| ARH | 26 | 64 | 0.5 |
| BAX | 0 | 64 | 2.2 |
| BJF | 0 | 64 | 0.1 |
| CEE | 13 | 64 | 1.2 |
| CEK | 6 | 64 | 2 |
| CSY | 0 | 64 | 0.5 |
Distance 3
| Destination | Found paths | Connection time (h) | Search time (s) |
|---|---|---|---|
| AER | 17 | 64 | 2 |
| AEY | 24 | 8 | 0.1 |
| ANX | 38 | 32 | 0.1 |
| BIA | 8 | 8 | 0.3 |
| BIA | 0 | 32 or 64 | 0.3 |
| BMA | 90 | 8 | 0.2 |
| BMA | 22 | 64 | 0.02 |
As you see sometimes there are less paths with a higher connection time that are paths with the shorter connection time, which is illogical, because lesser connection time paths are subset of a higher connection time paths.
This happens due a bug: when searching with a longer connection time, actually we get more results, but the query is limited to first 100 records set, in which paths may be incorrect; the clustering search path may contain transfers, that previous transfer arrival time is later than the next transfer departure time. After finding set of paths with the clustered search query, in the next step the paths are filtered by a condition, that arrival and departure times of the connected transfers in the path do not overlap. So if the first 100 records are incorrect, they are filtered out, though the limit statement may hide other paths that would be correct.
Proposition for a real usage.
To search best path I would recommend to make several simultaneous requests and then get the best results of the first few that gives these results. Simultaneous requests would have different connection times, and different searched path lengths like these:
| Path length | Connection time |
|---|---|
| 2 | 4 |
| 2 | 8 |
| 2 | 32 |
| 2 | 64 |
| 3 | 4 |
| 3 | 8 |
| 3 | 32 |
| 3 | 64 |
| 4 | 4 |
| 4 | 8 |
| 4 | 32 |
| 4 | 64 |
| 5 | 4 |
| 5 | 8 |
| 5 | 32 |
| 5 | 64 |
16 simultaneous requests total.
For searching the cheepest and the shortest travel path I would try next search with the narrower arrival time range; also need to experiment with the limit 1000 instead of 100 and then select cheepest path, using application code instead of sql.
Summary
- Searching paths in the real data is slower due more dense travel net comparing to the randomly generated data.
- The main issue in the real data set is to find BEST paths instead any, and it is a slow algorithm, because there are hundreds of thousands of choices.
- Sometimes with a larger connection time, less found path are displayed, because they are filtered out using comparision of arrival and departure of the two connecting transfers in the path, and the real correct data is hidden with the ‘limit’ sql statement.
- Future searches proposed, using simultaneous searches, narrower arrival times and larger limit amounts.
Source code: https://github.com/kukulis/persedimai
2025-12-31