AirNav Systems Forum

AirNav RadarBox and RadarBox24.com => AirNav RadarBox and RadarBox24.com Discussion => Topic started by: jmhayes on May 16, 2009, 02:45:08 PM

Title: Type codes in the database
Post by: jmhayes on May 16, 2009, 02:45:08 PM
Why are the military type codes in the database still wrong?  It looks like ICAO codes are used for the civilian stuff, but "BC-A", "BKC5", "BKC3" etc. seem like they should have been fixed a long time ago.  I've seen a few references in the forum about this, but usually someone just says "You can change this with the Database Explorer" ...

Shouldn't this get changed in one place for everyone?

Airnav: if you need the real list, let me know; I'd be happy to give it to you.

Thanks!
Title: Re: Type codes in the database
Post by: jgrloit on May 16, 2009, 03:25:32 PM
Do you mean in the database table, or in the short-type on each aircraft entry?
It seems as though the Short to long type database table is a hang-over from a previous system.
The details including the short-type come from GAS.
Their data entry members could be using the wrong types!!!!
The tables within the database were asked about in another thread on the forum, and Allocator responded then.

Corrected from GATS to GAS - My Misunderstanding
Title: Re: Type codes in the database
Post by: jmhayes on May 16, 2009, 03:36:54 PM
Do you mean in the database table, or in the short-type on each aircraft entry?
I'm not sure of the distinction you're making.  Here's some SQL:

sqlite> SELECT * from Aircraft where AircraftTypeSmall LIKE 'BKC%';

This should produce zero rows :)
Title: Re: Type codes in the database
Post by: Brian on May 16, 2009, 03:57:55 PM
I agree with the first post on this topic! 
This should get fixed ASAP for everyone on the AirNav Database.
like the first post said.  This should have been fixed long time ago on the AirNav Database. So all AirNav RadarBox users can get the correct ICAO codes for those aircrafts.
If you go online where other users post the daily logs. You can see the errors he is talking about.

-Brian
RadarBox User Forum
http://radarspotters.eu/forum/
Title: Re: Type codes in the database
Post by: jgrloit on May 16, 2009, 04:19:31 PM
That table is populated from the GAS servers.
If the data is incorrect there then any New aircraft, or Mode-S ID's will contain incorrect data.    This is NOT under Airnav control.
The data that is shipped in the original database however IS under Airav control, and they can correct it there.   
If you load the new database, from a new download, then YES the data should have been corrected - I agree, but updates are automatic based on GAS!!!
Title: Re: Type codes in the database
Post by: jmhayes on May 16, 2009, 04:42:22 PM
That table is populated from the GATS servers.
I've been using GATS for many years, and I've never seen "BKC5" as a type code.  But taking an example AE04E0 in ANRB is "BKC5" and in GATS is (correctly) "K35R" ...
Quote
If the data is incorrect there then any New aircraft, or Mode-S ID's will contain incorrect data.
I believe that GATS is correct.
Quote
This is NOT under Airnav control.
Your emphatic NOT seems to be in conflict with Airnav's claim that this is a better alternative to SBS-1 because they take care of stuff like this "for you" ... :)
Quote
The data that is shipped in the original database however IS under Airav control, and they can correct it there. 
I don't know nothing 'bout the source of the data that comes with it, I only know that it's wrong.
Quote
If you load the new database, from a new download, then YES the data should have been corrected
Download a new database?  I thought the database was automatically maintained?  Isn't that the whole point of having a database supported by the vendor?
Quote
I agree, but updates are automatic based on GATS!!!
Are you saying that once a code gets in the database, it's there forever?  If the code gets transfered, tough luck?  If the aircraft gets upgraded to a new type code, tough luck?  If a plane gets sold and the Operator changes, tough luck?

