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.
No related posts.
Related posts brought to you by Yet Another Related Posts Plugin.
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.