diary of a geonumpty

I have a GIS problem:

There are two layers in the map above:

  • the green areas represent land lots, with owners Arnold, Beal, Carr, …
  • the red points represent wind turbines, or really anything that needs to be identified inside a lot.

While I used to be quite decent with plain ol’ SQL, the spatial extensions (such as are in SpatiaLite) are nipping my heid. What I need to query:

  1. How many turbines does each land owner get?
  2. Are there any turbines that aren’t in a lot?

Here’s the data, and I’ll show you a solution once I work it out.

Update: dang, the SpatiaLite tutorial is useful. Here’s how to get the count for each owner:

select Properties.owner, count(*) from Points, Properties where within(Points.geometry, Properties.geometry) group by Properties.owner;

which gives:

Owner count(*)
Arnold 1
Beal 2
Dockrill 2

Nifty, what!? Now to work out the other one, which I suspect will use a ‘not within()’.

Update #2: Nope, the SpatiaLite mailing list came through:

SELECT id,
       unitid
FROM   points
WHERE  id NOT IN (SELECT points.id
                  FROM   points,
                         properties
                  WHERE  Within(points.geometry,properties.geometry));

I have to admit that I still don’t really think of nested SELECTs – my SQL formative years were spent on a DB that didn’t support them.

manifold: ur doin it wrong

This is supposed to be a nice site map, but Manifold GIS has this impenetrable and counter-intuitive user interface. No wonder it has such an active user community; they’ve all paid for it and are desperately trying to get some value out of it.

my neighbourhood, according to CanVec

my neighbourhood, according to CanVec and QGIS

Canada has recently released most of its geodata for free – Go Canada! I was particularly interested in CanVec, the large vector topographical set. I downloaded the set for Toronto and environs, and slapped it into QGIS. With nearly all the layers on, my neighbourhood looks like the above.

I didn’t find any labels, or much in the way of documentation for this huge data set. It would be a shame if good metadata weren’t available, for it adds real utility to the map data.

mame is lame

I suddenly got a retrogaming jones on, and had a strong need to play Robotron. So I downloaded MAME and some ROMs, but no dice — every archive was missing files. Seems that to get the few games I need, I have to download a 16GB torrent of ever game that MAME supports.

My arcade game sensors withered about 18 years ago, so nothing past about 1988 registers with me. You could probably fit every pre-’88 ROM onto a couple of floppies. And it’s not like I’m not allowed to play the ancient Williams games; I have the Arcade Classics CD somewhere which has the games in licensed (but MAME-incompatible) form.

What’s with the Council of Canadians, eh?

Every couple of months, the Council of Canadians sends me a large and visually unappealing (1986 called; they want their typewriter font back) mailing, ranting about how those pesky Americans keep stealing our water.

Close reading of the mailing (which is hard, given the woeful typography) shows that the initiatives being railed at are either:

  1. run by Canadian companies, or
  2. are part of legislation voted for by Canadians.

Like most environmental things, Canada has an appalling record of looking after its abundant water. I think we think that the rest of the world thinks better of us than they do, or maybe even frankly cares about Canada.

I’m a bit worried by the CoC’s use of the n-word — nationalist — since it has unpleasant connotations, like the BNP and SNLA. Also, at least half of the mailing could be summed up as The Maude Barlow Fanzine, with only slightly lower production quality than the average zine.

And anyway, pesky Americans haven’t been stealing our water. Catherine hasn’t been sneaking any more out of the house than usual …