AirNav Systems Forum

AirNav RadarBox and RadarBox24.com => AirNav RadarBox and RadarBox24.com Discussion => Topic started by: testmonkey on August 29, 2008, 10:36:58 PM

Title: Route info sharing
Post by: testmonkey on August 29, 2008, 10:36:58 PM
Is anyone out there interested in using this thread to upload and share route information. I'm thinking along the lines of people saying which airline they are going to try and compile missing route information for so we don't duplicate each others work and then posting once they've got what they can. It's going to be tricky in many cases and those that want to take part or download will have to accept that there may be errors but not sure what we can do about that.

As an example I'm currently trying to compile as many route details as possible for Thomas Cook UK (TCX) flights but it's a hard task finding the data and even more difficult to verify it but not sure what other avenue we have for filling in the blanks so to speak.

Once I'm done I'll be happy to post for others to add to their own routes table with the obvious disclaimer that it may not be 100% accurate. Not ideal I know but due to the lack of information available in some cases if we exclude every route we can't confirm as 100% accurate we'll still be left with a lot of missing routes.

Format wise it would be good if we all stuck to a consistent layout so I would suggest all routes are uploaded as csv files with the columns FN, NO, ND, NV set as a header record. Might be good if we stick to some sort of naming convention as well so possibly [flight code]_[forum member]_[version number] so for above the first upload would be TCX_testmonkey_v1.csv. Would also be useful if all subsequent versions were complete replacements not just additions for ease of management.

Any thoughts?
Title: Re: Route info sharing
Post by: tarbat on August 30, 2008, 07:38:30 AM
Is there a particular problem with Thomas Cook flights then?  I've gathered around 170+ TCX routes using my regular method of using Planeplotter and FD6 to get routes from FlyteCom, FlightStats, and FlightAware.  CSV attached.

As a general comment, part of the problem is that routes change so frequently that anything posted could be out of date within a few weeks.  For routes to be correct you need to be checking them frequently.

I'm happy that we all post routes though.  For info, I've also attached the 2000+ routes I've added over the last couple of weeks for flights over Scotland and northern England - again, derived using PP/FD6.  Many are new routes, but others replace erroneous routes already in the routes table.

Suggest we include all the fields in the routes table.  Including the CH field will give people an indication of how "stale" the route information is.
Title: Re: Route info sharing
Post by: Deadcalm on August 30, 2008, 07:42:30 AM
Now, THAT'S a good idea in principle.  I've been trying to do something similar with Ryanair flights - I've got a vast list, but actually trying to pin down the alphanumeric callsigns to specific routes is a nightmare.  One way of achieving this is with guesswork - watching traffic passing me between Ryanair serviced airports in the UK and those in France and Spain, and trying to discover the likely destinations is fun but time consuming.  I use all the arrival/departure pages on the relevant airport sites and try to marry up the times.  I expect I'll soon get tired of that.  My wife thinks I'm mad, but has assisted by producing an Access database...

DC
Title: Re: Route info sharing
Post by: tarbat on August 30, 2008, 07:54:10 AM
DC, I've added 141 Ryanair routes if you're interested.  They're all in the tarbat.csv attachment in my previous post.  If you're primarily interest in flights in your own area, then I'd recommend using Planeplotter and FD6 to compile a list automatically.
Title: Re: Route info sharing
Post by: tarbat on August 30, 2008, 08:34:50 AM
A couple of you have asked how to import routes into ANRB.  The approach I use is with SQLite Maestro (http://www.sqlmaestro.com/products/sqlite/maestro/):
1. Open the routes table (in NavData.db3)
2. Use the import wizard
3. Select the csv file
4. Select all the fields
5. Select FN as the primary key
6. Select the "Update or Insert" option
Title: Re: Route info sharing
Post by: Deadcalm on August 30, 2008, 09:31:53 AM
Thanks Tarbat, I'll download and take a look.

DC
Title: Re: Route info sharing
Post by: testmonkey on August 30, 2008, 09:58:30 AM
Is there a particular problem with Thomas Cook flights then?  I've gathered around 170+ TCX routes using my regular method of using Planeplotter and FD6 to get routes from FlyteCom, FlightStats, and FlightAware.  CSV attached.

As a general comment, part of the problem is that routes change so frequently that anything posted could be out of date within a few weeks.  For routes to be correct you need to be checking them frequently.

.......

Suggest we include all the fields in the routes table.  Including the CH field will give people an indication of how "stale" the route information is.

Hi tarbat,

No real problem with TCX, I use FlyteComm to lookup 'live' flights and they're all on there it was mainly bought about by the upgrade to RB2009 as AirNav seem to be lacking most TCX flights in the data they're obtaining. It became apparent looking at the auto populated route details that there are specific airlines that they don't have much info on, SN Brussels being another example.

In terms of flight tracking, untl I got my RB a few weeks ago for me it was limited to my scanner and looking out of the window :) Luckily I live under the flight paths for both Birmingham and Coventry so get to see a far bit cracking off. Because I take my RB to various localities I'd like to have my tables as populated as possible for offline use.

I agree that flight routes change often and it really is going to be a pain to keep everything up to date until such time (if ever) that ALL such things become easily obtainable through the web. In terms of airlines like TCX it shouldn't be quite so bad as the routes should be pretty much set for each season. I know they still use the same codes for routes I flew with them a few years back.

