Databases used by Plane Plotter flightroute.sqb BaseStation.sqb Where to get databases Flightroute.sqb BaseStation.sqb Create Aircraft Database from scratch Updating the PPmobile (Android application) databases Some apparent misunderstandings Aircraft registrations User amended registrations Tools for editing the SQLite databases

There is some confusion and uncertainty about how Plane Plotter uses and stores data so I will spell it out as best as I can.

 

Databases used by Plane Plotter

 

PP uses two SQLite databases: FlightRoute.sqb and BaseStation.sqb.  You can get these databases from:

 

  BaseStation.sqb: downloaded through Plane Plotter directly or through the PlaneBase PBLink software.

  FlightRoute.sqb, twice weekly updates: http://groups.yahoo.com/group/PP-logs-and-routes/

 

To avoid ambiguity, be sure to unhide file extensions in Windows.  Here's how.

 

Whilst not used by Plane Plotter, you may add your information to the Plane Plotter Sharers Database, and look to see whether you have any neighbours who might help with providing Mlat data!  Be sure to enter your own details on the database.

 

flightroute.sqb

 

The first one is unique to Plane Plotter and stores only the relationship between flight numbers and routes, along with a time tag showing the time at which the route data was updated.  flightroute.sqb is stored in the Plane Plotter log file directory (Options..Directories..Log files) and if it does not exist when PP is first run, it is automatically created with its correct formatting information.   The database is checked when flight numbers are received and the route is displayed on the chart of that is enabled in Options..Chart..Options.

 

Since Plane Plotter of itself receives no route information, this database is only ever populated by one of the add-on scripts calling the "SetRouteByHex" method.

 

While on the subject, it is possible to get into a muddle whereby the flightroute.sqb file exists but has zero length and does not contain the required formatting information.  This can happen if the log file directory is changed while Plane Plotter is processing and when it consults the non-existent file, SQLite, not Plane Plotter, creates an empty file but with no formatting.  In this condition, Plane Plotter sees that the file exists, so it does not create it, but it cannot be written to because it does not have the formatting information.  The fix is easy.  Close Plane Plotter, delete the zero-length flightroute.sqb file from the current log file directory and run Plane Plotter again.  A new flightroute.sqb file will be created with the correct formatting.

 

BaseStation.sqb

 

Unlike the first one, this file is not of Plane Plotter's making.  It originates in the Kinetic SBS-1 BaseStation software and contains a large number of tables and fields, most of which Plane Plotter ignores.  If you have an SBS-1, this file will reside in the application directory used by the Kinetic software (typically C:\Program Files\Kinetic\BaseStation\) and in order for Plane Plotter to be able to find it, you must point Options..Directories..SBS1 to the correct place.  If you don't have an SBS1, you can put a BaseStation.sqb file that you obtain from elsewhere, into any directory that you like, but you must be sure to point Plane Plotter to it using Options..Directories..SBS1.

 

PP consults the BaseStation.sqb database (read only) pretty much any time a position report is received from a Mode-S receiver (not necessarily an SBS1) or from sharing.  It looks in the database for the registration and the aircraft type, neither of which arrive in the Mode-S/ADS-B messages off air, but which may have been populated into the SBS1 database.  If it finds them, it displays them on the chart (if such labelling is enabled in Options..Chart..Options).  If the information is not found in the database, then Plane Plotter will display any such information that it receives by sharing (which may or may not be right, depending on how the person uploading the data has populated their own database).

 

PP will only ever write to the BaseStation.sqb database of you have specifically enabled (checked) the option under Options..Mode-S..Kinetic..Update registrations.  If you disable (uncheck) that option, the BaseStation.sqb database will never be changed by PP.  

 

If you do enable the option, then there are three situations in which PP will write data to it.

 

