Numpty's Progress

Tag: distance

  • finding the nearest thing to another thing

    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 (√(Δx2+Δy2)) 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.

    (more…)

  • my first real spatial query: finding nearby libraries

    Me and Catherine are quite partial to libraries. I’m going to use the address points database we made yesterday to find the libraries within 2km of a given address. It’s not a very useful query, but it shows the very basics of searching by distance.

    I’m going to use the address from yesterday, 789 Yonge St. The fields I’m interested in are:

    • address – this is the street number (789)
    • lf_name – the street name, in all-caps, with the customary abbreviations for rd/ave/blvd, etc (YONGE ST)
    • fcode_desc – the type of the address. Most places don’t have this set, but here it’s ‘Library’.
    • geometry – the description of the feature’s locus. This isn’t human readable, but can be viewed with the AsText() function.

    I’m also going to use a calculated field for the distance to make the query shorter. Since my map units are metres, calculating Distance(…)/1000 will return kilometres. So:

    select t2.name, t2.address, t2.lf_name,
distance( t1.geometry, t2.geometry ) / 1000 as
 Distance_km
from TCL3_ADDRESS_POINT as t1,
 TCL3_ADDRESS_POINT as t2
where t1.address = 789 and t1.lf_name =
 'YONGE ST' and t2.fcode_desc = 'Library' and
 distance_km < 2
 order by distance_km

    Note I’m using two instances of the same table; one for the source address (t1), and the other for the destinations (t2). The results I get are:

    NAME ADDRESS LF_NAME Distance_km
    Toronto Reference 789 YONGE ST 0.0
    Yorkville 22 YORKVILLE AVE 0.161394052244849
    130 ST GEORGE ST 1.2973836702297
    Spadina Road 10 SPADINA RD 1.52482151385834
    252 MC CAUL ST 1.58040842489387
    40 ST GEORGE ST 1.59417399071161
    Lillian H.Smith Library 239 COLLEGE ST 1.81606690760918
    265 GERRARD ST E 1.86262658418202
    Parliament 269 GERRARD ST E 1.87733631488281
    Deer Park 40 ST CLAIR AVE E 1.9224871094566

    There’s one at zero distance, because 789 Yonge St is a library, so the search finds itself. Try any other address, and you wouldn’t get the zero. I’m pretty sure the 14 decimal places is overkill.

    I see that some of the libraries don’t have names. I’m pretty sure that the St George St ones are the UofT Library, and the McCaul St one is OCAD‘s, but the others, I can’t tell.