FAA Airport Update

From VPOIDS Documentation

Airports update

In order to keep the AFIDS airports database up to date, it will be updated every 56 days as the FAA updates its airport database. This document describes the update process.

The AFIDS airports table contains a reference to the faaSiteNumber, which is the FAA's unique airport ID. This ID is used to match the records. Data is over-written in the AFIDS airports record if the FAA data is different.

The AFIDS airports data only includes airports, and only airports that are either public or private (no military). The FAA database contains all facilities, so other facilities need to be filtered out. Steps in the 56 day process ¶

1. Download three files from the faa.gov site:

   * nfdcFacilities.xls. This is the complete database of airports with all the updates reflected.
   * nfdcRunways.xls. Also the complete database

The files need to end up in this directory in order to be processed: C:\downloads\faa_data\NfdcFacilities.xls. If necessary, delete any existing data files (or over-write).

2. Delete the contents of the table faaNfdcFacilities and faaNfdcRunways in the afids_development database.

  • truncate table faa_nfdc_facilities;
  • truncate table faa_nfdc_runways;

3. Import the downloaded files into a local database

To to this, we run two scripts on the development server:

   * http://afids/airport/importFAAData
   * http://afids/airport/importFAARunwaysData

Option #1: Replacing the entire aircraft table (not recommended)

4. If you plan to delete and copy the entire table over, run these SQL scripts to update the airports table in the afids_development database. Otherwise, skip to step 7.

/* Steps */
/* Update any existing records where the idents don't match */
UPDATE airports
Set ident = (SELECT locationID FROM faaNfdcFacilities WHERE airports.faaSiteNumber = faaNfdcFacilities.SiteNumber)
WHERE airportID IN
(SELECT airportID, a.ident, f.locationID, f.facilityName
FROM faaNfdcFacilities f JOIN airports a ON (f.SiteNumber = a.faaSiteNumber)
WHERE a.ident <> f.locationID)

/* Now, run the scripts you generated above, plus this command against the development database */
/* You do not need to save this */
/* Add any records where the SiteNumber does not exist in the airports table */
INSERT INTO airports
SELECT locationID as ident,
facilityName as name,
f.city,
f.state,
ARPLatitudeS as latitude,
ARPLongitudeS as longitude,
runwayLength = (SELECT TOP 1 RunwayLength FROM faaNfdcRunways WHERE faaNfdcRunways.SiteNumber = f.SiteNumber),
Null as wingID,
Null as gmtOffset,
Null as dstOffset,
Null as zipcode,
0 as closed,
SiteNumber as faaSiteNumber,
'ATCT' = CASE
	WHEN f.ATCT = 'Y' THEN 1
	ELSE 0
END,
'private' = CASE
	WHEN apUse = 'PR' THEN 1
	ELSE 0
END,
'nonCommercialLandingFee' = CASE
	WHEN f.nonCommercialLandingFee = 'Y' THEN 1
	ELSE 0
END,
ARPElevation as elevation,
f.ownerAddress,
f.ownerCSZ,
f.ownerPhone,
f.manager,
f.managerAddress,
f.managerCSZ,
f.managerPhone
FROM faaNfdcFacilities f LEFT JOIN airports a ON (f.SiteNumber = a.faaSiteNumber)
WHERE apType = 'AIRPORT'
AND (apUse = 'PU' OR apUse = 'PR')
AND a.faaSiteNumber is Null

/* Close any airports where the site number is no longer in the faa database */
UPDATE airports
Set ident = 'CLSD', closed = 1
WHERE airportID IN
(SELECT airportID
FROM airports a LEFT JOIN faaNfdcFacilities f ON (a.faaSiteNumber = f.SiteNumber)
WHERE a.faaSiteNumber is Not Null
AND f.SiteNumber is Null)

Run SQL command to update the airports with the zipcode (based on the zipcodes table), and then the gmtOffset and the dstOffset fields based on the zipcode. 

/* Update the zipcode field */
UPDATE airports
Set zipcode = (SELECT TOP 1 zipcodes.zipcode FROM zipcodes WHERE zipcodes.city = airports.city AND zipcodes.state = airports.state ORDER BY abs(zipcodes.latitude - airports.latitude) + abs(zipcodes.longitude - airports.longitude))
WHERE zipcode is Null

/* Update the gmtOffset and dstOffset using the zipcode */
UPDATE airports
Set gmtOffset = (SELECT TOP 1 zipcodes.gmtOffset FROM zipcodes WHERE zipcodes.zipcode = airports.zipcode)
WHERE gmtOffset Is Null

