Flights
To access the full sample data set on Amazon Web Services manually or programmatically, the S3 bucket URLs are provided here.
To get user name and password for manual access or access key and secret key for programmatic access, contact sales@ch-aviation.com.
| Column | Data Type | Data description | Key | Data Set | Join On |
|---|---|---|---|---|---|
| operator | varchar(255) | Operator name | |||
| operator_ch_code | varchar(5) | Operator ch-aviation code (assigned by ch-aviation). Operator ch-aviation codes can be 2, 3, or 5 characters. | Foreign | Operators | Operator ch-a Code |
| operator_iata | varchar(2) | Operator IATA two-character code | |||
| operator_icao | varchar(3) | Operator ICAO three-character code | |||
| callsign_operator | varchar(255) | Callsign operator name | |||
| callsign_ch_code | varchar(5) | Callsign operator ch-aviation code (assigned by ch-aviation). Operator ch-aviation codes can be 2, 3, or 5 characters. | Foreign | Operators | Operator ch-a Code |
| callsign_iata | varchar(2) | Callsign Operator IATA two-character code | |||
| callsign_icao | varchar(3) | Callsign Operator ICAO three-character code | |||
| wet_lease_customer | varchar(255) | Wet-lease customer name | |||
| wet_lease_cha_code | varchar(5) | Wet-lease customer ch-aviation code (assigned by ch-aviation). Same as Operator ch-aviation codes, can be 2, 3, or 5 characters. | |||
| wet_lease_customer_iata | varchar(2) | Wet-lease customer IATA two-character code | |||
| wet_lease_customer_icao | varchar(3) | Wet-lease customer ICAO three-character code | |||
| aircraft_id | int4 | Unique ch-aviation created aircraft ID, remains stable throughout an aircraft's life (assigned by ch-aviation), the aircraft_id and date_from_full fields are the combined primary key of the Aircraft Histories data set | Foreign (Combined) | Aircraft Histories | Aircraft ID |
| aircraft_variant | varchar(255) | Aircraft Variant designation (i.e. A321-200N) | Foreign | Aircraft Variants | variant_text |
| aircraft_role | varchar(32) | Aircraft's primary role (Passenger, Passenger-Freighter, Cargo, Combi, Quick Change (Passenger - Ambulance), Quick Change (Passenger - Cargo), Ambulance, Hospital, Maritime Patrol, Military, Oil Spill Response, Parachuting, Police, Tanker, Testbed, Water Bomber, VIP) | |||
| cn | varchar(16) | Construction/Line Number | |||
| msn | varchar(16) | Manufacturer Serial Number | |||
| registration | varchar(16) | Aircraft Registration, blank if unknown or no longer registered | |||
| hexcode | char(6) | ICAO 24-bit address/Mode-S hexcode | |||
| date_from_full | date | From date with operator, registration and variant, the aircraft_id and date_from_full fields are the combined primary key of the Aircraft Histories data set | Foreign (Combined) | Aircraft Histories | Date From Full |
| date | timestamp | Date of flight in UTC | |||
| flight_id | varchar(255) | Spire internal unique flight ID. Random UUIDv4 sequence | Primary | ||
| observation_start | timestamp | Movement observation start time, UTC | |||
| observation_end | timestamp | Movement observation end time, UTC | |||
| altitude_start | int4 | Altitude at observation start in feet | |||
| altitude_end | int4 | Altitude at observation end in feet | |||
| callsign | varchar(255) | Flight callsign | |||
| origin | varchar(255) | Origin airport display name (combination of City Name and Airport Name) | |||
| origin_cha_code | varchar(6) | Origin primary airport code (in order of availability: 1) IATA, 2) ICAO, 3) FAA, 4) Alternative code (if aircraft is inactive) | Foreign | Airports | Airport ch-a Code |
| origin_iata | varchar(3) | Origin airport IATA code | |||
| origin_icao | varchar(6) | Origin airport ICAO code | |||
| origin_continent | varchar(255) | Origin airport continent name | |||
| origin_subdivision | varchar(255) | Origin airport state name | |||
| origin_subdivision_cha_code | char(2) | Origin airport ISO 3166-2 Alpha-2 subdivision code (used for states) | Foreign | States | State ISO |
| origin_metro_group | varchar(255) | Origin airport Metro Group name | |||
| origin_metro_group_iata | char(3) | Origin airport Metro Group three character IATA code | Foreign | Metro Groups | Metro_Group |
| origin_country | varchar(255) | Origin airport country name | |||
| origin_country_iso | char(2) | Origin airport ISO 3166-1 Alpha-2 country code | Foreign | Countries | Country ISO |
| destination | varchar(255) | Destination airport display name (combination of City Name and Airport Name) | |||
| destination_cha_code | varchar(6) | Destination primary airport code (in order of availability: 1) IATA, 2) ICAO, 3) FAA, 4) Alternative code (if aircraft is inactive) | Foreign | Airports | Airport ch-a Code |
| destination_iata | varchar(3) | Destination airport IATA code | |||
| destination_icao | varchar(6) | Destination airport ICAO code | |||
| destination_continent | varchar(255) | Destination airport continent name | |||
| destination_subdivision | varchar(255) | Destination airport state name | |||
| destination_subdivision_cha_code | char(2) | Destination airport ISO 3166-2 Alpha-2 subdivision code (used for states) | Foreign | States | State ISO |
| destination_metro_group | varchar(255) | Destination airport Metro Group name | |||
| destination_metro_group_iata | char(3) | Destination airport Metro Group three character IATA code | Foreign | Metro Groups | Metro_Group |
| destination_country | varchar(255) | Destination airport country name | |||
| destination_country_iso | char(2) | Destination airport ISO 3166-1 Alpha-2 country code | Foreign | Countries | Country ISO |
| take_off | timestamp | Take off time, UTC | |||
| landing | timestamp | Landing time, UTC | |||
| take_off_latitude | varchar(255) | Take off latitude in degrees, between -90 and 90 (both inclusive) | |||
| take_off_longitude | varchar(255) | Take off longitude in degrees, between -180 (exclusive) and 180 (inclusive) | |||
| landing_latitude | varchar(255) | Landing latitude in degrees, between -90 and 90 (both inclusive) | |||
| landing_longitude | varchar(255) | Landing longitude in degrees, between -180 (exclusive) and 180 (inclusive) | |||
| start_latitude | varchar(255) | Observation start latitude in degrees, between -90 and 90 (both inclusive) | |||
| start_longitude | varchar(255) | Observation start longitude in degrees, between -180 (exclusive) and 180 (inclusive) | |||
| end_latitude | varchar(255) | Observation end latitude in degrees, between -90 and 90 (both inclusive) | |||
| end_longitude | varchar(255) | Observation end longitude in degrees, between -180 (exclusive) and 180 (inclusive) | |||
| duration | int4 | Flight duration formatted as hh:mm:ss, difference between landing time and take off time, NULL if one or both are not recorded | |||
| regional_partnership | varchar(255) | Regional partnership name under which the flight is operated | Foreign | Regional Subsidiaries | Rel Subsidiary |
| cargo_customer | varchar(255) | Name of cargo customer for whom the flights is operated | |||
| cargo_cust_cha_code | varchar(5) | Cargo customer ch-aviation code (assigned by ch-aviation). Can be 2, 3, 4, or 5 characters. | Foreign | Entities | Entity ch-a Code |
| flight_type | varchar(20) | Indicator of special flight types (ferry, technical, training) | |||
| notes | varchar(255) | Additional free text notes related to the flight |