The sharing idea really came about as it was apparent from the outset that a single person could never keep up on their own. I suppose I should look into things like Planeplotter and FD6. My problem is that I'd like to be able to go to the other end of the country and still have route information for flights that I'd never pick up at home via my RB. I have put a suggestion forward to AirNav that once the network route population is up and running properly they could consider introducing a change in the next software version that allows users to utilise that information to populate their local routes table. Obviously this would have to be a local process as there's no way we could all be hammering the server. My idea is to utilise a process whereby if a user clicks on a flight in the Network Flights list the route information already downloaded for that flight is checked against the local routes table and added / updated if necesary. I haven't had any reply to the suggestion yet though.

I terms of adding the CH field to the shared info I had decided to exclude it on the basis that route information may not be 100% correct. Without it each person's RB would try to update the information the first time the flight was seen and would then correct any errors that may have been distributed (if AirNav had the data of course). I can see that it would be useful for people to see when that route had been compiled though and they don't have to import that field to their table if they dont wish.

Thanks for the donwloads Tarbat. Once I've finished my TCX list I'll upload it - stands at about 250 routes so far.

Deadcalm - my wife thinks I'm mad as well. If I spend too long doing this I'll have a nicely populated routes table but no marriage LOL :)
Title: Re: Route info sharing
Post by: tarbat on August 30, 2008, 12:58:35 PM
Maybe Airnav support could also use the routes posted here to add to their central database of routes.
Title: Re: Route info sharing
Post by: testmonkey on August 30, 2008, 01:58:29 PM
I think they'll probably decline unless we can guarantee 100% accuracy though no reason why someone can't indicate the level of accuracy as they know it for each upload and they could take their pick based on that.
Title: Re: Route info sharing
Post by: AirNav Support on August 30, 2008, 02:34:43 PM
While the accuracy is important, its more to do with would it be updated continuously through a dynamic feed so you won't have out of date routes etc.

We are discussing the different ideas internally.
Title: Re: Route info sharing
Post by: testmonkey on August 30, 2008, 03:03:20 PM
We are discussing the different ideas internally.

Great stuff, thanks for the feedback. Be interesting to see which way it develops.
Title: Re: Route info sharing
Post by: John Racars on September 04, 2008, 11:34:45 AM
Hi All,

Verry good idea this Route info sharing. Maybe I can attribute with many routes I added the last time.

PE: KLM (ICAO-callsigns included), TRA(summer 2009).

Please let me know of you are interested in and I will see what I can do.
Title: Re: Route info sharing
Post by: DaveReid on September 04, 2008, 01:27:56 PM
I think they'll probably decline unless we can guarantee 100% accuracy though no reason why someone can't indicate the level of accuracy as they know it for each upload and they could take their pick based on that.

It's relatively easy, where you have an ADS-B aircraft, to do a reasonableness check on the route that comes back from any of the flight lookup services. 

That's the approach I've taken with my own FlightRouteLookup server at http://www.flightroutelookup.com/FlightRoute/ - when a client application posts a query with a flight number, the query includes the ADS-B lat/lon, where available, and that gets used to check that the flight route being returned is a feasible one given the location of the aircraft.  It's not foolproof, but it does serve to highlight where a routing has changed for a given flight number.

