but where am i, really?

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

which returns:

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.

Leave a Reply

Your email address will not be published. Required fields are marked *