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:
|Toronto Reference||789||YONGE ST||0.0|
|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.