Finding nearest crossing streets

[Randall, Eric-2]

Hi all,

I'm currently finding nearest crossing-streets by procedurally walking upstream and downstream
of any given centerline segment, looking at endpoints/startpoints and vice versa until a different street name occurs.
Is there a better way to do this?  Thanks!

Eric

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Brian Modra-2]

2009/10/14 Randall, Eric <ERandall@...>:
> Hi all,
>
> I'm currently finding nearest crossing-streets by procedurally walking upstream and downstream
> of any given centerline segment, looking at endpoints/startpoints and vice versa until a different street name occurs.
> Is there a better way to do this?  Thanks!

you could build a table contaiing all the linkages, by first looking
for co-incident endpoints for all lines in the database. then use this
table to more quickly find the street crossings for specific streets.

>
> Eric
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



--
Brian Modra   Land line: [phone]
Mobile: [phone]
5 Jan Louw Str, Prince Albert, 6930
Postal: P.O. Box 2, Prince Albert 6930
South Africa
http://www.zwartberg.com/
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Paragon Corporation-2]

Eric,

I would think a self-join would do the trick here unless you are asking
something more complicated than I think you are.

So

SELECT s1.st_name As street_1,  s2.st_name As street_2
FROM streets As s1 INNER JOIN streets As s2 ON( ST_Intersects(s1.the_geom,
s2.the_geom) AND s1.gid <> s2.gid)
WHERE NOT (s1.st_name = s2.st_name)

If you only want to consider the crossing at start and end points, then add
in a st_startpoint(s1.the_geom) != ... Etc.

I would still leave in the intersects check to take advantage of spatial
index.

Leo

-----Original Message-----
From: postgis-users-bounces@...
[mailto:postgis-users-bounces@...] On Behalf Of Randall,
Eric
Sent: Wednesday, October 14, 2009 11:47 AM
To: postgis-users@...
Subject: [postgis-users] Finding nearest crossing streets

Hi all,

I'm currently finding nearest crossing-streets by procedurally walking
upstream and downstream of any given centerline segment, looking at
endpoints/startpoints and vice versa until a different street name occurs.
Is there a better way to do this?  Thanks!

Eric

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users