HTH
Dave
Title: Re: Route info sharing
Post by: tarbat on September 04, 2008, 01:41:20 PM
Dave, maybe Airnav can do a deal with you to use your flightroute service, in the same way that the new FD7 beta does.
Title: Re: Route info sharing
Post by: John Racars on September 04, 2008, 05:32:17 PM
A couple of you have asked how to import routes into ANRB.  The approach I use is with SQLite Maestro (http://www.sqlmaestro.com/products/sqlite/maestro/)

Hi Tarbat,

Thank you for that tip!

Today I downloaded SQLite aswell and now I am experimenting with my route database I added with verry much flightinfo in the period version 1.5 was in use. Now I will try to copy the routedatabase from version 1.5 into 2.0 so that all the routeinformation from KLM, WZZ, BAW etc. will be displayed as I added this in the database. At this moment I see, unfortunately, verry much flights with incorrect or without flightinformation whereof I sure the correct information in my "1.5" routedatabase think to have..

But, in general, the version 2.0 looks to work verry well and stable. It is running the whole day by me until sofar without any problem.

I have made a backup from the routedatabase from version 1.5.

Do you, or any other member of this forum, have experience with this? If so, some help or more tips will be welcome.

Thanks in advance!
Title: Re: Route info sharing
Post by: tarbat on September 04, 2008, 05:41:53 PM
What I usually do if I want to copy data from one database to another is use the EXPORT/IMPORT functions in SQLite Maestro.

So, run a query that selects all the records you want to export from your old v1.5 routes table.  Export that to XML.  Then, import that XML into the v2.0 routes table.
Title: Re: Route info sharing
Post by: testmonkey on September 04, 2008, 07:09:55 PM
I have made a backup from the routedatabase from version 1.5.

Do you, or any other member of this forum, have experience with this? If so, some help or more tips will be welcome.

Hi John,

I do a similar process to Tarbat only I export into csv instead as it allows me to easily switch things around in Excel though you may need to split the file if there's more than 56k records in it.

You'll notice looking at the db's that the Routes table in v2.0 now has an extra CH column added. This column is used to add a 'last checked' datestamp to the record so that the RB knows whether to check a flight ID against the server or not. If you're adding your own routes to the table from v1.5 it would be worthwhile adding a datestamp to them before you import them so that the RB software doesn't try to overwrite them with info from the server. The datestamp is in the format YYYYMMDDhhmmss

It might be worth keeping a copy of any routes you add so that every few weeks you can change this datestamp to 'today' and re-mport thereby ensuring your information doesn't get overwritten.

If you want to upload your routes to this thread they would be a welcome addition. I've been really busy this week but will get back to working on my TCX routes soon and upload them when finished.

Cheers
Title: Re: Route info sharing
Post by: John Racars on September 04, 2008, 08:04:31 PM
Hi Testmonkey,

Thank you for your kind reaction!

I am almost the whole day trying to understand SQLight and "playing" with the routedatabase; until sofar without any succes. Maybe later I will more understand how to make the "tricks" and make my additions and edits made in version 1.5 useable in 2.0

I am also supprised about the "datastamp". An other new item for me. What should the date be to prevent that RB will overwrite my data and that I have to start over the whole procedure again? Why should RB do this anyway when a record is in (my) route-database? If I may say so: when at any moment RB perceives that there is no routeinformation for a flight in the routedatabase then should RB lookup in their own databases for routeinformation and write it to the routedatabase. Is that a strange thought of me?

And, ofcourse, when it is possible to me I will share my flightdata with you and other interested forummembers!

However, much to study about to see how to manage this "routeproblem". A difficult problem for me to come out.
Title: Re: Route info sharing
Post by: tarbat on September 04, 2008, 08:20:40 PM
For the datestamp, I use (for today for example) - 20080904010000.  i.e. 01:00am on 4th Sept 2008.  Airnav have stated that routes will be considered "live" for 1 month, so in a month's time any routes I've added today may get changed by Radarbox.  Although I'm not sure this process is working exactly as expected, and Airnav have said they're looking in to this.

In the meantime, I keep a separate table/database of the 2500+ routes I've updated so far, so if need be I can export them again and update the Radarbox routes table if they get overwritten.

Any questions about using SQLite Maestro, just ask.
Title: Re: Route info sharing
Post by: testmonkey on September 04, 2008, 08:45:25 PM
If I may say so: when at any moment RB perceives that there is no routeinformation for a flight in the routedatabase then should RB lookup in their own databases for routeinformation and write it to the routedatabase. Is that a strange thought of me?

That's exactly what RB does since v2.0 - when it picks up a flight ID it checks against your local route table to see if you have it. If you either;
1. Don't have it
2. Have it but no value is set for CH
3. Have it and the datestamp in CH is more than 1 month old (as Tarbat explained)
it will check the server at AirNav and add / update as necessary IF they have the information.

Prior to v2.0 of course the route table didn't get updated unless the user did it themselves. As Tarbat has said there seems to be a number of issues with this new feature that AirNav are looking into but in time it should work really well.

I'm pretty new to SQLite myself but have picked up as much as I need to know to get by. If you've got any questions fire away and we'll be happy to help or provide SQL code for you to learn from. In the meantime just take a copy of the NavData.db3 database, call it something new and use it to play around with to help you learn.

If you visit this thread that I started http://www.airnavsystems.com/forum/index.php?topic=1252.0 it's from when I first tried to learn SQLite. There's lots of my code there that you can use to play around with and learn from.
Title: Re: Route info sharing
Post by: testmonkey on September 09, 2008, 07:36:56 PM
Well I finally got round to finishing my TCX flights list. I can't guarantee 100% accuracy but I've tried hard to verify each flight from more than one source where possible.

The file contains 970 flight ID's although a few are duplicates such as TCX064K / TCX64K (flight number is 064 but the crew often just enter 64 instead).

Cheers
Title: Re: Route info sharing
Post by: DaveReid on September 09, 2008, 08:30:41 PM
Well I finally got round to finishing my TCX flights list. I can't guarantee 100% accuracy but I've tried hard to verify each flight from more than one source where possible.

Thanks - with your permission I'll include the list on www.flightroutelookup.com. 

Are you sure about the routeing for TCX255K/255L, shown as Gatwick to/from Izmir/Selcuk-Efes (LTFB) ?  The BAA timetable shows then as simply "Izmir", which usually means Adnan Menderes (LTBJ).

Quote
The file contains 970 flight ID's although a few are duplicates such as TCX064K / TCX64K (flight number is 064 but the crew often just enter 64 instead).

Those will be taken care of automatically - FRL doesn't mind whether your query specifies TCX64K or TCX064K, it will find the same record in either case.
Title: Re: Route info sharing
Post by: testmonkey on September 09, 2008, 08:50:08 PM
Hi Dave,

Please feel free to use the list.

As regards TCX255K/L I'll bow to your superior knowledge. That's one of those flights I'm not familiar with myself and could only find the source as Izmir but no confirmation as to which of the airports it was. I've corrected it in the new file attached.

Rgds
Title: Re: Route info sharing
Post by: John Racars on September 10, 2008, 09:03:15 PM
Hi Testmonkey,

New mistery again. I think someone hase "hacked" your TCX list.

This evening on my screen BUT NOT listed in yours: TCX791K=EGGD-LGRP

But how get I my flight info into the database like you did with the TCX? TCX flights OK and verry nice to see. But what about BAW, AFR, DLH, KLM, DAT, DAL, NWA, COA, AAL, JAL, etc etc etc etc Verry much of this flights are not or not correct on the screen. In other words, should not get Airliners like I just mentioned not get any priority to give correct information?
Title: Re: Route info sharing
Post by: testmonkey on September 10, 2008, 11:26:24 PM
Hi Testmonkey,

New mistery again. I think someone hase "hacked" your TCX list.


No mystery! Nobody has hacked my file :) My TCX list isn't 100% complete but I'll keep adding to it as I find new flight ID's and post occasional updates. Glad to see that 791K got auto added - my RB picked it up as well! You may want to add TCX791L = LGRP-EGGD as well (due in 06:20)