UPDATE airports
Set dstOffset = (SELECT TOP 1 zipcodes.dstOffset FROM zipcodes WHERE zipcodes.zipcode = airports.zipcode)
WHERE dstOffset Is Null

5. Copy the airports table from the afids_development server to each organization's database.

This will require removing the foreign key references.

ALTER TABLE pilots DROP CONSTRAINT FK_pilots_primaryAirportID
ALTER TABLE missionLegs DROP CONSTRAINT FK_missionLegs_fromAirportID
ALTER TABLE missionLegs DROP CONSTRAINT FK_missionLegs_toAirportID
ALTER TABLE fbos DROP CONSTRAINT FK_fbos_airportID
ALTER TABLE camps DROP CONSTRAINT FK_camps_airportID

The constraints may not have these exact names. You can use this command (in MSSQL 2005 and greater) to look them up:

SELECT f.name AS ForeignKey,
   OBJECT_NAME(f.parent_object_id) AS TableName,
   COL_NAME(fc.parent_object_id, 
   fc.parent_column_id) AS ColumnName,
   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
   COL_NAME(fc.referenced_object_id, 
   fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
   ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME (f.referenced_object_id) = 'airports'

6. Once you have copied the table, replace the foreign key references:

ALTER TABLE pilots
ADD FOREIGN KEY (primaryAirportID) REFERENCES airports(airportID)

ALTER TABLE fbos
ADD FOREIGN KEY (airportID) REFERENCES airports(airportID)

ALTER TABLE missionLegs
ADD FOREIGN KEY (fromAirportID) REFERENCES airports(airportID)

ALTER TABLE missionLegs
ADD FOREIGN KEY (toAirportID) REFERENCES airports(airportID)

ALTER TABLE camps
ADD FOREIGN KEY (airportID) REFERENCES airports(airportID)

Skip to step 10 below to complete the process.

Option #2: Update only changed records (recommended)

7. Rather than copying the entire database, we can create SQL commands with just the changes. The following SQL commands are run against the development database.

/* Result #1 */
/* This creates SQL commands to update any existing records where the idents don't match */
/* Run this and save the result before running the script against development or production */
SELECT 'UPDATE airports Set ident = ''' + f.locationID + ''' WHERE airportID = ' + Convert(varchar(12),a.airportID) + ' /* old ident ' + a.ident + ' */'
FROM faaNfdcFacilities f JOIN airports a ON (f.SiteNumber = a.faaSiteNumber) WHERE a.ident <> f.locationID

/* Result #2 */
/* This creates SQL commands to close any airports where the site number is no longer in the faa database */
/* Run this and save the result before running the script against development or production */
SELECT 'UPDATE airports Set ident = ''CLSD'', closed = 1 WHERE airportID = ' + Convert(varchar(12), a.airportID)
FROM airports a LEFT JOIN faaNfdcFacilities f ON (a.faaSiteNumber = f.SiteNumber)
WHERE a.faaSiteNumber is Not Null AND f.SiteNumber is Null AND a.closed <> 1

/* Get a list of the new faaSiteNumbers */
/* Run this and save the result before running the script against development or production */
/* You'll need to paste this into a command shortly */
SELECT '''' + Convert(varchar(25), SiteNumber) + ''','
FROM faaNfdcFacilities f LEFT JOIN airports a ON (f.SiteNumber = a.faaSiteNumber)
WHERE apType = 'AIRPORT'
AND (apUse = 'PU' OR apUse = 'PR')
AND a.faaSiteNumber is Null

/* Now, run the scripts you generated above, plus this command against the development database */
/* You do not need to save this */
/* Add any records where the SiteNumber does not exist in the airports table */
INSERT INTO airports
SELECT locationID as ident,
facilityName as name,
f.city,
f.state,
ARPLatitudeS as latitude,
ARPLongitudeS as longitude,
runwayLength = (SELECT TOP 1 RunwayLength FROM faaNfdcRunways WHERE faaNfdcRunways.SiteNumber = f.SiteNumber),
Null as wingID,
Null as gmtOffset,
Null as dstOffset,
Null as zipcode,
0 as closed,
SiteNumber as faaSiteNumber,
'ATCT' = CASE
	WHEN f.ATCT = 'Y' THEN 1
	ELSE 0
END,
'private' = CASE
	WHEN apUse = 'PR' THEN 1
	ELSE 0
END,
'nonCommercialLandingFee' = CASE
	WHEN f.nonCommercialLandingFee = 'Y' THEN 1
	ELSE 0
END,
ARPElevation as elevation,
f.ownerAddress,
f.ownerCSZ,
f.ownerPhone,
f.manager,
f.managerAddress,
f.managerCSZ,
f.managerPhone
FROM faaNfdcFacilities f LEFT JOIN airports a ON (f.SiteNumber = a.faaSiteNumber)
WHERE apType = 'AIRPORT'
AND (apUse = 'PU' OR apUse = 'PR')
AND a.faaSiteNumber is Null

/* Result #1 */
/* Run this and save the results */
/* Add the records that were just inserted into the master */
SELECT 'INSERT INTO airports (airportID, ident, name, city, state, latitude, longitude, runwayLength, closed, faaSiteNumber, ATCT, private, nonCommercialLandingFee,  elevation) VALUES (' + Convert(varchar(25),airportID) + ',''' + ident + ''',''' + name + ''',''' + city + ''',''' + state + ''',' + Convert(varchar(25),latitude) + ',' + Convert(varchar(25),longitude) + ',' + Convert(varchar(25),runwayLength) + ',0,''' + Convert(varchar(25),faaSiteNumber) + ''',' + Convert(char(1),ATCT) + ',' + Convert(char(1),private) + ',' + Convert(char(1),nonCommercialLandingFee) + ',' + Convert(varchar(25),elevation) + ')'
FROM airports a
WHERE faaSiteNumber IN (Paste in the list of new faaSiteNumbers you generated earlier into here)

8. Run the results of the scripts above (results #1, #2 and #3) against the region databases.

MySql variation

Step 1

/* Result #1 */
/* This creates SQL commands to update any existing records where the idents don't match */
/* Run this and save the result before running the script against development or production */
SELECT concat('UPDATE airport Set ident = ''',f.location_id,''' WHERE airport.id = ',a.id,'; /* old ident ',a.ident,' */')
FROM faa_nfdc_facilities f JOIN airport a ON (f.site_number = a.faa_site_number) WHERE a.ident <> f.location_id;

Step 2

/* Result #2 */
/* This creates SQL commands to close any airports where the site number is no longer in the faa database */
/* Run this and save the result before running the script against development or production */
SELECT concat('UPDATE airport Set ident = ''CLSD'', closed = 1 WHERE airport.id = ',a.id,';')
FROM airport a LEFT JOIN faa_nfdc_facilities f ON (a.faa_site_number = f.site_number)
WHERE a.faa_site_number is Not Null AND f.site_number is Null AND a.closed != 1;

Step 3

/* Get a list of the new faaSiteNumbers */
/* Run this and save the result before running the script against development or production */
/* You'll need to paste this into a command shortly */
SELECT concat('''',f.site_number,'''')
FROM faa_nfdc_facilities f LEFT JOIN airport a ON (f.site_number = a.faa_site_number)
WHERE ap_type = 'AIRPORT'
AND (apUse = 'PU' OR apUse = 'PR')
AND a.faa_site_number is Null;

Step 4

/* Now, run the scripts you generated above, plus this command against the development database */
/* You do not need to save this */
/* Add any records where the SiteNumber does not exist in the airports table */
INSERT INTO airport
SELECT null as id, location_id as ident,
facility_name as name,
f.city,
f.state,
arp_latitude_s as latitude,
arp_longitude_s as longitude,
(SELECT runway_length FROM faa_nfdc_runways WHERE faa_nfdc_runways.site_number = f.site_number order by runway_length desc limit 1) as runway_length,
Null as wing_id,
Null as gmt_offset,
Null as dst_offset,
Null as zipcode,
0 as closed,
site_number as faa_site_number,
'atct' = CASE
	WHEN f.atct = 'Y' THEN 1
	ELSE 0
END,
'private' = CASE
	WHEN apUse = 'PR' THEN 1
	ELSE 0
END,
'non_commercial_landing_fee' = CASE
	WHEN f.non_commercial_landing_fee = 'Y' THEN 1
	ELSE 0
END,
arp_elevation as elevation,
f.operations_air_taxi,
f.operations_commercial,
f.operations_commuter,
f.operations_ga_itin,
f.operations_ga_local,
null as fee_waiver_required,
null as fee_waiver_contact_name,
null as fee_waiver_contact_phone,
null as fee_waiver_contact_fax,
null as fee_waiver_contact_email,
null as fee_waiver_message
FROM faa_nfdc_facilities f LEFT JOIN airport a ON (f.site_number = a.faa_site_number)
WHERE ap_type = 'AIRPORT'
AND (apUse = 'PU' OR apUse = 'PR')
AND a.faa_site_number is Null;

Step 4

/* Run this and save the results */
/* Add the records that were just inserted into the master */
SELECT concat('INSERT INTO airport (id, ident, name, city, state, latitude, longitude, runway_length, closed, faa_site_number, atct, is_private, non_commercial_landing_fee,  elevation, operations_air_taxi, operations_commercial, operations_commuter, operations_ga_itin, operations_ga_local) VALUES (',airport.id,',',
'''',ident,''',',
'''',name,''',',
'''',city,''',',
'''',state,''',',
latitude,',',
longitude,',',
runway_length,',0,',
'''',faa_site_number,''',',
atct,',',
is_private,',',
non_commercial_landing_fee,',',
elevation,',',
operations_air_taxi,',',
operations_commercial,',',
operations_commuter,',',
operations_ga_itin,',',
operations_ga_local,');'
)
FROM airport
WHERE faa_site_number IN (Paste in the list of new faaSiteNumbers you generated earlier into here);

