Where can I find a list of ICAO aircraft id hex codes for all B748 made?
April 27, 2014 6:32 PM Subscribe
I'm building a ADS-B monitoring system to send myself a text message when a 747-800 (B748) flies nearby. The system I have (RTL dongle w/ rtl_adsb or dump1090) works well at receiving but I'd like to avoid having to query any outside services, such as Flightaware's API, to get the aircraft type. I thought that since I'm already getting each aircraft's ICAO24 id number I should just be able to check that against a list of all the 747-800s in existence. It seems like most of these ICAO24 lists are hoarded either by plane spotters or commercial aviation companies. Where can I find such a list for free without doing a ton of legwork?
Best answer: I think you want a "basestation.sqb" database file, which is what the planeplotter/planespotter people use. You're right, they're very proprietary about them and it's ridiculous, but the hobby uses 2012 technology while the culture is stuck in the late 80s.
There are lots of sources of basestation.sqb, for example see http://forum.virtualradarserver.co.uk/viewtopic.php?f=1&t=236. They're all different, so take your pick.
The file is in sqlite format, so once you have it open it with sqlite: "sqlite3 basestation.sqb". Then use SQL (see https://gist.github.com/wiseman/5707788 for schema info). From a basestation.sqb I have laying around:
posted by jjwiseman at 11:56 PM on April 27, 2014
There are lots of sources of basestation.sqb, for example see http://forum.virtualradarserver.co.uk/viewtopic.php?f=1&t=236. They're all different, so take your pick.
The file is in sqlite format, so once you have it open it with sqlite: "sqlite3 basestation.sqb". Then use SQL (see https://gist.github.com/wiseman/5707788 for schema info). From a basestation.sqb I have laying around:
sqlite> select count(*) from Aircraft where ICAOTypeCode like '%B748%' limit 5; 51 sqlite> select * from Aircraft where ICAOTypeCode like '%B748%' limit 5; 9834|2012-09-12 09:30:53.2087718|2012-09-12 09:30:53.2087718|4D0106|Luxembourg|LX-VCF|B748|35811|CLX|| |||||||||||||||||||0||||||0|0|0|0|0|||||||||| 15231|2012-09-04 09:49:22.6257718|2012-09-04 09:49:22.6257718|71BE10|South Korea|HL7610|B748|37133|KAL|| |||||||||||||||||||0||||||0|0|0|0|0|||||||||| 37910|2007-12-11 11:10:55.3637718|2007-12-11 11:10:55.3637718|71BE09|South Korea|HL7609|B748|37132|KAL|| |||||||||||||||||||0||||||0|0|0|0|0|||||||||| 60247|2011-11-23 11:17:35|2011-11-23 11:17:35|780232|China|B-LJB|B748|39239|CPA|||||||||||||||||||||0||||||0|0|0|0|0||||||0|0|0|0|0 73106|2010-07-23 20:08:08.000|2010-07-23 20:08:08.000|A643F9|United States|N50217|B748|Test||||||||||||||||||||||0||||||0|0|0|0|0||||||0|0|0|0|0
posted by jjwiseman at 11:56 PM on April 27, 2014
Best answer: jjwiseman has the right of it. I dragged through the v7 and v6 installers of SBS earlier this year with all sorts of msi-diggers and cab-extractors. it was such a pain that eventually I simply installed the software, copied the basestation.sqb file, and then uninstalled the software. ha.
the nice thing of the file already being in a sqlite format is that it works straight away with some of the free windows software like rtl1090. but again jjwiseman gave a great example for ganking text out of the file.
the simple web ui on dump1090 is surprisingly functional and awesome, but I also like to use adsbscope as a frontend (also works great as a front end to rtl1090 and some other free raw source generators.)
my unknown-airframes list on adsbscope has gotten pretty big, but that may be because I have been pointing it at dump1090 for a while now. possibly pointing it back at rtl1090 with its new-ish basestation.sqb might reconcile a few of those. (or not - I think even the sbs v7 isn't exactly recent). the known-file that it comes with is ~ adsb_all\pc_software\adsbscope\27\extra\icao24plus.txt not extensive but decent.
you can also try excavating the search engine here.
personally I found that rtl_adsb is an ugly chore, but dump1090 is easy (and, again, the decent web-ui, with easy to hack on JS etc.) and a lot of fun. I don't even have a Rpi but I prefer Malcolm Robb's fork - more up to date git repo than the original antirez source and it builds+works fine on any generic lunix arm or x86, that I've tried.
aside: it's surprising how just about any antenna will work well. I have been too lazy to make proper ones, instead simply using what I had - mini-discones, and j-pole/slimjims, both meant for 2m/70cm - these actually do amazingly great.
posted by dorian at 5:17 AM on April 28, 2014
the nice thing of the file already being in a sqlite format is that it works straight away with some of the free windows software like rtl1090. but again jjwiseman gave a great example for ganking text out of the file.
the simple web ui on dump1090 is surprisingly functional and awesome, but I also like to use adsbscope as a frontend (also works great as a front end to rtl1090 and some other free raw source generators.)
my unknown-airframes list on adsbscope has gotten pretty big, but that may be because I have been pointing it at dump1090 for a while now. possibly pointing it back at rtl1090 with its new-ish basestation.sqb might reconcile a few of those. (or not - I think even the sbs v7 isn't exactly recent). the known-file that it comes with is ~ adsb_all\pc_software\adsbscope\27\extra\icao24plus.txt not extensive but decent.
you can also try excavating the search engine here.
personally I found that rtl_adsb is an ugly chore, but dump1090 is easy (and, again, the decent web-ui, with easy to hack on JS etc.) and a lot of fun. I don't even have a Rpi but I prefer Malcolm Robb's fork - more up to date git repo than the original antirez source and it builds+works fine on any generic lunix arm or x86, that I've tried.
aside: it's surprising how just about any antenna will work well. I have been too lazy to make proper ones, instead simply using what I had - mini-discones, and j-pole/slimjims, both meant for 2m/70cm - these actually do amazingly great.
posted by dorian at 5:17 AM on April 28, 2014
agh, past the edit window. just for anyone using the web ui on dump1090 - find your (approximate) lat/lon (in decimal), this makes the map webpage more pleasant:
edit public_html/config.js :
CONST_CENTERLAT = xx.xxxxxx;
CONST_CENTERLON = -yy.yyyyyy;
CONST_ZOOMLVL = 9;
SiteShow = true;
SiteLat = xx.xxxxxx;
SiteLon = -yy.yyyyyy;
SiteCirclesDistances = new Array(25,50,100);
and try running the command as:
./dump1090 --interactive --net --aggressive --lat xx.xxxxxx --lon -yy.yyyyyy --enable-agc --modeac
I'm still too lazy to set up a service, I simpy run that from the build directory, in a screen session.
the mode A and mode C will not show on the web ui nor to raw clients like adsbscope. but they will show in the interactive terminal. so it's more of a curiosity - mode c will basically get you altitude, and mode a is total random crap (as it was designed to be, ha) - you are not going to get icao from either of them as far as I remember.
the aggressive and tuner agc settings... well, see what works better or doesn't for each of your specific installation.
posted by dorian at 5:36 AM on April 28, 2014
edit public_html/config.js :
CONST_CENTERLAT = xx.xxxxxx;
CONST_CENTERLON = -yy.yyyyyy;
CONST_ZOOMLVL = 9;
SiteShow = true;
SiteLat = xx.xxxxxx;
SiteLon = -yy.yyyyyy;
SiteCirclesDistances = new Array(25,50,100);
and try running the command as:
./dump1090 --interactive --net --aggressive --lat xx.xxxxxx --lon -yy.yyyyyy --enable-agc --modeac
I'm still too lazy to set up a service, I simpy run that from the build directory, in a screen session.
the mode A and mode C will not show on the web ui nor to raw clients like adsbscope. but they will show in the interactive terminal. so it's more of a curiosity - mode c will basically get you altitude, and mode a is total random crap (as it was designed to be, ha) - you are not going to get icao from either of them as far as I remember.
the aggressive and tuner agc settings... well, see what works better or doesn't for each of your specific installation.
posted by dorian at 5:36 AM on April 28, 2014
Response by poster: Thank you all so much for your help and suggestions. I found a suitable basestation.sqb file here: http://pp-sqb.mantma.co.uk/download.php and it appears to be what I need. This will also let me more easily add notifications when other, more rare aircraft show up in the neighborhood.
posted by ChrisHartley at 7:50 AM on April 28, 2014
posted by ChrisHartley at 7:50 AM on April 28, 2014
I'm glad dorian mentioned Malcolm Robb's fork of dump1090 because I forgot to, and it's really a significant upgrade (hopefully antirez will merge those changes at some point, but he seemed to have abandoned the project for several months). And the overall simplicity and portability of dump1090 is hard to beat.
And yeah, I've picked up aircraft from over 100 miles away using the standard little antenna that comes with it for digital TV.
I've considered trying to create an accurate, up-to-date replacement for basestation.sqb with a license that lets you do anything (as opposed to something like the airframes.org terms, which says "It is not allowed to collect and transfer these data anywhere else. Copyright rules apply"), but it's a lot of work. I tend to think that the free databases probably infringe on someone's copyright already.
posted by jjwiseman at 11:05 AM on April 28, 2014
And yeah, I've picked up aircraft from over 100 miles away using the standard little antenna that comes with it for digital TV.
I've considered trying to create an accurate, up-to-date replacement for basestation.sqb with a license that lets you do anything (as opposed to something like the airframes.org terms, which says "It is not allowed to collect and transfer these data anywhere else. Copyright rules apply"), but it's a lot of work. I tend to think that the free databases probably infringe on someone's copyright already.
posted by jjwiseman at 11:05 AM on April 28, 2014
ha ha yeah the standard little antenna is such a wacky improbable thing. I also wish I could find a use for those IR remotes, aside from the fact they usually have CR2025 batteries which are nice to have extra of.
it sounds like a couple people have been working on branches for equipment ID - terribl added a local sqlite db and is not making any external queries. bdavenport is doing sort of the opposite, pulling data (with explicit permission) from mantma (so, obviously this 2nd one is not what you wanted ChrisHartley, but it's still rather interesting...
added hilarity for readers who may not know already - equipment can have multiple icao24, to switch among for different carrier/route/etc. or sometimes if the pilot just screws it up (as they not-infrequently seem to do with transpoder/squawk as well) and uses the wrong preset ID, or gets it even wronger by inputting a completely invalid one. I believe it's even possible for the ID of dead airframes to get re-used - but, as we're all discussing, the state of maintaining databases is so splintered and often out of date, so: extra extra hilarity!
and yeah, no doubt on what's legitimate or not. mantma/planeplotter definitely collects data from users (although I imagine there's some sort of EULA. and doubleplus: you get to pay them for that privilege) - dump1090 even has flags for output to planeplotter! and things like flightradar or flightaware or airframes probably don't have the cleanest data provenance either.
posted by dorian at 4:40 AM on April 29, 2014
it sounds like a couple people have been working on branches for equipment ID - terribl added a local sqlite db and is not making any external queries. bdavenport is doing sort of the opposite, pulling data (with explicit permission) from mantma (so, obviously this 2nd one is not what you wanted ChrisHartley, but it's still rather interesting...
added hilarity for readers who may not know already - equipment can have multiple icao24, to switch among for different carrier/route/etc. or sometimes if the pilot just screws it up (as they not-infrequently seem to do with transpoder/squawk as well) and uses the wrong preset ID, or gets it even wronger by inputting a completely invalid one. I believe it's even possible for the ID of dead airframes to get re-used - but, as we're all discussing, the state of maintaining databases is so splintered and often out of date, so: extra extra hilarity!
and yeah, no doubt on what's legitimate or not. mantma/planeplotter definitely collects data from users (although I imagine there's some sort of EULA. and doubleplus: you get to pay them for that privilege) - dump1090 even has flags for output to planeplotter! and things like flightradar or flightaware or airframes probably don't have the cleanest data provenance either.
posted by dorian at 4:40 AM on April 29, 2014
This thread is closed to new comments.
posted by Sunburnt at 9:18 PM on April 27, 2014