• If you are simultaneously receiving ACARS messages (not limited to position reports, any ACARS message will do) and Mode-S/ADS-B messages, and if the same flight number occurs in an ACARS message and in a Mode-S/ADS-B message, then because ACARS messages all contain the registration but Mode-S messages do not, PP can tie up the registration with the Mode-S hex code and it will write the registration to the BaseStation.sqb database automatically (if enabled).  Note that this only populates the registration, not the aircraft type because the type is not available in ACARS messages either. Sadly, because the format of the flight number in ACARS messages and in Mode-S/ADS-B messages is almost always subtly different, this useful facility hardly ever works.
• If you perform a Look-Up on an aircraft by right clicking on the aircraft symbol and then selecting the lookup button, PP will extract the registration and type from the response and write that data to BaseStation.sqb if enabled.
• If you have an external script that does something similar and then accesses PP using the SetPlaneTypeByHex or SetPlaneRegByHex methods, then that data will be written to BaseStation.sqb, if enabled.

 

Where to get databases

 

With both of these databases, you can download databases from the Internet, using those as starter databases and updating them yourself, or using them as your own database and updating them as new versions are announced on the Internet.  Be careful in checking the options in Plane Plotter to accept shared routes or registrations, as you then are relying on others to get the data right, and you may get personalised data which does not suit your needs.

 

These database can also be used with the Plane Plotter mobile program for Android.  As the Android program does not have the option to ignore shared routes and registrations, it is particularly important to use the downloaded and checked data.  You will find instructions for using the databases with Plane Plotter Mobile here.  Please note that both the files flightroute.sqb and basestation.sqb reside in the same directory in Plane Plotter Mobile.  Do remember to copy both files!

 

Flightroute.sqb

 

You can download the flight route data from the files are of this Yahoo group.  Data using either the three-letter IATA codes or the four-letter ICAO codes is available.  The data is cleaned of reported errors, but some routes may have only ICAO format names.  This file must be unzipped and renamed "flightroute.sqb".  To protect the integrity of this file users should ensure that in Plane Plotter Options/Sharing/Setup "Accept shared routes" is unticked.  This data may not be copied, published, or incorporated into other databases without the explicit permission of David J Taylor, Edinburgh.

 

To avoid ambiguity, be sure to unhide file extensions in Windows.  Here's how.

 

New updates are notified in the Yahoo group directly, and usually happen on Sundays and Wednesdays.  You may find it easier to use the Options, Script, Fetch latest flight and route database menu.

 

Updates: if you have corrections or updates, please e-mail them to Jim (jim@crowwoodgrange.com) and ask him what format he prefers.

 

BaseStation.sqb

 

Please investigate PlaneBase.  Pre-prepared files are made available monthly - please see the Plane Plotter Yahoo group for announcements.

In Planeplotter: Click on black square to stop processing then click on Options > SQB hex database options, > Fetch hex SQB database. This will start a process to download a zip file and replace your current database after first automatically backing it up.

 

Create Aircraft Database from scratch

 

1. Download an empty file called bs.sqb from Yahoo groups Files section and rename it BaseStation.sqb
2. Save it in folder called BaseStation within the PlanePlotter folder (or anywhere else so long as you remember where it is).
3. In Plane Plotter open Options > SQB database options navigate to your newly created BaseStation.sqb file within BaseStation folder in the PlanePlotter folder, highlight and click open.  That will show Plane Plotter where the file is.
4. Now follow the process to download a new populated version of BaseStation using Options > script > Fetch SQB hex database.  It will automatically unzip and replace the empty file.
5. Make your required selections in Options > SQB database options.  It is recommended that you just tick the 2 Edit options and Local.

The database is normally refreshed twice monthly to keep it up to date with additions and deletions.

Information from: Richard Jones.

 

Updating the PPmobile (Android application) databases

 

Personal note from David Taylor: On my Android phone, when I connect it to the PC, I get a new "disk drive" named XT1068 in the Windows File Explorer.  On that "drive", I see two directories: Internal Storage and SD Card.  Double-clicking on Internal Storage shows a directory: PPmobile, and double-clicking on that shows a directory "db".  The SD Card does not have a PPmobile directory.  To update the data, from my installation of Plane Plotter on my PC I can drag-and-drop both the files:

BaseStation.sqb

flightroute.sqb

 

to the XT1068\Internal Storage\PPmobile\db directory and have both available to PPmobile.  I don't know whether the file name "BaseStation.sqb" is case sensitive as far as PPmobile is concerned - I hope not.

The process is similar on my tablet (Nexus 7) except that there is no SD card directory, and the device appears to have to be plugged in twice before the directories appear on the PC!

Some apparent misunderstandings

 