Not sure what you mean with BAW, AFR etc. etc. If you are asking how to add them just make your own list and add the same way as you did my TCX list. If you're asking why the information displayed is incorrect even though you've got the correct information in your routes table then that is a known issue and will be fixed in a future update to the RB software.

I know you're getting frustrated with some of the routes problems but stick with it. It's a new feature and will only improve over time.

Are you using a SQL editor to import routes (like my TCX file) or are you entering them manually?
Title: Re: Route info sharing
Post by: John Racars on September 11, 2008, 06:11:32 AM
Quote from: testmonkey link=topic=1356.msg10828#msg10828Are you using a SQL editor to import routes (like my TCX file) or are you entering them manually?[/quote

Hi Testmonkey,

Last week I purchased a SQL licence. I stil do not know how to get your routes in my database with the help of this programm. May be there exists a step-by-step-instruction how to do this? However, any help will be verry welcome.
Title: Re: Route info sharing
Post by: testmonkey on September 11, 2008, 08:22:47 AM
Hi John

First the criticism - why did you purchase a SQL license when you don't know SQL? I was going to suggest that you downloaded a copy of SQLite Database Browser which is FREE and will do everything you need it to do whilst you learn SQL. Anyway not to worry :)

You should be able to adapt the instructions below to apply to whichever program you have installed.

1. Create 2 copies of the NavData.db3 database from the 'AirNav Radarbox 2009\Data' folder and place where you wish (keep one safe as a backup)
2. Start your program and use the File - Open or Database - Open function to open one of your copies of NavData.db3
3. Find the Import or Import From function in your program. There should be an option to Import Data From CSV File. Browse to my file and import the data from it. Somewhere in the function you should be asked to name your new table - call it 'Routes2'. You should also have an option to 'Extract Field Names From First Line' or similar - tick or set that option to Yes / True.
4. You should now have a new table in your database called Routes2 that contains my TCX route data.
5. Find the area in your program where you can enter SQL code and paste the following:
REPLACE INTO routes (FN,NO,ND,NV,CH)
SELECT FN,NO,ND,NV,CH
FROM routes2
and click the Execute, Execute Query, Run Query or whatever button you have to perform the query above.
6. If you now browse your routes table in the program you should find that my data has been added to it.
7. Now run the following query to set your database back to its standard format:
DROP TABLE routes2
8. If you browse your database you should now see that the Routes2 table you created no longer exists.
9. Save the database.
10. Copy your edited NavData database back into the \Data folder to overwrite the original.
11. Start up RB. If all starts okay you should now see the TCX flights getting populated with flight numbers (MyFlights only). If RB doesn't start copy the backup version of NavData back to restore the original.

Did you read my other thread (http://www.airnavsystems.com/forum/index.php?topic=1252.0)? If you take a copy of NavData and play around with it using the code in that thread you should find you soon get to understand SQLite. I hardly knew any of it a few weeks ago but it's not that complicated to understand and you can't do any harm if you're using a copy of the database.

Let me know how you get on.
Title: Re: Route info sharing
Post by: John Racars on September 11, 2008, 12:31:21 PM
Thank you Testmonkey! I will try out all this tonight when I am at home. I will keep you informed.
Title: Re: Route info sharing
Post by: nortonbeak on September 11, 2008, 03:55:53 PM
You should be able to adapt the instructions below to apply to whichever program you have installed.


I have just amended my NavData database using your excellent set of instructions. It was really simple and straightforward :-)

What happens if there are matching entries already in the Routes database to those in the file which you are adding to it?

Title: Re: Route info sharing
Post by: testmonkey on September 11, 2008, 04:37:22 PM

I have just amended my NavData database using your excellent set of instructions. It was really simple and straightforward :-)

What happens if there are matching entries already in the Routes database to those in the file which you are adding to it?


Glad it was of use :)

Using the SQL code in my instructions any entry with a matching FN value that already exists in the database will be overwritten with the new data from the file.
Title: Re: Route info sharing
Post by: nortonbeak on September 11, 2008, 04:45:11 PM
Using the SQL code in my instructions any entry with a matching FN value that already exists in the database will be overwritten with the new data from the file.

That will be really good :-)

I might now be tempted to dip my feet further into the SQL pool.
Title: Re: Route info sharing
Post by: Deadcalm on September 11, 2008, 06:36:46 PM
Testmonkey, I've been diligently following your instructions using SQLite Database Browser, and all seems to go well up to the "Execute Query" stage.
When I hit the button, all that happens is a "No Error" message is displayed in the 'Error message from database engine' box.  When I go back to  the Routes table in the Database Structure, your data hasn't been added.  Any ideas, please?

DC
Title: Re: Route info sharing
Post by: testmonkey on September 11, 2008, 06:44:53 PM
Testmonkey, I've been diligently following your instructions using SQLite Database Browser, and all seems to go well up to the "Execute Query" stage.
When I hit the button, all that happens is a "No Error" message is displayed in the 'Error message from database engine' box.  When I go back to  the Routes table in the Database Structure, your data hasn't been added.  Any ideas, please?

DC

The 'No Error' message is correct - it means the code ran successfully.

The updated records are added to the end of the routes table and you should be able to see them by going to the Browse Data tab and choosing the Routes table. Check how many records are in the table before and after you execute the code using the Browse Data tab (although they should already be in there now so no change will happen if you run the code again).
Title: Re: Route info sharing
Post by: Deadcalm on September 11, 2008, 06:57:45 PM
Sorry, Testmonkey - you're quite right!  I was expecting whizzy things to happen when I pressed the "Execute Query" button, and I didn't go far enough when I thought I was at the end of the Routes database.  All is well, thanks for your efforts.  You may go to the top of the class!

