[postgis-users] Finding nearest crossing streets

Paragon Corporation lr at pcorp.us
Thu Oct 15 10:29:48 PDT 2009


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 at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Randall,
Eric
Sent: Wednesday, October 14, 2009 11:47 AM
To: postgis-users at postgis.refractions.net
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 at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users






More information about the postgis-users mailing list