Route info sharing

[tarbat]

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.

[testmonkey]

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

[John Racars]

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.

[tarbat]

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.

[testmonkey]

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.

[testmonkey]

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

[DaveReid]

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).

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.

[testmonkey]

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

[John Racars]

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?

[testmonkey]

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?

[John Racars]

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.

[testmonkey]

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.

[John Racars]

Thank you Testmonkey! I will try out all this tonight when I am at home. I will keep you informed.

[nortonbeak]

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?

[testmonkey]

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.