In my first post I asked where am i? The gps in my phone said I was standing at 43.73066°N, 79.26482°W. In real life, I was standing at the junction of Kenmark Blvd and Chevron Cres.
With the Open Toronto centreline data, I can check the location of road intersections:
select distinct ( astext ( transform ( intersection ( r1.geometry, r2.geometry ), 4326 ) ) ) from centreline as r1, centreline as r2 where r1.lf_name = 'KENMARK BLVD' and r2.lf_name = 'CHEVRON CRES'
which returns:
NULL POINT(-79.262423 43.730019) POINT(-79.264815 43.730591)
Hmm; three answers. NULL I can’t answer; I’ll put it down as an exhortation to Be Here Now. The second is given a clue by one of the street names: Chevron Crescent – first thing I learned when I had my newspaper round is that a crescent’s going to end you up back on the same road you started from. The last one, though, agrees to 5(ish) decimal places – well within the accuracy of my simple GPS.
Update: This query gets really, really slow on long streets. Both Chevron and Kenmark only have two road segments. Kennedy and Steeles East each have over 90.
2 replies on “but where am i, really?”
I don’t know if it’s a feature of the updated sets, but one can find the road segment containing the intersection with:
select * from centreline
where lf_name like ‘eglinton ave%’ and fnode in
(select fnode from centreline where lf_name like ‘kennedy rd%’)
More work is required, but it doesn’t take forever.
… and this finds it:
select astext(intersection(t1.geom, t2.geom))
from centreline as t1, centreline as t2
where t1.lf_name like ‘victoria park%’
and t2.lf_name like ‘sheppard%’
and t1.fnode=t2.fnode;
It’s really quick, but that’s because I created an index on fnode:
create index idx_fnode on centreline(fnode);
It also works on the older data release.