I maintain my SBS-1 database, and I do it because that's what I bought.  The ANRB website "features" list says "Accurate Aircraft information from the Gatwick Aviation Society" ... as far as I can tell, this has never been accurate.
Title: Re: Type codes in the database
Post by: jgrloit on May 16, 2009, 04:54:39 PM
There is another thread on this forum where updates to existing database entries, due to changes in Operator or resulting from the reuse of Mode-S ID's is being discussed.
Airnav have put their comments in there!!!
The Point I was making was that IF GAS, or any other source used by Airnav is incorrect then the updates for NEW entries will propogate that data.
The Original Database IS supplied by Airnav, and should have been corrected prior to being shipped - the original source being unknown.
Another thread commented on NON-ICAO types being used by data submitters somewhere, but was not followed through for Corrective action!!!
Data vetting does I agree need to be added, probably at the RB servers, otherwise WE the users get supplied with incorrect information and GIGO occurs!!
It is hoped that in a version beyond 2.10 that updates to existing data will be implemented.     Airnav commented on this in one of the 2.10 new version streams.
Title: Re: Type codes in the database
Post by: jmhayes on May 17, 2009, 05:23:32 PM
The Original Database IS supplied by Airnav, and should have been corrected prior to being shipped - the original source being unknown.
So we agree.  Great!
Title: Re: Type codes in the database
Post by: AirNav Support on May 17, 2009, 05:29:24 PM
Send us the details on to what is wrong and we will correct it.
Title: Re: Type codes in the database
Post by: jmhayes on May 17, 2009, 05:31:44 PM
Send us the details on to what is wrong and we will correct it.
See the first post of this thread for details.
Title: Re: Type codes in the database
Post by: AirNav Support on May 17, 2009, 05:34:44 PM
Send us the real list you mentioned. Reality is folks the databases are massive and of course things will go through the net. If you think things need updating and you have the correct information send it us :)

(Before we get 100 messages about aircraft data, send those to GAS, we are trying to find out whats happening regarding there update page)
Title: Re: Type codes in the database
Post by: ACW367 on May 17, 2009, 11:15:30 PM
Support from previous posts in the forums I have found some.  My experience is US Mil KC135s populate with BKC and a number, C17s and US Mil 737s as BC-A, Canadian C17s as BCC7 and Fokker F27s as FF-2.

This has been raised in forums as follows
http://www.airnavsystems.com/forum/index.php?topic=2158.msg17946#msg17946

http://www.airnavsystems.com/forum/index.php?topic=2346.msg19737#msg19737
The examples below all show with the correct code on GAS and each time I have got one of these codes from your servers, I immediately checked GAS and found them to be showing the correct ICAO codes.

http://www.airnavsystems.com/forum/index.php?topic=2190.msg18238#msg18238
43C04E   ZZ175   Boeing C-17A Globemaster III   BC-A

A sample from radarbox logs on the mode S yahoo group, if you do a search for those codes on that site you will find many more.
AE0840 58-0058 BKC3 USA - Air Force
2009/03/18 10:25:00
* AE025F 61-0280 BKC3 USA - Air Force
2009/03/18 10:01:58
AE04E2 59-1446 BKC3 2009/03/16 11:21:49
AE0156 57-1456 BKC3 USA - Air Force
2009/03/12 14:06:28
C2B3EB CFC4050 177703 BCC7 Canada - Air Force
2009/03/12 00:48:40
AE04DB 165832 BC-A 2009/03/16 09:39:59
AE146A RCH7172 07-7172 BC-A USA - Air Force
2009/03/15 13:58:00
300131 I-MLQT  FF-2  Untitled (Miniliner)
2009/03/16 21:32:25
498422 CEF05B OK-AAB FF-2 ABA Air
2009/03/04 04:45:15




Title: Re: Type codes in the database
Post by: cessna24v on May 18, 2009, 06:12:38 PM
Gents,

First and foremost, if you have any questions or updates to the GAS (not GATS) database, please send them to modesinfo at the GAS address. A brief explanation has been posted on another part of this forum.

I am not the person to comment on the workings of the RB data model. However, I am the one to answer questions about GAS data and I see there are a lot of misunderstandings.

My answer to the specific problem of military ICAO codes is straightforward. These do not come from the GAS database and never have.

If you ever want to check, just do the obvious as ACW367 has, and others previously including Dave Reid, and look them up on the GAS website.