Update data from the zipcode table

9. Run the scripts that update the new/changed airports:

/* Update the zipcode field */
UPDATE airports
Set zipcode = (SELECT TOP 1 zipcodes.zipcode FROM zipcodes WHERE zipcodes.city = airports.city AND zipcodes.state = airports.state ORDER BY abs(zipcodes.latitude - airports.latitude) + abs(zipcodes.longitude - airports.longitude))
WHERE zipcode is Null

/* Update the gmtOffset and dstOffset using the zipcode */
UPDATE airports
Set gmtOffset = (SELECT TOP 1 zipcodes.gmtOffset FROM zipcodes WHERE zipcodes.zipcode = airports.zipcode)
WHERE gmtOffset Is Null

UPDATE airports
Set dstOffset = (SELECT TOP 1 zipcodes.dstOffset FROM zipcodes WHERE zipcodes.zipcode = airports.zipcode)
WHERE dstOffset Is Null

MySql variation:

/* Update the zipcode field */
UPDATE airport
Set zipcode = (SELECT zipcode.zipcode FROM zipcode WHERE zipcode.city = airport.city AND zipcode.state = airport.state ORDER BY abs(zipcode.latitude - airport.latitude) + abs(zipcode.longitude - airport.longitude) limit 1)
WHERE zipcode is Null;

