Tweet
I found a possible way to mass update flightradar24 database for all registrations starting with N. You could easily import every registration and Mode-s Hex associated from the official FAA registry.
Aircraft Database Download (updated every 7 days):
http://www.faa.gov/licenses_certific...raft_download/
Only challenge I found was that FAA's MASTER.txt doesn't contain ICAO Type Code and the file references Make and model of aircraft by numbers which then exists on a different file.
First thing I saw right away, is that you could mass import all registration with Mode-S hex code so that way it covers all aircraft. FAA has the registration locked to the Mode-S hex code, so they wont change.
This was tested using MySQL 5.6.14.
I did find an easy way to easily import the entire FAA registry into MySQL using the following SQL commands:
Here is a simple php code that quickly displays a csv file into html with tables:
Aircraft Database Download (updated every 7 days):
http://www.faa.gov/licenses_certific...raft_download/
Only challenge I found was that FAA's MASTER.txt doesn't contain ICAO Type Code and the file references Make and model of aircraft by numbers which then exists on a different file.
First thing I saw right away, is that you could mass import all registration with Mode-S hex code so that way it covers all aircraft. FAA has the registration locked to the Mode-S hex code, so they wont change.
This was tested using MySQL 5.6.14.
I did find an easy way to easily import the entire FAA registry into MySQL using the following SQL commands:
Code:
-- -- Table structure for table `faa` -- DROP TABLE IF EXISTS `faa`; CREATE TABLE `faa` ( `N_NUMBER` char(8) NOT NULL, `SERIAL_NUMBER` varchar(30) DEFAULT NULL, `MFR_MDL_CODE` varchar(7) DEFAULT NULL, `ENG_MFR_MDL` varchar(5) DEFAULT NULL, `YEAR_MFR` varchar(4) DEFAULT NULL, `TYPE_REGISTRANT` varchar(1) DEFAULT NULL, `NAME` varchar(50) DEFAULT NULL, `STREET` varchar(33) DEFAULT NULL, `STREET2` varchar(33) DEFAULT NULL, `CITY` varchar(18) DEFAULT NULL, `STATE` varchar(2) DEFAULT NULL, `ZIP_CODE` varchar(10) DEFAULT NULL, `REGION` varchar(1) DEFAULT NULL, `COUNTY` varchar(3) DEFAULT NULL, `COUNTRY` varchar(2) DEFAULT NULL, `LAST_ACTION_DATE` varchar(8) DEFAULT NULL, `CERT_ISSUE_DATE` varchar(8) DEFAULT NULL, `CERTIFICATION` varchar(36) DEFAULT NULL, `TYPE_AIRCRAFT` varchar(1) DEFAULT NULL, `TYPE_ENGINE` varchar(2) DEFAULT NULL, `STATUS_CODE` varchar(2) DEFAULT NULL, `MODE_S_CODE` varchar(8) DEFAULT NULL, `FRACT_OWNER` varchar(1) DEFAULT NULL, `AIR_WORTH_DATE` varchar(8) DEFAULT NULL, `OTHER_NAMES_1` varchar(50) DEFAULT NULL, `OTHER_NAMES_2` varchar(50) DEFAULT NULL, `OTHER_NAMES_3` varchar(50) DEFAULT NULL, `OTHER_NAMES_4` varchar(50) DEFAULT NULL, `OTHER_NAMES_5` varchar(50) DEFAULT NULL, `EXPIRATION_DATE` varchar(8) DEFAULT NULL, `UNIQUE_ID` varchar(8) DEFAULT NULL, `KIT_MFR` varchar(30) DEFAULT NULL, `KIT_MODEL` varchar(20) DEFAULT NULL, `MODE_S_CODE_HEX` varchar(10) DEFAULT NULL, PRIMARY KEY (`N_NUMBER`), UNIQUE KEY `N_NUMBER_UNIQUE` (`N_NUMBER`), UNIQUE KEY `MODE_S_CODE_HEX_UNIQUE` (`MODE_S_CODE_HEX`), UNIQUE KEY `UNIQUE_ID_UNIQUE` (`UNIQUE_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Code:
LOAD DATA INFILE 'C:\FAA\MASTER.txt' INTO TABLE faa FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY ',\r\n' (N_NUMBER,SERIAL_NUMBER,MFR_MDL_CODE,ENG_MFR_MDL,YEAR_MFR,TYPE_REGISTRANT,NAME,STREET,STREET2,CITY,STATE,ZIP_CODE,REGION,COUNTY,COUNTRY,LAST_ACTION_DATE,CERT_ISSUE_DATE,CERTIFICATION,TYPE_AIRCRAFT,TYPE_ENGINE,STATUS_CODE,MODE_S_CODE,FRACT_OWNER,AIR_WORTH_DATE,OTHER_NAMES_1,OTHER_NAMES_2,OTHER_NAMES_3,OTHER_NAMES_4,OTHER_NAMES_5,EXPIRATION_DATE,UNIQUE_ID,KIT_MFR,KIT_MODEL,MODE_S_CODE_HEX)
Code:
<?php
echo "<html><body><table>\n\n";
$f = fopen("MASTER.txt", "r");
while (($line = fgetcsv($f)) !== false) {
echo "<tr>";
foreach ($line as $cell) {
echo "<td>" . htmlspecialchars($cell) . "</td>";
}
echo "<tr>\n";
}
fclose($f);
echo "\n</table></body></html>";
?>
Comment