If any errors originate from GAS, please let us know via the above address and, given time, we will sort them.

In terms of data in general, let me say this...

There is no aircraft database I know that is 100% complete and accurate despite what any advertising says. If anyone knows of one, then please stand up and be counted, and please explain just how this is achieved bearing in mind the volume of daily worldwide changes/additions and the 'secrecy' of some registration authorities.

Add to that minor, but growing, problems like the military tactical allocations (German CL60s and LH F-15s, for example) and you begin to appreciate some of the gating factors.

The GAS data is extensive but obviously suffers from the above problems. It is actually very current with some registers refreshed 100% on a weekly basis. Even the US one with 500,000 plus entries is completely replaced monthly. In between individual updates are made as and when.

Add to all this, the clowns and idiots who wish to avoid work and constantly try to infiltrate, and you begin to appreciate the task we have.

Now back to RB specifics.

GAS provide an agreed PULL feed to Airnav. It is not a PUSH feed and, therefore we only provide data they request.

Airnav, for their part, try to minimise any impact on our server performance and, in the last couple of years this has worked well. This is important for us as we currently average around 12 transactions per second from direct desktop program and online accesses from other sources.

I am also of the understanding that the Airnav data has always been constructed from multiple sources, not just GAS.

I hope that answers a few questions. If I have missed any, I am sure you will tell me.

However, be advised I will not answer any more EMails telling GAS to get their act together with Airnav.

Gary
Title: Re: Type codes in the database
Post by: jgrloit on May 18, 2009, 06:34:11 PM
Q. AIrnav support - if GAS only supplies the Civ Airframe data - where does the RB Server system get the Military information from?
It looks as though the short-types on that system are incorrect!!!
Title: Re: Type codes in the database
Post by: Brian on May 18, 2009, 06:39:48 PM
as the above post is asking.

Maybe AirNav Systems support can add it to the FAQ(AirNav RadarBox FAQ) topic.
Just make a list of them.  And add a direct link to the lookup page for that source.

So other users can find it easy without finding this topic again in the future.
Title: Re: Type codes in the database
Post by: AirNav Support on May 18, 2009, 09:05:03 PM
Thank you Gary for posting. We are very grateful for your teams work and anyone telling you otherwise should be ashamed of themselves.

We echo your comments about the accuracy and the level of changes required and can only hope that anyone using the databases understands the level of complexity around this to make it as close to accurate as possible.

jgrloit,

We originally used static database and from that we use airliners.net and GAS for the updates.
Title: Re: Type codes in the database
Post by: Brian on May 18, 2009, 09:15:54 PM
AirNav Support,

So the "Military information" came from the "static database" ?

Sorry you aren't making it very clear where those Military ICAO type codes came.
Title: Re: Type codes in the database
Post by: CoastGuardJon on May 18, 2009, 09:21:19 PM
Hi Gary, like 99.9% of us in here, many thanks for all the info., effort and hard work that is put into the DB.    You can please most of the people all of the time, a few most of the time, and then there's those who will never be satisfied - I'm just grateful for the RB, you, AN and this Forum for the superb contribution to my hobby.    THANKS all.
Title: Re: Type codes in the database
Post by: AirNav Support on May 18, 2009, 09:35:12 PM
We have added a FAQ:

http://www.airnavsystems.com/forum/index.php?topic=2688.new#new

We will add to that if we get further queries regarding how it works.

Brian,

