Routes situation

[tarbat]

I get the error message attached (hopefully) below?

Cut & pasted your query - so shouldn;t be any typos...

Cut & Paste from this forum seems to remove end-of-line breaks.  Try this:

SELECT DISTINCT
       FlightRoute.flight AS FN,
       substr(FlightRoute.route,1,4) AS NO,
       substr(FlightRoute.route,length(FlightRoute.route)-3,4) AS ND,
       "" AS NV,
       substr(date('now'),1,4) || substr(date('now'),6,2) || substr(date('now'),9,2) || "090000" AS CH
FROM FlightRoute
ORDER BY
      FN

[Pinza]

Many thanks for that Tarbat - that re-posted query worked fine.

I now have 60214 routes in a format ready to export to XML and re-import into my AirNav routes database.

Pushing the limits of my computer skills, but will give it a go later!

Need to eat first and then make sure everything possible is backed up before I proceed...

Will report back how it goes.

Thanks again
Chris

[Pinza]

For someone who is very interested in the queries you post but isnt aware of how these queries work, where do you put the query and what software do you need to get it to work.

I use SQLite Maestro.
1. Open the Yahoo groups routes database.
2. Run the query, and export to XML.
3. Open the navdata.db3 database.
4. Empty the routes table.
4. Import the XML file to the routes table.

I do this on a regular basis, and currently have 60,000+ routes.  Of course I can't vouch for the accuracy of the Yahoo groups database, but I've never spotted any obvious errors.

Okay, have followed steps 1 & 2 successfully. I now have a file called routes.xml in a location that I know where to retrieve it from.

Steps 3, 4 and 5 are confusing me:

When you say " Open the navdata.db3 database." do you mean in SQlite Maestro or in ANRB itself.

If the latter, do I need to be in Database Explorer? - I see no option for 'emptying' the routes. And only import option seems to be for SQB files.

Sorry to be pain, but would like to get this to work - feel I'm very close!

Chris

[tarbat]

When you say " Open the navdata.db3 database." do you mean in SQlite Maestro or in ANRB itself.

In SQLite Maestro.  Take a backup before you mess around with the database though.

[Pinza]

When you say " Open the navdata.db3 database." do you mean in SQlite Maestro or in ANRB itself.

In SQLite Maestro.  Take a backup before you mess around with the database though.

Almost there!

Now have navdata.db3 database open in SQLite Maestro and navigated to the routes table.

How do I empty the data from this table (showing 396358 dodgy routes) before I import my XML file of 60214+ updated routes?

This is scary stuff for me - everything backed up though!!!!

Chris

[tarbat]

Right click on the table and select "Empty Table (routes)".  Then open the empty table and use the Import wizard.

[Pinza]

Right click on the table and select "Empty Table (routes)".  Then open the empty table and use the Import wizard.

Hmm, got as far as emptying my routes table but struggled with the import wizard in SQLite Maestro.

Import button was greyed out and don't know anything about XML file structures so gave up after several hours!

However, I started again from scratch - this time using SQLite Manager (a free add-on for Firefox) and exported and imported using CSV structure which I am more comfortable with.

Just fired up ANRB and all working fine.

Many, many thanks Tarbat for your 'query' to convert the data from the Yahoo groups, and subsequent assistance via this this thread

Happy boy now!!!

[tarbat]

Many, many thanks Tarbat for your 'query' to convert the data from the Yahoo groups, and subsequent assistance via this this thread

Happy boy now!!!

Glad you got it working.

[nortonbeak]

I have just got my Routes database up to date. I downloaded the latest file from PP-Logs. Then I used SQLite (which is free!) to run Tarbats little SQ routine.

My thanks to Tarbat for the routine :-)

If anyone wants to try it, SQLite can be downloaded at

http://www.sqlite.org/download.html