My ADS-B Log is Now Online

I used to have a page on this site called Today’s Flights. It provided a list of all the flights recorded by my BaseStation setup for the day, and at midnight it would start fresh from zero. At some point, it stopped working, and I never bothered to fix it. After all, why fix it if I could make it better?

Many weeks of work have produced a complete, online version of my BaseStation database, updated every five minutes and completely searchable. You can visit it by clicking My ADS-B Log at the top of this page.

Dating back to July 21, 2013, the online database shows a record of every identifiable aircraft that has flown within range of my receiver at home in Richmond Hill, Ontario, Canada. By default, the results show the newest flights first.

Where known, the callsign is displayed and from that the origin and destination of the flight are determined. The distance between those two points is calculated on-the-fly (so to speak). Clicking on the callsign will show you a list of other aircraft that have flown the same flight. Clicking on the ModeS code, which is unique to every aircraft, will show you a list of other flights flown by the same plane. And lastly, clicking on the FlightID will give you more detailed information about that flight, including maps that are also generated on-the-fly.

This searchable database was made possible by the contributions of two individuals I’ve never met.

• Mario Trunz is an aviation enthusiast and web developer in Barrie, Ontario, Canada. Most of the code used to generate the tables at flights.sonicgoose.com was provided by him and is based on code he wrote for his own aviation website, barriespotter.com.
• Christian Sorensen is an aviation enthusiast and database developer from Denmark. He contacted me by email after seeing my tutorials on this site. He and I brainstormed back and forth via email to create a SQL query that spanned multiple tables on multiple databases, and essentially formed the query I use to get the data on this site from my personal computer at home.

These can be because of decline in health associated issues which cialis no prescription come along with ageing. The capsule lends lots of time to viagra from canada pharmacy make love satisfactorily, which is unlikely with other ED medicines as it may lead to nasty side effects which can include nausea and headaches, chest pains and even kidney disease. Since it can stimulate the growth of wholesale cialis price children. The weak erection brings relationship issues and cheap viagra even divorce.
Happy searching!

 

UPDATE:

I explained in my original Today’s Flights post how to extract information from the BaseStation database using SQLite3, so I won’t go into detail about every command required to do this. You must also have Virtual Radar Server 2 installed and running (in order for the routes database to be updated nightly). You also need the SQLite3 precompiled binary for Windows. Put this in the root level of your c: hard drive.

You need an online MySQL database with a table on it to store the information. Here’s the schema for my online MySQL database, which creates a table called ‘flights’:

CREATE TABLE `flights` (
`ID` int(11) NOT NULL,
`ModeS` varchar(6) CHARACTER SET utf8 NOT NULL,
`Country` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
`Registration` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`Operator` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
`Callsign` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`ModelCode` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`AircraftModel` varchar(40) CHARACTER SET utf8 DEFAULT NULL,
`OperatorCode` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
`FirstSeen` datetime NOT NULL,
`FirstLatitude` double DEFAULT NULL,
`FirstLongitude` double DEFAULT NULL,
`FirstAltitude` int(11) DEFAULT NULL,
`LastSeen` datetime NOT NULL,
`LastLatitude` double DEFAULT NULL,
`LastLongitude` double DEFAULT NULL,
`LastAltitude` int(11) DEFAULT NULL,
`NumPositionReports` int(11) DEFAULT NULL,
`FromICAO` char(4) CHARACTER SET utf8 DEFAULT NULL,
`FromIATA` char(3) CHARACTER SET utf8 DEFAULT NULL,
`FromName` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
`FromLat` double DEFAULT NULL,
`FromLong` double DEFAULT NULL,
`FromLocation` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
`FromCountry` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
`ToICAO` char(4) CHARACTER SET utf8 DEFAULT NULL,
`ToIATA` char(3) CHARACTER SET utf8 DEFAULT NULL,
`ToName` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
`ToLat` double DEFAULT NULL,
`ToLong` double DEFAULT NULL,
`ToLocation` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
`ToCountry` varchar(80) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

You need three files in order to get the information out of BaseStation.sqb and into your online MySQL database. You’ll also need to have some way of scheduling a php script to run repeatedly on your web server (or your host’s web server). Let’s start with the files you need on your Windows 7 PC first, though.

Open Notepad and in a blank document enter the following:

set THEDATABASE=c:\Kinetic\BaseStation\Basestation.sqb
set THECSVFILE=c:\ftp\Flights.csv
if exist %THECSVFILE% del %THECSVFILE%
:: allow time for the csv file to be deleted
timeout /t 2 /nobreak
c:\sqlite3.exe %THEDATABASE% < c:\dbquerycommands.txt
::allow time for the csv to be written to file
timeout /t 2 /nobreak
@echo off
echo user [username]> ftpcmd.dat
echo [password]>> ftpcmd.dat
echo bin>> ftpcmd.dat
echo cd [/path/to/website]>> ftpcmd.dat
echo put %THECSVFILE%>> ftpcmd.dat
echo quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat [ftp.yoursite.com]
del ftpcmd.dat

You’ll need to replace any text in square brackets with the correct information, i.e. the right username, password and address of your FTP site, and the path to your website on your host’s web server. Delete the square brackets or else this will not work. Save the file as db_query.bat at the root level of your c: hard drive.

Create a new blank Notepad document and enter the following:

attach database 'c:\Users\[username]\AppData\Local\VirtualRadar\StandingData.sqb' as StandingData;
.mode list
.separator "|"
.output 'c:\ftp\Flights.csv'
select Flights.FlightID as 'ID', Aircraft.ModeS as 'ModeS', Aircraft.ModeSCountry as 'Country', Aircraft.Registration as 'Registration', Aircraft.RegisteredOwners as 'Operator', Flights.Callsign as 'Callsign', Aircraft.ICAOTypeCode as 'ModelCode', Aircraft.Type as 'AircraftModel', Aircraft.OperatorFlagCode as 'OperatorCode', strftime('%Y-%m-%d %H:%M:%S',Flights.StartTime) as 'FirstSeen', Flights.FirstLat as 'FirstLatitude', Flights.FirstLon as 'FirstLongitude', Flights.FirstAltitude as 'StartingAltitude', strftime('%Y-%m-%d %H:%M:%S',Flights.EndTime) as 'LastSeen', Flights.LastLat as 'LastLatitude', Flights.LastLon as 'LastLongitude', Flights.LastAltitude as 'EndingAltitude', Flights.NumAirPosMsgRec as 'NumPositionReports', StandingData.RouteView.FromAirportICAO as 'FromICAO', StandingData.RouteView.FromAirportIATA as 'FromIATA', StandingData.RouteView.FromAirportName as 'FromName', StandingData.RouteView.FromAirportLatitude as 'FromLat', StandingData.RouteView.FromAirportLongitude as 'FromLong', StandingData.RouteView.FromAirportLocation as 'FromLocation', StandingData.RouteView.FromAirportCountry as 'FromCountry', StandingData.RouteView.ToAirportICAO as 'ToICAO', StandingData.RouteView.ToAirportIATA as 'ToIATA', StandingData.RouteView.ToAirportName as 'ToName', StandingData.RouteView.ToAirportLatitude as 'ToLat', StandingData.RouteView.ToAirportLongitude as 'ToLong', StandingData.RouteView.ToAirportLocation as 'ToLocation', StandingData.RouteView.ToAirportCountry as 'ToCountry' from Aircraft INNER JOIN Flights ON (Aircraft.AircraftID=Flights.AircraftID) LEFT JOIN StandingData.RouteView ON (Flights.Callsign=StandingData.RouteView.Callsign) where Flights.EndTime >= strftime('%Y-%m-%d 00:00','now','localtime') order by Flights.Endtime desc;
.output stdout
detach database StandingData;

Save this as dbquerycommands.txt in the root folder of your c: hard drive. The name is important because it is called by the sqlite3 command in the first file you created.

Create a third Notepad document and enter the following:

$databasehost = "[databasehost.com]";
$databasename = "[dbname]";
$databasetable = "[dbtable]";
$databaseusername="[dbuser]";
$databasepassword = "[dbpwd]";
$fieldseparator = "|";
$lineseparator = "\n";
$csvfile = "/path/to/csv/file/Flights.csv";
if(!file_exists($csvfile)) {
die("File not found. Make sure you specified the correct path.");
}
try {
$pdo = new PDO("mysql:host=$databasehost;dbname=$databasename",
$databaseusername, $databasepassword,
array(
PDO::MYSQL_ATTR_LOCAL_INFILE => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
)
);
} catch (PDOException $e) {
die("database connection failed: ".$e->getMessage());
}
$affectedRows = $pdo->exec("
LOAD DATA LOCAL INFILE ".$pdo->quote($csvfile)." REPLACE INTO TABLE `$databasetable`
FIELDS TERMINATED BY ".$pdo->quote($fieldseparator)."
LINES TERMINATED BY ".$pdo->quote($lineseparator));
echo "Loaded a total of $affectedRows records from this csv file.\n";

WordPress won’t let me wrap the above in php tags, so you’ll have to add <?php to the beginning before the first line and ?> at the end after the last line. Save this file as flightimport.php anywhere you want. You’ll need to upload this file to your web server and then schedule a cron job to run the php script on a repeating schedule. Mine runs every five minutes.

On your Windows 7 PC, use Task Scheduler to run your db_query.bat script on a schedule that matches your php script. For instructions, see “Scheduling Your Batch File to Run Regularly” in my original post.

That’s all there is to it. db_query.bat opens your BaseStation database in SQLite3 and executes the commands in dbquerycommands.txt to export the data to c:\ftp\Flights.csv with | as a field separator, then it uploads Flights.csv to your FTP server. flightimport.php then reads Flights.csv and imports the data into your online MySQL database.

Now you’d like to know how to run queries against the database and display the results on your website. Sorry, I can’t help you with that. The majority of the code I use to do that was given to me by Mario Trunz, who wrote for his barriespotter.com website. It’s not my code to share.