DC
Title: Re: Route info sharing
Post by: John Racars on September 11, 2008, 07:01:56 PM
3. Find the Import or Import From function in your program. There should be an option to Import Data From CSV File. Browse to my file and import the data from it.

Hello Testmonkey,

After doing so only the field FN was filled with your TCX flightid's (more then 950 record where added). The fields NO, ND, NV and CH become "nill". Also only flightnumbers where imported so it looks.

For your information: as you know I am "newbe" with SQLLight Maestro also I do something wrong with the settings I have to make.

Any tips are verry welcome.
Title: Re: Route info sharing
Post by: testmonkey on September 11, 2008, 07:14:21 PM
For your information: as you know I am "newbe" with SQLLight Maestro also I do something wrong with the settings I have to make.

Don't worry John, we all have to start somewhere - I'm still a newbie to it myself!

Are you saying that you've now got a 'routes2' table that only contains flight numbers?

I've never used SQLite Maestro as you have to pay for it. Please post a step by step list of what you did including which options you've chosen in the program and I'll see if I can find the error.
Title: Re: Route info sharing
Post by: testmonkey on September 11, 2008, 07:57:26 PM
I might now be tempted to dip my feet further into the SQL pool.

Go for it. Well worth the effort for tidying up your Aircraft table.

If you want any help re scripts just shout. As I say I'm not a SQL expert but have got to grips with enough to do what I need to for these purposes. Here's a starter for you:

Run this script
CREATE TABLE aircraft2
(
MS varchar(6),
AR varchar(50),
AT varchar(80),
AN varchar(80),
AC varchar(80),
CN varchar(80),
PT varchar(80),
LK varchar(80),
PT2 varchar(80),
LK2 varchar(80)
)


This will create a second aircraft table called 'aircraft2' with the same structure as the original (you can find the structure for a table by looking under the Database Structure tab)

Now run this script
INSERT INTO aircraft2 (MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2)
SELECT MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2
FROM aircraft
WHERE AT LIKE '...'


This will make a COPY (in aircraft2) of all the records (in aircraft) where the aircraft short type is set to ... - these aircraft won't display a sillouhette when seen in the list within RB. Browse through this table and just look how many there are! If you double click on any cell when browsing a new box will open that allows you to manually change the data within it. Click the 'Apply Changes' button to save any changes you make.

To put your changes back into the original aircraft table run
REPLACE INTO aircraft (MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2)
SELECT MS,AR,AT,AN,AC,CN,PT,LK,PT2,LK2
FROM aircraft2


To delete the records from the aircraft2 table use
DELETE FROM aircraft2 to delete all records or add a second line such as
WHERE AC LIKE 'British Airways' to only delete records where the company (AC field) is set to British Airways. You can also use wildcards:
WHERE AC LIKE 'British%' any company starting with 'British'
WHERE AC LIKE '%Airways' any company ending with 'Airways' or
WHERE AC LIKE '%Air%' any company name containing the string 'Air' anywhere in the AC field. The search string isn't case sensitive by the way. In fact neither is the code but it's easier to understand if written like this.

To delete the aircraft2 table just use
DROP TABLE aircraft2 - no need to delete the records from it first

Modifying the above you could make a copy of all the aircraft that have the AT field set as 'BE4X' into aircraft2 for you to edit. There's no sillouhette for those and they should be 'BE40'. To change all records where the AT field is BE4X you could run the following script
UPDATE aircraft2
SET AT = 'BE40'
WHERE AT = 'BE4X'

- missing the WHERE line off would set the AT field for EVERY record currently in aircraft2 to BE40.

Using the above you should have everything you need as a starting base to write your own scripts to do pretty much most changes you'll want to make. Take a copy of NavData, rename it, stick it somewhere out of the way and use it to practise on. Just don't blame me if you lose hours of your life tweaking the database :)

Oh yes, Golden Rule No. 1 - ALWAYS make a backup copy of NavData before editing just in case. I always make 2 copies - 1 as a backup and 1 to edit therefore never directly editing the one in the RB\data folder.
Title: Re: Route info sharing
Post by: John Racars on September 11, 2008, 08:32:57 PM
Please post a step by step list of what you did including which options you've chosen in the program and I'll see if I can find the error.

Hi Testmonkey,

I am happy to say: this is not necessary because I added your TCX database succesfully into my local "NavData.db3" file.

Thank you verry much again for all the help and tips until sofar!
Title: Re: Route info sharing
Post by: testmonkey on September 11, 2008, 08:37:29 PM
Thank you verry much again for all the help and tips until sofar!

You're welcome :)
Title: Re: Route info sharing
Post by: John Racars on September 11, 2008, 08:45:48 PM
If you are interested, this (new) ones appeared on my screen this evening:

TCX779K,EGCC,LTBJ,,20080909121314
TCX423L,LEAL,EGKK,,20080909121314
Title: Re: Route info sharing
Post by: testmonkey on September 11, 2008, 09:18:57 PM
If you are interested, this (new) ones appeared on my screen this evening:

TCX779K,EGCC,LTBJ,,20080909121314
TCX423L,LEAL,EGKK,,20080909121314


Thanks John.

TCX779K/L are original ones that were already in the database. I didn't include any of those in my file unless I had confirmed / updated information for them.

