Airport Codes
Overview
Sonra has published a data set for airport codes on the Snowflake marketplace.
Table of Contents
Airport codes are used to distinguish airports around the world to ensure that there is no confusion between countries and cities. Airport codes appear on passengers’ documents related to their reservations, flight tickets and they are also used by air traffic controllers and pilots.
This dataset contains the ISO Country and ISO Region codes. The Airports are classified as 7 major types such as (Large Airport, Medium Airport, Balloon port, SeaplaneBase, Small Airport, Heliport and some are closed).
The ICAO airport code or location indicator is a four-letter code designating aerodromes around the world. The codes are used by air traffic control and airline operations such as flight planning. ICAO codes are also used to identify other aviation facilities such as weather stations, international flight service stations or area control centres, whether or not they are located at airports. Flight information regions are also identified by a unique ICAO-code.
ICAO codes are separate and different from IATA codes, which are generally used for airline timetables, reservations, and baggage tags. For example, the IATA code for London’s Heathrow Airport is LHR and its ICAO code is EGLL. ICAO codes are commonly seen by passengers and the general public on flight-tracking services such as FlightAware.
In general IATA codes are usually derived from the name of the airport or the city it serves, while ICAO codes are distributed by region and country. Far more aerodromes (in the broad sense) have ICAO codes than IATA codes, which are sometimes assigned to railway stations as well. Selection of ICAO codes is partly delegated to authorities in each country, while IATA codes which have no geographic structure must be decided centrally by IATA.
Dataset Explained
The Airports dataset contains information about all the airports worldwide, including details such as exact geographical location, elevation, country, region, etc. The column ‘IDENT’ is used for identification for interoperability purposes and it is treated as a business key.
The below table summarises all the columns and their data types, along with description:
| Column Name | Data Type | Description |
|---|---|---|
| ID | NUMBER | It is the Internal integer identifier for the airport. This will stay persistent, even if the airport code changes. |
| IDENT | TEXT | The text identifier used to identify the codes of the airports. This is the ICAO code where applicable. Otherwise, it is a local airport code (if no conflict), or if nothing else is available, an internally-generated code starting with the ISO2 country code, followed by a dash and a four-digit number. |
| TYPE | TEXT | The type of the airport. The values are “closed_airport”, “heliport”, “large_airport”, “Balloon port”, “medium_airport”, “seaplane_base”, and “small_airport” |
| NAME | TEXT | The official airport name, including “Airport”, “Airstrip”, etc. |
| LATITUDE_DEG | DECIMAL | The airport latitude in decimal degrees (positive for north). |
| LONGITUDE_DEG | DECIMAL | The airport longitude in decimal degrees (positive for east). |
| ELEVATION_FT | NUMBER | The airport elevation MSL in feet (not metres). |
| ELEVATION_MT | NUMBER | The airport elevation MSL in metres. |
| CONTINENT | TEXT | The code for the continent where the airport is (primarily) located. The values such as “AF” (Africa), “AN” (Antarctica), “AS” (Asia), “EU” (Europe), “NA” (North America), “OC” (Oceania), or “SA” (South America). |
| ISO_COUNTRY | TEXT | The two-character ISO 3166:1-alpha2 code for the country where the airport is (primarily) located. A handful of unofficial, non-ISO codes are also in use, such as “XK” for Kosovo. |
| ISO_REGION | TEXT | An alphanumeric code for the high-level administrative subdivision of a country where the airport is primarily located (e.g. province, governorate), prefixed by the ISO2 country code and a hyphen. OurAirports uses ISO 3166:2 codes whenever possible, preferring higher administrative levels, but also includes some custom codes. |
| MUNICIPALITY | TEXT | The primary municipality that the airport serves (when available). Note that this is not necessarily the municipality where the airport is physically located. |
| SCHEDULED_SERVICE | TEXT | “yes” if the airport currently has scheduled airline service; “no” otherwise. |
| GPS_CODE | TEXT | The code that an aviation GPS database (such as Jeppesen’s or Garmin’s) would normally use for the airport. This will always be the ICAO code if one exists. This is not guaranteed to be globally unique. |
| IATA_CODE | TEXT | The three-letter IATA code for the airport (if it has one). |
| LOCAL_CODE | TEXT | The local country code for the airport, if different from the gps_code and iata_code fields (used mainly for US airports). |
| HOME_LINK | TEXT | URL of the airport’s official home page on the web, if one exists. |
| WIKIPEDIA_LINK | TEXT | URL of the airport’s page on Wikipedia, if one exists. |
| KEYWORDS | TEXT | Extra keywords/phrases to assist with search, comma-separated. May include former names for the airport, alternate codes, names in other languages, nearby tourist destinations, etc. |
Sample SQL Query on Airport codes data
Usage
Example 1
Number of all the large airports in the US.
|
1 2 3 4 5 6 7 8 |
SELECT ISO_COUNTRY, COUNT(*) AS NUMBER_OF_PORTS FROM AIRPORT_CODES WHERE TYPE = 'large_airport' AND ISO_COUNTRY = 'US' GROUP BY ISO_COUNTRY |
Example 2
Give the details of all the airports belonging to continent Europe.
|
1 2 3 4 |
SELECT * FROM AIRPORT_CODES WHERE CONTINENT = 'EU' |
Additional information
There are a few exceptions to the regional structure of the ICAO code made for political or administrative reasons. For example, the RAF Mount Pleasant air base in the Falkland Islands is assigned the ICAO code EGYP as though it were in the United Kingdom, but nearby civilian Port Stanley Airport is assigned SFAL, consistent with South America.
ICAO airport codes do not begin with I or J or X or Q. Codes beginning with I (Ixx and Ixxx) are often used for navigational aids such as radio beacons, while Q is reserved for international radiocommunications and non-geographical special uses. In Russia and the CIS, Latin letter X is used to designate government, military and experimental aviation airfields in internal airfield codes similar in structure and purpose to ICAO codes but not used internationally. ZZZZ is a pseudo-code, used in flight plans for aerodromes with no ICAO code assigned.
