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.

• count_points_in_polygons – shape files
• count_points_in_polygons – Spatialite

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:

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.