I've now added TCX423K/L to my file but interestingly it appears it should be LEAM (Almeria) not LEAL (Alicante)! I'll post a new file in a week or so when there's a few more updates.

Thanks again.
Title: Re: Route info sharing
Post by: John Racars on September 11, 2008, 10:46:26 PM
I've now added TCX423K/L to my file but interestingly it appears it should be LEAM (Almeria) not LEAL (Alicante)!

Oops, that was wrong information from my site. (Type error...) It should be LEAM in this case indeed so you are totaly right Testmonkey! Sorry for any caused confusion.
Title: Re: Route info sharing
Post by: testmonkey on September 11, 2008, 10:50:42 PM
No problem :)
Title: Re: Route info sharing
Post by: John Racars on September 11, 2008, 11:06:06 PM
Hi Testmonkey,

An other interesting question I think. Some moments ago flight TCX162K appeared on my screen WITHOUT flightinformation. I checked your and mine databases and in both the flight is there as EGCC-LTBS.

There after I closed AirNav Radarbox software and restarted it again. Flight TCX162K appeared again and now......WITH the correct information. So I am asking me: where get AirNav this information from? From my local database or from an other outside database?

If the information is not from my local database why should I add your TCX database in to mine if RB ignore mine local database?
Title: Re: Route info sharing
Post by: testmonkey on September 12, 2008, 12:23:32 AM
Did you have Network flights enabled?

If so there is a bug in the software which means if a flight is seen in your network list before it appears in MyFlights the route information doesn't get displayed. Untick the 'Process Hardware Flights' box at the bottom of the MyFlights page and then re-tick it again to re-enable MyFlights. The route information should now be displayed.

AirNav are aware of the issue and it should be fixed in the next update.

The information is taken from your local database (for MyFlights) and that's why you need my list of TCX flights :)
Title: Re: Route info sharing
Post by: John Racars on September 12, 2008, 08:40:41 AM
If so there is a bug in the software which means if a flight is seen in your network list before it appears in MyFlights the route information doesn't get displayed. Untick the 'Process Hardware Flights' box at the bottom of the MyFlights page and then re-tick it again to re-enable MyFlights. The route information should now be displayed.

Today I added my "WZZ" flights into my localdatebase at the sameway as I did with yor TCX-database.

Edit: during this morning TCX-flights (p.e. TCX608K) apearring on my screen WITHOUT route information again.

Then I did as you described above: until sofar without any result. In other words: NO flightinformation.

For those who are interested to experiment with it, the "WZZ"-file comes with this.
Title: Re: Route info sharing
Post by: testmonkey on September 12, 2008, 10:39:09 PM
during this morning TCX-flights (p.e. TCX608K) apearring on my screen WITHOUT route information again.

Then I did as you described above: until sofar without any result. In other words: NO flightinformation.

Did you still have the network flights turned on when you did the above? If so did flight TCX608K show amongst the network flights when your local flights were turned off?

That's the only reason I can think of that the flight route still didn't get shown. Not to worry, it will hopefully be working as expected after the next update.

Thanks for the Wizz routes list. They fly into one of my 2 local airports and I often watch them pass over my office at around 1,500ft when I'm at work. They were on my list as one of the primary airlines to get done because of this.
Title: Re: Route info sharing
Post by: testmonkey on September 12, 2008, 10:39:36 PM
Does anybody have route information for Brussels Airlines as I'd be very interested in that? It's one of the major missing ones as far as MyFlights go for me. I've taken a brief look at their routes but I'm struggling to find any consistent tie-up between the (passenger) flight numbers and (ADS-B) flight id's at first glance.

Cheers
Title: Re: Route info sharing
Post by: John Racars on September 13, 2008, 09:09:55 PM
Quote
author=testmonkey link=topic=1356.msg10914#msg10914Not to worry, it will hopefully be working as expected after the next update.

Hi Testmonkey,

Do you have any idea when this next update will come? Can't they bring us a bugfix or so that we can see our route informations as they are in our local database?
Title: Re: Route info sharing
Post by: testmonkey on September 13, 2008, 09:39:34 PM
Do you have any idea when this next update will come? Can't they bring us a bugfix or so that we can see our route informations as they are in our local database?

That's one for Support or Development to answer - I'm just another user like yourself (and a fairly new one at that seeing as I've only had my RB for a few weeks). It would appear that updates have been fairly frequent though so I wouldn't expect it to be too long.

On another note I finally opened your WZZ file tonight. First thing I noticed is that it's not actually a csv file. CSV stands for Comma Seperated Values but your file is seperated by semi-colons (;). Not really a problem though and I still appreciate the effort you put into it. I'm going to do a little research as I may be able to massively increase that list :)
Title: Re: Route info sharing
Post by: John Racars on September 13, 2008, 09:50:05 PM
On another note I finally opened your WZZ file tonight. First thing I noticed is that it's not actually a csv file.

I am verry sorry about that. I corrected this immediately and sending you a new file herewith.
Title: Re: Route info sharing
Post by: testmonkey on September 14, 2008, 01:32:38 AM
Not a problem John, I just mentioned it for consistency. You could have called it .txt as it's easy enough to work with.

I've started updating your WZZ file and it looks like I should be able to expand it to 500+ flight ID's :)
Title: Re: Route info sharing
Post by: testmonkey on September 15, 2008, 12:02:57 AM
I've built a new Wizz Air (WZZ) file built on the basis of the one by John Racars (thanks John). When I analysed John's file a pattern became clear to me so I then checked a few live airport boards to confirm how they matched up to the actual flight numbers. It appeared there was a direct conversion that could be applied to match the flight ID's to the flight no's.