/* Update the gmtOffset and dstOffset using the zipcode */
UPDATE airport
Set gmt_offset = (SELECT zipcode.gmt_offset FROM zipcode WHERE zipcode.zipcode = airport.zipcode limit 1)
WHERE gmt_offset Is Null;

UPDATE airport
Set dst_offset = (SELECT zipcode.dst_offset FROM zipcode WHERE zipcode.zipcode = airport.zipcode limit 1)
WHERE dst_offset Is Null;

Organization-specific updates

10. Each region's database now must be updated with the wing values. The scripts below are run only on the one region's database.

Angel Flight West

/* AFW */
UPDATE airport
Set wing_id = 1
WHERE state = 'AZ';

UPDATE airport
Set wing_id = 4
WHERE state = 'CO';

UPDATE airport
Set wing_id = 5
WHERE state = 'ID';

UPDATE airport
Set wing_id = 6
WHERE state = 'MT';

UPDATE airport
Set wing_id = 7
WHERE state = 'NM';

UPDATE airport
Set wing_id = 8
WHERE state = 'NV';

UPDATE airport
Set wing_id = 8
WHERE state = 'NV';

UPDATE airport
Set wing_id = 9
WHERE state = 'OR';

UPDATE airport
Set wing_id = 10
WHERE state = 'UT';

UPDATE airport
Set wing_id = 11
WHERE state = 'WA';

UPDATE airport
Set wing_id = 12
WHERE state = 'WY';

UPDATE airport
Set wing_id = 13
WHERE state = 'AK';

UPDATE airport
Set wing_id = 14
WHERE state = 'HI';

UPDATE airport
Set wing_id = 2
WHERE state = 'CA';

UPDATE airport
Set wing_id = 3
WHERE state = 'CA' AND zipcode IN
(SELECT zipcode FROM zipcode WHERE state = 'CA'
AND county_name = 'San Luis Obispo'
OR county_name = 'Kern'
OR county_name = 'San Bernardino'
OR county_name = 'Santa Barbara'
OR county_name = 'Ventura'
OR county_name = 'Los Angeles'
OR county_name = 'Orange'
OR county_name = 'Riverside'
OR county_name = 'San Diego'
OR county_name = 'Imperial');

