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.

Create a new task in Windows 7 Task Scheduler.
Create a new task in Windows 7 Task Scheduler.

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

The General tab in Task Scheduler.
The General tab in Task Scheduler.

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!

Set a trigger to run the task whenever you login to Windows.
Set a trigger to run the task whenever you login to Windows.

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.

The second trigger has no delay.
The second trigger has no 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.

Specify the batch file to be run by the Task Scheduler.
Specify the batch file to be run by the Task Scheduler.

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.

17 replies on “Today’s Flights — How To Extract Data from BaseStation.sqb”

  1. Hi, great piece of work and I’m looking at using on my website in lieu of my heavy and time consuming MySQL database see http://nzradar.com/?page_id=2315 for the work in progress. I need to work on a few things like importing some other tables into the .sqb but the thing that bugs me at the moment is the constant DOS box flashing up with the write and ftp using the task scheduler. Is there away to hide this process or run minimal? Thanks and a brilliant source for donglers and the like!

    Mike

    1. Hi, Mike. I actually like seeing the command prompt window pop up because that tells me it’s working. I like to see things working. However, when you create a task in Task Scheduler, on the General tab there is a checkbox for “Hidden” in the bottom-left corner. I don’t know if that actually works, though, because I haven’t tried it myself. Like I said, I like to see things working.
      Your site inspired me to create my own “Flights” page, so I’ve added a link to your site on my “Connecting Flights” (Links to the layman) page. Thanks.

      1. Glad to hear I inspire somebody!

        I’ve almost got things under control but have some issues with the population of the PP .sqb Flights table using VRS. Is VRS the only tool that will add entries to the table? There seems to be nothing being entered except for the flightid, sessionid, aircraftid,starttime, endtime (only after flight lost) and callsign. All the other fields like squawk, altitude and lat/lon are 0 values! Using VRS as the data tool only things seem a bit better with some of the fields populated but others not.

        Is this the norm? I’m using a Beast.

        1. Have a look at a couple of the apps available from the Gatwick Aviation Society. (See Connecting Flights page for the link.) SBS Populate and its replacement, Active Display, will both populate the BaseStation.sqb database. The only catch is that BaseStation must be running in order for these two apps to work. I have lat and long data in my database using SBS Populate. I’ve never used the Beast so I’m not sure how that fits into the equation. However, my BaseStation install is being fed from VRS, so I imagine you should be able to do the same thing.

  2. Rather than using the environment variable THEDATE and worrying about system date formats, I found it easier to change:

    like ‘%THEDATE% %%’

    to:

    like strftime(‘%%Y-%%m-%%d%%%%’, ‘now’)

    Note that there are multiple percent signs due to batch file escaping.

    1. Ensure your BaseStation database has flights in it for today’s date, or else you’ll get nothing.

        1. And you have VRS installed? Check that the StandingData.sqb file is in c:\Users\username\AppData\Local\VirtualRadar. The AppData folder is a hidden one, so you’ll have to view Hidden Folders and Files first if you can’t see it.

  3. StandingData.sqb is available in the location you mentioned.

    Maybe I misunderstood a few things.
    – my Basestaion is located in C:\BaseStation\BaseStation.sqb
    – my VRS uses this file C:\BaseStation.sqb

    Should VRS point to the same sqb as Basestation?

    1. I don’t know why you need/want to have two different BaseStation.sqb databases but that’s up to you. Just make sure that the first line of db_query.bat points to the correct one:

      set THEDATABASE=c:\Kinetic\BaseStation\Basestation.sqb
      or
      set THEDATABASE=c:\Basestation.sqb

      The StandingData.sqb is used by VRS to store information about aircraft, airports and flights, and to link them all together based on the Mode-S codes being received. It doesn’t contain any live data; rather it’s more static data that changes infrequently. For example, C-GWSZ is a Westjet Boeing 738 and always will be unless that aircraft is sold to another operator or decommissioned. Only when that happens will the data in StandingData.sqb need to be updated. If you enable the setting in VRS, StandingData.sqb is updated every night with any changes that may have been reported in the industry, including flight information.

      So, with all that out of the way, here are three things I would do to test your setup:

      1) Check that everything from “select Flights.FlightID…” to “…order by Flights.Endtime desc;” is on one line in your dbquerycommands.txt file.

      2) Replace “…where Flights.EndTime >= strftime(‘%Y-%m-%d 00:00′,’now’,’localtime’)…” in the query with “…where Flights.EndTime like ‘2014-11-06%’…” (or whatever date you wish to search for) to see if your PC is interpreting the date calculation correctly.

      3) Try executing the commands in dbquerycommands.txt manually using the sqlite3.exe command line interface. To do this, go to Start and in the Search box type “cmd” (without quotes) and then hit Enter. Run the program that shows up in the results. That should get you a command prompt window. Type “cd c:\” and hit Enter. Then type “sqlite3.exe c:\Kinetic\BaseStation\Basestation.sqb” to open the database in SQLite3. Then copy and paste and execute separately each line of dbquerycommands.txt. If you’d rather see the results of the query on the screen rather than have them output to Flights.csv, omit the lines that start with “.output”. To exit SQLite3 when done, type “.exit” and hit Enter.

      Hopefully this will point you towards a correctable issue.

      1. You’ve lost me with dbquerycommands.txt

        I’ve stripped the FTP for the moment and running the batch file in a command prompt to be able to see the output. My file db_query.bat looks like this:
        set THEDATE=%DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%
        set THEDATABASE=c:\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

        The output looks like this:

        c:\>set THEDATE=/06/- 1-Th

        c:\>set THEDATABASE=c:\Basestation\Basestation.sqb

        c:\>set THECSVFILE=c:\Flights.csv

        c:\>if exist c:\Flights.csv del c:\Flights.csv

        c:\>timeout /t 2 /nobreak

        Waiting for 2 seconds, press CTRL+C to quit ...10

        c:\>c:\sqlite3.exe -header -csv c:\Basestation\Basestation.sqb "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 '/06/- 1-Th %' order by Flights.EndTime DESC;" 1>>c:\Flights.csv

        c:\>timeout /t 5 /nobreak

        Waiting for 5 seconds, press CTRL+C to quit ...43210

        Time and date settings look OK to me:
        c:\>date /t
        Thu 11/06/2014

        1. The problem is with your date. Your regional settings are likely different from mine. When I run:
          echo %DATE:~6,4%-%DATE:~3,2%-%DATE:~0,2%
          I get 2014-11-06 but you’re getting /06/- 1-Th. That’s definitely not the right date format.
          If you enter just DATE what do you get? My system returns 06/11/2014. You may need to adjust the line that sets THEDATE so it is getting the right information from your system’s clock. The way it is formatted now, in plain English, is thus:
          %DATE:~6,4 means get the four characters after (but not including) the sixth one in the date. Mine is 2014.
          %DATE:~3,2 gets the two characters after the third, which for me is 11.
          %DATE:~0,2 gets the two characters right at the beginning, so 06.
          You’ll need to pull out the relevant characters from your returned date and reconstruct that line that sets THEDATE. Don’t forget the hyphens in between.

        2. Just realized you mentioned what your date settings are: Thu 11/06/2014

          So your line in db_query.bat would have to read:

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

          Just a case of changing a few numbers.

Comments are closed.