Next step was to search through RB and SBS-1 user logs to collect as many Wizz Air flight ID's as possible that had been seen over the last month. Checking some of these confirmed my theory on how the relationship between the flight ID's and flight no's were generated to the point that for all the ones I could confirm route data for I didn't find a single instance that didn't comply with my theory.

On that basis I've built a file that contains 6129 flight ID's!!! Of those approx. 600 have been seen at some point and the others match the criteria of 'possible' flight ID's that could appear.

For anyone who is performing any kind of flight look-up service the relationship works like this:   Flight ID WZZ106G = Flight No W6106

WZZ -> W6 (some theories suggest W60 but I couldn't find anything to confirm this)
106   -> 106 (some flights appear to have 4 digit flight numbers - crew error?)
G      -> alpha character (if present) is dropped

At first there appeared to be some logic to the last alpha character used but as I researched more flights this theory got disproved. I'm sure there are probably only limited ones used for each route but I've included all possible combinations as I couldn't find any conflicts from doing so.

As usual any updates or corrections gratefully received :)
Title: Re: Route info sharing
Post by: DaveReid on September 15, 2008, 07:02:18 AM
For anyone who is performing any kind of flight look-up service the relationship works like this:   Flight ID WZZ106G = Flight No W6106

WZZ -> W6 (some theories suggest W60 but I couldn't find anything to confirm this)
106   -> 106 (some flights appear to have 4 digit flight numbers - crew error?)
G      -> alpha character (if present) is dropped

At first there appeared to be some logic to the last alpha character used but as I researched more flights this theory got disproved. I'm sure there are probably only limited ones used for each route but I've included all possible combinations as I couldn't find any conflicts from doing so.

As usual any updates or corrections gratefully received :)

W6 is indeed the IATA two-character prefix for Wizz Air, and I would agree with the theory that the alpha suffix doesn't appear to signify anything relating to the actual route.  I've seen a few 4-digit WZZ callsigns, usually WZZ9xxx, although of course they don't have a letter suffix as the flightplan only allows for 7 characters in total.

HTH
Dave
Title: Re: Route info sharing
Post by: testmonkey on September 20, 2008, 08:08:34 PM
Thanks for the confirmation Dave, much appreciated.

Attached is an update to my Thomas Cook TCX list which includes a few updates and also 1 or 2 amendments. The file now contains 982 flight ID's
Title: Re: Route info sharing
Post by: Deadcalm on September 20, 2008, 08:54:39 PM
Testmonkey, I've been adding your TCX data as you post it, but the routes don't seem to show up on the map.  For example, I'm watching TCX516K (live traffic)just south of the Pyrenees at the moment, and although it's in your data, there is no departure/destination airfield shown in the target on the map.  Any ideas?

DC
Title: Re: Route info sharing
Post by: testmonkey on September 20, 2008, 11:45:51 PM
Testmonkey, I've been adding your TCX data as you post it, but the routes don't seem to show up on the map.  For example, I'm watching TCX516K (live traffic)just south of the Pyrenees at the moment, and although it's in your data, there is no departure/destination airfield shown in the target on the map.  Any ideas?

DC

Hi DC

I take it when you refer to live traffic you are referring to MyFlights and not Network Flights? If so it should be showing the route information but there are a couple of things to consider / check:-

Do you have Network Flights turned on or have you recently had them turned on during the duration of this flight? If so there's a high probability that the flight has previously been listed under Network Flights and therefore the route information won't show due to the bug that AirNav have been made aware of.

If it's not that can you use the Database Browser within RB to check that the flight exists in the Routes table. If it's not there then obviously the import must have failed for some reason. Might be worth trying it again. If the import isn't working send me a PM and we'll see if we can step through the process you're doing to find where the error is.

If it's neither of the above then I'm at a bit of a loss as to why it's not showing. Certainly if it's in your routes table it should be showing up. I've just checked my own MyLog and that exact flight is listed with the route information so there shouldn't be a problem with that individual record within my TCX file.

Cheers
Title: Re: Route info sharing
Post by: Deadcalm on September 21, 2008, 07:11:16 AM
Thanks, Testmonkey.  Just briefly looking at another one this morning - TCX983K.
This is live (via my antenna) - no route, doesn't appear in MyFlights, yet it's in the database.
Network is on, but have now turned it off.
Restarted RadarBox in network off state, and the route appears.  I assume, therefore, that this is the bug in the database software to which you refer.
I wonder if Airnav are any closer to fixing it, if so?  If my memory serves me right, this was highlighted in the previous version and was apparently supposed to have been fixed for v2.0.  I have long been dubious about the routes, and hope that this can be addressed soon.

DC
Title: Re: Route info sharing
Post by: testmonkey on September 21, 2008, 06:29:01 PM
I assume, therefore, that this is the bug in the database software to which you refer.

That's the one. No need to restart RB though - just uncheck and then re-check the Process Hardware Flights tickbox after turning Network Flights off.

I wasn't aware that it existed in v1.5 but then I wasn't paying so much attention to the routes until this version and the autopopulate function. Plus I only had my RB for a couple of weeks before v2.0 was released anyway so maybe didn't spot it if it was in that version. Glad it's not a problem with my file anyway :)
Title: Re: Route info sharing
Post by: PEPITO on September 21, 2008, 11:02:02 PM
I haven’t followed the whole line but just to drop a story in.

