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;
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.
I just downloaded the first 1000 
This is the clickwrap login usage licence at work. The threat of ” is particularly chilling.
Dammit, E-UAE is fiddly to set up. I finally got hard disk images working, by doing something like this:
