| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Databases

This version was saved 8 years, 11 months ago View current version     Page history
Saved by David-Taylor
on April 30, 2015 at 4:37:44 pm
 

Note the withdrawal of the Manchester data, provide link to PlaneBase, and provide a link to a document describing how to merge 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: http://pp-sqb.mantma.co.uk/

  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.

 

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

 

Warning - the originator of this database changed the format as of March 2015, so it no longer suitable for general use as it has only a subset of the information required.It is hoped that this unilateral change will be reversed, and this page will be updated should that happen.  At the moment, the Manchester database can no longer be recommended.  It appears that the originator has decided to withdraw this database from public use, so the information below is for historical interest only, but is left in case the service is restored.  In the mean time you may like to investigate PlaneBase.

 

The latest BaseStation.sqb file can be downloaded from the Manchester TMA support site here.  The data is updated twice a month.  Data is obtained 'world wide' and consolidated into one large database file. Simply unzip the file from this archive and place it into your 'Log Files' folder within Plane Plotter (remembering to back up your original!).

 

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

 

Note:  This database file is for use by Plane Plotter users who do not use an SBS/RadarBox and need information to populate the appropriate areas of PP on desktop PCs or laptops. This is a .ZIP archive file - don't forget to extract the contents for correct use!

 

Updates:  If you have updated information, please e-mail it to:  admin@mantma.co.uk

 

 

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.

 

 

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.

 

 

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.

 

 

 

Comments (0)

You don't have permission to comment on this page.