Angel Flight Central

/* AFC */
UPDATE airport
Set wing_id = 15;

UPDATE airport
Set wing_id = 1
WHERE state = 'IL';

UPDATE airport
Set wing_id = 2
WHERE state = 'IN';

UPDATE airport
Set wing_id = 4
WHERE state = 'KS';

UPDATE airport
Set wing_id = 5
WHERE state = 'MN';

UPDATE airport
Set wing_id = 6
WHERE state = 'MO';

UPDATE airport
Set wing_id = 7
WHERE state = 'NE';

UPDATE airport
Set wing_id = 9
WHERE state = 'SD';

UPDATE airport
Set wing_id = 17
where state = 'IA';

UPDATE airport
Set wing_id = 18
where state = 'ND';

UPDATE airport
Set wing_id = 19
where state = 'WI';

Mercy Flight Southeast

  • MFSE separates their zones by County. See #535 for the county/zone relationship and update information.
    
UPDATE airports SET wingid = coalesce(w.wingid,22)
FROM zipcodes z
LEFT JOIN wingmap wm ON z.state = wm.state AND z.countyName = wm.county
LEFT JOIN wings w ON w.name = CASE WHEN wm.wingname = 'Zone 22' THEN 'Zone 99' ELSE wm.wingname END
JOIN airports a ON a.zipcode = z.zipcode
WHERE a.wingid IS NULL
    
UPDATE airport SET wing_id = coalesce(w.id,22)
FROM zipcode z
LEFT JOIN wing_map wm ON z.state = wm.state AND z.county_name = wm.county
LEFT JOIN wing w ON w.name = CASE WHEN wm.wing_name = 'Zone 22' THEN 'Zone 99' ELSE wm.wing_name END
JOIN airport a ON a.zipcode = z.zipcode
WHERE a.wing_id IS NULL;

PALS

update airport
set wing_id = 20

AFE

AFE wings are divided by states.

update airport set wing_id = 2 WHERE state = 'PA';
update airport set wing_id = 3 WHERE state = 'CT';
update airport set wing_id = 4 WHERE state = 'DC';
update airport set wing_id = 5 WHERE state = 'DE';
update airport set wing_id = 6 WHERE state = 'IN';
update airport set wing_id = 7 WHERE state = 'KY';
update airport set wing_id = 8 WHERE state = 'MA';
update airport set wing_id = 9 WHERE state = 'MD';
update airport set wing_id = 10 WHERE state = 'NC';
update airport set wing_id = 11 WHERE state = 'NH';
update airport set wing_id = 12 WHERE state = 'NJ';
update airport set wing_id = 13 WHERE state = 'NY';
update airport set wing_id = 14 WHERE state = 'OH';
update airport set wing_id = 15 WHERE state = 'SC';
update airport set wing_id = 16 WHERE state = 'VA';
update airport set wing_id = 17 WHERE state = 'DC';

AFNE

AFNE has one wing.

update airport
set wing_id = 1;

AFOK

AFOK has one wing

update airport
set wing_id = 1;

AFSC

update afsc_afids.airport set wing_id = 1 where state = 'LA';
update afsc_afids.airport set wing_id = 2 where state = 'AR';
update afsc_afids.airport set wing_id = 3 where state = 'OK';
update afsc_afids.airport set wing_id = 10 where state = 'NM';
update afsc_afids.airport set wing_id = 4 where state = 'TX';

Texas airports

