Something I used to have to do a lot was to maintain a table of the nearest houses to prospective wind farm layouts. While the list of houses didn’t change very much, the layouts did. I came up with an only semi-unwieldy spreadsheet to do the calculations. The table was ultimately used in submissions to the Ontario Ministry of the Environment.

I’ve mapped out a trivially small example below; three houses, three wind turbines. In real life, there would be hundreds of each.

Sorry to include it as an image, but WordPress really doesn’t like pasted tables. If you really must, the text content is below the fold.

Though it’s small, it’s a bit of a horror, so you might want to download near.ods (opendocument spreadsheet). The mauve section contains the house coordinates, the blue the turbines. The green section is a simple Cartesian distance calculator (√(Δx^{2}+Δy^{2})) for those coordinates. The beige (or orange; or is it salmon?) bit is where things get difficult. Finding the closest distance is easy with the MIN function. Finding the column heading in which that minimum distance occurs is a bit more tricky, using INDIRECT, ADDRESS, COLUMN and MATCH to pull out the contents of the cell. This is one of the few spreadsheets I’ve written that will break if you rearrange it; hardcoded cell address mathematics will do that.

Getting the same result in SQL is little more difficult. I mean, I can make the table of distances easily enough:

select houses.ref as House, turbines.id as Turbine, distance(houses.geom, turbines.geom) as Distance from houses, turbines order by House, Turbine

but producing a nice compact table of houses, the nearest turbine, and the distance will need more pondering.

The table of coordinates:

Cartesian Distance Tabulator | Turbine ID |
1 |
2 |
3 |
||||

Easting |
322113 | 323253 | 323082 | |||||

Northing |
4841669 | 4842121 | 4840693 | |||||

House Ref |
Easting |
Northing |
Nearest Turbine / m |
Nearest Turbine ID |
||||

A |
322694 | 4841827 | 603 | 1 | 603 | 631 | 1199 | |

B |
323142 | 4841448 | 682 | 2 | 1052 | 682 | 757 | |

C |
322548 | 4841254 | 602 | 1 | 602 | 1117 | 774 |

flarp

i think this might be the “nearest neighbour” problem