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:
- How many turbines does each land owner get?
- 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;
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.