I let both my instrument currency and my club annual ride expire in the last little while, so I had to do that. I contacted my favourite CFI, Jim Hood, and he agreed that we could get both requirements satisfied with one IPC.
(more…)
The main loop query of my waypoint generator app is kind of hairy. And trying to do an “explain” on a typical query shows why it’s so slow.
explain SELECT a.id, c.pdb_id, internalid, a.type, name,
address, state, country, latitude, longitude,
declination, main_frequency, elevation,
b.category, chart_map, tpa, ispublic
FROM waypoint a, type_categories b,
id_mapping c
WHERE a.type = b.type AND
a.id = c.id AND
country in (’US’, ‘CA’) AND
(a.type in (’AIRPORT’, ‘VOR’, ‘NDB’) or
(category = 3 and (chart_map & 7) != 0)) AND
deletedon is null;
QUERY PLAN
——————————————————————————————————————————————————————–
Hash Join (cost=4442.82..19955.46 rows=34752 width=111)
Hash Cond: ((a.id)::text = (c.id)::text)
-> Hash Join (cost=4.83..12938.32 rows=34752 width=107)
Hash Cond: ((a.”type”)::text = (b.”type”)::text)
Join Filter: (((a.”type”)::text = ANY ((’{AIRPORT,VOR,NDB}’::character varying[])::text[])) OR ((b.category = 3) AND (((a.chart_map)::integer & 7) <> 0)))
-> Seq Scan on waypoint a (cost=0.00..10759.48 rows=72467 width=103)
Filter: ((country = ANY (’{US,CA}’::bpchar[])) AND (deletedon IS NULL))
-> Hash (cost=4.37..4.37 rows=37 width=15)
-> Seq Scan on type_categories b (cost=0.00..4.37 rows=37 width=15)
-> Hash (cost=2091.77..2091.77 rows=127777 width=12)
-> Seq Scan on id_mapping c (cost=0.00..2091.77 rows=127777 width=12)
(11 rows)
Adding indexes on country and type doesn’t help. There is still that nasty looking “Seq Scan on waypoint a” line. And also, another “Seq Scan on id_mapping c”, which I don’t understand at all because the joining column, c.id, is a primary key, so shouldn’t there be an index involved?
I’ve got a few ideas on how to use the spatial capability of PostGIS to improve that query, so I’m going to have to run a few tests. The first few ideas I’ve had aren’t showing major improvements in “explain”. It looks like the whole “type in … or ((chart_map & NN) != 0)” is going to force a sequential scan on waypoint no matter what I do. Hmmm.
Ok, I just added some indexes, and now the PostGIS version runs in 45 seconds. Phew! Man, I love “-d:DProf”.
In order to support a new product development I mentioned in an earlier blog post, I re-did my existing waypoint database as a PostGIS geographical database. I also added some foreign keys and some other cleanup that I’ve been meaning to do for a while. But obviously, I don’t want to support two databases, so today I’ve been converting one of my existing waypoint generator perl scripts to use the new PostGIS database instead of the MySQL database it was on before, but without any actual GIS functionality. And Houston? We have a problem. Doing a full US + Canada data load on the MySQL version takes about a minute and a half. Doing the same load on the PostGIS version takes twenty five minutes. Something tells me that I need to make some adjustments here.
I probably shouldn’t give too many details, but I’ve been in talks with a certain freeware developer over developing a flight planning application for a web connected hand-held device. (Anybody who knows anything about me can probably guess the developer and the device.)
My part would be a server app that would respond to requests for data from the device and send new data or updates. Nothing too different than what I’ve been doing before, but one of the things we’ve been talking about is managing “areas”. His concept was that if the user entered an id that wasn’t on the device already, my server would send the device a whole “area”, and the device would keep track of what areas it had in memory already, when they were last updated, and would occasionally request updates of the areas it knew. He thought that each area could be a whole country. The first thing that struck me about that is that if the point you asked for was in the US, you could be asking for thousands of waypoints (70,584 in the current database). That could take a long, long time on an Edge network. Then we discussed maybe breaking it down by state or province in the US and Canada.
But the thing is, I used to be a GIS (Geographic Information Systems) programmer. I know there are better ways. At first I started looking around for the HHCode algorithm since I worked with Herman Varma and the Oracle guys implementing the original Oracle “Spatial Data Option”, until that scumbag Jim Rawlings screwed me out of three months pay. But I can’t find the source code anywhere.
So my next idea was a modified quad tree. Basically, when populating the database, I made a “rectangle” that incorporates the whole world and start adding points. When I hit a threshold, I subdivide that “rectangle” into 4 equal sub-rectangles, and move the points into whichever rectangle they belong to. This means that where points are sparse, the rectangles are large, and where they are dense, the rectangles are small. That way I’ve got some consistency in the size of the file to be sent to the device, and I’m not wasting people’s time sending the 19 waypoints in Wake Island, say, as an individual file.
I’ve been experimenting today with PostGIS, which is an extension to Postgresql which adds some very efficient geographic query tools. The program I wrote to take the data from my old MySQL database and put it into the PostGIS database while building these quad cells runs pretty fast. Surprisingly fast, even. PostGIS is pretty capable. Too bad the manual for it sucks rocks.
One thing that I keep forgetting is how much faster computers are now than when I was doing GIS for a living. I keep expecting things to take hours when they end up taking minutes, because the last time I did this sort of thing I was using a 40MHz SPARC and now I’m using a dual core 1.86GHz Intel Core2 Duo, and I’ve got more RAM at my disposal now than I had hard drive space back then.
Anyway, mostly I’m writing this because I’m really enjoying working with GIS-type stuff again. I wish I could do it full time again.
A first look at my lovely new toy, “Snowbird”, named after the best precision flying team IN THE WHOLE WORLD.
I finally got to lift my head up from my computer and go out and do something for myself. I went flying. No goal except to get re-acquainted with flying and have fun. I had our club’s Dakota booked from 1pm, and somebody else had it booked in the morning. I was hoping that would mean I wouldn’t have to pre-heat it, but the other guy evidently didn’t fly. The weather was saying low clouds in the morning, but higher in the afternoon, and the reports were coming in showing it better than the forecast all morning. Sure enough by the time I got out there the ATIS was reporting a few clouds at around 3200 feet and a broken layer way up high, so it was prefect visual flight conditions.
The airport was incredibly busy, as about 4 jets arrived and departed while I waited behind a Commander to take off. And then after take off, it took the tower controller until I was at 2300 feet before he could turn me over to the departure controller. Within 2 seconds of the GPS saying I crossed outside of the outer ring of the class C airspace, the controller turned me loose, so obviously the rush was going to continue for him.
I decided to try a couple of steep turns, and they weren’t good. I had trouble getting over a 30 degree bank, and I kept gaining altitude. And then I tried one stall, which I hate doing in this plane because it seems like the nose never drops. That was enough practice stuff, so I flew over and did a touch and go at Ledgedale (7G0) and another at Batavia/Genesee County (KGVQ). Both were passable but not great. I flew the first bit of the VOR-A approach into Canadiagua (D38) just to get some experience flying a VOR radial again.
My feeling is that I’m still rusty, and I’m going to need at least one more flight before I’m ready to do my club annual ride. And then I’m going to need to do 6 approaches with a CFI-I or a IPC in order to get instrument current again.
The Dakota is a nice plane, but I miss my Lance. I felt perfectly at home flying that plane, but not so much with this one.
In anticipation of today’s announcement of the new MacBook Pro, I ordered the computer skin a few days ago from iToppers.com. Today the guy producing it sent me this image of what it’s going to look like.
I can hardly wait. I hope there is an airshow nearby where I can get the team to sign it.
Ok, the sky is actually clearing up a bit, and there are lots of blue patches. But on the other hand, it’s cold out, and I’m tired, and I’ve decided today is not the day to go flying for the first time since November.
I was just reading the story of British Airways Flight 9 on Wikipedia. I hope that some day when I get into difficulty as a pilot, I’m as calm as Captain Moody:
Ladies and Gentlemen, this is your Captain speaking. We have a small problem. All four engines have stopped. We are doing our damnedest to get them going again. I trust you are not in too much distress.
That’s just beat out Captain Al Haynes “You want to be particular and make it a runway, huh?” as my favourite aviation quote.
Ok, I missed last year. I really want to go again, and I really want to fly. But we don’t have a Lance any more and somebody already booked the Dakota, so I’m going to have to be *really* careful about weight if I’m planning to go in an Archer. The other problem is that last time I went we camped, but after a day walking around I’m in incredible pain and I was damn near useless around the camp, and I felt bad about letting Mark do all the work. Maybe what I should do is pack a tent, a sleeping bag, and a bike to get to the Chinese buffet place across the road? Or maybe I should fly in, but park in GAP (General Aviation Parking) and find a place in one of the dorms or something?
I’m also thinking that sacrilege of sacrilege, I might not go for the whole week. Maybe come in Saturday before things get started, stay for Jay Hoeneck’s famous party on Wednesday, and leave on Thursday. Much as I hate to admit it, the air shows get pretty repetitive after 4 or 5 days, and unless you’re specifically in the market for something, you can see it all in that time as well. Plus, when I stay the full week I’m in real danger of getting talked into buying a kit. After all, I built a canoe from scratch, how much harder could an airplane be? Oh, that much harder? Ok, never mind then.
I was reading a forum thread about a scuba accident that killed a friend of my brother’s, and which my brother was also involved in finding the body after the RCMP tried and failed.
One of the thread contributors posted this thing for divers, but it made me think of Mike and Dave’s recent death in their float plane, and my own thoughts about the possibility of dying in a plane crash.
If I should die while diving.
If I should die while diving please do not hesitate to discuss the incident and assess every element with a view to furthering your understanding of how to enhance diver safety.
If I should die while diving get the facts. They won’t be readily available and will definitely not be correct as reported by the media. But get the facts as best you can.
If I should die while diving understand, as I already do, that it will most likely involve fault on my part to some degree or another so do not hesitate to point that out.
If I should die while diving some of the fault will probably belong to my buddy and that needs to be honestly assessed as well though I must admit this is one area where I hope that compassion will be in the mix.
If I should die while diving there might be those who try to squelch discussion out of a misplaced notion of respect for the deceased, family and friends. They can say nice things about me at my funeral… but in the scuba community I want the incident discussed.
If I should die while diving at least I didn’t die in bed.
I could do a search and replace of “diving” with “flying”, and it’s pretty close to something I’d like to say to my fellow pilots and my nervous but understanding wife. Well, except for the bit about buddies - we don’t use a buddy system in flying even when we should.
I just got to see “One Six Right” in HD, and as far as I’m concerned that one movie justifies the purchase of the HD tv and the TiVoHD. (Although I’m sort of hoping they’ll re-show “Planet Earth” in HD one of these days.)
I got an email this morning to my waypoint generator email address asking where the person could get “waypoints for eastern Canada”. I asked if there was some specific problem with the waypoints that my generator provided? He responded that he couldn’t seem to get them to work with this list of three or four programs that he’d tried. I had never heard of them, so I looked up the ones I could find, and none of them said anything about supporting any data format that I provide in my waypoint generator (although one was listed as supporting GPX on the page that lists programs known to support GPX). I got the name of the data file that he’d produced from him and looked it up in the logs, and it appears that he’d generated a CoPilot file, a GPX file, and an AeroCalc file.
So it appears he was just trying random combinations of file formats and programs to see if he could magically find a combination that went together. I asked if that was what he was doing, and suggested he find a program that does what he wants and find what sort of data files it takes, he said that he was a pilot and a photographer, not a database expert.
I tried to explain that was like trying to open an Excel file in Photoshop, but I don’t think it’s getting through.
I guess I’ll never understand how people’s minds work. And I’m not entirely unhappy about that.
Somebody clicked the “buy it now” and bought the Lance. The speed at which it sold this time says that maybe we could have gotten a bit higher price, but this gives us time to deliver it before the annual runs out.
We’re not sure if the buyer is going to come to pick it up, or if he’ll want it delivered. I volunteered if it’s the latter. The buyer is in South Dakota, which means taking the longest trip I’ve ever made in a private plane. But even if I don’t end up delivering it, I enjoy flight planning, so here’s what I did.
(more…)