If you mean the actype table in the database. Then yes it was made from different sources originally and is static (it doesn't change by GAS updates). This is the first time the actype table has been mentioned in terms of updating.

If we get the list jmhayes has mentioned, we can add them.
Title: Re: Type codes in the database
Post by: DaveReid on May 18, 2009, 09:39:40 PM
We have added a FAQ:

http://www.airnavsystems.com/forum/index.php?topic=2688.new#new

We will add to that if we get further queries regarding how it works.

Brian,

If you mean the actype table in the database. Then yes it was made from different sources originally and is static (it doesn't change by GAS updates). This is the first time the actype table has been mentioned in terms of updating.

If we get the list jmhayes has mentioned, we can add them.

If you simply want a list of valid ICAO type codes, you can download it yourself from www.icao.int/anb/ais/8643/index.cfm
Title: Re: Type codes in the database
Post by: AirNav Support on May 18, 2009, 10:02:15 PM
Thanks Dave, we will try and squeeze that into 2.10
Title: Re: Type codes in the database
Post by: cessna24v on May 18, 2009, 10:20:28 PM
Quote
Q. AIrnav support - if GAS only supplies the Civ Airframe data - where does the RB Server system get the Military information from?
It looks as though the short-types on that system are incorrect!!!

Just to prevent further confusion, GAS supplies both Civil and Military codes.

I was referring to the strange ones like BC-A etc. which are not and never have been in the GAS DB.
Gary
Title: Re: Type codes in the database
Post by: jgrloit on May 18, 2009, 10:31:15 PM
Thanks for the clarification Gary.

I was starting to suspect MY reading of the messages was giving me a misleading conclusion!!!!

I am, at present only interested in the Mode-S to Registration links - the types and Operators can follow later!!!
Title: Re: Type codes in the database
Post by: Brian on May 18, 2009, 10:47:55 PM
AirNav Support,
Thanks for making the new topic on "how the database works" Nice to read it on one page.  Just trying to learn everything about it. and these ICAO codes stuff!.

That pages needs more info on how the FAA N-numbers works with Mode-S ID's.
You only talked about UK stuff.  I'm sure you are still working on that page to make it better.


Edit:
You can also link to some other stuff on that new page. I'm sure you can find some links to add that talks about Mode-S and N-numbers on the wiki sites.  Just make it look pretty :)

Another question.  Where did these codes(BC-A, etc. see first post) that is showing up in the radarbox database come from.  Do you have a link to learn more about it.
Title: Re: Type codes in the database
Post by: DaveReid on May 19, 2009, 07:00:32 AM
Thanks for making the new topic on "how the database works" Nice to read it on one page.  Just trying to learn everything about it. and these ICAO codes stuff!.

That pages needs more info on how the FAA N-numbers works with Mode-S ID's.
You only talked about UK stuff.  I'm sure you are still working on that page to make it better.

Yes, I'd agree, that's a useful addition.

However one of the examples quoted is a bit misleading:

Mode-S IDs can also change there link to registrations. Example G-EUUT could go out of service and then 401240 is assigned to G-ELLB. This will mean the aircraft details in your local database will be wrong.  

The UK never reassigns the Mode S code from an aircraft which has gone out of service.  Other countries such as the US reassign codes from one aircraft to another, but that's only because they reassign registrations, and the FAA matches registrations permanently to Mode S codes.

In fact there are very few examples worldwide of Mode S codes being reused on a different aircraft with a different registration - the only ones I can think of, off the top of my head, would be the Czech Republic which changed its Mode S allocation system from a permanent reg/code match (like the USA) to an ad-hoc one.  This resulted in, for example, code 49D0A3 being reassigned from C172 OK-TEC to B735 OK-XGC.

HTH
Dave
Title: Re: Type codes in the database
Post by: AirNav Support on May 19, 2009, 07:49:41 AM
Thanks Dave, we will clarify that paragraph so it talks about change in operators.

Brian,

The ICAO codes are in the actype table in the database. As Dave mentioned the site below can give you a quick view of what they mean.
http://www.icao.int/anb/ais/8643/index.cfm
Title: Re: Type codes in the database
Post by: malc41 on May 19, 2009, 08:02:34 AM
Dave

When you say never reassign s-codes in the UK, could this be taken that we have a large block and not enough aircraft in this country to use them all up for some time?
Title: Re: Type codes in the database
Post by: DaveReid on May 19, 2009, 08:43:49 AM
When you say never reassign s-codes in the UK, could this be taken that we have a large block and not enough aircraft in this country to use them all up for some time?

You could say that   :-)

The UK is allocated just over a quarter of a million codes.  Of those, around 23,000 have been allocated to G- civil aircraft, an unknown number to the military and a handful to the Isle of Man, Bermuda and Caymans.

I think that still leaves quite a few !!
Title: Re: Type codes in the database
Post by: malc41 on May 19, 2009, 09:01:57 AM
Dave

When you put it that way, they won't run out in my lifetime, no at least while we are in a credit crunch!

So I take it the M- registrations are included with us?
Title: Re: Type codes in the database
Post by: jgrloit on May 19, 2009, 10:55:07 AM
I think using UK in the previous message is technically incorrect as the range of radio prefixes and country codes that use the 'UK' allocation are those from the UK and British Commonwealth.
The G-, M-, VP- and other callsign prefixes also stem from the UK and British Commonwealth allocations from the ITU  decades ago!!!
Title: Re: Type codes in the database
Post by: DaveReid on May 19, 2009, 11:22:12 AM
I think using UK in the previous message is technically incorrect as the range of radio prefixes and country codes that use the 'UK' allocation are those from the UK and British Commonwealth.
The G-, M-, VP- and other callsign prefixes also stem from the UK and British Commonwealth allocations from the ITU  decades ago!!!

The range of codes listed against "United Kingdom" in ICAO Annex 10, Volume III, Part I, Table 9-1 is indeed shared with Bermuda and the Caymans (both British Overseas Territories) and the Isle of Man (a Crown Dependency), but not of course with the Commonwealth as a whole since the independent countries that make up the latter each have their own allocation.
Title: Re: Type codes in the database
Post by: DaveReid on May 19, 2009, 05:03:54 PM
Thanks Dave, we will clarify that paragraph so it talks about change in operators.

I note that you have changed it so that it now reads:

"Mode-S IDs can also change there link to registrations. Example G-EUUT could go out of service and then 401240 is assigned to G-ELLB (In rare cases, usually the Mode-S link to registration stays the same).  Or an aircraft is leased/bought by another company. This will mean the aircraft details (whether registration or operator) in your local database will be wrong."

But it's still a poor example regarding registrations.  You would be better to use the relatively common scenario of a UK G-registered aircraft changing to another G- registration.  In such cases, the Mode S code doesn't change, but the registration does - in other words that's a valid example of the link between a code and a registration changing.
Title: Re: Type codes in the database
Post by: Brian on May 19, 2009, 07:48:37 PM
AirNav Support,
I can't find these ("BC-A", "BKC5", "BKC3") ICAO codes on that site.  Can someone provide a direct link please. So I know where AirNav got them as I learn the database some more.

Quote
Brian,
The ICAO codes are in the actype table in the database. As Dave mentioned the site below can give you a quick view of what they mean.
http://www.icao.int/anb/ais/8643/index.cfm


For more information on the two sources.  Maybe you can explain this on that new FAQ page "Untitled" vs "Tie-up extracted from Country Sequence"
One of the AirNav Support person has done it before on a regular topic.  It should be added to that new FAQ page.
Title: Re: Type codes in the database
Post by: ACW367 on May 19, 2009, 08:57:22 PM
Brian
The BC-A, BKC codes etc are not ICAO codes and are not coming from either GAS or Airliners.net.  What we are still trying to understand is where the codes came from originally and whether Airnav can find them in their dataset and do another pull from GAS to get the correct codes ICAO codes displayed against the aircraft on that site or change them themselves manually even if that is to the '...' code.

Another regularly appearing spurious code that I have just remembered is BE- for US Navy E6A Mercury aircraft.

On the network I just had AE0414 populate as 164387 with the non-ICAO (or IATA) code of BE-, I went immediately to GAS and of course they have the correct ICAO code E6. I will do the manual change as I seem to have done for every E6, KC-135, C-40 etc recieved so far on my box.

This also occurs for some civil aircraft types notably Boeing 777-300ER.  When a new one appears Airnav populates with B773 which is not the ICAO code for the 300ER. GAS has the correct code of B77W.

This happened today for me with brand new Air Austral aircraft F-ONOU. It populated on my box as a B773 but GAS is showing it correctly as a B77W. This aircraft only flew for the first time a few days ago so the pull must have come from GAS and not from a legacy airnav dataset from an earlier release, so how did the code get supplanted/corrupted???

Confused of High Wycombe
Regards
ACW
Title: Re: Type codes in the database
Post by: Brian on May 19, 2009, 09:19:26 PM
I asked that on Page 2. and AirNav Support said to go look on that site for it.
but that didn't work out to well....

Look at my last post on page 2.

"Another question.  Where did these codes(BC-A, etc. see first post) that is showing up in the radarbox database come from.  Do you have a link to learn more about it."
Title: Re: Type codes in the database
Post by: AirNav Support on May 19, 2009, 10:07:35 PM
Thats not exactly what we said, we were answering your question related to you finding out more about icao codes, not specifically that's where we got these codes from.
Title: Re: Type codes in the database
Post by: ACW367 on May 19, 2009, 10:12:53 PM
Airnav support

I agree with Brian, and tried to explain this in February and reinforce it with my post 11.  As you say the FAQ you created does not answer this issue that we are explaining, where many aircraft including newly registered ones have full details and correct ICAO codes on GAS but we get a corrupted ICAO code or the three dots supplanted when taken onto your servers onto our Navdatas.

I.E when a brand new aircraft is correctly reported in GAS like F-ONOU showing as B77W there.  That info must be taken forward to your server, however when we pull the info from your server as the aircraft appears on our box we get a corrupted code of B773.  Or the many examples where GAS have the full ICAO code but we get the dreaded Three dots.

All we are looking for is for you to say that you will investigate whether there is a corruption/supplanting of a few specific aircraft codes on your servers from a third unknown source after you have pulled the correct ICAO ones from GAS. Whether you can think of likely sources and if not whether you will investigate this as a bug for future software releases beyond V2.1
Title: Re: Type codes in the database
Post by: Brian on May 19, 2009, 10:32:31 PM
AirNav Support,
Simply, answer this question, where did you get that ("BC-A", "BKC5", "BKC3") from ?

So us radarbox user can contact that souce and get it corrected.

You are making it very hard to find the source and get it corrected for other radarbox users.
Title: Re: Type codes in the database
Post by: DaveReid on May 19, 2009, 10:43:11 PM
AirNav Support,
Simply, answer this question, where did you get that ("BC-A", "BKC5", "BKC3") from ?

So us radarbox user can contact that souce and get it corrected.

You are making it very hard to find the source and get it corrected for other radarbox users.

I agree - what's so hard about validating the type codes in the master AirNav database against the valid values from the ICAO website ?
Title: Re: Type codes in the database
Post by: AirNav Support on May 19, 2009, 10:49:09 PM
Ok Firstly there has been some confusion in this thread. We orginally assumed that you meant the BC-A values were in the actype table in the database not that they are being passed through for aircraft details when a new aircraft is found.

As explained in the FAQ, data comes from either GAS, Airliners.net or data in the database which was compiled from various other static sources at the time of the database build. (No we don't know the exact source of these static database per aircraft)

ACW367, We are investigating the 777 issue you have mentioned, it could be that the data came from airliners.net.

As mentioned in the FAQ, We are looking into the database system and making it sure its kept as accurate as possible without the whole process of updating being complicated.
Title: Re: Type codes in the database
Post by: AirNav Support on May 19, 2009, 10:50:39 PM
The BC-A etc.. is a strange one as there not even in the main database as a actype. There not in the RB local database either.

Meaning there probably likely old stale data from the past.

This leads on to our headache, if we have one database which feeds from airliners, gas, something else in the future. Which one do we take as the most accurate data how does main database know which one to choose (especially when some don't have timestamps to show the last updated time)
Title: Re: Type codes in the database
Post by: Brian on May 19, 2009, 10:53:37 PM
Okay what static database sources did you use for anything ?.
So us radarbox users can contact them about it.

Quote
As explained in the FAQ, data comes from either GAS, Airliners.net or data in the database which was compiled from various other static sources at the time of the database build. (No we don't know the exact source of these static database per aircraft)
Title: Re: Type codes in the database
Post by: jgrloit on May 19, 2009, 10:57:14 PM
I think that the validation of aircraft type should be extended to the vetting and validation of data from ANY source that is going to effect the aircraft type - both the Type code and the type string.
At present the correct data could be received from GAS via AirNav Servers, and the Aircraft type sting - long description, the string can then be overwritten by data attached to the Photograph, received.
The latter should be prevented - IF it does NOT match, to some degree that in the table for the ICAO type.
It becomes a question - who has the more valid data?   I would suggest GAS!!!
Title: Re: Type codes in the database
Post by: AirNav Support on May 19, 2009, 11:05:15 PM
Brian, It doesn't matter what they were as they only used once to populate the database. They are not part of any future updates to the database.
Title: Re: Type codes in the database
Post by: Brian on May 19, 2009, 11:09:14 PM
I just want to know for information.  So I can also check out that website/source.
I just love to read stuff.  So where can I get that information.  Just trying to learn more about it.

All I need is a link.
Title: Re: Type codes in the database
Post by: ACW367 on May 19, 2009, 11:15:03 PM
Airnav thank you for your assurance that for you will investigate the issue, I am sorry that this ran as you are very busy trying to launch V2.1, but we were having problems communicating that this solely related to the 4-digit ICAO code and not any wider interpretation of the long aircraft type/owner fields.  
Title: Re: Type codes in the database
Post by: jmhayes on May 20, 2009, 05:38:51 AM
If we get the list jmhayes has mentioned, we can add them.
I gave you a handful of them (and others have mentioned even more), but it's clear that you don't know what's all in your database.  If you want me to track them down, can I bill you for my standard hourly rate?  I'd be more than happy to fix your database: PM me.
Title: Re: Type codes in the database
Post by: jmhayes on May 20, 2009, 05:41:44 AM
Which one do we take as the most accurate data how does main database know which one to choose (especially when some don't have timestamps to show the last updated time)
There's a straightforward way to use database technology to track this, but you've got to want to do it.
Title: Re: Type codes in the database
Post by: DaveReid on May 20, 2009, 06:31:19 AM
If you want me to track them down, can I bill you for my standard hourly rate?  I'd be more than happy to fix your database: PM me.

Get in line  :-)

Here's a freebie:

Select * from Aircraft where Aircraft.ICAOTypeCode is Null or not exists (Select ICAOTypeCode from ICAOTypeCodes where Aircraft.ICAOTypeCode = ICAOTypeCodes.ICAOTypeCode)

It's hardly rocket science!
Title: Re: Type codes in the database
Post by: AirNav Support on May 20, 2009, 07:48:49 AM
jmhayes,

We orginally thought you just meant the actype table was out of date. Yes the SQL is mentioned isn't hard but thats not the issue here.

The issue is incorrect or no ICAO data ix being passed through into the database. Its better we investigate the feed and stop it getting in rather than dealing with it afterwards.

"There's a straightforward way to use database technology to track this, but you've got to want to do it."

If you know of way then please share it with us.
 
Title: Re: Type codes in the database
Post by: DaveReid on May 20, 2009, 09:37:34 AM
The issue is incorrect or no ICAO data ix being passed through into the database. Its better we investigate the feed and stop it getting in rather than dealing with it afterwards.

This might be stating the obvious, but if you were to run that query against each of your feeds, before their data gets imported to your database, then you could identify which feed is at fault (as well as using it to trap bad data which has already made it into the database).

I emailed Andre yesterday with a suggestion for another approach to sourcing data, I'm awaiting his response.
Title: Re: Type codes in the database
Post by: jmhayes on May 20, 2009, 03:25:59 PM
"There's a straightforward way to use database technology to track this, but you've got to want to do it."

If you know of way then please share it with us.
How about you clean out what's there, and then watch for new codes and confirm them?  You've been given the authoritative source, a few good examples of what's wrong, and even some sample queries.  All you're lacking at this point is the will to produce and maintain a correct and useful database for your customers.

You're almost there!
Title: Re: Type codes in the database
Post by: jmhayes on May 20, 2009, 03:27:19 PM
Here's a freebie:
Oh great, now you've let out the Super Seekrits of the Database Ultra Masters!
Title: Re: Type codes in the database
Post by: Brian on May 20, 2009, 07:23:38 PM
AirNav Support,
That wasn't my question.  Please go back 1 page and re-read the question.  Stop adding words to my one question when you read it again.  Please I just need 1 link from you.  Please answer it today.... So I can look into other stuff.

Quote
AirNav Support
Brian, It doesn't matter what they were as they only used once to populate the database. They are not part of any future updates to the database.
Title: Re: Type codes in the database
Post by: Brian on May 20, 2009, 07:26:31 PM
I agree! Some stuff in the database needs to be "Flushed Out" and "Refresh" with the stuff everyone talked about on this topic.
 
Quote
There's a straightforward way to use database technology to track this, but you've got to want to do it."

Quote
If you know of way then please share it with us.
Quote
How about you clean out what's there, and then watch for new codes and confirm them?  You've been given the authoritative source, a few good examples of what's wrong, and even some sample queries.  All you're lacking at this point is the will to produce and maintain a correct and useful database for your customers.

You're almost there!
Title: Re: Type codes in the database
Post by: AirNav Support on May 20, 2009, 08:41:06 PM
Brian,

We didn't add words. You asked for the link because you wanted to get them changed which we simply assume you wanted so it would change in our database. As they don't update this way it doesn't affect the database online.

Other than some sources which we purchased data (we will not release this information) from we used.
http://registry.faa.gov/aircraftinquiry/
http://www.caa.co.uk/default.aspx?catid=60&pagetype=90&pageid=122
http://www.jetphotos.net/census/

jmhayes,

We agree that would be nice start but the issue arrises when it picks a aircraft in South America which GAS shows the operator as Varig while Airliner.net (or another source) has it has TAM.

I have chosen South America deliberately here  as GAS is great for Europe and good for US but the rest of the world Airliners.net can be better. While we can add some clever code to use airliners for certain countries. There will be times where the data is going to be incorrect as the one chosen is not the latest.

That and the issue of having the local database updated and the icoa feed errors is what we are looking into.
Title: Re: Type codes in the database
Post by: Brian on May 20, 2009, 08:59:05 PM
AirNav Support,
I didn't really needed the links.
I just needed to know the source name.  Such as jetphotos.net , FAA Registry Aircraftinquiry.  Since I know how to use a search engine like Google. but It's always a good idea to post the link if you know it. So everyone spends less time searching.  So they can get a head start on the information they are looking for.
All I wanted to know.  What other stuff those websites might provide.  such as photos, forums, chatroom, local groups, etc etc.  The question didn't really have anything to do with future data for AirNav Database or the like.

Just wanted to look at more photos if those sources had any.

The best way to answer the question was to answer the user question.
Then give a 2nd answer if you think it would provide more information whatever you were thinking in your head. :)

Thanks again for the answer today. 
I go back waiting for 2.10 version.

~Happy Person Now~  :)
-Brian


Quote
Brian,

We didn't add words. You asked for the link because you wanted to get them changed which we simply assume you wanted so it would change in our database. As they don't update this way it doesn't affect the database online.

Other than some sources which we purchased data (we will not release this information) from we used.
http://registry.faa.gov/aircraftinquiry/
http://www.caa.co.uk/default.aspx?catid=60&pagetype=90&pageid=122
http://www.jetphotos.net/census/
Title: Re: Type codes in the database
Post by: jmhayes on May 20, 2009, 10:40:08 PM
We agree that would be nice start but the issue arrises when it picks a aircraft in South America which GAS shows the operator as Varig while Airliner.net (or another source) has it has TAM.
I agree, it's true: maintaining a database like this is a full-time job, to be taken seriously.  Making a commitment to database quality would be a big leap forward.
Title: Re: Type codes in the database
Post by: Brian on May 21, 2009, 05:03:40 AM
Thank you again for posting these links.  I'm sure others will find it helpful.
A very interesting looking website at the jetphotos.net/census/ page.
Even have some nice statistics looking at the results from the search.

Thank You - AirNav Support :)