Author: Paul Tomblin

Blow off work, go paddling, good times

We knew that today was going to be an amazing day – sunny, temps in the mid 70s, so Rob and I decided to blow off work and go paddling. And we planned it in advance, so we invited the rest of the Huggers Ski Club to go with us, and 5 or 6 others accepted the invitation.

We went somewhere new for me, Black Creek. The put-in is about half way between the Rochester Flying Club tie downs and Vicki’s work at RIT. And the creek runs very close under the approach path for runway 4 at Greater Rochester International Airport. Runway 4 was in use, so every few minutes our conversation was interrupted by a low flying aircraft. Not that I’m complaining, mind.

The river was pretty high, and it had flooded a lot of land, making route finding fun and interesting. Right from the start it we ended up taking wrong turns several times. Even more exciting was near the beginning there was a bridge where the water was so high I had to lie back as flat as I could on the back coaming of the kayak and paddle like that. One or two people just shipped their paddles and pushed themselves along the top of the arch. At another point there was a bit of shallow stream with a strong current coming over it but a tree lying across it making it impossible to paddle, but there was a much longer deep channel. I paddled ahead a few hundred metres and ended up on the other side of the same tree, telling the stragglers where they had to go.

In a lot of ways, it reminded me of some of the times Mom and I paddled through the Minnesing Swamp in spring time.

I couldn’t tell you how far we went, because of all the twists and turns and retracing our steps. But it was a lot of fun, and I think I’ve got a hell of a sun burn to show for it.

I LOLed

Conversation at lunch today between R (been using emacs since 1981) and me (been using vi since 1987). It started off with him saying something about how he knew our latitude and longitude because he had to put it in his emacs config.

Me: When I’m looking for an editor, the ability to tell me sunrise and sunset times isn’t high on my list.
R: Emacs isn’t an editor, it’s an environment.
Me: Emacs isn’t an editor, it’s a lifestyle.
R: Yup. There’s straight, gay, and emacs.

At that point, I was literally laughing out loud (and I mean “literally” literally, not in the internet sense of “I smiled a bit”). I was lucky I didn’t have any food or drink in my mouth at the time.

Optimize this?

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.

Back to the drawing board

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.