update afsc_afids.airport
set wing_id = 4
where zipcode in
(select zipcode from zipcode where state = 'TX'
and county_name in ('Anderson',
'Angelina',
'Archer',
'Bosque',
'Bowie',
'Camp',
'Cass',
'Cherokee',
'Clay',
'Collin',
'Comanche',
'Cooke',
'Dallas',
'Delta',
'Denton',
'Eastland',
'Ellis',
'Erath',
'Fannin',
'Franklin',
'Freestone',
'Grayson',
'Gregg',
'Hamilton',
'Harrison',
'Henderson',
'Hill',
'Hood',
'Hopkins',
'Hunt',
'Jack',
'Johnson',
'Kaufman',
'Lamar',
'Limestone',
'Marion',
'McLennan',
'Montague',
'Morris',
'Nacogdoches',
'Navarro',
'Palo Pinto',
'Panola',
'Parker',
'Rains',
'Red River',
'Rockwall',
'Rusk',
'Sabine',
'San Augustine',
'Shelby',
'Smith',
'Somervell',
'Stephens',
'Tarrant',
'Titus',
'Upshur',
'Van Zandt',
'Wichita',
'Wise',
'Wood',
'Young'));
update afsc_afids.airport
set wing_id = 7
where zipcode in
(select zipcode from zipcode where state = 'TX'
and county_name in ('Aransas',
'Atascosa',
'Bandera',
'Bastrop',
'Bee',
'Bell',
'Bexar',
'Blanco',
'Brooks',
'Burnet',
'Caldwell',
'Calhoun',
'Cameron',
'Comal',
'Concho',
'Coryell',
'De Witt',
'Dimmit',
'Duval',
'Edwards',
'Frio',
'Gillespie',
'Goliad',
'Gonzales',
'Guadalupe',
'Hays',
'Hidalgo',
'Jim Hogg',
'Jim Wells',
'Karnes',
'Kendall',
'Kenedy',
'Kerr',
'Kimble',
'Kinney',
'Kleberg',
'La Salle',
'Lampasas',
'Live Oak',
'Llano',
'Mason',
'Maverick',
'McCulloch',
'McMullen',
'Medina',
'Menard',
'Mills',
'Nueces',
'Real',
'Refugio',
'San Patricio',
'San Saba',
'Starr',
'Travis',
'Uvalde',
'Victoria',
'Webb',
'Willacy',
'Williamson',
'Wilson',
'Zapata',
'Zavala'));
update afsc_afids.airport
set wing_id = 6
where zipcode in
(select zipcode from zipcode where state = 'TX'
and county_name in ('Austin',
'Brazoria',
'Brazos',
'Burleson',
'Chambers',
'Colorado',
'Falls',
'Fayette',
'Fort Bend',
'Galveston',
'Grimes',
'Hardin',
'Harris',
'Houston',
'Jackson',
'Jasper',
'Jefferson',
'Lavaca',
'Lee',
'Leon',
'Liberty',
'Madison',
'Matagorda',
'Milam',
'Montgomery',
'Newton',
'Orange',
'Polk',
'Robertson',
'San Jacinto',
'Trinity',
'Tyler',
'Walker',
'Waller',
'Washington',
'Wharton'));
/* Northwest Texas, wing_id = 61 */
update afsc_afids.airport
set wing_id = 61
where zipcode in
(select zipcode from zipcode where state = 'TX'
and county_name in ('Dallam',
'Sherman',
'Hansford',
'Ochiltree',
'Lipscomb',
'Hartley',
'Moore',
'Hutchinson',
'Roberts',
'Hemphill',
'Oldham',
'Potter',
'Carson',
'Gray',
'Wheeler',
'Deaf Smith',
'Randall',
'Armstrong',
'Donley',
'Collingsworth',
'Parmer',
'Castro',
'Swisher',
'Briscoe',
'Hall',
'Childress',
'Bailey',
'Lamb',
'Hale',
'Floyd',
'Motley',
'Cottle',
'Hardeman',
'Foard',
'Wilbarger',
'Cochran',
'Hockley',
'Lubbock',
'Crosby',
'Dickens',
'King',
'Knox',
'Baylor',
'Yoakum',
'Terry',
'Lynn',
'Garza',
'Kent',
'Stonewall',
'Haskell',
'Throckmorton',
'Gaines',
'Dawson',
'Borden',
'Scurry',
'Fisher',
'Jones',
'Shackelford'));
/* Southwest Texas, wing_id = 62 */
update afsc_afids.airport
set wing_id = 62
where zipcode in
(select zipcode from zipcode where state = 'TX'
and county_name in 
('Andrews',
'Martin',
'Howard',
'Mitchell',
'Nolan',
'Taylor',
'Callahan',
'Loving',
'Winkler',
'Ector',
'Midland',
'Glasscock',
'Sterling',
'Coke',
'Runnels',
'Coleman',
'Brown',
'Ward',
'Crane',
'Upton',
'Reagan',
'Irion',
'Tom Green',
'Crockett',
'Schleicher',
'Sutton',
'Val Verde',
'El Paso',
'Hudspeth',
'Culberson',
'Reeves',
'Jeff Davis',
'Pecos',
'Presidio',
'Brewster',
'Terrell'));

VITAL

update airport
set wingID = 1;