• PP uses only two external databases - one flightroute.sqb; one BaseStation.sqb.
• You do not need an SBS1 to use the BaseStation.sqb feature, you just need a copy of that file.  You must, however, point PP to the file using Options..Directories..SBS1.  Note that because this directory points to both the database file and the SBS1 log file (if you use that access method) then you cannot use two copies of the file (one for BS itself and another one in another place for PP) if you also want PP to monitor the SBS1 log file.  If you use TCP access to the SBS1, then you could, if you wanted to, have two copies.
• PP will only write to BaseStation.sqb if you enable it (Options..Mode-S..Kinetic..Update registrations.
• PP will always take the registration and type from your own database (BaseStation.sqb) if it is found there (and your directory is correctly identified).  It will only show that info from sharing if there is nothing in your own database (or you haven't done the directory thing right).
• PP will never put data received from sharing into the BaseStation.sqb database.  Your own database cannot be polluted by corrupt data from other users.

 

Aircraft registrations

 

Bev recently clarified what happens with Plane Plotter 5.1.5 and later when dealing with aircraft registrations:

 

• Accessing the SBS1 traffic from either the log file or the TCP port gives just about everything except the registration.
• For local traffic, PP will try to get the registration from the aforementioned database - provided the directory path is correct.
• For shared traffic, it will accept the registration provided by the sharing contributor, if any.  Otherwise it will look in the sqb database.
• In either case, if it still has no registration, and if the aircraft is from one of the countries with an algorithmic approach to hex/rego conversions, PP will compute the registration from the hex code.  This list includes USA (civil), Australia, Portugal, Romania, Switzerland, Turkey, Yugoslavia (?), Singapore, Canada, South Africa, Russia, Belgium, Denmark, Finland, Greece, France and Germany.  For UK users, the absence of G-,PH- and EI- from this list is a nuisance.
• I haven't spelt it out elsewhere, but registrations are only inserted into the BaseStation.sqb database (if enabled) when they are obtained from the airframes site or if you are running ACARS decoding and the SBS1 and there is a flight number match between the two.  The BaseStation.sqb database is not updated with registrations computed locally from the hex code.

 

This information was taken directly from a post by Bev in the Plane Plotter Yahoo group.

 

User amended registrations

 

Over the last month there has been an increasing number of plots where the registration has an additional character which is not part of the registration. Examples of these characters are *  # and T, which sometimes convolute the full registration as there is insufficient space in the dialogue box. Sharers noted against these are ?? and other share codes. 

 

While Bev has now put a filter in place to remove extraneous characters from the registration field in the PlanePlotter server database there is the risk that other sharers' amended registrations can appear on your aircraft list and populate your own SQB database file.

Bev says that the answer to this problem is to configure PP not to accept shared registrations and to ensure that your own local SQB file of hex/registrations is complete and up to date and stripping these extra characters from your own aircraft database.  However if you have Hypersharing selected the data goes directly from user to user so there is no opportunity at this stage to clean up the data when it arrives.

 

Tools for editing the SQLite databases

 

There are two programs which can be recommended for editing SQLite databases with a GUI interface, and one which uses the command-line and therefore more suitable for use in command files (perhaps triggered automatically for regular overnight operations).  Both the GUI programs will accept SQL commands from external files so that you can run operations which others have written.

 

• The simpler of the GUI tools is SQLite Database Browser - download here: http://sqlitebrowser.sourceforge.net/
• A more complex program, which has both free and paid-for versions, is SQLite Expert Personal - download here: http://www.sqliteexpert.com/download.html

 

Until you become proficient with these programs, do be sure to take a backup of the database before you edit it, so that you can recover from any gross mistakes.  If you get an error message when double-clicking on a .SQB file, you may need to tell Windows which program to use to open files of type: .SQB.  You can do this with right-click, Open with... on the .SQB file from Windows Explorer, or use the technique described here, or here.

 

• A command-line program which I have used in a number of scheduled tasks is SQLite.exe - download here: http://www.sqlite.org/version3.html  I use this program in my Plane Plotter Reporting utility, so the command script there may provide some help.

 

There is information on merging databases in the file:  Update_basestation_sqb.pdf  in the Plane Plotter Yahoo group Files area.

 

You can ask for help about using these programs with Plane Plotter in the Yahoo Group.