Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

OpenFlights – airport and airline data

openflights.org

Post reply on HN
Print view View original

1–10 of 62 posts

Re: OpenFlights – airport and airline data

#4

Post by einpoklum »

The US Bureau of Transport statistics provides historic flight schedule and actual flight performance data in CSV tables:

http://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=23...

But it's cumbersome to work with.

I am working (on and off) on a DBMS benchmark based on this data. As part of that endeavor, I have a script which:

* Automates downloading the CSVs.

* Creates an appropriate SQL database schema.

* Performs a bit of rudimentary cleaning (e.g. invalid character codes; optional)

* Loads the CSV files into the database.

So that, from the command-line, you could get the flight on-time performance data by merely typing in something like:

  /path/to/usdt-ontime-tools/scripts/setup-usdt-ontime-db -r  -db-name ontime --first-year 2019 --last-year 2020
it's available within this repository:

https://github.com/eyalroz/usdt-ontime-tools

the caveat is that, for now, the only DBMS supported directly is MonetDB: https://www.monetdb.org/ , a FOSS analytics-oriented columnar DBMS.

An adaptation of the script for other systems (MySQL/Maria, PostgreSQL) should be straightforward, since the commands are SQL'ish after all. If you're interested in that, open an issue or write me.

Re: OpenFlights – airport and airline data

#5

Post by wyozi »

Downloadable airport data on OpenFlights tends to be quite dated, missing notably e.g. the Berlin Brandenburg Airport. OurAirports (https://ourairports.com/) has a slightly different format but the data there is significantly more recent.

source: started with OpenFlights but had to switch to OurAirports for my project https://flightnotebook.com

Re: OpenFlights – airport and airline data

#7

Post by phsource »

It's ridiculous that the only original source of this data, the IATA [0], charges $700+ for this list, so kudos to OpenFlights.

I can't stress just how important (and how hard) it is to get a great source of data for airports -- I've now built 3 travel-related projects (the latest, Wanderlog [https://wanderlog.com], keeps people's flight reservations, so uses it for an autocomplete), and it's been a key building block for all of them.

The main datasets we use are:

- OpenFlights [1]: mentioned in this post, but this dataset was great since it had timezone too.

- OurAirports [2]: no timezone here, but the "type" and "scheduled_service" columns in this dataset are essential. "Type" lets you distinguish between small/medium/large airports, and "scheduled_service" lets you easily filter out airports without real flights (which you often might not care about).

- Random other GitHub Gist [3]: I have no idea where this data comes from, but it was surprisingly complete and has a few golden nuggets like "num_flights" and "runway_length" in addition to "timezone". The presence of a "woeid" suggests Yahoo-related origins, but it's hard to be sure.

- We now supplement this with airports from autocomplete APIs like Skyscanner's, because they're still the most up-to-date.

Long story short, it'd be AWESOME to have one complete, updated database with all this data in one place. This kind of data really should be public and a public service, but until then it's unfortunately up to the community.

[0] https://www.iata.org/en/publications/store/airline-coding-di...

[1] https://github.com/jpatokal/openflights/

[2] http://ourairports.com/data/

[3] https://gist.github.com/tdreyno/4278655

Re: OpenFlights – airport and airline data

#8

Post by gbrindisi »

does anyone know any current dataset I could query to check historical routes status?

Since the pandemic I've found plenty of airlines selling tickets and systematically cancel the flight a few days before. I was looking to scrape some data to avoid this kind of unreliable flights.

Re: OpenFlights – airport and airline data

#9

Post by chillydawg »

phsource wrote: post #7

It's ridiculous that the only original source of this data, the IATA [0], charges $700+ for this list, so kudos to OpenFlights. I can't stress just how important (and how hard) it is to get a great source of data for airports -- I've now built 3 travel-related projects (the latest, Wanderlog [ https://wanderlog.com ], keeps people's flight reservations, so uses it for an autocomplete), and it's been a key building bl…

openstreetmap might happen to have a list of airports with decent metadata, but it'll have zero info about actual flights.

Re: OpenFlights – airport and airline data

#10

Post by squeaky-clean »

phsource wrote: post #7

It's ridiculous that the only original source of this data, the IATA [0], charges $700+ for this list, so kudos to OpenFlights. I can't stress just how important (and how hard) it is to get a great source of data for airports -- I've now built 3 travel-related projects (the latest, Wanderlog [ https://wanderlog.com ], keeps people's flight reservations, so uses it for an autocomplete), and it's been a key building bl…

I've done something similar for my current job. I've used all these same data sources, even got access to the IATA stuff eventually. I also used GeoNames a lot, it's not specific to airports but it has decent airport data and I need a lot of the surrounding features as well.

Every source was definitely useful, but I think ultimately crawling Wikipedia was the most useful and highest quality set of data for me (after some significant data cleaning). The List of Airports By IATA Code [0] is almost as comprehensive as the official list from IATA, and you can follow the links to crawl info about the airport and city served. Getting info about what city the airport is considered to "serve" is so useful, as most airports are technically not in the city people consider them to be the major airports of, and some "serve" multiple cities.

Of course the difficult part there is that Wikipedia data isn't really clean or standardized. The page HTML isn't standard, even things that look very standardized like the sidebar will have 30 variations when you crawl all the airport pages. There is WikiData, but I found it still wasn't simple to get the data from there, and it also didn't include most of the page content which I wanted. [1]

Nowadays we have direct relationships with the airlines/GDS/so on, and also a department of people to add and manage the data ourselves, because even the direct source gives you pretty poor quality data. The project was way more fun when I was wrangling data from a dozen places around the web :) Now it's more of an enterprise CRUD webapp with some fancy localization and GIS tooling.

[0] https://en.wikipedia.org/wiki/List_of_airports_by_IATA_airpo...

[1] This was a while ago, so maybe WikiData has changed

Post reply on HN