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:

FROM   points
WHERE  id NOT IN (SELECT points.id
                  FROM   points,
                  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.

1 comment

Leave a comment

Your email address will not be published. Required fields are marked *