ASA supplemental data: over 100 airports not listed in airport-locations.csv ?
Robert Allison
But I'm finding that many of the airports (over 100 of them) aren't in the airport-locations.csv file :-\
Is it possible that some airports were inadvertently left out of the csv file?
(or, were they not available from the original database source of the data?)
Here are the details...
The flight data has 346 unique Origin airports, but 157 of these are not in the airport-locations.csv file.
Similarly there are 351 unique Destination airports, but 159 of them are not in the airport-locations.csv file.
Here are my SAS sql queries that determined this:
create table foo1 as select unique origin from asadata.alldata;
NOTE: Table WORK.FOO1 created, with 346 rows and 1 columns.
create table foo2 as select * from foo1 where foo1.origin not in (select unique iata from asadata.locations);
NOTE: Table WORK.FOO2 created, with 157 rows and 1 columns.
create table foo3 as select unique dest from asadata.alldata;
NOTE: Table WORK.FOO3 created, with 351 rows and 1 columns.
create table foo4 as select * from foo3 where foo3.dest not in (select unique iata from asadata.locations);
NOTE: Table WORK.FOO4 created, with 159 rows and 1 columns.
-----
I manually verified a few of these, to make sure they indeed aren't in the csv file...
Here's what one looks like that *is* in the csv file, for example:
$ grep RDU airport-locations.csv
"RDU","raleigh-durham",35.8775,-78.7872,"0435"
And here's where I try to look up several that apparently aren't in the csv file (they get no matches):
$ grep ABE airport-locations.csv
$ grep BGM airport-locations.csv
$ grep FAY airport-locations.csv
$ grep TEX airport-locations.csv
$ grep XNA airport-locations.csv
---
And here's the complete list of all the airports that aren't in the csv file (ie, the complete output of the 2 queries above)...
Airports (origin) in data that are not in airport-locations.csv
Obs Origin
1 ABE
2 ABY
3 ACV
4 ALO
5 ANI
6 APF
7 ASE
8 ATW
9 AVL
10 AVP
11 AZO
12 BFF
13 BGM
14 BIL
15 BIS
16 BJI
17 BMI
18 BQK
19 BQN
20 BTM
21 BZN
22 CAK
23 CCR
24 CEC
25 CHO
26 CID
27 CKB
28 CLD
29 CMI
30 CMX
31 COD
32 CRW
33 CSG
34 CWA
35 DAB
36 DBQ
37 EAU
38 EGE
39 EKO
40 ELM
41 ERI
42 EUG
43 EVV
44 FAR
45 FAY
46 FCA
47 FLG
48 FNT
49 FSD
50 FWA
51 GCC
52 GCN
53 GJT
54 GLH
55 GPT
56 GSO
57 GSP
58 GST
59 GTR
60 GUC
61 GUM
62 HDN
63 HHH
64 HNL
65 HSV
66 HTS
67 HVN
68 IDA
69 ILE
70 ISO
71 ITH
72 ITO
73 IYK
74 JAC
75 KOA
76 KSM
77 LAW
78 LEX
79 LIH
80 LMT
81 LNY
82 LSE
83 LWB
84 LWS
85 LYH
86 MAZ
87 MBS
88 MEI
89 MFR
90 MGM
91 MHT
92 MKC
93 MKG
94 MKK
95 MLI
96 MRY
97 MSO
98 MTH
99 MTJ
100 OAJ
101 OGD
102 OGG
103 ORH
104 OXR
105 PFN
106 PIA
107 PIH
108 PIR
109 PLN
110 PSC
111 PSE
112 PSG
113 PVU
114 RAP
115 RDD
116 RDM
117 RFD
118 RHI
119 RKS
120 ROA
121 ROP
122 ROR
123 RST
124 SBA
125 SBN
126 SBP
127 SCE
128 SDF
129 SGF
130 SGU
131 SJU
132 SLE
133 SMX
134 SOP
135 SPI
136 SPN
137 SRQ
138 STT
139 STX
140 SUN
141 TEX
142 TOL
143 TRI
144 TUP
145 TVC
146 TVL
147 TWF
148 UCA
149 VCT
150 VIS
151 VLD
152 VPS
153 WRG
154 WYS
155 XNA
156 YAP
157 YKM
Airports (dest) in data that are not in airport-locations.csv
Obs Dest
1 ABE
2 ABY
3 ACV
4 ALO
5 ANI
6 APF
7 ASE
8 ATW
9 AVL
10 AVP
11 AZO
12 BFF
13 BGM
14 BIL
15 BIS
16 BJI
17 BMI
18 BQK
19 BQN
20 BTM
21 BZN
22 CAK
23 CCR
24 CEC
25 CHO
26 CID
27 CKB
28 CLD
29 CMI
30 CMX
31 COD
32 CRW
33 CSG
34 CWA
35 DAB
36 DBQ
37 EAU
38 EGE
39 EKO
40 ELM
41 ERI
42 EUG
43 EVV
44 FAR
45 FAY
46 FCA
47 FLG
48 FNT
49 FSD
50 FWA
51 GCC
52 GCN
53 GJT
54 GLH
55 GPT
56 GSO
57 GSP
58 GST
59 GTR
60 GUC
61 GUM
62 HDN
63 HHH
64 HNL
65 HSV
66 HTS
67 HVN
68 IDA
69 ILE
70 ISO
71 ITH
72 ITO
73 IYK
74 JAC
75 KOA
76 KSM
77 LAR
78 LAW
79 LBF
80 LEX
81 LIH
82 LMT
83 LNY
84 LSE
85 LWB
86 LWS
87 LYH
88 MAZ
89 MBS
90 MEI
91 MFR
92 MGM
93 MHT
94 MKC
95 MKG
96 MKK
97 MLI
98 MRY
99 MSO
100 MTH
101 MTJ
102 OAJ
103 OGD
104 OGG
105 ORH
106 OXR
107 PFN
108 PIA
109 PIH
110 PIR
111 PLN
112 PSC
113 PSE
114 PSG
115 PVU
116 RAP
117 RDD
118 RDM
119 RFD
120 RHI
121 RKS
122 ROA
123 ROP
124 ROR
125 RST
126 SBA
127 SBN
128 SBP
129 SCE
130 SDF
131 SGF
132 SGU
133 SJU
134 SLE
135 SMX
136 SOP
137 SPI
138 SPN
139 SRQ
140 STT
141 STX
142 SUN
143 TEX
144 TOL
145 TRI
146 TUP
147 TVC
148 TVL
149 TWF
150 UCA
151 VCT
152 VIS
153 VLD
154 VPS
155 WRG
156 WYS
157 XNA
158 YAP
159 YKM
-------------------------------------
Robert Allison, PhD
Robert....@sas.com
hadley wickham
Thanks for bringing this up. I'll try and look into this in the next
couple of days, but in case you want to do your own investigation I've
attached the original data (from
http://www.partow.net/miscellaneous/airportdatabase/index.html, found
googling for airport lat long or something similar) and the R script I
used to clean it up. It's possible the original data is lacking (or
incorrect), so if you find a better source please let me know.
Otherwise there may be a bug in my R script - you should be able to
follow the logic even if you're not familiar with R.
Hadley
hadley wickham
> couple of days, but in case you want to do your own investigation I've
> attached the original data (from
> http://www.partow.net/miscellaneous/airportdatabase/index.html, found
> googling for airport lat long or something similar) and the R script I
> used to clean it up. It's possible the original data is lacking (or
> incorrect), so if you find a better source please let me know.
> Otherwise there may be a bug in my R script - you should be able to
> follow the logic even if you're not familiar with R.
It looks like http://www.faa.gov/airports_airtraffic/airports/airport_safety/airportdata_5010/
might be a better source (it has 20,000 airports in the US alone!).
I'll try and update the data on the website in the next couple of
days.
Hadley
Robert Allison
Here's an excel spreadsheet on an faa.gov website that's got about 3,000 airports listed.
I found it using a google search, so I'm not sure of the exact "context" (the year, the purpose, etc, of the list), but it seems to have just about what I'm looking for. It also has the state abbreviation, which might be useful to add to airport-locations.csv - the state abbreviation is useful because sometimes I might not recognize the city name, and the same city names could be repeated in multiple states.
http://www.faa.gov/airports_airtraffic/airports/planning_capacity/npias/reports/states/media/NPIAS_airports.xls
On the bright side, at least it's easier to replace the small airport-locations.csv file, than the huge departure/arrival files! :)
hadley wickham
>
> Wow - 20,000 airports in the US?!?!? That's way more than I would have guessed! (that list must include some very small ones).
>
> Here's an excel spreadsheet on an faa.gov website that's got about 3,000 airports listed.
> I found it using a google search, so I'm not sure of the exact "context" (the year, the purpose, etc, of the list), but it seems to have just about what I'm looking for. It also has the state abbreviation, which might be useful to add to airport-locations.csv - the state abbreviation is useful because sometimes I might not recognize the city name, and the same city names could be repeated in multiple states.
>
> http://www.faa.gov/airports_airtraffic/airports/planning_capacity/npias/reports/states/media/NPIAS_airports.xls
>
That looks great, thanks! Does it match all of the locations in the
flight table?
Hadley
Ryan
like you have already found the solution, but for what it's worth:
One way to get these is just to paste them into this website:
http://www.wotton.org/map/batchgeo.html
It gives kml output that you can parse for lat/long.
I pasted in your 159 airport codes, each one followed by the word
"airport", and got lat/long for all but 4 of them - mostly island
locations, which I looked up manually (colon-separated list below).
It actually looks like some of these (such as YAP, SPN) don't show up
in either of the two sources that have been mentioned, so maybe this
is worth something! :)
Ryan
ABE:-75.441982:40.652131 :Lehigh Valley International Airport, USA
ABY:-84.18884:31.53264 :Southwest Georgia Regional Airport, USA
ACV:-124.106678:40.971209 :Arcata Airport, USA
ALO:-92.40035:42.557078 :Waterloo Municipal Airport, USA
ANI:159.533333:61.583333 :Aniak Airport, USA
APF:-81.774651:26.14869 :Naples Muni Airport, United States
ASE:-106.865053:39.22038 :Aspen-Pitkin County Airport, USA
ATW:-88.511605:44.260087 :Outagamie County Regional Airport, USA
AVL:-82.541807:35.436203 :Asheville Regional Airport, USA
AVP:-75.728533:41.335958 :Wilkes-Barre-Scranton International
Airport, United States
AZO:-85.552049:42.234879 :Kalamazoo-Battle Creek International
Airport, USA
BFF:-103.593516:41.870014 :Western Nebraska Regional-William B Helig
Field, United States
BGM:-75.980955:42.208326 :Binghamton Regional Edwin A Link Field
Airport, United States
BIL:-108.537291:45.803775 :Billings Logan International Airport, USA
BIS:-100.756841:46.775969 :Bismarck Municipal Airport, USA
BJI:-94.933667:47.506444 :Bemidji-Beltrami County Airport, United
States
BMI:-88.91589:40.481848 :Central Illinois Regional Airport, United
States
BQK:-81.465682:31.255713 :Glynco Jetport, USA
BQN:-67.135546:18.495552 :Aeropuerto Rafael Hernandez, Puerto Rico
BTM:-112.49746:45.954799 :Bert Mooney Airport, USA
BZN:-111.160018:45.777666 :Gallatin Field, United States
CAK:-81.438422:40.914851 :Akron-Canton Regional Airport, USA
CCR:-122.054706:37.986468 :Buchanan Field Airport, United States
CEC:-124.236157:41.778625 :Jack Mcnamara Field, United States
CHO:-78.45286:38.138639 :Charlottesville-Albemarle Airport, USA
CID:-91.707267:41.884664 :Eastern Iowa Airport, United States
CKB:-80.22809:39.296634 :Benedum Airport, United States
CLD:-117.278727:33.127231 :MC Clellan-Palomar Airport, United States
CMI:-88.26482:40.035504 :University of Illinois-Willard Airport,
USA
CMX:-88.489059:47.16842 :Houghton County Memorial Airport, USA
COD:-109.023795:44.520195 :Yellowstone Regional Airport, USA
CRW:-81.596244:38.370808 :Yeager Airport, USA
CSG:-84.940141:32.518044 :Columbus Metropolitan Airport, USA
CWA:-89.67025:44.776805 :Central Wisconsin Airport, USA
DAB:-81.059626:29.182663 :Daytona Beach International Airport, USA
DBQ:-90.703893:42.396268 :Dubuque Regional Airport, USA
EAU:-91.485075:44.86529 :Chippewa Valley Regional Airport, USA
EGE:-106.917691:39.642553 :Eagle County Regional Airport, USA
EKO:-115.786951:40.827965 :Elko Regional Airport, USA
ELM:-76.89144:42.159909 :Elmira-Corning Regional Airport, USA
ERI:-80.180533:42.08292 :Erie International Airport, USA
EUG:-123.214291:44.11877 :Mahlon Sweet Field Airport, United States
EVV:-87.529156:38.04454 :Evansville Regional Airport, USA
FAR:-96.825236:46.920276 :Hector International Airport, USA
FAY:-78.879939:34.991451 :Fayetteville Regional-Grannis Field,
United States
FCA:-114.252805:48.307847 :Glacier Park International Airport, USA
FLG:-111.671161:35.136642 :Flagstaff Pulliam Airport, USA
FNT:-83.743449:42.9655 :Bishop International Airport, USA
FSD:-96.732656:43.581181 :Joe Foss Field, USA
FWA:-85.188448:40.986396 :Fort Wayne International Airport, USA
GCC:-105.539151:44.348361 :Gillette-Campbell County Airport, USA
GCN:-112.136307:35.96066 :Grand Canyon National Park Airport, USA
GJT:-108.526406:39.123974 :Grand Junction Regional Airport, USA
GLH:-90.989778:33.480927 :Mid-Delta Regional Airport, United States
GPT:-89.072273:30.410859 :Gulfport-Biloxi Regional Airport, United
States
GSO:-79.937882:36.105765 :Piedmont Triad International Airport, USA
GSP:-82.217731:34.891564 :Greenville Spartanburg International
Airport, USA
GST:-135.707372:58.424364 :Gustavus Airport, United States
GTR:-88.590398:33.452018 :Golden Triangle Regional Airport, USA
GUC:-106.935017:38.53615 :Gunnison-Crested Butte Airport, United
States
GUM:-91.738118:35.211692 :Searcy Municipal, United States
HDN:-107.217563:40.481866 :Yampa Valley Airport, USA
HHH:-80.697629:32.224384 :Hilton Head, United States
HNL:-157.919777:21.331717 :Honolulu International Airport, USA
HSV:-86.77652:34.643008 :Huntsville International Airport, USA
HTS:-82.55806:38.366649 :Tri-State-Milton J Ferguson Field Airport,
United States
HVN:-72.884219:41.264339 :Tweed-New Haven Airport, USA
IDA:-112.067648:43.515334 :Idaho Falls Regional Airport, USA
ILE:-97.6865:31.085829 :Killeen Municipal Airport, United States
ISO:-77.616049:35.327083 :Kinston Jetport Stallings, United States
ITH:-76.458474:42.491005 :Tompkins County Airport, United States
ITO:-155.039419:19.714466 :Hilo International Airport, USA
IYK:-117.827388:35.652431 :Inyokern County Airport, United States
JAC:-110.736222:43.603556 :Jackson Hole Airport, USA
KOA:-156.041368:19.736363 :Kona International at Keahole Airport,
United States
KSM:163.183333:62.050651 :Saint Marys Airport, USA
LAR:-105.675066:41.311867 :Laramie Regional Airport, USA
LAW:-98.416631:34.567719 :Lawton-Fort Sill Regional Airport, USA
LBF:-100.688557:41.125786 :Lee Bird Field, United States
LEX:-84.60539:38.036969 :Blue Grass Airport, USA
LIH:-159.338951:21.975987 :Lihue Airport, USA
LMT:-121.733245:42.156143 :Klamath Falls International Airport,
United States
LNY:-156.949528:20.789416 :Lanai, United States
LSE:-91.262722:43.875426 :La Crosse Municipal Airport, USA
LWB:-80.399472:37.858278 :Greenbrier Valley Airport, USA
LWS:-117.011069:46.377383 :Lewiston-Nez Perce County Airport, USA
LYH:-79.200431:37.326688 :Lynchburg Regional Airport, USA
MAZ:-67.14917:18.25346 :Aeropuerto Eugenio María de Hostos, Puerto
Rico
MBS:-84.086248:43.531932 :MBS International Airport, USA
MEI:-88.751206:32.333106 :Key Field, USA
MFR:-122.873622:42.369446 :Rogue Valley International-Medford
Airport, USA
MGM:-86.390628:32.302976 :Montgomery Regional Airport, USA
MHT:-71.437143:42.93469 :Manchester-Boston Regional Airport, USA
MKC:-94.590893:39.124334 :Kansas City Downtown Airport, United
States
MKG:-86.23822:43.169489 :Muskegon County Airport, USA
MKK:-157.093009:21.155366 :Molokai Airport, USA
MLI:-90.507539:41.448529 :Quad City International Airport, USA
MRY:-121.850861:36.588085 :Monterey Peninsula Airport, USA
MSO:-114.083492:46.918338 :Missoula International Airport, USA
MTH:-81.05138:24.726139 :Marathon Airport, United States
MTJ:-107.890438:38.493067 :Montrose Regional Airport, USA
OAJ:-77.612164:34.829167 :Albert J Ellis Airport, USA
OGD:-112.011892:41.194262 :Ogden Hinckley, United States
OGG:-156.437876:20.893046 :Kahului Airport, USA
ORH:-71.871463:42.27059 :Worcester Regional Airport, USA
OXR:-119.206732:34.19853 :Oxnard Airport, United States
PFN:-85.683027:30.208761 :Panama City-Bay County International
Airport, United States
PIA:-89.690972:40.664735 :Greater Peoria Regional Airport, USA
PIH:-112.59586:42.911299 :Pocatello Regional Airport, USA
PIR:-100.285976:44.382668 :Pierre Regional Airport, USA
PLN:-84.796719:45.570929 :Pellston Regional Airport of Emmet County
Airport, United States
PSC:-119.114748:46.259477 :Tri-Cities Airport, USA
PSE:-66.56638:18.01159 :Aeropuerto Mercedita, Puerto Rico
PSG:-132.945294:56.80165 :Petersburg James A Johnson Airport, United
States
PVU:-111.719648:40.216922 :Provo Municipal, United States
RAP:-103.057384:44.045298 :Rapid City Regional Airport, USA
RDD:-122.293392:40.508978 :Redding Municipal Airport, USA
RDM:-121.148758:44.254042 :Roberts Field, United States
RFD:-89.097639:42.197394 :Chicago-Rockford International Airport,
USA
RHI:-89.467453:45.631181 :Rhinelander-Oneida County Airport, USA
RKS:-109.072361:41.595706 :Rock Springs-Sweetwater County Airport,
USA
ROA:-79.974268:37.319968 :Roanoke Regional Airport, USA
ROP:101.378334:14.078333 :Prachinburi, Thailand
ROR:134.544167:7.367222 :Babelthoup/Koror, Palau
RST:-92.491322:43.911197 :Rochester International Airport, USA
SBA:-119.839823:34.427203 :Santa Barbara Municipal Airport, United
States
SBN:-86.313626:41.701005 :South Bend Regional Airport, USA
SBP:-120.641588:35.237901 :San Luis Obispo County Regional Airport,
USA
SCE:-77.846302:40.851206 :University Park, United States
SDF:-85.7412:38.184978 :Louisville International-Standiford
Airport, United States
SGF:-93.384208:37.241783 :Springfield-Branson Regional Airport,
United States
SGU:-113.592044:37.097084 :Saint George Municipal Airport, USA
SJU:-66.00446:18.43702 :Luis Muñoz Marin Int'L-Salidas, Puerto
Rico
SLE:-123.0002:44.908642 :McNary Field, United States
SMX:-120.450534:34.898032 :Santa Maria Public Airport, United States
SOP:-79.391037:35.237887 :Moore County Airport, USA
SPI:-89.677381:39.842873 :Abraham Lincoln Capital Airport, USA
SPN:145.621384:14.996111 :Tinian International Airport, N Mariana
Islands
SRQ:-82.553221:27.387926 :Sarasota-Bradenton International Airport,
USA
STT:-64.972009:18.334664 :Cyril E. King International Airport,
Virgin Islands
STX:-64.797543:17.699602 :Henry E. Rohlsen International Airport,
Virgin Islands
SUN:-114.296589:43.504839 :Friedman Memorial Airport, USA
TEX:-107.908452:37.953745 :Telluride Regional Airport, USA
TOL:-83.806075:41.592223 :Toledo Express Airport, USA
TRI:-82.404216:36.478511 :Tri-Cities Regional TN-VA Airport, United
States
TUP:-88.757967:34.265621 :Tupelo Regional Airport, United States
TVC:-85.584965:44.744235 :Cherry Capital Airport, USA
TVL:-119.998361:38.892903 :Lake Tahoe Airport, USA
TWF:-114.484843:42.483655 :Joslin Field, United States
UCA:-75.380473:43.147555 :Oneida County Airport, United States
VCT:-96.920177:28.845317 :Victoria Regional Airport, USA
VIS:-119.396822:36.326085 :Visalia Municipal Airport, USA
VLD:-83.27866:30.789107 :Valdosta Regional Airport, USA
VPS:-86.549593:30.495847 :Okaloosa Regional Airport, United States
WRG:-132.36983:56.484319 :Wrangell Airport, United States
WYS:-111.114756:44.688145 :Yellowstone, United States
XNA:-94.305094:36.280856 :Northwest Arkansas Regional Airport,
United States
YAP:138.100000:9.516700 :Yap International, Federated States of
Micronesia
YKM:-120.53977:46.569673 :Yakima Air Terminal, USA
On Aug 27, 11:43 am, "hadley wickham" <h.wick...@gmail.com> wrote:
>
> > Wow - 20,000 airports in the US?!?!? That's way more than I would have guessed! (that list must include some very small ones).
>
> > Here's an excel spreadsheet on an faa.gov website that's got about 3,000 airports listed.
> > I found it using a google search, so I'm not sure of the exact "context" (the year, the purpose, etc, of the list), but it seems to have just about what I'm looking for. It also has the state abbreviation, which might be useful to add to airport-locations.csv - the state abbreviation is useful because sometimes I might not recognize the city name, and the same city names could be repeated in multiple states.
>
Robert Allison
I re-ran my query using the locations in the xls spreadsheet, and got matches for all but a handful... http://www.faa.gov/airports_airtraffic/airports/planning_capacity/npias/reports/states/media/NPIAS_airports.xls
Airports (origin) in data that are not in NPIAS_airports.xls:
Obs Origin
1 CLD
2 HHH
3 MIB
4 MQT
5 RDR
6 ROP
7 ROR
8 SCE
9 SPN
10 YAP
Airports (dest) in data that are not in NPIAS_airports.xls:
Obs Dest
1 CBM
2 CLD
3 HHH
4 MIB
5 MQT
6 RCA
7 RDR
8 ROP
9 ROR
10 SCE
11 SKA
12 SPN
13 YAP
-----Original Message-----
From: data-e...@googlegroups.com [mailto:data-e...@googlegroups.com] On Behalf Of hadley wickham
Sent: Wednesday, August 27, 2008 11:44 AM
To: data-e...@googlegroups.com
Subject: Re: ASA supplemental data: over 100 airports not listed in airport-locations.csv ?
hadley wickham
This is interesting! It looks like Ryan got most of those, but there
are still a few missing. It's interesting what is considered a US
commercial airport!
Hadley