Today’s Flights — How To Extract Data from BaseStation.sqb

I recently added a page to this website, Today’s Flights, that shows all of the aircraft recorded by my virtual radar setup during the calendar day. If you’re interested in doing the same on your website, here’s a tutorial that explains how I did it with my WordPress-based blog.

What You Will Need

• sqlite3.exe — The BaseStation database is a sqlite database. This is a pretty basic type of database but retains some powerful functions of the larger, more commercial types. In order for applications like BaseStation and PlanePlotter to access the database, only one dynamic link library (dll) file is needed. For you to be able to query the database from the Windows command prompt, however, you need the sqlite3.exe application. You can place this application file anywhere on your computer, but I suggest the root level of your hard drive (c:\)so it’s easy to access. Note: On the download page, you want to get the sqlite command line shell listed under Precompiled Binaries for Windows.
• sqlite3.dll — This might already be installed in c:\Windows\System32, but if it isn’t you can get it from the same page as sqlite3.exe, above. Put it in c:\Windows\System32.
• The CSV To SortTable WordPress plug-in. Installation instructions for this plug-in can be found here.
• A batch file that will execute the database query and upload the results to your website. See below.
• A way to execute the batch file on a set schedule. I’m using Windows 7, which has a Task Scheduler built in, so I’m using that.

The next day she and a high school classmate met amerikabulteni.com levitra free sample and declared themselves to be soulmates. We by some means maintained to shuffle careers generic india viagra with baseball and cheer-leading follow; housework with PTA conferences; sleep with sleep-overs and birthday parties. Insulin resistance can lead to anovulation, in which body does not produce eggs properly. 3. cialis 40 mg http://amerikabulteni.com/2011/09/30/amerika-beyzbola-kilitlendi-heyecan-firtinasi-bu-aksam-basliyor/ A Diminished Sexual Drive Issue Men buy cialis tadalafil believe that sexual problems (such as low testosterone, erectile dysfunction, premature ejaculation etc.) are the part of aging.

The Batch File

I used Notepad to write this batch file and then saved it to the root of my hard drive (c:\) with the name db_query.bat. This file assumes a few things:

1. You have BaseStation installed in c:\Kinetic\BaseStation.
2. You have FTP access to your WordPress installation on your web hosting site. Most do. You’ll need to know your username and password for this and place them in the batch file accordingly.

Here’s the file in its entirety. A line by line explanation follows below.

set THEDATE=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%
set THEDATABASE=c:\Kinetic\BaseStation\Basestation.sqb
set THECSVFILE=c:\Flights.csv
if exist %THECSVFILE% del %THECSVFILE%
timeout /t 2 /nobreak
c:\sqlite3.exe -header -csv %THEDATABASE% "select Aircraft.ModeS, Aircraft.ModeSCountry as Country, Aircraft.Registration, Aircraft.RegisteredOwners as Owner, Flights.Callsign, Aircraft.ICAOTypeCode as 'ICAO Code', Aircraft.Type, Aircraft.OperatorFlagCode as 'Operator Code', Flights.EndTime as 'Last Seen' from Aircraft INNER JOIN Flights ON (Aircraft.AircraftID=Flights.AircraftID) where Flights.EndTime like '%THEDATE% %%' order by Flights.EndTime DESC;" >> %THECSVFILE%
timeout /t 5 /nobreak
@echo off
echo user yourusername> ftpcmd.dat
echo yourpassword>> ftpcmd.dat
echo bin>> ftpcmd.dat
echo cd yourdirectory>> ftpcmd.dat
echo put %THECSVFILE%>> ftpcmd.dat
echo quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat your.ftphost.com
del ftpcmd.dat

Here’s what all of this does, line by line:

set THEDATE=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%

This gets your system’s date, which is returned in a format dependent on your localisation settings in Windows, and changes it into the format YYYY-MM-DD, which is how the database formats dates. You may have to change the ~6, ~3 and ~0 numbers in this line of code to extract the right data from your system’s date string. To figure out what numbers to use, open a Command Prompt window (Start > All Programs > Accessories > Command Prompt) and type:

date /t

The system I’m on at the moment returns:

Mon 03/11/2013

The numbers you need to use represent the character before the data you want, starting from zero. So in the above, M=1, O=2, N=3 … 2=11, 0=12, 1=13, 3=14. We need to get the year first, so we need to know how many characters there are before 2013. The / before 2013 is character number 10, so our line of code would start with:

