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.