A few weeks ago one of our pilots inserted in the FMC BONJOUR instead of the flight ID. The flight was to France and he thought that only our ops would see that as we have ACARS com with ops. Well the fact was that he did a broadcast of BONJOUR during the whole flight.

Two days later we received a formal complaint from the French authorities.

This is to say that a call sign could be inserted wrong by the crew and second don’t trust pilots!!
Title: Re: Route info sharing
Post by: Allocator on September 22, 2008, 02:06:58 PM
Now here is an interesting one that I haven't seen before.

CWL93, an RAF HS125 Dominie with a route that resolves from the military callsign -
Cranwell to Kinloss.  I presume that this is an airways training flight and therefore the callsign is in the system.

(http://farm4.static.flickr.com/3009/2879263590_11ea901916.jpg)

And here it is in the Routes table having populated today:

(http://farm4.static.flickr.com/3285/2878433187_f11ce37d88.jpg)

Title: Re: Route info sharing
Post by: DaveReid on September 22, 2008, 04:48:08 PM
Now here is an interesting one that I haven't seen before.

CWL93, an RAF HS125 Dominie with a route that resolves from the military callsign -
Cranwell to Kinloss.  I presume that this is an airways training flight and therefore the callsign is in the system.

I very much doubt that you can reliably tie a CWLxx callsign to a specific route.

If CWL93 was always Cranwell to Kinloss, I wouldn't have 51 instances of it logged from here (Reading) - unless the standard of navigation in the RAF has significantly deteriorated  :-)

Dave
Title: Re: Route info sharing
Post by: testmonkey on September 22, 2008, 06:19:13 PM
This is to say that a call sign could be inserted wrong by the crew and second don’t trust pilots!!

Haha, no sense of humour some people!

Got a TCX flight the other day that was showing the route as Birmingham to Ibiza but was on approach to Birmingham. Had to check the BHX arrivals to check whether it had turned back but just turned out to be a case of the pilots not updating the FMC and still broadcasting the outbound flight ID instead of the return one.

Until there's some sort of compulsory flight id broadcast legislation we're at the mercy of the crew.
Title: Re: Route info sharing
Post by: Allocator on September 22, 2008, 06:42:22 PM
Now here is an interesting one that I haven't seen before.

CWL93, an RAF HS125 Dominie with a route that resolves from the military callsign -
Cranwell to Kinloss.  I presume that this is an airways training flight and therefore the callsign is in the system.

I very much doubt that you can reliably tie a CWLxx callsign to a specific route.

If CWL93 was always Cranwell to Kinloss, I wouldn't have 51 instances of it logged from here (Reading) - unless the standard of navigation in the RAF has significantly deteriorated  :-)

Dave

Yes, quite possible, but I haven't seen this aircraft with a route before.  It just struck me as unusual.
Title: Re: Route info sharing
Post by: testmonkey on October 05, 2008, 06:55:32 PM
Here's the latest version of my Thomas Cook TCX routes file containing a number of new additions.

File now contains 992 routes.
Title: Re: Route info sharing
Post by: dumpty on October 06, 2008, 07:43:36 AM
Great work testmonkey.

As a total computer "thicko" where do i put the TCX routes and how do i do it?

Simon
Title: Re: Route info sharing
Post by: testmonkey on October 06, 2008, 06:56:08 PM
Hi Simon,

The file in its downloaded form can't be placed into RB without a bit of work so to import the routes you're going to improve your PC skills somewhat :)

First of all you'll need to download a free SQLite database editor to use. Seeing as you just need something simple for doing this I'd suggest that you downloaded a copy of SQLite Database Browser which is FREE and will do everything you need it to do whilst you learn SQL.
Once you've downloaded and placed a copy of it somewhere convenient you'll need to do the following.

1. Create 2 copies of the NavData.db3 database from the 'AirNav Radarbox 2009\Data' folder and place where you wish (keep one safe as a backup)
2. Start SQLite Database Browser and use the File - Open Database function to open one of your copies of NavData.db3
3. Now use File - Import - Import Table From CSV File. Browse to my file and import the data from it.
4. A new window will now open. In the New Table Name entry box enter Routes2. Tick the box for the option Extract field names from first line. Now click the Create button and wait until you receive confirmation that the process is complete. You should now have a new table in your database called Routes2 that contains my TCX route data.
5. Click on the Execute SQL tab and in the SQL String: box paste the following:
REPLACE INTO routes (FN,NO,ND,NV,CH)
SELECT FN,NO,ND,NV,CH
FROM routes2
and click the Execute Query button to perform the query above.
6. Now enter the following query in place of the last one and execute it to set your database back to its standard format:
DROP TABLE routes2
7. Now use File - Save Database to save the changes.
8. Copy your edited NavData database back into the \Data folder to overwrite the original.
9. Start up RB. If all starts okay you should now see the TCX flights getting populated with flight numbers (MyFlights only). If RB doesn't start copy the backup version of NavData back to restore the original.

VERY IMPORTANT - Make sure you make the 2 copies of NavData.db3 in step 1 - one to edit and the other as a backup just in case the process goes wrong (it shouldn't if you follow the instructions correctly!).

HTH
Title: Re: Route info sharing
Post by: CoastGuardJon on October 06, 2008, 07:04:10 PM
Hmmmmm, now you're scaring me!   I won't even touch spreadsheets at work, having wiped one out on a previous occasion.
Title: Re: Route info sharing
Post by: testmonkey on October 08, 2008, 11:02:37 PM
Hmmmmm, now you're scaring me!   I won't even touch spreadsheets at work, having wiped one out on a previous occasion.

Don't be scared! It's all quite simple if you follow the instructions :)