set THEDATE=%DATE:~10,4

The 4 after ~10, is the number of characters to include. We need a four-digit year, so we capture the four characters after the tenth. In my example date, 03 is the month and 11 is the day, so to capture these I need to know how many characters to the space in front of the 0 and how many to the / in front of 11. The space is four, and the / is seven. So my full line of code to capture the date in the right format is:

set THEDATE=%DATE:~10,4%-%DATE:~4,2%-%DATE:~7,2%

The next two lines of code define some variables we’ll use elsewhere in the batch file. We want to assign a variable for the path to our BaseStation.sqb database and a second variable for the comma-separated text file we’re going to use to store our query results. My file is called Flights.csv and is saved at the root level of my hard drive, so the code becomes:

set THEDATABASE=c:\Kinetic\BaseStation\Basestation.sqb
set THECSVFILE=c:\Flights.csv

If the text file we’re saving our results to already exists, we’ll want to delete it before continuing. We delete it because we want our results to be new each time and not simply added to the end of the last set of results. You can see how the variable we assigned above comes in handy now. Instead of typing out the full path to the text file each time, we simply reference the variable:

if exist %THECSVFILE% del %THECSVFILE%
timeout /t 2 /nobreak

The second line is a built-in delay of two seconds to allow the system to delete the file. This is probably too long, but we don’t want any errors to halt our script, so longer is better than shorter (or no delay at all).

The next line is the actual query to the database and is pretty complicated if you’ve never done anything like this before. Note that this is all on one line:

c:\sqlite3.exe -header -csv %THEDATABASE% "select Aircraft.ModeS, Aircraft.ModeSCountry as Country, Aircraft.Registration, Aircraft.RegisteredOwners as Owner, Flights.Callsign, Aircraft.ICAOTypeCode as 'ICAO Code', Aircraft.Type, Aircraft.OperatorFlagCode as 'Operator Code', Flights.EndTime as 'Last Seen' from Aircraft INNER JOIN Flights ON (Aircraft.AircraftID=Flights.AircraftID) where Flights.EndTime like '%THEDATE% %%' order by Flights.EndTime DESC;" >> %THECSVFILE%

Let’s break this down into junks for easier understanding. We’ll start with the call to the sqlite3 application:

c:\sqlite3.exe -header -csv %THEDATABASE%

This launches the sqlite3 application from the root level of the hard drive (c:\) with two options. The first, -header, tells sqlite3 to include the column headers as the first row of data returned. The second option, -csv, tells sqlite3 to return the data as comma-separated values. Lastly, we use our predefined variable to tell sqlite3 which database to query.

Next comes the actual query. Whole sites are devoted to writing database queries of this nature, so I’m not going to go into any great detail here because neither of us has the time right now, but here’s a quick and dirty rundown of what’s going on in this part of the line:

"select Aircraft.ModeS, Aircraft.ModeSCountry as Country, Aircraft.Registration, Aircraft.RegisteredOwners as Owner, Flights.Callsign, Aircraft.ICAOTypeCode as 'ICAO Code', Aircraft.Type, Aircraft.OperatorFlagCode as 'Operator Code', Flights.EndTime as 'Last Seen' from Aircraft INNER JOIN Flights ON (Aircraft.AircraftID=Flights.AircraftID) where Flights.EndTime like '%THEDATE% %%' order by Flights.EndTime DESC;"

The database stores the information we want in two different tables — Aircraft and Flights — so we need a way to structure the query such that the data for aircraft X is taken from both tables. We do this by selecting data from the tables with an “inner join.”

"select ... from Aircraft INNER JOIN Flights ON (Aircraft.AircraftID=Flights.AircraftID) ..."

Both tables have a column called AircraftID, which is a number the database assigns to any new aircraft that are detected. The same number is written to both tables; it’s a way for the database to know that the data for aircraft 123456 in table A corresponds to the data for the same aircraft in Table B.

We want to select only the aircraft that have been seen today and we want the most recent ones at the top, so we need to include a qualifier in our query:

"...where Flights.EndTime like '%THEDATE% %%' order by Flights.EndTime DESC;"

This returns only those flights that were last seen on %THEDATE%, which is the variable we assigned on the very first line of our batch file. The last half, “order by…” sorts the results in descending order based on the date and time the flights were last seen.

