World airport codes with geo name data
At work, we needed a database of airports in the world, and they had to be locatable by dutch city- and country names. A list of airports can be found on www.world-airport-codes.com. We (Bigsmoke and I) wrote a spider to download and parse all the data of the World Airport Codes website and store it in a CSV file. We then downloaded a data file of all geographical locations in the world from www.geonames.org and a related data file with localized names in a lot of languages. We wrote another script to do matching between the country- and city names of the airport codes and the Geonames data and store it in a database.
The result is a database with airports from World Airport Codes, supplemented by geo_name_ids to match the cities and countries to specific geographical locations which can be found in the Geonames data. Additionally, the dutch names for cities and countries is also present in the dump (very non-relational, I know, but it served our purpose well...).
If you need translations for the city- and country names in another language, all you have to do is locate records with a geo_name_id which matches rows for your particular language in the "Alternate Names" table provided by Geonames.
- Airport codes with geo_name_ids. The zip file contains a readme which describes the format of the CSV dump. It was updated on april 14th, 2008.
- The collection of scripts we used to compile the csv file. It also contains csv files with (recent) airport codes. The readme file should tell you everything you need to know...
Future development and other resources
Because we decided on a different approach how to utilize the airports, this version of the airport scraper and the version we use at work, have grown apart too much. Because of this, it's too cumbersome for me to add new features to the code, because the database infrastructure no longer exists. However, fixing small bugs should still be possible.
If this airport scraper and the provided csv file is not complete enough for you, you could take a look at Partow's Global Airport Database. It's provided as a downloadable database, so no need to hassle with scraper scripts. Unfortunately, the information seems to be a lot less complete than the world-airport-codes database (there are a lot of airports with zero coordinates), but perhaps that will change in the future. The coordinates provided by world-airport-codes are often wrong as well, so you're trapped between a rock and a hardplace anyway...