Lastly we need to specify what columns we want returned for our data. There are plenty of columns in the database (you can read more about them here) but we require just a handful, namely: the ModeS code, the country this belongs to, the aircraft’s registration and owners, the flight’s current callsign, the ICAO Type Code for the aircraft and it’s full description type, the operator’s code and lastly the time the flight was last seen.

Now some of those columns have names that aren’t reader-friendly on a website in a table, so as part of our query we’re going to rename those column headers on the fly:

"select Aircraft.ModeS, Aircraft.ModeSCountry as Country, Aircraft.Registration, Aircraft.RegisteredOwners as Owner, Flights.Callsign, Aircraft.ICAOTypeCode as 'ICAO Code', Aircraft.Type, Aircraft.OperatorFlagCode as 'Operator Code', Flights.EndTime as 'Last Seen'..."

The column headers that I’m renaming are the ones followed by as, for example “Flights.EndTime as ‘Last Seen’.” The last part of this line is what outputs the results to the text file:

>> %THECSVFILE%

This is followed by another delay — five seconds this time — to allow the database query to run and the data to be written out. Five seconds is again probably too long but it works:

timeout /t 5 /nobreak

The final nine lines of code are the ones that upload your text file to your website. FTP is used to transfer the file (FTP = File Transfer Protocol) and fortunately FTP is built into Windows. You’ll need to replace some of the text in these lines for it to work correctly:

@echo off
echo user yourusername> ftpcmd.dat
echo yourpassword>> ftpcmd.dat
echo bin>> ftpcmd.dat
echo cd yourdirectory>> ftpcmd.dat
echo put %THECSVFILE%>> ftpcmd.dat
echo quit>> ftpcmd.dat
ftp -n -s:ftpcmd.dat your.ftphost.com
del ftpcmd.dat

Substitute yourusername, yourpassword, yourdirectory and your.ftphost.com with your actual username and password to upload files to your site, the directory to put the file in — for example wp-content/uploads — and the address of the FTP access to the site. This information is written to a temporary file called ftpcmd.dat, which is referenced by the FTP application when it’s run by this line:

ftp -n -s:ftpcmd.dat your.ftphost.com

The last line deletes the temporary file once the upload has completed.

del ftpcmd.dat

To include your text file in a page or post in your WordPress blog, you need to insert a shortcode that is interpreted by the CSV To SortTable plug-in that you installed above. (You did do that, right?) I have mine in a page, not a post, and the shortcode I use looks like the one that follows. (Note: I had to add a space after the opening square bracket and one before the closing square bracket in order for the code to appear here instead of the actual table; you’ll need to remove these spaces when you enter the shortcode in your page or post for the shortcode to work properly.)

[ csv2table source="http://sonicgoose.com/Flights.csv" unsortable="1,2,3,4,5,6,7,8,9,10" ]

The plug-in has some parameters that you can add. I have used the unsortable parameter to render the sort order of my columns static. In other words, you can’t sort them. You can find other parameters on the plug-in’s download page. Information on how to style your columns and rows can be found here.

Scheduling Your Batch File to Run Regularly

Your batch file can be run once by opening a Command Prompt and entering:

cd c:\
db_query.bat

You can use this method to make sure your batch file executes all its code properly and that the FTP upload works. If it does, you may want to have it run at set intervals so that your website has a constantly updating list of flights. You can use Task Scheduler in Windows 7 to do this. You’ll find it in Start > All Programs > Accessories > System Tools. Start by selecting Action > Create task.

On the General tab, give your task a name and specify that it should run only when you’re logged in.

Now go over to the Triggers tab. Here you’ll specify two triggers that start execution of the task. The first trigger is when you login to Windows. I have mine set to a 10 minute delay to give me time to launch all of my other apps. (Note that 10 minutes isn’t in the drop-down list for Delay but you can select 15 minutes and then change the 5 to a 0.) I’ve also set this trigger to repeat the task every 10 minutes for an indefinite amount of time. Don’t forget to Enable the task!

The second trigger will run the task when you create the schedule and whenever you modify this schedule. This one will also be set to repeat every 10 minutes for an indefinite amount of time, but unlike the first trigger this one does not have a 10-minute delay.

Finally on the Action tab you specify which program the task should execute whenever it runs. Click Browse and select the batch file you created. Mine is c:\db_query.bat.

And that’s it. Save your task and watch it run immediately…and every 10 minutes after that. Then enjoy sharing your data on the Internet.